CODEWITHSUNDEEP
postgresql
Dimon
Dimon

Reputation: 803

PostgreSQL - restoring one table from database dump

How can I restore one table from database dump ? I make dump using the next command:

pg_dump -U admin -h localhost my-db-name | gzip - > /home/a2_db_backup/my-db-name-backup.sql.gz

Upvotes: 13

Views: 31127

Answers (2)

maniek
maniek

Reputation: 7307

There is no easy way, except for some hacks (like using awk to cut the part of the file).

If the dump is not too big, the easiest thing to do is restore the full backup to a temporary database (gzcat backup_file.gz | psql -h host -U user database_name) dump the one table (pg_dump -t my_table), then restore it.

For future reference, the custom and directory pg_dump formats do allow restoring a single table. Use either of the following commands to create dumps:

pg_dump -Fc database_name > database.dump

or

pg_dump -Fd database_name -f database.dump

-Fd produces a directory, but this doesn't affect the pg_restore command; just use the same name that you specified for the dump output. See pg_dump man page for the details - especially the examples at the end.

Then you can use pg_restore to restore a single table:

pg_restore -t my_table -d database_name database.dump

Upvotes: 24

NiklasMM
NiklasMM

Reputation: 2972

As suggested in the accepted answer, you could use awk to cut the file like so:

awk '/COPY public.<tablename>/,/\\./' dump_file.sql > restore_table.sql

and then restore using psql:

psql [...] < restore_table.sql

Obviously, this only works if the table has been truncated and not dropped.

Upvotes: 3

Related Questions