Merge two PostgreSQL dumps

Attention

This is an edge-case. You probably don't want to mix different database dumps. It also requires that the mixed tables do not share relations to other database objects.

Scenario

• You've got two database dumps:
  • dump_a.pgdump
  • dump_b.pgdump
• Both dumps include these tables:
  • foobar
  • barfoo
  • foobaz
• You want to create a dump with:
  • dump_a.foobar
  • dump_b.barfoo
  • dump_a.foobaz

Invalid and incomplete restore

These commands might be obvious but won't actually work:

Copypg_restore --clean --if-exists --no-owner --no-privileges -d restore dump_a.pgdump
pg_restore --clean --if-exists --no-owner --no-privileges -d restore -t barfoo dump_b.pgdump

This will restore the table but won't restore depended objects like indices, primary keys, sequences as the pg_restore Show archive.org snapshot man page states:

When -t is specified, pg_restore makes no attempt to restore any other database objects that the selected table(s) might depend upon. Therefore, there is no guarantee that a specific-table restore into a clean database will succeed.

Valid restore

To archive a complete restore, first create a dump with only the tables you want to merge:

Copy pg_dump -Fc -t barfoo -f dump_b-barfoo.pgdump database

pg_dump Show archive.org snapshot will also dump all depended database objects.

Now you can create merge the two dumps with:

Copypg_restore --clean --if-exists --no-owner --no-privileges -d restore dump_a.pgdump
pg_restore --clean --if-exists --no-owner --no-privileges -d restore dump_b-barfoo.pgdump

Check

You can check the new database by comparing the output of \d+ in psql.