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
2 Answers
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
1 Comment
Dimon
Is it restores full database? I need only a single table.
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.
pg_dumpwith the custom or directory format. You can do that with a "plain text" (SQL script) dump.pg_dumpoptions, like this: dev.to/andyatkinson/dump-and-load-a-postgresql-table-p0o