How to restore a PostgreSQL backup

Q: Should I restore a PostgreSQL backup with psql or pg_restore?

It depends on the format the dump was taken in, not on preference. A plain SQL file goes back through psql. A custom, directory or tar archive goes back through pg_restore. Using the wrong one produces the error "input file appears to be a text format dump. Please use psql."

Q: How do I know which format my PostgreSQL dump file is in?

Run "file dump_name" or open the first line. A plain dump starts with readable SQL comments, while a custom-format archive starts with the binary marker PGDMP. You can also run "pg_restore -l dump_name", which lists the contents of an archive and fails on a plain file.

Q: Do I need to create the database before restoring a PostgreSQL backup?

Yes, unless you use the -C flag with pg_restore, which issues the CREATE DATABASE itself. When you use -C, the database named with -d must be an existing one such as postgres, because pg_restore connects there first to create the target.

Q: Can I restore a PostgreSQL backup to a different PostgreSQL version?

Restoring into a newer version generally works. Restoring a dump taken by a newer pg_dump into an older server often fails with an unsupported version error in the file header. When you have to move between versions, use the pg_dump binary from the target version to take the dump.

Q: How do I restore just one table from a PostgreSQL backup?

Only from an archive format. Use "pg_restore -t table_name -d db_name dump_name.dump" to restore a single table. A plain SQL dump has to be edited by hand or restored in full, which is one of the main reasons to take backups in custom format.

If you are here with a dump file and a database that needs it back, the first thing to settle is which restore command your file needs. PostgreSQL has two, they are not interchangeable, and picking the wrong one produces an error that looks like corruption but is not.

This guide covers both paths, psql and pg_restore, and the errors that stop a restore part-way through.

PostgreSQL Backup Restore

Prerequisites

PostgreSQL installed
PostgreSQL user credentials
A PostgreSQL backup file; if you don't already have one, SimpleBackups provides a managed PostgreSQL backup service that produces one on a schedule
An existing PostgreSQL database

Understanding Backup Types:

PostgreSQL backups can be generated in different formats using pg_dump. Understanding these formats is crucial for choosing the correct restoration method:

SQL script file: Plain text file containing SQL commands.
TAR file, Directory, or Custom format: These formats require a different approach for restoration.

Which one you are holding was decided when the backup was taken, so if you are not sure, custom vs plain export explains how to tell them apart and why the choice matters at restore time. The flags that produced each one are covered in the pg_dump and pg_restore guide.

How to restore a PostgreSQL backup

There are two ways to restore a PostgreSQL database:

psql - for restoring from a plain SQL script file that is created using pg_dump
pg_restore for restoring from a .tar file, directory, or custom format created using pg_dump

If you prefer a GUI, pgAdmin 4 runs both paths and is covered below.

Restore a database with psql

Create a new database where you will restore your backup, or use an existing database.
Run the following command in your terminal:
```
psql -U db_user db_name < dump_name.sql
```
where db_user is the database user, db_name is the database name, and dump_name.sql is the name of your backup file.

Restore a database with pg_restore

If you choose custom, directory, or archive format when creating a backup file, then you will need to use pg_restore in order to restore your database.

To restore your backup, run the following command in your terminal:

pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

where db_user is the database user, db_name is the database name, and /path/to/your/file/dump_name.tar is the full path of your backup file.

Using pg_restore provides you various options, for example:

-c to drop database objects before recreating them,
-C to create a database before restoring into it,
-e exit if an error has encountered,
-F format to specify the format of the archive.

Use pg_restore --help if you want to get the full list of available options.

Restore a database with pgAdmin

If you would rather not touch the command line, pgAdmin 4 can drive the same restore. The thing to know before you start is that pgAdmin's Restore dialog is a wrapper around pg_restore, so the format rule above still decides what you can do:

Custom, directory or tar archive: use the Restore dialog.
Plain SQL file: the Restore dialog will not take it. Open the Query Tool instead, load the file, and execute it.

To restore an archive:

Create the target database if it does not exist yet. Right-click Databases, then Create, then Database.
Right-click the target database and choose Restore.
Set Format to match your file, Custom or tar for a .dump or .tar, Directory for a directory dump.
Pick the file in Filename, and set Role name to the user that should own the restored objects.
Open the Restore options tab if you need the GUI equivalents of the flags above. Clean before restore is -c, and Single transaction rolls the whole restore back if any statement fails, which is what you want when restoring over a live database.
Click Restore, then open the Process Watcher to read the output. A failed restore reports there, not in a popup.

One error catches most people the first time:

Error: Please configure the PostgreSQL Binary Path in the Preferences dialog.
Description: pgAdmin cannot find the pg_restore binary it needs to shell out to. It is a configuration problem, not a problem with your backup.
Solution: Go to File, then Preferences, then Paths, then Binary paths, and point the entry for your PostgreSQL version at the directory holding pg_restore.

Common Issues and Solutions in PostgreSQL Backup Restoration

These are the errors you are most likely to hit part-way through a restore, with the exact strings PostgreSQL prints and what each one actually means:

Issue: Permission Denied Error

Error: psql: FATAL: permission denied for database "db_name"
Description: This error occurs when the user does not have the necessary permissions to access the database or the backup file.
Solution: Ensure the user specified in the psql or pg_restore command has appropriate permissions. For file access issues, verify the file permissions and adjust them as needed using chmod.

Issue: Database Does Not Exist

Error: psql: error: could not connect to server: FATAL: database "db_name" does not exist
Description: An error indicating that the specified database does not exist on your PostgreSQL server.
Solution: Before restoring, create the database using CREATE DATABASE [db_name]; or use the -C flag with pg_restore to create it automatically. Note that with -C, the database you pass to -d must already exist, because pg_restore connects to it in order to issue the CREATE DATABASE. Pass -d postgres and let -C create the target.

Issue: Corrupt Backup File

Error: pg_restore: [archiver] could not read from input file: end of file
Description: Restoration fails due to corruption in the backup file.
Solution: Verify the integrity of your backup file. If possible, generate a new backup and attempt restoration again. Regularly testing backups is crucial to ensure their reliability.

Issue: Version Mismatch

Error: pg_restore: [archiver] unsupported version (1.13) in file header
Description: Occurs when there's a mismatch between the PostgreSQL version used for backup and restoration.
Solution: Ideally, use the same PostgreSQL version for both backup and restoration. If that's not possible, consider upgrading the database or using tools designed to handle version discrepancies.

Issue: Insufficient Disk Space

Error: pg_restore: [tar archiver] could not write to output file: No space left on device
Description: Restoration fails because there is not enough disk space on the server.
Solution: Free up disk space or add more storage to your server before attempting to restore the backup again.

Issue: Connection Timeouts

Error: psql: error: could not connect to server: Connection timed out
Description: The restoration process is interrupted due to connection timeouts.
Solution: Check the network stability and server load. Adjust the timeout settings if necessary and ensure a stable network connection during the restoration process.

Issue: Wrong Restore Tool for the Format

Error: pg_restore: [archiver] input file appears to be a text format dump. Please use psql.
Description: The file is a plain SQL dump, and pg_restore only reads archive formats. This one reads like corruption and is not: the backup is fine, the command is wrong.
Solution: Restore it with psql -U db_user db_name < dump_name.sql instead. If you are unsure which format you have, pg_restore -l dump_name lists the contents of an archive and fails on a plain file.

Issue: Encoding Mismatches

Error: ERROR: invalid byte sequence for encoding "UTF8"
Description: The dump contains bytes that are not valid in the target database's encoding, which usually means the source and target databases were created with different encodings.
Solution: Create the target database with the same encoding as the source, for example CREATE DATABASE db_name WITH ENCODING 'UTF8';. Check the source encoding with \l in psql before you take the dump.

Working through these covers most of what stops a restore. The one that catches people out is the last one on this list only in hindsight: none of these errors appear until you actually attempt a restore, which is the argument for testing one on a schedule rather than the day you need it.

If the database you are restoring into runs in a container, the same commands apply but you have to reach them through Docker first, which backing up and restoring Postgres in Docker covers step by step.

FAQ

Should I restore a PostgreSQL backup with psql or pg_restore?

It depends on the format the dump was taken in, not on preference. A plain SQL file goes back through psql. A custom, directory or tar archive goes back through pg_restore. Using the wrong one produces the error "input file appears to be a text format dump. Please use psql."

How do I know which format my PostgreSQL dump file is in?

Run "file dump_name" or open the first line. A plain dump starts with readable SQL comments, while a custom-format archive starts with the binary marker PGDMP. You can also run "pg_restore -l dump_name", which lists the contents of an archive and fails on a plain file.

Do I need to create the database before restoring a PostgreSQL backup?

Yes, unless you use the -C flag with pg_restore, which issues the CREATE DATABASE itself. When you use -C, the database named with -d must be an existing one such as postgres, because pg_restore connects there first to create the target.

Can I restore a PostgreSQL backup to a different PostgreSQL version?

Restoring into a newer version generally works. Restoring a dump taken by a newer pg_dump into an older server often fails with an unsupported version error in the file header. When you have to move between versions, use the pg_dump binary from the target version to take the dump.

How do I restore just one table from a PostgreSQL backup?

Only from an archive format. Use "pg_restore -t table_name -d db_name dump_name.dump" to restore a single table. A plain SQL dump has to be edited by hand or restored in full, which is one of the main reasons to take backups in custom format.

This article is part of The complete guide to PostgreSQL backup, an honest, practical reference from the team that backs up PostgreSQL every day.