I have a database tables with millions of measurements. New data is coming in every day. For analysis, I want to replicate the data to my laptop (local postgres db). No need for automatic replication. It's OK to start a script for this.

I feel like the standard replication solutions are inadequate and overengineered for my use because

  • I need asynchron replication,
  • some solutions are inefficient because they replicate row-wise (yet data on the server is bulk-inserted),
  • and they need too much configuration.

I would like to use COPY TO STDOUT | COPY FROM STDIN but here I have to select only the new data. How can I do that?

The table has this form:

   Column   |           Type
 devicename | character varying(30)
 id         | integer
 timestamp  | timestamp with time zone
 value      | numeric
 variable   | text

PK would be (devicename, id). Note that id alone is not unique because the data is coming from multiple devices.

How can I select only new data for COPY? Any other approaches for this replication requirements?

  • 1
    copy from (select * from the_table where "timestamp" > "last sync time") to ... then replace the "last sync time" with the actual timestamp value when you copied the data – a_horse_with_no_name Jul 10 '18 at 11:50
  • Thanks horse! But this does not work because I cannot trust the timestamp column. It does not hold the time when the row was inserted into the table. It is the timestamp of the measurement and because there are many devices (devicename) which push their data from now and then, I cannot use the timestamp. – schoettl Jul 10 '18 at 12:50
  • Then there is no way you can identify the new rows – a_horse_with_no_name Jul 10 '18 at 12:51
  • I think it's best to redefine the table structure and add a unique SERIAL id and use the approach horse suggested (... where sid > last_synchronized_sid). – schoettl Jul 10 '18 at 12:54
  • For a given devicename, are the id values always increasing? If so, maintaining a record of the last id copied over for each devicename could be an option. It would still require a new table, but shouldn't require changing the structure of the existing tables. – RDFozz Jul 10 '18 at 13:01

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 (serial or 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),
  id          integer,
  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 (
  select *
  from data 
  where (id,devicename) not in (select id,devicename from synchronized)
), done as (
  insert into synchronized (id, devicename)
  select id, devicename 
  from todo
select *
from todo;

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
select *
from remote_fdw_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.

| improve this answer | |
  • Thank you. I decided for the first solution. I think it is most efficient with respect to CPU, memory/storage, and network load. At my local computer I use select max(serial_id) ... and then copy (select * from source where serial_id > the_max) to stdout and pipe that to copy target from stdin. – schoettl Jul 10 '18 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.