CODEWITHSUNDEEP
postgresqlpg-dumppg-restore
Alexander Gorg
Alexander Gorg

Reputation: 1078

How to use pg_dump/pg_restore to update database?

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.

Upvotes: 6

Views: 2763

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246848

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.

Upvotes: 1

Related Questions