[SOLVED] How to restore a single table from a .sql postgresql backup?

Issue

A table's rows were mistakenly deleted from the database. We have a db backup which results in a sql file that can restored like so:

psql -h localhost -d proddump -f /Users/U/Desktop/prod_db_backup/PostgreSQL/site_prod.sql

This ends up doing a full restore locally. But what we need is to restore a single table's rows to production. Any tips on how to make this work with PostgreSQL 9.1?

Thanks

Solution

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.

Answered By - Craig Ringer

Answer Checked By - David Goodson (WPSolving Volunteer)