PostgreSQL - restoring one table from database dump

Question

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

Answer 1

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 the future the custom format (pg_dump -Fc > database.dump) is the way to go. The you can use pg_restore to restore a single table : pg_restore -t my_table -d database_name database.dump .