PostgreSQL: Non-continious replication of new data with COPY
0
votes
1
answer
280
views
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?
Asked by schoettl
(101 rep)
Jul 10, 2018, 11:26 AM
Last activity: May 11, 2025, 04:04 AM
Last activity: May 11, 2025, 04:04 AM