How to restore a PostgreSQL database backup

Restoring a PostgreSQL backup rebuilds data after accidental deletes, failed migrations, or storage incidents, and it provides a practical way to prove that backups are actually recoverable.

Logical backups created by pg_dump are commonly stored as a plain SQL script (.sql) or as an archive such as custom format (.dump/.backup), tar, or directory. Archive backups are restored with pg_restore, which replays objects in dependency order and supports selective restores and ownership/privilege controls.

A restore can overwrite objects, change ownership and privileges, and require downtime depending on application behavior and locking. Ensure the destination database is correct before starting, confirm the restore role has sufficient permissions, and plan for exclusive access when restoring into an existing production database.

1. Confirm the backup file is readable by the local postgres account.

   $ sudo -u postgres ls -lh /var/backups/postgresql/appdb-2025-12-29_090421.dump
   -rw------- 1 postgres postgres 25K Dec 29 09:04 /var/backups/postgresql/appdb-2025-12-29_090421.dump

   Backups are commonly stored under /var/backups or a dedicated backup directory with permissions restricted to postgres.

2. Inspect the backup metadata to confirm it is an archive suitable for pg_restore.

   $ sudo -u postgres pg_restore --list /var/backups/postgresql/appdb-2025-12-29_090421.dump
   ;
   ; Archive created at 2025-12-29 09:04:22 UTC
   ;     dbname: appdb
   ;     TOC Entries: 61
   ;     Compression: gzip
   ;     Dump Version: 1.15-0
   ;     Format: CUSTOM
   ;
   ##### snipped #####

   Plain SQL backups (.sql) are restored with psql, and pg_restore typically reports input file appears to be a text format dump on those files.

3. Create the destination database for the restore.

   $ sudo -u postgres createdb appdb_restore

   Restoring to a new database name avoids overwriting an existing database during testing or validation.

4. Restore the archive into the destination database.

   $ sudo -u postgres pg_restore --dbname=appdb_restore --verbose /var/backups/postgresql/appdb-2025-12-29_090421.dump
   pg_restore: connecting to database for restore
   pg_restore: creating TABLE "public.events"
   pg_restore: creating TABLE "public.events_2025_02"
   pg_restore: creating TABLE "public.events_2025_03"
   pg_restore: creating TABLE "public.events_default"
   pg_restore: creating SEQUENCE "public.events_event_id_seq"
   pg_restore: creating TABLE "public.orders"
   pg_restore: creating SEQUENCE "public.orders_id_seq"
   pg_restore: creating SEQUENCE OWNED BY "public.orders_id_seq"
   pg_restore: creating TABLE "public.users"
   pg_restore: creating SEQUENCE "public.users_id_seq"
   pg_restore: creating SEQUENCE OWNED BY "public.users_id_seq"
   pg_restore: creating TABLE ATTACH "public.events_2025_02"
   pg_restore: creating TABLE ATTACH "public.events_2025_03"
   pg_restore: creating TABLE ATTACH "public.events_default"
   pg_restore: creating DEFAULT "public.orders id"
   pg_restore: creating DEFAULT "public.users id"
   pg_restore: processing data for table "public.events_2025_02"
   pg_restore: processing data for table "public.events_2025_03"
   pg_restore: processing data for table "public.events_default"
   pg_restore: processing data for table "public.orders"
   pg_restore: processing data for table "public.users"
   pg_restore: executing SEQUENCE SET events_event_id_seq
   pg_restore: executing SEQUENCE SET orders_id_seq
   pg_restore: executing SEQUENCE SET users_id_seq
   pg_restore: creating CONSTRAINT "public.events events_pkey"
   pg_restore: creating CONSTRAINT "public.events_2025_02 events_2025_02_pkey"
   pg_restore: creating CONSTRAINT "public.events_2025_03 events_2025_03_pkey"
   pg_restore: creating CONSTRAINT "public.events_default events_default_pkey"
   pg_restore: creating CONSTRAINT "public.orders orders_pkey"
   pg_restore: creating CONSTRAINT "public.users users_pkey"
   pg_restore: creating INDEX "public.events_event_type_idx"
   pg_restore: creating INDEX "public.events_2025_02_event_type_idx"
   pg_restore: creating INDEX "public.events_user_id_idx"
   pg_restore: creating INDEX "public.events_2025_02_user_id_idx"
   pg_restore: creating INDEX "public.events_2025_03_event_type_idx"
   pg_restore: creating INDEX "public.events_2025_03_user_id_idx"
   pg_restore: creating INDEX "public.events_default_event_type_idx"
   pg_restore: creating INDEX "public.events_default_user_id_idx"
   pg_restore: creating INDEX "public.idx_orders_customer_id"
   ##### snipped #####

   Using --clean drops objects before recreating them, and running a restore against the wrong database destroys data.

##### snipped #####</code>

1. List restored tables to confirm the schema is present.

   $ sudo -u postgres psql -d appdb_restore -c "\dt"
                      List of relations
    Schema |      Name      |       Type        |  Owner   
   --------+----------------+-------------------+----------
    public | events         | partitioned table | postgres
    public | events_2025_02 | table             | postgres
    public | events_2025_03 | table             | postgres
    public | events_default | table             | postgres
    public | orders         | table             | appowner
    public | users          | table             | appowner
   (6 rows)

2. Confirm the restored database contains data by checking its size.

   $ sudo -u postgres psql -d appdb_restore -c "SELECT pg_size_pretty(pg_database_size(current_database())) AS size;"
     size   
   ---------
    8132 kB
   (1 row)

3. Reconnect the application to the restored database.

   Application configuration may reference a fixed database name, so a restore into a new database can require a connection string update.

4. Validate key application workflows against the restored data.

   Validation should include writes, because restores can affect sequences, permissions, and ownership.