How to restore a single table from a .sql postgresql backup?
Don't do SQL backups if you need single table restore, etc. Use pg_dump
's -Fc
option - the "custom" format. This can be restored using pg_restore
. Selective restore is possible, as are all sorts of other handy features. pg_restore
can convert a custom-format dump into an SQL dump later if you need it.
If you're stuck with an existing dump, your only options are:
Use a text editor to extract the target table data to a separate file and just restore that; or
Restore the dump to a throwaway database then use
pg_dump
to take a selective dump including just that table. Since it's throwaway, you can use a separate Pg instance on some unloaded fast-but-unsafe machine where you turn on all the "make it fast but eat my data if you like" options likefsync=off
. You should NEVER set that in production.
I'm not aware of any tool for this, but this one-liner extracts precious_table
from my_backup.sql
file:
sed -n '/^COPY precious_table /,/^\\\.$/p' my_backup.sql