In this article
- What you will learn
- Prerequisites
- How it works
- Prepare the lab
- Back up and restore with a plain SQL dump
- Custom format: one file, selective restores
- Large databases: directory format and parallel jobs
- pg_dumpall: roles, tablespaces and the whole cluster
- Schedule a nightly logical backup
- Common mistakes and how to fix them
- Practice exercise
- Quick reference
- Summary and what's next
This is Part 17 of the PostgreSQL DBA series, and the first part about backups. pg_dump exports one database as SQL or as an archive file, pg_restore loads an archive back, and pg_dumpall exports the whole cluster, including the roles that pg_dump leaves out. By the end of this part you will be able to back up a database in every format, restore all of it or a single table, move a database to another server with its owners and grants intact, and schedule a nightly logical backup.
In Part 16 (Checkpoints and the background writer) we tuned how PostgreSQL writes data files to disk. Now we protect that data. Everything here was run on Ubuntu with the PostgreSQL 18 client tools from the official PGDG repository, and every output block is from a real run.
What you will learn
- The difference between a logical backup (SQL commands) and a physical backup (copies of data files), and when each one fits.
- The four
pg_dumpformats: plain, custom, directory and tar, and which one to pick. - How to restore a whole database, or only one table, with
psqlandpg_restore. - How to dump roles and tablespaces with
pg_dumpall --globals-only, and why a restore fails without them. - How to speed up large dumps and restores with parallel jobs.
- How to schedule a nightly logical backup with retention.
Prerequisites
- Parts 1 to 16 of this series, especially Part 4 (psql), Part 7 (roles and privileges) and Part 8 (databases and template databases).
- One Ubuntu server (db1) with PostgreSQL 18 from the PGDG repository and the cluster 18 main running on port 5432, as set up in Part 2.
- A user with sudo rights. In Step 14 we create a second cluster on port 5433 on the same machine to act as the "new server".
- About 50 MB of free disk space for the lab.
How it works
PostgreSQL has two families of backup. A logical backup asks the running server for its data and writes it out as SQL commands: CREATE TABLE, COPY with the rows, CREATE INDEX, GRANT. A physical backup copies the data files themselves, byte for byte. This part covers logical backups. Physical backups start in Part 18.
A simple comparison: a logical backup is a recipe that cooks the database again from scratch, while a physical backup is a photocopy of the finished dish. The recipe works in any kitchen (a newer PostgreSQL version, a different CPU architecture), but cooking takes time. The photocopy is fast to restore, but only into the same PostgreSQL major version.
| Logical (pg_dump) | Physical (Part 18 onwards) | |
|---|---|---|
| What it contains | SQL commands and table rows | Copies of the data files and WAL |
| Scope | One database, one schema or one table | The whole cluster only |
| Restore to a newer major version | Yes | No |
| Restore to a point in time | No, only to the moment the dump started | Yes, with WAL archiving (PITR) |
| Restore speed on large databases | Slow: rows are loaded and indexes rebuilt | Fast: files are copied back |
| Typical use | Moving or copying databases, upgrades, restoring single tables, extra safety copy | Main production backup |
pg_dump is an ordinary client program. It connects like any application, opens one transaction and reads everything through a single snapshot, the same MVCC snapshot idea you met in Part 11. That is why a dump is consistent: it shows the database exactly as it was when the dump began, even if users keep writing while it runs. It does not block readers or writers. Only commands that need an exclusive lock, such as most forms of ALTER TABLE, have to wait for it.
Three tools work together:
| Tool | What it does |
|---|---|
| pg_dump | Exports one database. Does not include roles or tablespaces, because those belong to the whole cluster. |
| pg_dumpall | Exports roles, tablespaces and every database, as one plain SQL script. With --globals-only it exports only roles and tablespaces. |
| pg_restore | Restores an archive made by pg_dump in custom, directory or tar format. It can list the archive, pick items and run in parallel. |
pg_dump can write four formats, chosen with -F:
| Format | Option | Restore with | Compressed by default | Parallel dump | Pick items on restore |
|---|---|---|---|---|---|
| plain | -Fp (default) | psql | No | No | No |
| custom | -Fc | pg_restore | Yes (gzip) | No | Yes |
| directory | -Fd | pg_restore | Yes (gzip) | Yes (-j) | Yes |
| tar | -Ft | pg_restore | Not supported | No | Yes |
The rule most DBAs follow: use custom (-Fc) for everyday dumps, directory (-Fd) for large databases where parallel jobs save time, and plain only when a person needs to read or edit the SQL.
Prepare the lab
Step 1: Switch to the postgres user and create a backup directory
Where: on db1, as the postgres user. The postgres operating system user can connect as the postgres superuser through peer authentication (Part 6), so no password is needed. Its home directory is /var/lib/postgresql.
sudo -i -u postgres
mkdir -p ~/backups
chmod 700 ~/backups
ls -ld ~/backups
pg_dump --version| Option | What it does |
|---|---|
| sudo -i -u postgres | Starts a login shell as the postgres user, with its home directory and environment. |
| mkdir -p | Creates the directory, and does nothing if it already exists. |
| chmod 700 | Only the owner (postgres) can read, write or enter the directory. Dumps contain all your data. |
drwx------ 2 postgres postgres 4096 Nov 12 12:47 /var/lib/postgresql/backups
pg_dump (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg24.04+2)The directory belongs to postgres and nobody else can open it. The version line confirms you are using the PostgreSQL 18 tools.
Step 2: Create the sample database shopdb
Where: on db1, as the postgres user. Save the script below as ~/lab.sql. It creates two roles, a database owned by app_owner, three related tables with 91,000 rows, an index, a view and SELECT grants for report_user. Those roles and grants are what make restores interesting.
CREATE ROLE app_owner LOGIN PASSWORD 'AppOwner#2026';
CREATE ROLE report_user LOGIN PASSWORD 'Report#2026';
CREATE DATABASE shopdb OWNER app_owner;
\c shopdb
SET ROLE app_owner;
CREATE SCHEMA sales;
CREATE TABLE sales.customers (
customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL,
city text NOT NULL
);
CREATE TABLE sales.orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES sales.customers,
order_date date NOT NULL,
amount numeric(10,2) NOT NULL
);
CREATE TABLE sales.payments (
payment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES sales.orders,
paid_on date NOT NULL,
method text NOT NULL
);
CREATE INDEX orders_customer_idx ON sales.orders (customer_id);
INSERT INTO sales.customers (full_name, city)
SELECT 'Customer ' || g, (ARRAY['Hyderabad','Pune','Chennai','Delhi'])[1 + g % 4]
FROM generate_series(1, 1000) AS g;
INSERT INTO sales.orders (customer_id, order_date, amount)
SELECT 1 + g % 1000, date '2026-01-01' + g % 300, round((100 + random() * 9900)::numeric, 2)
FROM generate_series(1, 50000) AS g;
INSERT INTO sales.payments (order_id, paid_on, method)
SELECT order_id, order_date + 1, (ARRAY['UPI','card','netbanking'])[1 + order_id % 3]
FROM sales.orders WHERE order_id % 5 <> 0;
CREATE VIEW sales.daily_revenue AS
SELECT order_date, sum(amount) AS revenue FROM sales.orders GROUP BY order_date;
GRANT USAGE ON SCHEMA sales TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO report_user;
RESET ROLE;psql -X -f ~/lab.sqlCREATE ROLE
CREATE ROLE
CREATE DATABASE
You are now connected to database "shopdb" as user "postgres".
SET
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 1000
INSERT 0 50000
INSERT 0 40000
CREATE VIEW
GRANT
GRANT
RESETTo check every restore in this part, save a small check script as ~/check.sql. It counts rows and adds up all order amounts, so any missing or changed row shows up as a different number.
SELECT current_database(),
(SELECT count(*) FROM sales.customers) AS customers,
(SELECT count(*) FROM sales.orders) AS orders,
(SELECT count(*) FROM sales.payments) AS payments,
(SELECT sum(amount) FROM sales.orders) AS order_total;psql -X -d shopdb -f ~/check.sql current_database | customers | orders | payments | order_total
------------------+-----------+--------+----------+--------------
shopdb | 1000 | 50000 | 40000 | 251720646.65
(1 row)Your order_total will be different because the amounts are random. Write down your own number: every restored copy must match it.
Back up and restore with a plain SQL dump
Step 3: Dump shopdb as a plain SQL script
Where: on db1, as the postgres user.
pg_dump -d shopdb -f ~/backups/shopdb.sql
ls -lh ~/backups/| Option | What it does |
|---|---|
| -d shopdb | The database to dump. |
| -f file | Send output to this file instead of standard output. |
| (no -F) | Plain-text SQL script, the default format. |
total 2.6M
-rw-rw-r-- 1 postgres postgres 2.6M Nov 12 12:47 shopdb.sqlNo output from pg_dump means success. It prints only warnings and errors, on standard error, and you should always read them. A plain dump is not compressed, so it is the largest format.
Step 4: Look inside the dump
Where: on db1, as the postgres user.
head -12 ~/backups/shopdb.sql
grep -n '^COPY\|^CREATE INDEX\|^GRANT' ~/backups/shopdb.sql--
-- PostgreSQL database dump
--
\restrict qFl3FC68fyJnGNp4uNOdfa6hKzEBkMHwv8FmmzqNfmMWLjH9B2SGaIED4dZJZXy
-- Dumped from database version 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
-- Dumped by pg_dump version 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
135:COPY sales.customers (customer_id, full_name, city) FROM stdin;
1143:COPY sales.orders (order_id, customer_id, order_date, amount) FROM stdin;
51151:COPY sales.payments (payment_id, order_id, paid_on, method) FROM stdin;
91204:CREATE INDEX orders_customer_idx ON sales.orders USING btree (customer_id);
91227:GRANT USAGE ON SCHEMA sales TO report_user;
91234:GRANT SELECT ON TABLE sales.customers TO report_user;
91241:GRANT SELECT ON TABLE sales.orders TO report_user;
91248:GRANT SELECT ON TABLE sales.daily_revenue TO report_user;
91255:GRANT SELECT ON TABLE sales.payments TO report_user;The file is ordinary SQL. Tables are created first, then rows are loaded with COPY, and only then are indexes, constraints and grants added. Building indexes after loading the rows is much faster than updating them row by row.
The \restrict line, with a random key, is a psql meta-command that pg_dump writes since PostgreSQL 18 and the August 2025 minor releases of older versions (such as 17.6). While it is active, psql refuses any other backslash command, so text coming from a compromised source server cannot be run as psql meta-commands, which could start shell commands on the machine doing the restore. A matching \unrestrict line ends the file. Leave both alone.
Notice what is not there: no CREATE ROLE and no CREATE DATABASE. The dump says OWNER TO app_owner and GRANT ... TO report_user, but it expects those roles to exist already. We come back to this in Step 13.
Step 5: Restore the plain dump into a new database
Where: on db1, as the postgres user. psql does not create the database for you, so create an empty one first. The documentation says to create it from template0: dumps are made relative to template0, and anything you added to template1 would otherwise clash with the dump.
createdb -T template0 -O app_owner shopdb_copy
psql -X --set ON_ERROR_STOP=on -1 -d shopdb_copy -f ~/backups/shopdb.sql| Option | What it does |
|---|---|
| createdb -T template0 | Build the new database from template0, which is always clean. |
| createdb -O app_owner | The role that will own the new database. |
| psql -X | Do not read ~/.psqlrc or the system psqlrc, so your personal settings cannot change the restore. |
| --set ON_ERROR_STOP=on | Stop at the first error and exit with status 3, instead of carrying on. |
| -1 | Wrap the whole file in one transaction (BEGIN ... COMMIT). Either everything is restored, or nothing is. |
| -f file | Read commands from this file. |
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
...
COPY 1000
COPY 50000
COPY 40000
...
CREATE INDEX
ALTER TABLE
ALTER TABLE
GRANT
GRANT
GRANT
GRANT
GRANTEvery line is the result of one command in the script. With -1 and ON_ERROR_STOP, a single failure rolls everything back, so you never get a half-restored database. The trade-off, as the documentation warns, is that one small error at the end of a long restore throws away hours of work.
Step 6: Verify the copy and refresh planner statistics
Where: on db1, as the postgres user.
psql -X -d shopdb_copy -f ~/check.sql
psql -X -d shopdb_copy -c '\dt sales.*'
psql -X -d shopdb_copy -c 'ANALYZE;' current_database | customers | orders | payments | order_total
------------------+-----------+--------+----------+--------------
shopdb_copy | 1000 | 50000 | 40000 | 251720646.65
(1 row)
List of tables
Schema | Name | Type | Owner
--------+-----------+-------+-----------
sales | customers | table | app_owner
sales | orders | table | app_owner
sales | payments | table | app_owner
(3 rows)
ANALYZESame counts, same total, same owner: the copy is identical. The ANALYZE matters. By default a dump does not carry the planner's statistics, so a freshly restored database has none until autovacuum gets to it, and the first queries may get poor plans. PostgreSQL 18 adds a --statistics option to pg_dump that includes most of them (--no-statistics is the default), but the documentation still recommends running ANALYZE after a restore.
Custom format: one file, selective restores
Step 7: Dump shopdb in custom format
Where: on db1, as the postgres user.
pg_dump -d shopdb -Fc -f ~/backups/shopdb.dump
ls -lh ~/backups/shopdb.dump| Option | What it does |
|---|---|
| -Fc | Custom-format archive for pg_restore. Compressed with gzip by default. |
| -Z method[:level] | Optional. Choose gzip, lz4, zstd or none, and a level, for example -Z zstd:5. |
-rw-rw-r-- 1 postgres postgres 677K Nov 12 12:48 shopdb.dumpThe same data takes 677 kB instead of 2.6 MB. More useful than the size: pg_restore can read this archive's table of contents and restore any part of it, in any order.
Step 8: List the contents of the archive
Where: on db1, as the postgres user.
pg_restore -l ~/backups/shopdb.dump| Option | What it does |
|---|---|
| -l, --list | Print the table of contents (TOC) of the archive. Nothing is restored. The output can be edited and fed back with -L. |
;
; Archive created at 2026-11-12 12:48:33 UTC
; dbname: shopdb
; TOC Entries: 29
; Compression: gzip
; Dump Version: 1.16-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
; Dumped by pg_dump version: 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
;
;
; Selected TOC Entries:
;
6; 2615 16387 SCHEMA - sales app_owner
3427; 0 0 ACL - SCHEMA sales app_owner
217; 1259 16389 TABLE sales customers app_owner
3428; 0 0 ACL sales TABLE customers app_owner
216; 1259 16388 SEQUENCE sales customers_customer_id_seq app_owner
219; 1259 16397 TABLE sales orders app_owner
3429; 0 0 ACL sales TABLE orders app_owner
222; 1259 16421 VIEW sales daily_revenue app_owner
3430; 0 0 ACL sales TABLE daily_revenue app_owner
218; 1259 16396 SEQUENCE sales orders_order_id_seq app_owner
221; 1259 16408 TABLE sales payments app_owner
3431; 0 0 ACL sales TABLE payments app_owner
220; 1259 16407 SEQUENCE sales payments_payment_id_seq app_owner
3416; 0 16389 TABLE DATA sales customers app_owner
3418; 0 16397 TABLE DATA sales orders app_owner
3420; 0 16408 TABLE DATA sales payments app_owner
3432; 0 0 SEQUENCE SET sales customers_customer_id_seq app_owner
3433; 0 0 SEQUENCE SET sales orders_order_id_seq app_owner
3434; 0 0 SEQUENCE SET sales payments_payment_id_seq app_owner
3263; 2606 16395 CONSTRAINT sales customers customers_pkey app_owner
3266; 2606 16401 CONSTRAINT sales orders orders_pkey app_owner
3268; 2606 16414 CONSTRAINT sales payments payments_pkey app_owner
3264; 1259 16420 INDEX sales orders_customer_idx app_owner
3269; 2606 16402 FK CONSTRAINT sales orders orders_customer_id_fkey app_owner
3270; 2606 16415 FK CONSTRAINT sales payments payments_order_id_fkey app_ownerThe header tells you when the dump was taken, its format and compression, and which server and pg_dump versions made it. Each line after it is one item: the first number is the item's ID in the archive, then come its type (TABLE, TABLE DATA, INDEX, ACL for grants), schema, name and owner. Running pg_restore -l is also the quickest test that an archive file is readable.
Step 9: Restore the whole archive into a new database
Where: on db1, as the postgres user.
createdb -T template0 -O app_owner shopdb_restore
pg_restore -v -d shopdb_restore ~/backups/shopdb.dump
psql -X -d shopdb_restore -f ~/check.sql| Option | What it does |
|---|---|
| -d dbname | Connect to this database and restore into it. Without -d, pg_restore prints the SQL script instead. |
| -v | Verbose: print a progress line for each item. |
pg_restore: connecting to database for restore
pg_restore: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_restore: creating SCHEMA "sales"
pg_restore: creating TABLE "sales.customers"
pg_restore: creating SEQUENCE "sales.customers_customer_id_seq"
pg_restore: creating TABLE "sales.orders"
pg_restore: creating VIEW "sales.daily_revenue"
pg_restore: creating SEQUENCE "sales.orders_order_id_seq"
pg_restore: creating TABLE "sales.payments"
pg_restore: creating SEQUENCE "sales.payments_payment_id_seq"
pg_restore: processing data for table "sales.customers"
pg_restore: processing data for table "sales.orders"
pg_restore: processing data for table "sales.payments"
pg_restore: executing SEQUENCE SET customers_customer_id_seq
pg_restore: executing SEQUENCE SET orders_order_id_seq
pg_restore: executing SEQUENCE SET payments_payment_id_seq
pg_restore: creating CONSTRAINT "sales.customers customers_pkey"
pg_restore: creating CONSTRAINT "sales.orders orders_pkey"
pg_restore: creating CONSTRAINT "sales.payments payments_pkey"
pg_restore: creating INDEX "sales.orders_customer_idx"
pg_restore: creating FK CONSTRAINT "sales.orders orders_customer_id_fkey"
pg_restore: creating FK CONSTRAINT "sales.payments payments_order_id_fkey"
pg_restore: creating ACL "SCHEMA sales"
pg_restore: creating ACL "sales.TABLE customers"
pg_restore: creating ACL "sales.TABLE orders"
pg_restore: creating ACL "sales.TABLE daily_revenue"
pg_restore: creating ACL "sales.TABLE payments"
current_database | customers | orders | payments | order_total
------------------+-----------+--------+----------+--------------
shopdb_restore | 1000 | 50000 | 40000 | 251720646.65
(1 row)You can follow the restore order: schema and tables, then data, then sequence positions, then constraints and indexes, then grants. The check query matches the original.
Unlike psql with ON_ERROR_STOP, pg_restore keeps going after an error by default and only reports pg_restore: warning: errors ignored on restore: N at the end, with a non-zero exit status. Always look for that line. Add -e (--exit-on-error) to stop at the first error, or -1 to restore in a single transaction (which implies -e).
Step 10: Restore one dropped table
Where: on db1, as the postgres user. This is the most common real request: "someone dropped the payments table, bring it back". We simulate it in the restored copy, never in shopdb.
psql -X -d shopdb_restore -c 'DROP TABLE sales.payments;'DROP TABLEThe obvious tool is -t. Try it and look at what comes back:
pg_restore -d shopdb_restore -n sales -t payments ~/backups/shopdb.dump
psql -X -d shopdb_restore -c '\d sales.payments'| Option | What it does |
|---|---|
| -n sales | Only objects in the schema sales. |
| -t payments | Only the definition and/or data of the table named payments. |
Table "sales.payments"
Column | Type | Collation | Nullable | Default
------------+--------+-----------+----------+---------
payment_id | bigint | | not null |
order_id | bigint | | not null |
paid_on | date | | not null |
method | text | | not null |The 40,000 rows are back, but the table lost its identity column, its primary key, its foreign key and the grant to report_user. The documentation says so directly: with -t, pg_restore makes no attempt to restore the objects the table depends on or that hang off it. Those items have their own lines in the table of contents.
The reliable method is a list file: take the TOC from Step 8, keep only the lines for the payments table, and give that list to pg_restore with -L. Drop the half-restored table first.
psql -X -d shopdb_restore -c 'DROP TABLE sales.payments;'
pg_restore -l ~/backups/shopdb.dump | grep payment > ~/backups/payments.list
cat ~/backups/payments.listDROP TABLE
221; 1259 16408 TABLE sales payments app_owner
3431; 0 0 ACL sales TABLE payments app_owner
220; 1259 16407 SEQUENCE sales payments_payment_id_seq app_owner
3420; 0 16408 TABLE DATA sales payments app_owner
3434; 0 0 SEQUENCE SET sales payments_payment_id_seq app_owner
3268; 2606 16414 CONSTRAINT sales payments payments_pkey app_owner
3270; 2606 16415 FK CONSTRAINT sales payments payments_order_id_fkey app_ownerRead the list before you use it. Here it has exactly the seven items that make up the table: definition, grant, identity sequence, rows, sequence position, primary key and foreign key. You can also delete lines or comment them out with a semicolon.
pg_restore -d shopdb_restore -L ~/backups/payments.list ~/backups/shopdb.dump
psql -X -d shopdb_restore -c '\d sales.payments' \
-c "SELECT count(*) FROM sales.payments;" \
-c "SELECT has_table_privilege('report_user', 'sales.payments', 'SELECT');"| Option | What it does |
|---|---|
| -L list-file | Restore only the items listed in the file, in the order they appear. |
Table "sales.payments"
Column | Type | Collation | Nullable | Default
------------+--------+-----------+----------+------------------------------
payment_id | bigint | | not null | generated always as identity
order_id | bigint | | not null |
paid_on | date | | not null |
method | text | | not null |
Indexes:
"payments_pkey" PRIMARY KEY, btree (payment_id)
Foreign-key constraints:
"payments_order_id_fkey" FOREIGN KEY (order_id) REFERENCES sales.orders(order_id)
count
-------
40000
(1 row)
has_table_privilege
---------------------
t
(1 row)Now the table is complete: identity, primary key, foreign key, rows and grant. Keep in mind that the rows are as they were at dump time. Any payment inserted after the dump is not in it; a logical dump cannot replay later changes.
Large databases: directory format and parallel jobs
Step 11: Dump with 4 parallel jobs
Where: on db1, as the postgres user.
pg_dump -d shopdb -Fd -j 4 -f ~/backups/shopdb_dir
ls -l ~/backups/shopdb_dir| Option | What it does |
|---|---|
| -Fd | Directory-format archive: one file per table plus a table-of-contents file, toc.dat. |
| -j 4 | Dump 4 tables at the same time. Only works with the directory format. Opens 4 + 1 connections. |
| -f dir | The directory to create. It must not exist yet, or must be empty. |
total 684
-rw-rw-r-- 1 postgres postgres 4865 Nov 12 12:49 3416.dat.gz
-rw-rw-r-- 1 postgres postgres 455990 Nov 12 12:49 3418.dat.gz
-rw-rw-r-- 1 postgres postgres 223287 Nov 12 12:49 3420.dat.gz
-rw-rw-r-- 1 postgres postgres 7657 Nov 12 12:49 toc.datEach .dat.gz file holds the rows of one table, named by its TOC ID: 3416 is customers, 3418 orders and 3420 payments, the same IDs you saw in Step 8. Parallel dumping helps when a database has several large tables. One huge table is still dumped by one job.
Two cautions from the documentation. First, pg_dump opens njobs + 1 connections, so check max_connections (Part 10). Second, the leader process holds shared locks on every table it will dump. If another session then asks for an exclusive lock on one of those tables, the dump can fail, so avoid running DDL during a parallel dump.
Step 12: Restore with 4 parallel jobs
Where: on db1, as the postgres user.
createdb -T template0 -O app_owner shopdb_par
pg_restore -d shopdb_par -j 4 ~/backups/shopdb_dir
psql -X -d shopdb_par -f ~/check.sql| Option | What it does |
|---|---|
| -j 4 | Load data, build indexes and create constraints in up to 4 sessions at once. Works with custom and directory archives, even if the dump itself was not parallel. |
current_database | customers | orders | payments | order_total
------------------+-----------+--------+----------+--------------
shopdb_par | 1000 | 50000 | 40000 | 251720646.65
(1 row)On a large database, restore time is mostly spent loading rows and building indexes, and that is exactly what -j spreads across CPU cores. A common starting point is the number of CPU cores on the target server.
pg_dumpall: roles, tablespaces and the whole cluster
Step 13: Dump the roles with --globals-only
Where: on db1, as the postgres user. Roles and tablespaces are global objects: they belong to the cluster, not to one database, so pg_dump never includes them. The documentation says that pg_dumpall --globals-only is necessary to fully back up the cluster when you use pg_dump for the databases.
pg_dumpall --globals-only -f ~/backups/globals.sql
cat ~/backups/globals.sql| Option | What it does |
|---|---|
| -g, --globals-only | Dump only global objects (roles and tablespaces), no databases. |
| -r, --roles-only | Dump only roles. |
| --no-role-passwords | Leave out password hashes. Restored roles have no password until you set one. |
--
-- PostgreSQL database cluster dump
--
\restrict z2oxBLjZ1bGskoTj9HiHVF8X3jyQ1MtewsLLkN5TqrP48i7VXQnbh8MrAcbKDgz
SET default_transaction_read_only = off;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
--
-- Roles
--
CREATE ROLE app_owner;
ALTER ROLE app_owner WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:YOyqqrJ25B0Z2brV5tSe+g==$1V1TeUwJmLY36Mc1IpHz9nOngaL1l02cJnx/aX9cBs8=:+fg0w9B0Qq1Qw5pGaf2lhDudSVMMYGqoFZLP3A5QnO0=';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS;
CREATE ROLE report_user;
ALTER ROLE report_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:DjOcmWSFV+lY+yQKig31eA==$M7e0W5IqGCjx49snEE60/2rSSE6tDVVjbUK99W69AGo=:n+5+bvgCywlGlNQ83T8mtVtNU7iSCQuzqTe3qdI19bs=';
--
-- User Configurations
--
\unrestrict z2oxBLjZ1bGskoTj9HiHVF8X3jyQ1MtewsLLkN5TqrP48i7VXQnbh8MrAcbKDgz
--
-- PostgreSQL database cluster dump complete
--Every role is here with its attributes and its SCRAM password hash (Part 6), so users keep their passwords after a restore. If you had tablespaces (Part 9), CREATE TABLESPACE commands would appear too, with their directory paths.
Step 14: Move shopdb to a new server with its roles
Now the full "new server" drill. We create a second cluster named restore on port 5433 to play the new server. On a real second machine you would use -h with its address instead of -p 5433.
db1:5432 (18 main) --pg_dump/pg_dumpall--> ~/backups --psql/pg_restore--> db1:5433 (18 restore)Where: on db1, as your sudo user (type exit to leave the postgres shell first).
sudo pg_createcluster 18 restore --port 5433 --start
pg_lsclusters| Option | What it does |
|---|---|
| 18 restore | Create a cluster named restore for PostgreSQL 18, with data in /var/lib/postgresql/18/restore and configuration in /etc/postgresql/18/restore. |
| --port 5433 | Listen on port 5433 so it does not clash with 18 main. |
| --start | Start the new cluster right after creating it. |
Ver Cluster Port Status Owner Data directory Log file
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
18 restore 5433 online postgres /var/lib/postgresql/18/restore /var/log/postgresql/postgresql-18-restore.logWhere: on db1, as the postgres user. First restore the roles. Connect to the postgres database of the new cluster:
sudo -i -u postgres
psql -X -p 5433 -d postgres -f ~/backups/globals.sqlSET
SET
SET
CREATE ROLE
ALTER ROLE
psql:/var/lib/postgresql/backups/globals.sql:18: ERROR: role "postgres" already exists
ALTER ROLE
CREATE ROLE
ALTER ROLEThe one error is expected and harmless: every new cluster already has the postgres role. The ALTER ROLE right after it still runs. Every other role is created.
Now restore the database. With -C, pg_restore creates the database itself, using the name, owner and settings stored in the archive. The database named with -d is only used to run CREATE DATABASE.
pg_restore -p 5433 -C -d postgres ~/backups/shopdb.dump
psql -X -p 5433 -d shopdb -f ~/check.sql
psql -X -p 5433 -c '\du app_owner|report_user'| Option | What it does |
|---|---|
| -p 5433 | Connect to the server on port 5433. |
| -C | Create the database before restoring into it. |
| -d postgres | With -C, only used to issue CREATE DATABASE; the data goes into the database named in the archive (shopdb). |
current_database | customers | orders | payments | order_total
------------------+-----------+--------+----------+--------------
shopdb | 1000 | 50000 | 40000 | 251720646.65
(1 row)
List of roles
Role name | Attributes
-------------+------------
app_owner |
report_user |Same rows, same total, and both roles exist with their passwords. The order matters: globals first, then databases. The Common mistakes section shows what happens if you swap it.
Step 15: Dump and restore a whole cluster with pg_dumpall
Where: on db1, as the postgres user. pg_dumpall without options writes roles, tablespaces and every database into one plain SQL script. It is convenient for small clusters and for major-version upgrades by dump and restore.
pg_dumpall -f ~/backups/cluster_all.sql
ls -lh ~/backups/cluster_all.sql-rw-rw-r-- 1 postgres postgres 2.6M Nov 12 12:49 cluster_all.sqlTo restore it into an empty cluster, run it with psql while connected to the postgres database. The documentation notes that restoring a pg_dumpall file always needs superuser access, because it creates roles and tablespaces.
psql -X -p 5433 -d postgres -f ~/backups/cluster_all.sqlExpect the same harmless role "postgres" already exists error as in Step 14. Two limits to know: pg_dumpall writes only plain SQL, so you cannot restore one table from it with pg_restore, and each database is dumped with its own snapshot, so the databases are not consistent with each other in time. For most production setups, globals.sql plus one custom-format dump per database (next section) is more flexible.
Schedule a nightly logical backup
Step 16: Write the backup script
Where: on db1, as the postgres user. Create the script /var/lib/postgresql/bin/pg_logical_backup.sh. It dumps the globals, then every database that accepts connections in custom format, checks that each archive can be read, and deletes folders older than 7 days.
#!/bin/bash
# Nightly logical backup: roles + one custom-format dump per database
set -euo pipefail
umask 077
BACKUP_ROOT=/var/lib/postgresql/backups/nightly
KEEP_DAYS=7
DAY=$(date +%F)
DEST="$BACKUP_ROOT/$DAY"
mkdir -p "$DEST"
pg_dumpall --globals-only -f "$DEST/globals.sql"
for db in $(psql -X -At -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate ORDER BY 1"); do
pg_dump -Fc -d "$db" -f "$DEST/$db.dump"
pg_restore -l "$DEST/$db.dump" > /dev/null # fails if the archive is unreadable
done
find "$BACKUP_ROOT" -mindepth 1 -maxdepth 1 -type d -mtime +"$KEEP_DAYS" -exec rm -rf {} +
echo "$(date '+%F %T') backup OK: $DEST"| Line | What it does |
|---|---|
| set -euo pipefail | Stop the script at the first failing command, so a failed dump is never reported as OK. |
| umask 077 | New files can be read only by postgres. |
| datallowconn AND NOT datistemplate | Skip template0 and template1; dump every real database. |
| pg_restore -l ... > /dev/null | A quick readability check of each archive. It is not a full restore test (that is Part 22). |
| find ... -mtime +7 ... rm -rf | Delete day folders whose last change is more than 7 days old. |
mkdir -p ~/bin
nano ~/bin/pg_logical_backup.sh
chmod 700 ~/bin/pg_logical_backup.sh
~/bin/pg_logical_backup.sh
ls -l ~/backups/nightly/*2026-11-12 12:50:05 backup OK: /var/lib/postgresql/backups/nightly/2026-11-12
total 688
-rw------- 1 postgres postgres 1072 Nov 12 12:50 globals.sql
-rw------- 1 postgres postgres 1113 Nov 12 12:50 postgres.dump
-rw------- 1 postgres postgres 692733 Nov 12 12:50 shopdb.dumpOne folder per day, with the globals and one archive per database, readable only by postgres. To test the retention, create a folder dated 1 November and run the script again:
mkdir ~/backups/nightly/2026-11-01 && touch -d 2026-11-01 ~/backups/nightly/2026-11-01
ls ~/backups/nightly
~/bin/pg_logical_backup.sh
ls ~/backups/nightly2026-11-01
2026-11-12
2026-11-12 12:50:14 backup OK: /var/lib/postgresql/backups/nightly/2026-11-12
2026-11-12The 11-day-old folder is gone and today's folder stays.
Step 17: Run it every night with cron
Where: on db1, as the postgres user. Open the postgres user's crontab with crontab -e and add this line. It runs the script at 01:30 every night and appends its messages to a log file.
30 1 * * * /var/lib/postgresql/bin/pg_logical_backup.sh >> /var/lib/postgresql/backups/backup.log 2>&1Check it with crontab -l: it prints the line you saved. The next morning, tail ~/backups/backup.log should show a "backup OK" line. If a dump fails, the script stops before printing it, so a missing "backup OK" line is your alarm. To undo, run crontab -e again and delete the line.
Step 18: Clean up the lab
Where: on db1, the first command as the postgres user, the second as your sudo user.
dropdb shopdb_copy
dropdb shopdb_restore
dropdb shopdb_par
exit
sudo pg_dropcluster 18 restore --stop
pg_lsclustersVer Cluster Port Status Owner Data directory Log file
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.logOnly 18 main remains. Keep shopdb and the backups folder if you want to do the practice exercise.
Common mistakes and how to fix them
pg_restore: error: input file appears to be a text format dump. Please use psql.
pg_restore -d shopdb_restore ~/backups/shopdb.sqlpg_restore: error: input file appears to be a text format dump. Please use psql.Plain dumps are SQL scripts, and only psql runs them. Use psql -X -d dbname -f file.sql. If you want pg_restore features, dump again with -Fc.
The input is a PostgreSQL custom-format dump.
psql -X -d shopdb_restore -f ~/backups/shopdb.dumpThe input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.The opposite mistake. Custom, directory and tar archives are binary files: restore them with pg_restore.
ERROR: role "app_owner" does not exist
pg_restore: error: could not execute query: ERROR: role "app_owner" does not exist
Command was: ALTER DATABASE shopdb OWNER TO app_owner;
pg_restore: error: could not execute query: ERROR: role "app_owner" does not exist
Command was: ALTER SCHEMA sales OWNER TO app_owner;This is what Step 14 looks like if you skip globals.sql. pg_restore carries on, so the tables are created, but owned by the user who ran the restore, and the grants are missing. Fix: drop the half-restored database, restore globals.sql with psql, then run pg_restore again. To restore deliberately without the old owners, use pg_restore -O -x (--no-owner, --no-privileges).
FATAL: Peer authentication failed for user "postgres"
pg_dump -U postgres -d shopdb -f /tmp/shopdb.sqlpg_dump: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"You ran pg_dump as your own Linux user. Peer authentication (Part 6) only lets Linux user postgres connect as database user postgres. Run it as postgres: sudo -u postgres pg_dump -d shopdb -f ~postgres/backups/shopdb.sql, or use a role with a password and a host line in pg_hba.conf.
pg_dump: error: directory ... is not empty / parallel backup only supported by the directory format
pg_dump: error: directory "/var/lib/postgresql/backups/shopdb_dir" is not empty
pg_dump: error: parallel backup only supported by the directory formatA directory dump must go to a new or empty directory, so use a new name (for example with the date) for each run. And -j only works with -Fd; for a custom-format archive, parallelism is available on the restore side with pg_restore -j.
ERROR: unrecognized configuration parameter "transaction_timeout"
psql:/var/lib/postgresql/backups/shopdb.sql:13: ERROR: unrecognized configuration parameter "transaction_timeout"This happens when you load a dump made by pg_dump 18 into an older server, here PostgreSQL 16, which has no transaction_timeout setting. The documentation is clear that loading a dump into an older major version is not supported. Dumps go forwards: dump with the pg_dump of the newer version, restore into the newer server.
The reverse also fails: an older pg_dump refuses to dump a newer server and stops with pg_dump: error: aborting because of server version mismatch. On Ubuntu, the pg_dump command is a wrapper from postgresql-common that picks the client version; call /usr/lib/postgresql/18/bin/pg_dump directly if you need to be sure which one runs.
Restoring a single table with -t and losing its keys
As Step 10 showed, pg_restore -t brings back the table and its rows, but not its primary key, foreign keys, identity sequence or grants. Use a list file (-l, edit, -L) for single-table restores.
Practice exercise
- Make a custom-format dump of shopdb that contains only the
sales.ordersandsales.customerstables (definitions and data), and check its table of contents. - Restore only the data of
sales.customersfrom~/backups/shopdb.dumpas a plain SQL script on screen, without touching any database, and count the data lines.
Quick reference
# become the postgres user and create a private backup folder
sudo -i -u postgres
mkdir -p ~/backups && chmod 700 ~/backups
# plain SQL dump, and restore it into a new database in one transaction
pg_dump -d shopdb -f ~/backups/shopdb.sql
createdb -T template0 -O app_owner shopdb_copy
psql -X --set ON_ERROR_STOP=on -1 -d shopdb_copy -f ~/backups/shopdb.sql
psql -X -d shopdb_copy -c 'ANALYZE;'
# custom-format dump, list it, restore it
pg_dump -d shopdb -Fc -f ~/backups/shopdb.dump
pg_restore -l ~/backups/shopdb.dump
createdb -T template0 -O app_owner shopdb_restore
pg_restore -v -d shopdb_restore ~/backups/shopdb.dump
# restore one table with all its pieces using a list file
pg_restore -l ~/backups/shopdb.dump | grep payment > ~/backups/payments.list
pg_restore -d shopdb_restore -L ~/backups/payments.list ~/backups/shopdb.dump
# parallel dump and restore (directory format)
pg_dump -d shopdb -Fd -j 4 -f ~/backups/shopdb_dir
createdb -T template0 -O app_owner shopdb_par
pg_restore -d shopdb_par -j 4 ~/backups/shopdb_dir
# roles and tablespaces, then a whole cluster
pg_dumpall --globals-only -f ~/backups/globals.sql
pg_dumpall -f ~/backups/cluster_all.sql
# move to a new cluster: globals first, then the database
psql -X -p 5433 -d postgres -f ~/backups/globals.sql
pg_restore -p 5433 -C -d postgres ~/backups/shopdb.dump
# nightly backup script and cron line (crontab -e as postgres)
~/bin/pg_logical_backup.sh
# 30 1 * * * /var/lib/postgresql/bin/pg_logical_backup.sh >> /var/lib/postgresql/backups/backup.log 2>&1
# clean up the lab (as your sudo user for pg_dropcluster)
dropdb shopdb_copy; dropdb shopdb_restore; dropdb shopdb_par
sudo pg_dropcluster 18 restore --stopSummary and what's next
- A logical backup is a set of SQL commands. It moves across versions and machines, but restores only to the moment the dump started.
- pg_dump gives a consistent snapshot of one database without blocking normal reads and writes.
- Use -Fc for everyday dumps, -Fd with -j for big databases, plain SQL only when a person needs to read it.
- Create the target from template0, restore plain dumps with psql -X, ON_ERROR_STOP and -1, and archives with pg_restore.
- pg_restore keeps going after errors: look for "errors ignored on restore", or use -e or -1.
- Restore a single table with a list file (-l, edit, -L), not with -t alone.
- pg_dump does not include roles or tablespaces: always keep a pg_dumpall --globals-only file, and restore it first.
- Run ANALYZE after a restore, and check every restore against row counts or totals.
In Part 18 we move to physical backups with pg_basebackup, which copy the whole cluster and become the base for point-in-time recovery.