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.pgdumpdump_b.pgdump
- Both dumps include these tables:
foobarbarfoofoobaz
- You want to create a dump with:
dump_a.foobardump_b.barfoodump_a.foobaz
Invalid and incomplete restore
These commands might be obvious but won't actually work:
pg_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
-tis specified,pg_restoremakes 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:
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:
pg_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.
