How to Restore Data from a Backup in PostgreSQL
Alexander Myasnikov
1 Goal
Sometimes there’s a need to create a PostgreSQL backup using readily available tools and restore this backup into another database, for example, for testing purposes.
We will use the pg_dump and pg_restore utilities to achieve this.
Depending on the specifics, there are other solutions too, such as connecting a replica or using logical subscriptions.
However, these may require administrative privileges that are often not available when using cloud infrastructure.
We’ll also refresh our memory on working with Docker and SQL.
2 Creating a Cloud Database
We’ll use a remote database on Vercel via the Neon service.
Neon is a cloud-native, serverless-compatible PostgreSQL platform.
Sign up at vercel.com, create a Neon database, and save the connection string in a variable PG_DSN:
export PG_DSN="postgresql://user:password@host/database?sslmode=require"3 Connecting to the Remote Database
We have a remote PostgreSQL DB, and its connection string is stored in PG_DSN.
Let’s connect to it using Docker and psql to check what data is in there:
❯ docker run --rm -it bitnami/postgresql psql "$PG_DSN"
postgresql 19:27:44.27 INFO ==>
postgresql 19:27:44.30 INFO ==> Welcome to the Bitnami postgresql container
postgresql 19:27:44.35 INFO ==>
psql (17.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.
neondb=> \d
public | test | table | neondb_owner
public | test_id_seq | sequence | neondb_owner
neondb=> select * from test LIMIT 5;
id | name
----+--------
1 | test 1
2 | test 2
3 | test 3
4 | test 4
5 | test 5
(5 rows)We can see test table with some data.
4 Creating a Backup
We run the pg_dump utility in a similar way, specifying the connection string and the path to the output file.
Note: The dump is saved inside the Docker container, so we need to mount a directory from the host to persist the file.
❯ docker run --rm -it --volume ~/data:/mnt/data bitnami/postgresql pg_dump -Fc -f /mnt/data/pg.dump --verbose "$PG_DSN"
postgresql 19:31:09.45 INFO ==>
postgresql 19:31:09.47 INFO ==> Welcome to the Bitnami postgresql container
postgresql 19:31:09.53 INFO ==>
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading dependency data
...
pg_dump: saving encoding = UTF8
pg_dump: saving "standard_conforming_strings = on"
pg_dump: saving "search_path = "
pg_dump: saving database definition
pg_dump: dumping contents of table "neon_auth.users_sync"
pg_dump: dumping contents of table "public.test"The dump is created. Let’s inspect the file:
❯ ls -l pg.dump
-rw-r--r-- 1 root root 9981 May 27 22:31 pg.dump
❯ file pg.dump
pg.dump: PostgreSQL custom database dump - v1.16-05 Creating a Local Database
To keep things simple, we’ll spin up a local PostgreSQL container using Docker.
❯ docker run -e POSTGRES_USER=labuser -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=database -p 5432:5432 bitnami/postgresql
postgresql 20:07:23.57 INFO ==>
postgresql 20:07:23.59 INFO ==> Welcome to the Bitnami postgresql container
postgresql 20:07:23.65 INFO ==>
postgresql 20:07:23.82 INFO ==> ** Starting PostgreSQL setup **
postgresql 20:07:23.91 INFO ==> Validating settings in POSTGRESQL_* env vars..
postgresql 20:07:23.98 INFO ==> Loading custom pre-init scripts...
postgresql 20:07:24.03 INFO ==> Initializing PostgreSQL database...
postgresql 20:07:24.21 INFO ==> pg_hba.conf file not detected. Generating it...
postgresql 20:07:24.23 INFO ==> Generating local authentication configuration
postgresql 20:07:27.03 INFO ==> Starting PostgreSQL in background...
postgresql 20:07:28.39 INFO ==> Creating user labuser
postgresql 20:07:28.49 INFO ==> Granting access to "labuser" to the database "database"
postgresql 20:07:28.66 INFO ==> Setting ownership for the 'public' schema database "database" to "labuser"
postgresql 20:07:28.81 INFO ==> Configuring replication parameters
postgresql 20:07:29.14 INFO ==> Configuring synchronous_replication
postgresql 20:07:29.16 INFO ==> Configuring fsync
postgresql 20:07:29.40 INFO ==> Stopping PostgreSQL...
waiting for server to shut down.... done
server stopped
postgresql 20:07:29.87 INFO ==> Loading custom scripts...
postgresql 20:07:29.92 INFO ==> Enabling remote connections
postgresql 20:07:30.02 INFO ==> ** PostgreSQL setup finished! **
postgresql 20:07:30.11 INFO ==> ** Starting PostgreSQL **
2025-05-28 20:07:30.198 GMT [1] LOG: pgaudit extension initialized
2025-05-28 20:07:30.209 GMT [1] LOG: starting PostgreSQL 17.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2025-05-28 20:07:30.213 GMT [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2025-05-28 20:07:30.213 GMT [1] LOG: listening on IPv6 address "::", port 5432
2025-05-28 20:07:30.217 GMT [1] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-05-28 20:07:30.225 GMT [143] LOG: database system was shut down at 2025-05-28 20:07:29 GMT
2025-05-28 20:07:30.244 GMT [1] LOG: database system is ready to accept connectionsLet’s try connecting to the local DB using psql and verify that the test table is not there:
❯ export PG_LOCAL_DSN="postgres://labuser:secret@host.docker.internal:5432/testdb"
❯ docker run --rm -it bitnami/postgresql psql "$PG_LOCAL_DSN"
postgresql 19:34:50.30 INFO ==>
postgresql 19:34:50.32 INFO ==> Welcome to the Bitnami postgresql container
postgresql 19:34:50.38 INFO ==>
psql (17.5)
Type "help" for help.
testdb=> select * from test LIMIT 5;
ERROR: relation "test" does not exist
LINE 1: select * from test LIMIT 5;
^Great! The local database is running, and the client was able to connect.
6 Restoring Data from the Dump
Let’s run pg_restore, passing the connection string and the path to the dump:
❯ docker run --rm -it --volume ~/data:/mnt/data bitnami/postgresql pg_restore -d "$PG_LOCAL_DSN" --verbose --clean ./mnt/data/pg.dump
postgresql 19:36:24.72 INFO ==>
postgresql 19:36:24.74 INFO ==> Welcome to the Bitnami postgresql container
postgresql 19:36:24.80 INFO ==>
pg_restore: connecting to database for restore
...
pg_restore: creating SEQUENCE "public.test_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.test_id_seq"
pg_restore: creating DEFAULT "public.test id"
pg_restore: processing data for table "public.test"
pg_restore: executing SEQUENCE SET test_id_seq
pg_restore: creating CONSTRAINT "public.test test_pkey"7 Verifying the Restored Data
We connect to the local DB again and run the same queries. The data is there:
❯ docker run --rm -it bitnami/postgresql psql "$PG_LOCAL_DSN"
postgresql 19:34:50.30 INFO ==>
postgresql 19:34:50.32 INFO ==> Welcome to the Bitnami postgresql container
postgresql 19:34:50.38 INFO ==>
psql (17.5)
Type "help" for help.
database=> select * from test LIMIT 5;
id | name
----+--------
1 | test 1
2 | test 2
3 | test 3
4 | test 4
5 | test 5
(5 rows)Success! We’ve created a backup and restored it into a new database.