How to use pg_dump/pg_restore to update database?

Question

Say I have db0 on the local machine and db1 on a remote server. I just want to keep db1 up-to-date with db0.

Say I added few (thousands) rows to my_table in db0 and now I want to insert it to my_table in db1.

All the recipes and docs about pg_dump/pg_restore say about full restoration of the table, however, I don't need (and don't want) to drop and to restore my_table from scratch.

Is there any clear and simple way to make a file, scp it to the server and just pg_restore from it?

Just two commands: for pg_dump and for pg_restore. Because I'm looking through docs and walkthroughs for about two hours getting new errors and becoming confused more and more.

Abducent

It sounds like pg_dump and pg_restore aren't really what you should be using... to my knowledge they're really more for fully restoring databases / tables not just inserting specific records. I'd be interested in the solution, I'm thinking some kind of temp table creation from the db0 file (maybe with COPY?), then INSERT FROM SELECT on the temp table where records don't match the target table. But there's probably some postgres magic that would be a little more simple. – Synovitis

COPY sometimes goes broken on newlines (#38315621), that's why I wanted to stick to pg_restore... – Abducent

@Synovitis If you're intrested in a solution - please, vote up the question to make it more noticeable for pg wizards – Abducent

Answer 1

You will have to use a statement like INSERT ... ON CONFLICT DO NOTHING or MERGE (which will probably come in v12), but that requires that you have access to both tables at the same time.

So you either have to use a foreign table or dump/restore the table into a different table in the destination database.

Elaterid

I'm using this recipe: carlosbecker.com/posts/dump-postgres-table-inserts But it is not easy on thousands records... – Abducent

Yes, but what about duplicates? – Elaterid

Very good question! I'm editing the output file to remove INSERTs for duplicates. – Abducent