Restoring a Dump
by Mayur Patel
Restoring a PostgreSQL database dump is an essential process in maintaining and recovering database states. This guide explains how to use pg_dump and pg_restore tools for restoring database dumps, focusing on both text and non-text dump files. By the end of this guide, you will understand how to recreate a database from a dump file, the key considerations to ensure success, and common pitfalls to avoid.
Methods to Restore a Database Dump
1. Restoring from a Text File
Text files created by pg_dump are typically restored using the psql program. The general command to restore a dump is:
psql dbname < dumpfile
Where dumpfile is the file output by the pg_dump command, and dbname is the name of the target database.
Note: The dbname will not be automatically created by this command. You must manually create the database using the following command before executing psql:
createdb -T template0 dbname
This command creates the database from the template0, ensuring it is clean and ready for the restore.
The psql program supports options similar to pg_dump for specifying the database server to connect to and the user name to use. For more detailed usage, refer to the psql reference page.
2. Restoring from Non-text Files
For non-text file dumps, the pg_restore utility must be used. This handles binary dump formats that cannot be processed by psql.
Important Considerations
User Accounts
Before restoring an SQL dump, ensure that all user accounts that own objects or have been granted permissions in the dumped database already exist. If these users are not present, the restore will fail to recreate the objects with their original ownership and permissions. This is a common issue that needs attention, as the absence of user accounts can lead to incomplete or failed restores.
Error Handling
By default, the psql script continues to execute even after encountering an SQL error. If you'd prefer that psql stops execution on error, use the following command:
psql --set ON_ERROR_STOP=on dbname < dumpfile
This setting forces psql to exit with an error code of 3 if an SQL error occurs, helping you detect issues early.
3. Restoring as a Single Transaction
For a more controlled restore, you can execute the entire dump as a single transaction. This ensures that the database restore is either fully completed or fully rolled back. This can be done by adding the -1 or --single-transaction options:
psql -1 dbname < dumpfile
When using this option, be aware that even a minor error can rollback a restore that has already run for many hours.
However, that might still be preferable to manually cleaning up a complex database after a partially restored dump.
4. Direct Server-to-Server Restore
A powerful feature of pg_dump and psql is their ability to write to or read from pipes. This allows you to directly dump a database from one server and restore it to another without needing intermediate storage. Here's how you can do this:
pg_dump -h host1 dbname | psql -h host2 dbname
This command sends the dump from host1 and directly restores it on host2.
Important Notes
pg_dump Dumps Relative to Template0
The dumps produced by pg_dump are based on template0. This means that any languages, procedures, or customisations added via template1 will also be included in the dump. Therefore, if you're using a customised template1, ensure you create the empty database from template0 (as shown earlier).
Restoring a database dump requires careful attention to detail, but following these steps ensures a smooth recovery process.
Need more development tips?
Stay tuned to our blog.