Restore PostgreSQL Database: Full Guide

Restoring a PostgreSQL database means loading a backup file (custom, plain SQL, or tar format) back into a PostgreSQL server to recover data after loss, corruption, or migration. You typically use the pg_restore command for custom and tar backups, or psql for plain SQL dumps. This guide covers the exact commands, options, and common pitfalls so you can restore your database correctly.

Understanding PostgreSQL Backup Formats

Before restoring, you must know what kind of backup file you have. PostgreSQL offers three main dump formats, each with a different restore method:

• Plain SQL (.sql) – Created with pg_dump without -F flags. It contains SQL statements (CREATE TABLE, COPY, etc.). Restore with psql. No parallel restore, but works everywhere.
• Custom format (.dump or .backup) – Created with pg_dump -Fc. Compressed, allows selective restore (choose tables or schemas), and supports parallel restore. Restore with pg_restore.
• Tar format (.tar) – Created with pg_dump -Ft. Similar to custom but not compressed by default. Restore with pg_restore; also allows selective restore.

Also note the difference between a cluster backup (using pg_basebackup for physical files) and a logical backup (using pg_dump). This article focuses on logical backups, which are the most common for restoring individual databases.

Prerequisites for a Successful Restore

Before running any restore command, verify these items to avoid failures:

• PostgreSQL version – The restore server should be the same or a newer major version than the backup. Restoring an older version into a newer one is usually fine, but the reverse may fail.
• Target database – For pg_restore, you must create an empty database first (e.g., createdb mydb) unless you are restoring into an existing one. For psql, the database must exist as well.
• Permissions – You need sufficient privileges to create tables and objects. Typically, you run the restore as the database owner or a superuser.
• Disk space – Ensure enough free space for the restored data, plus temporary space for indexes and constraints.
• Connection settings – Use -h, -p, -U to specify host, port, and user if not using defaults.

How to Restore Using pg_restore (Custom/Tar)

For custom or tar backups, the recommended tool is pg_restore. The basic syntax is:

pg_restore -d mydatabase /path/to/backup.dump

This restores all objects into the database mydatabase. Add -U username and -h host if needed. For a clean restore into an existing database (dropping existing objects first), use --clean:

pg_restore --clean -d mydatabase backup.dump

To restore only specific tables or schemas, use -t (table) or -n (schema):

pg_restore -d mydatabase -t public.users backup.dump

For faster restores on large databases, enable parallel jobs with -j (e.g., -j 4 uses 4 concurrent processes). Note that parallel restore only works with custom format.

How to Restore Using psql (Plain SQL)

Plain SQL dumps are restored with psql. The command is:

psql -d mydatabase -f /path/to/backup.sql

If the SQL file contains commands to create the database itself (from pg_dump -C), you can restore into a default database like postgres:

psql -f backup.sql postgres

For large SQL files, you might want to suppress errors (e.g., if some objects already exist) with --set ON_ERROR_STOP=off (default is off). To stop on the first error, use --set ON_ERROR_STOP=on.

Common Restore Issues and Solutions

Here are frequent problems you may encounter and how to fix them:

• Role does not exist – The backup references roles (users) that are not present on the target server. Create the missing roles before restoring, or use --no-owner with pg_restore to skip ownership commands.
• Database does not exist – For pg_restore, create the database first: createdb mydb. For psql, connect to a database that exists.
• Permission denied – Ensure the user you are restoring as has CREATE and USAGE privileges on the target schema.
• Out of memory or disk full – Increase resources or restore in smaller chunks (e.g., by table).
• Version mismatch – If the backup is from a newer PostgreSQL version, restore may fail. Use the same or newer server version.
• Encoding issues – If the backup was taken with a different encoding, you may need to specify --encoding or convert the dump.

Restoring a Single Table or Schema

Sometimes you only need part of the backup. With custom format, you can list the contents and restore selectively:

pg_restore --list backup.dump

This shows all objects. Then restore a specific table:

pg_restore -d mydb -t public.orders backup.dump

Or a whole schema:

pg_restore -d mydb -n sales backup.dump

For plain SQL, you would need to extract the relevant part manually, which is error-prone. That is why custom format is preferred for large or complex databases.

Best Practices and Safety Tips

Restoring a database can overwrite existing data. Follow these guidelines to avoid data loss:

• Always back up the current database before restoring over it, especially if you use --clean.
• Test the restore on a staging server before applying to production.
• Use transactions – pg_restore supports --single-transaction to wrap the entire restore in one transaction, so it either fully succeeds or fully rolls back. This is safer but slower.
• Verify the restore – Run SELECT count(*) FROM key_tables; or use pg_dump to compare row counts.
• Document your restore procedure – For disaster recovery, have a written runbook.

Frequently Asked Questions

How do I restore a PostgreSQL database from a .sql file?

Use psql -d your_database -f backup.sql. If the file includes CREATE DATABASE, you can run psql -f backup.sql postgres.

What is the difference between pg_restore and psql for restoring?

pg_restore is for custom and tar formats; it allows selective restore and parallel jobs. psql is for plain SQL dumps and executes the file as a script.

Can I restore a PostgreSQL backup to a different database name?

Yes. For pg_restore, create the new database and use -d newname. For plain SQL, edit the file or use psql with the new database name (if the dump doesn’t contain CREATE DATABASE).

Why do I get “role does not exist” when restoring?

The backup contains ownership references to roles not present on the target server. Create the roles first, or use --no-owner with pg_restore to ignore ownership.

How long does a restore take?

It depends on database size, server hardware, and settings. Using parallel restore (-j) can speed up large restores, but it’s only available with custom format.

What should I do if the restore fails halfway?

Check the error message. Common causes include missing roles, insufficient privileges, or disk space. Fix the issue and retry. If you used --single-transaction, the failed restore will have rolled back completely.

Related

• Nvidia GeForce Now RTX 5080 Ultimate: What It Means
• Mini Thermal Printer: How It Prints a Morning Newspaper
• Rust Fearless Concurrency: A Practical Guide