How to Restore a Backup in PostgreSQL

How do I restore a backup in PostgreSQL using pg_restore or psql?

Restore a dumped PostgreSQL database into a live cluster using pg_restore or psql.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does “restore a backup” mean in PostgreSQL?

Restoring brings the data, schema, roles, and other objects inside a dump file back into a running PostgreSQL instance so the database is usable again.

When should I choose pg_restore vs. psql?

Use pg_restore for custom (-Fc) or directory (-Fd) format dumps—it can parallelize and selectively restore objects.Use psql when the dump is plain SQL (-Fp).

What prerequisites do I need?

• A valid dump file created with pg_dump.
• PostgreSQL server running and accepting connections.
• A superuser or owner role to create/drop objects.

How do I create the target database?

Create a fresh database so you start clean: createdb -U postgres galaxy_shop. pg_restore can also create it with -C.

How do I run pg_restore?

Run pg_restore -U postgres -d galaxy_shop -j 4 -v /backups/galaxy_shop.dump.The -j 4 flag loads four parallel jobs; -v prints progress.

Example: restore into “galaxy_shop”

pg_restore -h localhost -p 5432 -U postgres -C -d postgres /backups/galaxy_shop.dump creates the database and immediately restores it.

How do I restore a plain SQL dump?

1. Create the database.
2. psql -U postgres -d galaxy_shop -f galaxy_shop.sql

How do I handle ownership and large objects?

Add --no-owner if the original roles don’t exist.Use -l to list large objects and -j for parallel restore.

How do I verify the restore finished?

Run quick checks: \dt to list tables, count rows in critical tables, and inspect errors in the pg_restore output.

Best practices for production restores

• Restore into a staging DB first.
• Enable maintenance_work_mem and disable autovacuum temporarily.
• Monitor disk, CPU, and WAL generation.

Common mistakes and how to avoid them

• Forgetting -C when the target DB doesn’t exist.
• Restoring into an existing DB without -c, leaving stale objects.

FAQs

Can pg_restore overwrite existing tables?

Yes—add -c (clean) to drop conflicting objects before restoring.

How do I speed up large restores?

Use -j for parallelism, raise maintenance_work_mem, and restore on SSD storage.

What if the dump uses extensions not installed?

Install the required extensions with CREATE EXTENSION before restoring or exclude them with -E extension_name.

Why How to Restore a Backup in PostgreSQL is important

How to Restore a Backup in PostgreSQL Example Usage


-- Restore a custom-format dump into a new ecommerce DB
pg_restore \
  -h localhost \
  -U postgres \
  -C \
  -d postgres \
  -j 4 \
  -v /backups/galaxy_shop.dump

How to Restore a Backup in PostgreSQL Syntax


pg_restore [connection-options] [restore-options] -d target_db backup_file

Connection Options:
  -h host              -- host name
  -p port              -- port number
  -U user              -- connect as user
  -d database          -- target database (omit when using -C)

Restore Options:
  -C                   -- create database before restoring
  -c                   -- clean (drop) database objects before recreating
  -j jobs              -- number of parallel jobs (directory or custom format)
  -F format            -- archive format (c=custom, d=directory)
  -n schema            -- restore only specific schema
  -t table             -- restore only specific table
  -v                   -- verbose output
  --no-owner           -- skip ownership commands

Plain SQL dump restore:
  createdb -U postgres galaxy_shop
  psql -U postgres -d galaxy_shop -f galaxy_shop.sql

Common Mistakes

Restoring into an existing database without -c. Old objects remain, causing duplicate conflicts. Fix by adding -c or dropping the DB first.
Running pg_restore on a plain SQL (.sql) file. pg_restore only works with custom or directory format dumps; use psql instead.