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-24_215548.dump
   -rw------- 1 postgres postgres 1.9M Dec 24 13:55 /var/backups/postgresql/appdb-2025-12-24_215548.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-24_215548.dump
   ;
   ; Archive created at 2025-12-24 13:55:48 UTC
   ;     dbname: appdb
   ;     TOC Entries: 40
   ;     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

   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 --verbose /var/backups/postgresql/appdb-2025-12-24_215548.dump
   pg_restore: connecting to database for restore
   pg_restore: creating EXTENSION "pg_stat_statements"
   pg_restore: creating COMMENT "EXTENSION pg_stat_statements"
   pg_restore: creating TABLE "public.customers"
   pg_restore: creating SEQUENCE "public.customers_id_seq"
   pg_restore: creating SEQUENCE OWNED BY "public.customers_id_seq"
   pg_restore: creating TABLE "public.events"
   pg_restore: creating SEQUENCE "public.events_id_seq"
   pg_restore: creating SEQUENCE OWNED BY "public.events_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 DEFAULT "public.customers id"
   pg_restore: creating DEFAULT "public.events id"
   pg_restore: creating DEFAULT "public.orders id"
   pg_restore: creating DEFAULT "public.users id"
   pg_restore: processing data for table "public.customers"
   pg_restore: processing data for table "public.events"
   pg_restore: processing data for table "public.orders"
   pg_restore: processing data for table "public.users"
   pg_restore: executing SEQUENCE SET customers_id_seq
   pg_restore: executing SEQUENCE SET events_id_seq
   pg_restore: executing SEQUENCE SET orders_id_seq
   pg_restore: executing SEQUENCE SET users_id_seq
   pg_restore: creating CONSTRAINT "public.customers customers_pkey"
   pg_restore: creating CONSTRAINT "public.events events_pkey"
   pg_restore: creating CONSTRAINT "public.orders orders_pkey"
   pg_restore: creating CONSTRAINT "public.users users_pkey"
   pg_restore: creating INDEX "public.idx_orders_customer_id"
   pg_restore: creating FK CONSTRAINT "public.orders orders_customer_id_fkey"
   pg_restore: creating ACL "public.FUNCTION pg_stat_statements(showtext boolean, OUT userid oid, OUT dbid oid, OUT toplevel boolean, OUT queryid bigint, OUT query text, OUT plans bigint, OUT total_plan_time double precision, OUT min_plan_time double precision, OUT max_plan_time double precision, OUT mean_plan_time double precision, OUT stddev_plan_time double precision, OUT calls bigint, OUT total_exec_time double precision, OUT min_exec_time double precision, OUT max_exec_time double precision, OUT mean_exec_time double precision, OUT stddev_exec_time double precision, OUT rows bigint, OUT shared_blks_hit bigint, OUT shared_blks_read bigint, OUT shared_blks_dirtied bigint, OUT shared_blks_written bigint, OUT local_blks_hit bigint, OUT local_blks_read bigint, OUT local_blks_dirtied bigint, OUT local_blks_written bigint, OUT temp_blks_read bigint, OUT temp_blks_written bigint, OUT blk_read_time double precision, OUT blk_write_time double precision, OUT temp_blk_read_time double precision, OUT temp_blk_write_time double precision, OUT wal_records bigint, OUT wal_fpi bigint, OUT wal_bytes numeric, OUT jit_functions bigint, OUT jit_generation_time double precision, OUT jit_inlining_count bigint, OUT jit_inlining_time double precision, OUT jit_optimization_count bigint, OUT jit_optimization_time double precision, OUT jit_emission_count bigint, OUT jit_emission_time double precision)"
   pg_restore: creating ACL "public.FUNCTION pg_stat_statements_info(OUT dealloc bigint, OUT stats_reset timestamp with time zone)"
   pg_restore: creating ACL "public.FUNCTION pg_stat_statements_reset(userid oid, dbid oid, queryid bigint)"
   pg_restore: creating DEFAULT ACL "public.DEFAULT PRIVILEGES FOR FUNCTIONS"
   ##### snipped #####

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

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

   $ sudo -u postgres psql -d appdb -c "\dt"
              List of relations
    Schema |   Name    | Type  |  Owner   
   --------+-----------+-------+----------
    public | customers | table | postgres
    public | events    | table | postgres
    public | orders    | table | appowner
    public | users     | table | appowner
   (4 rows)

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

   $ sudo -u postgres psql -d appdb -c "SELECT pg_size_pretty(pg_database_size(current_database())) AS size;"
    size  
   -------
    18 MB
   (1 row)

7. 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.

8. Validate key application workflows against the restored data.

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