Skip to content
DBAExperts
PostgreSQL Backups & recovery

How to restore a PostgreSQL backup with pg_restore and psql

Restoring correctly depends on the backup format. Custom or directory dumps are restored with pg_restore; plain-text dumps (.sql) are loaded with psql.

  1. 1

    Create the empty target database

    pg_restore does not create the database on its own unless you use -C. The most predictable approach is to create it first and restore into it.

    createdb -U postgres ventas_restore
  2. 2

    Restore a custom dump with pg_restore

    For the -Fc format, use pg_restore. The -j option parallelizes the load and speeds up large databases; --no-owner avoids failures caused by roles that do not exist on the target.

    pg_restore -U postgres -d ventas_restore -j 4 --no-owner ventas.dump
  3. 3

    Restore a plain SQL dump with psql

    If the backup is a .sql file generated in plain format, pg_restore does not apply: load it with psql.

    psql -U postgres -d ventas_restore -f respaldo.sql
  4. 4

    Restore just one table from the dump

    The custom format supports selective restore. Use -t to bring back a single table without loading the entire backup.

    pg_restore -U postgres -d ventas_restore -t clientes ventas.dump

// common mistake

Restoring over a database that has data using --clean drops existing objects before recreating them: if you point at the wrong database, you wipe production. Always double-check the -d target before running.

// when it's worth an expert

When a restore is part of a real recovery during an incident, time matters and mistakes are costly. If you need a defined and tested RTO/RPO, at dba.mx we design and rehearse the recovery procedure before you ever need it, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.