1

Is there some way to tell pg_restore to only append rows that don't already exist?

Background: I'm trying to restore an archive-db from a main-db. Both are using PostgreSQL 12.

The catch is I don't want to keep lots of the table entries on my main-db. I generally only care about entries in the last few hours, but need everything to be pretty fast so I want to drop old data on the main-db.

There is a small use case to keep old data around though, so for that I'd like to run pg_dump on the main-db to dump my data to a file before I drop old data. Then on the archive-db I want to use pg_restore to add the new stuff without loosing any old rows that existed on the main-db in previous iterations of this workflow.

I don't see an append option in the pg_restore docs but it seems like something that should exist, so other suggestions for how to approach this problem are welcome. Thanks!

Nick
  • 3,172
  • 3
  • 37
  • 49
  • 1
    There is no append procedure for ```pg_restore```. You will have to do something else, probably a custom built solution. – Adrian Klaver Aug 08 '20 at 01:17
  • 1
    You might also want to take a look at [partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE) – Adrian Klaver Aug 08 '20 at 18:34
  • 1
    Another option is to create your archive as a table with the same structure and column names in the same database then create procedure to insert into the archive and delete from main. Finally create a VIEW that merges the main and archive for those times where the history is needed. If you run the archive regularly (daily or shorter intervals) performance should be satisfactory and you do not have the worry of the lack of append option on pg_restore, not recovery operations for data not yet archived. – Belayer Aug 08 '20 at 21:52
  • Thanks for your advise @AdrianKlaver – I'm trying monthly partitions to start. Seems like this will help preserve performance while keeping all my data together and not requiring custom functions to move it around. – Nick Aug 10 '20 at 22:45

0 Answers0