postgresql - Moving a table from a database to another - Only insert missing rows -

i have 2 databases alike, 1 called datastore , other called datarestore.

datarestore copy of datastore created backup image. problem accidentally deleted little data datastore.

both databases located on different aws instances , typically connect them using pgadmin iii or python create scripts handle data.

i want rows accidentally deleted datastore in datarestore datastore. have idea of how can achieved. both databases contain close 1.000.000.000 rows , on version 9.6.

i have seen backup/import/restore options within pgadmin iii, don't know how work , if support needs? thought creating python script, querying database has become pretty slow, seems not option either.

----------------------------------------------------- | id (serial - auto incrementing int) | - primary key | did (varchar) | | sensorid (int) | | timestamp (bigint) | | data (json) |  | db_timestamp (bigint) | ----------------------------------------------------- 

if preserved primary keys between databases create foreign tables pointing datarestore datastore , check keys missing (using example select pk old_table except select pk new_table) , fetch missing rows using same foreign table created. should limit first check missing pk index scans (+ network transfer) , index scan fetch missing data. if missing small part of shouldn't take long.

if require more detailed example i'll update answer.

edit:

example of foreign table/server usage

those commands need exuecuted on datarestore (or datastore if choose push data instead of pulling it).

if don't have foreign data wrapper "installed" yet:

create extension postgres_fdw; 

this create virtual server on datarestore host. metadata pointing @ foreign server:

create server foreign_datastore foreign data wrapper postgres_fdw options (host 'foreign_hostname', dbname 'foreign_database_name',          port '5432_or_whatever_you_have_on_datastore_host'); 

this tell datarestore host user should connect when using fdw on server foreign_datastore. used your_local_role_name logged in on datarestore:

create user mapping your_local_role_name server foreign_datastore  options (user 'foreign_username', password 'foreign_password'); 

you need create schema on datarestore. new foreign tables created.

create schema schema_where_foreign_tables_will_be_created; 

this log in remote host , create foreign tables on datarestore, pointing tables @ datastore. tables done way. no data copied, structure of tables.

import foreign schema foreign_datastore_schema_name_goes_here   server foreign_datastore schema_where_foreign_tables_will_be_created; 

this return list of id missing in datarestore database table

select id foreign_datastore_schema_name_goes_here.table_a except select id datarestore_schema.table_a 

you can either store them in temp table (create table table_a_missing_pk [query above here] or use them right away:

insert datarestore_schema.table_a (id, did, sensorid, timestamp, data, db_timestamp) select id, did, sensorid, timestamp, data, db_timestamp   foreign_datastore_schema_name_goes_here.table_a  id = any((    select array_agg(id)      (        select id foreign_datastore_schema_name_goes_here.table_a        except        select id datarestore_schema.table_a      ) sub   )::int[]) 

from tests, should push-down (meaning send remote host) that:

remote sql: select id, did, sensorid, timestamp, data, db_timestamp foreign_datastore_schema_name_goes_here.table_a ((id = ($1::integer[]))) 

you can make sure running explain verbose on full query see plan execute. should see remote sql in there.

in case not work expected, can instead create temp table mentioned earlier , make sure temp table on datastore host.

alternative approach create foreign server on datastore pointing datarestore , push data old database new 1 (you can insert foreign tables). way won't have worry list of id not being pushed down datastore , instead fetching data , filtering them afterwards (with extremely slow).