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 like fsync=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