I see the following possible solutions:
Change the source table
Change the source table to include a column that you can use to (uniquely) identify the data you have already synchronized. A column based on a sequence (
identity in Postgres 10) or column defined as
timestamp that is populated through a trigger.
If that column is indexed, this is probably the most efficient solution.
Keep track of copied data
Create a table in the source database that contains the already synchronized rows.
create table synchronized
devicename character varying(30),
primary key (id, devicename)
The following statement would return all rows that are not yet synchronized and "marks" them as transferred in a single query. It should be possible to use that as the source for the
copy statement (but I haven't tested it).
with todo as (
where (id,devicename) not in (select id,devicename from synchronized)
), done as (
insert into synchronized (id, devicename)
select id, devicename
Create a foreign table and use that for synchronization.
Using a foreign data wrapper you create a foreign table in your local Postgres server that connects to the remote server. Then whenever you want to synchronize the data, you can do that with a single statement:
insert into local_table
on conflict do update
set "timestamp" = excluded.timestamp,
value = excluded.value,
variable = excluded.variable;
That is probably the least efficient solution as it will always transfer the complete table to your local computer and overwrite/insert as needed. But this requires no changes on the source database and other than the I/O needed for the SELECT, has not impact on the source.
The connection that is used by the FDW does not need to be valid all the time. It only needs to work, when you run the synchronization. As long as you don't access the foreign table, it doesn't matter if your computer can connect to the source or not.
Copy everything into a staging table, then use "upsert"
A variation of the solution with the foreign table is to simple export the whole source table, import that into a staging table with the same structure, then use the above
INSERT ON CONFLICT approach to insert/update the real table.
That requires one more step for synchronization but might be easier to handle depending on your environment.