PostgreSQL pg_restore: Restore Dumps & Fix Errors

Restoring a PostgreSQL database is a critical operation, often underestimated until it’s urgently needed. A backup is only effective if restoration proceeds smoothly and within defined SLA times. The pg_restore command is the fundamental tool for managing dumps created with pg_dump, offering flexibility and control over the process. Whether it’s a full restoration after a disaster, granular recovery of a single table, or data migration between different environments, pg_restore is your ally. However, it’s not uncommon to encounter errors like ‘role does not exist’ or version incompatibility issues, which can turn a routine operation into a race against time. Understanding the different dump formats, restoration options, and troubleshooting strategies for common problems is essential for any DBA or SysAdmin managing PostgreSQL environments. This guide provides practical knowledge to confidently approach your database restorations.

Tested on: PostgreSQL 16.1 · Ubuntu 24.04 LTS · October 2026

Prerequisites / Test Environment

Before you begin, ensure you have:

• A running PostgreSQL server (version 12 or higher recommended).
• The pg_restore client installed on the machine from which you are performing the restoration. It’s part of the postgresql-client package.
• A database dump created with pg_dump in custom, directory, or tar format. Dumps in SQL (plain text) format should be restored with the psql command.
• Credentials for a user with sufficient privileges to create databases and objects (typically postgres or a superuser).

Custom, Directory, or SQL Format: Which Tool for Which Dump?

pg_dump can generate backups in various formats, each with its peculiarities and preferred restoration tool:

• Custom format (-Fc): This is the most flexible and recommended format for pg_dump. It’s compressed by default and allows selective restoration of tables, schemas, or other objects. It requires pg_restore.
• Directory format (-Fd): Similar to the custom format, but the backup is saved as a directory containing multiple files. It’s useful for very large backups and supports parallel restoration. It requires pg_restore.
• Tar format (-Ft): A standard tar archive. This also supports selective restoration but is less flexible than the custom format for metadata management. It requires pg_restore.
• Plain text (SQL) format: This is a simple SQL script that recreates the database. It’s not compressed by default and doesn’t allow selective restoration without manual modifications. It must be restored with psql.

To inspect the contents of a dump (e.g., to see which tables or schemas it contains), you can use the -l (list) option with pg_restore:

pg_restore -l backup.dump | head

This will show you the first few lines of the dump’s contents, useful for understanding what was saved and how to proceed with a granular restoration.

Restoring to a New Database

The cleanest way to restore a dump is to create a new, empty database and then restore the content into it. This avoids conflicts with pre-existing objects.

First, create a new database. Make sure to specify the correct owner (-O), which will be the user who uses the database:

createdb -O appuser appdb

Now you can perform the restoration into the appdb database. The -d option specifies the target database:

pg_restore -d appdb backup.dump

Read also: Oracle RMAN: Database Recovery After Failure

Overwriting an Existing Database (–clean –if-exists)

Sometimes it’s necessary to overwrite an existing database, for example, to refresh a staging environment with production data. In this case, you can use the --clean and --if-exists options.

• --clean: Drops all objects (tables, indexes, functions, etc.) within the target database before restoring.
• --if-exists: When combined with --clean, ensures that DROP is executed only if the object exists, preventing errors. It’s useful in conjunction with --clean.

pg_restore -d appdb --clean --if-exists backup.dump

WARNING: This operation is destructive. Ensure you have a backup of the appdb database before proceeding, especially in production environments.

Speeding Up with Parallel Restoration

For large databases, a sequential restoration can take hours. pg_restore supports parallel restoration using the -j (jobs) option, which specifies the number of worker threads to use. This is particularly effective with directory or custom format dumps.

A good starting point is to set the number of jobs equal to the number of CPU cores on the server, or slightly less to avoid overloading it.

pg_restore -d appdb -j 4 backup.dump

This command will use 4 threads to restore the appdb database, significantly reducing the total time. Read also: Oracle DBA: Daily Checks for Real-World Output

Restoring a Single Table or Schema

One of the biggest advantages of custom and directory formats is the ability to restore only a portion of the database. This is extremely useful for recovering specific data or for testing changes on a subset of the database.

To restore a single table (e.g., customers):

pg_restore -d appdb -t customers backup.dump

To restore an entire schema (e.g., sales):

pg_restore -d appdb -n sales backup.dump

You can specify multiple -t or -n options to include more objects.

‘role does not exist’ Error and Permissions

The error ERROR: role "username" does not exist is one of the most common during restoration. It occurs when the dump includes information about the owner of the database or objects (tables, functions, etc.), but the specified role does not exist on the target server.

There are two solutions:

1. Create the missing role: If the role username must exist, create it on the target PostgreSQL server with CREATE ROLE username WITH LOGIN PASSWORD 'password';.
2. Ignore owners and privileges: If it’s not crucial to maintain the exact owners and permissions from the original database, you can use the --no-owner and --no-privileges (or --no-acl) options.

pg_restore -d appdb --no-owner --no-privileges backup.dump

This solution is often the quickest for restorations in development or test environments, where exact replication of permissions is not fundamental. After restoration, you will need to assign the correct permissions to the new owner or desired roles.

Version Mismatch Between Dump and Server

Another frequent issue is version incompatibility between the server from which the dump was made and the one to which you are attempting to restore. For example, a dump from PostgreSQL 16 might not be restorable on PostgreSQL 12 due to differences in catalog schema or features.

pg_restore is generally backward compatible (a dump from an older version can be restored to a newer one), but forward compatibility is not guaranteed. If you encounter version-related errors, the best solution is:

1. Use the same version: Perform the restoration on a PostgreSQL server of the same version or newer than the server from which the dump was generated.
2. Ignore version (with caution): In specific, non-critical cases, you can try the --ignore-version option. However, this option can lead to an inconsistent database if the version differences are significant.

pg_restore -d appdb --ignore-version backup.dump

Use --ignore-version only if you understand the risks and it’s not a production environment. It’s always preferable to have a version match or a well-tested upgrade path.

Verifying the Restoration

After executing pg_restore, it’s crucial to verify that the restoration was successful and that the database is intact and accessible. Some quick checks include:

• Database connection: Try connecting to the database with psql or a graphical client.
• Table/record count: Verify that the number of tables and, for some key tables, the number of records matches expectations.

psql -d appdb -c '\dt+'

This command lists all tables in the appdb database with their details. You can also run SELECT COUNT(*) queries on specific tables to verify data presence. Read also: Linux find Command: 20 Practical Examples

Common Errors and Troubleshooting

• Insufficient permissions: If pg_restore fails with access or creation errors, ensure that the user executing the command has superuser privileges or at least permissions to create databases and objects within the target database.
• Insufficient disk space: A restoration can fail if the target server does not have enough disk space to hold the restored database. Check available space before starting.
• Corrupt dump file: If the backup.dump file is damaged or incomplete, pg_restore will fail. Ensure the file is intact (e.g., by checking the file’s hash if available).
• Interrupted network connection: For restorations to remote servers, an unstable connection can cause failures. Ensure you have a stable network connection.

FAQ — Frequently Asked Questions

Can I restore a pg_dump dump to a database with a different name?

Yes, you can restore a dump to a database with a different name than the original database. The important thing is to specify the target database name with the -d option (e.g., pg_restore -d new_db_name backup.dump). Just ensure that the new_db_name database has been created beforehand.

Is it possible to restore only data without the schema?

Yes, pg_restore offers the --data-only option to restore only data and --schema-only to restore only the schema. These are very useful for specific scenarios such as loading data into an existing schema or creating an empty test database.

What is the difference between pg_restore and psql for restoration?

pg_restore is used for custom, directory, or tar format dumps, which are binary and compressed. It offers greater flexibility such as selective and parallel restoration. psql is used to restore plain text (SQL) dumps, which are simple SQL scripts. psql executes the script sequentially and does not offer the same selective restoration options.

Do I need to stop the database during restoration?

It is not necessary to stop the entire PostgreSQL server. However, it is good practice to ensure there are no active connections to the target database during restoration, especially if you are overwriting an existing database. You can block connections with ALTER DATABASE appdb CONNECTION LIMIT 0; and re-enable them after restoration.

How can I restore a single record or a specific row?

pg_restore does not support restoring individual records. If you need to restore a limited number of rows, the best solution is to restore the complete table to a temporary database and then extract the desired rows using SQL queries (INSERT INTO ... SELECT ... WHERE).

Conclusions with Operational Takeaways

Effective management of PostgreSQL backups and restorations is a fundamental skill. pg_restore is a powerful and flexible tool that, once understood, can save hours of work and reduce downtime in critical situations. Always remember to:

1. Choose the correct dump format based on your flexibility and restoration performance needs.
2. Regularly test your restorations to ensure backups are valid and the procedure works as expected.
3. Utilize parallel restoration (-j N) to reduce recovery times for large databases.
4. Be aware of the --no-owner, --no-privileges, and --ignore-version options to resolve common errors, but use them with informed judgment.
5. Verify database integrity after each restoration with control queries and record counts. Read also: systemd Service Creation: Auto-Restart & Dependencies

By implementing these practices, you can manage your PostgreSQL databases with greater security and responsiveness, ensuring operational continuity even in the face of unforeseen events.

Sources

• PostgreSQL Documentation: pg_restore

Updated: October 2026