PostgreSQL DBA Part 18: Physical Backups with pg_basebackup

PostgreSQL DBA Part 18: Physical Backups with pg_basebackup
In this article
  1. What you will learn
  2. Prerequisites
  3. How it works
  4. Prepare the primary server
  5. Take and verify a plain-format backup
  6. Restore the backup into a second cluster
  7. Compressed tar backups
  8. Incremental backups (PostgreSQL 17 and later)
  9. Run backups in production
  10. Common mistakes and how to fix them
  11. Practice exercise
  12. Quick reference
  13. Frequently asked questions
  14. Summary and what's next

This is Part 18 of the PostgreSQL DBA series. pg_basebackup takes a physical backup of a running PostgreSQL cluster: a copy of the data files themselves, taken over a normal database connection without stopping the server. By the end of this part you will be able to take a plain or compressed backup, check it with pg_verifybackup, restore it into a second cluster and prove the data is there, and use the incremental backups that arrived in PostgreSQL 17.

In Part 17 (Logical backups with pg_dump, pg_dumpall and pg_restore) we exported databases as SQL and archive files. A logical dump is a set of instructions to rebuild the data. A physical backup is a photocopy of the files. That photocopy restores much faster on a large database, and it is the starting point for WAL archiving, point-in-time recovery and replication in the next parts.

What you will learn

  • The difference between a physical and a logical backup, and when each one fits
  • How to prepare the server: a REPLICATION role, pg_hba.conf and a .pgpass file
  • How to take plain and compressed tar backups with pg_basebackup and what each option does
  • How to verify a backup with pg_verifybackup and restore it into a second cluster
  • How incremental backups work with summarize_wal and pg_combinebackup
  • How to watch a running backup and schedule a nightly one

Prerequisites

  • Parts 1 to 17 of this series, especially Part 3 (the data directory), Part 6 (pg_hba.conf), Part 7 (roles) and Part 15 (WAL).
  • One Ubuntu server with PostgreSQL 18 from the PGDG repository and the cluster 18 main running on port 5432, as installed in Part 2.
  • sudo rights, and about 200 MB of free disk space for the lab backups.
  • In production the backups go to a separate backup host or disk. In this lab one machine plays both roles, and the restore test runs in a second cluster on port 5433.

How it works

A physical backup copies the files under the data directory, /var/lib/postgresql/18/main, byte for byte. Think of it as photocopying a notebook while someone is still writing in it. Some pages will be copied before a change and some after, so the copy is not consistent on its own.

PostgreSQL solves this with the write-ahead log (WAL) from Part 15. Every change is first written to WAL. pg_basebackup asks the server for a checkpoint, notes the WAL position where the backup starts, copies all the files, and notes where it ends. When you start a server on the copy, it replays the WAL between those two positions and the half-copied pages become consistent again. That is why a backup without its WAL is useless.

By default pg_basebackup opens two connections to the server. One streams the data files. The other streams the WAL generated while the copy runs, using a temporary replication slot (a marker that stops the server from deleting WAL the backup still needs). Both use the replication protocol, the same protocol a standby server uses, which is why the setup steps talk about replication.

primary db1 (10.0.0.11:5432)  --data files + WAL (replication protocol)-->  backup host bk1 (10.0.0.13)
                                                                               /var/lib/postgresql/backups/
The setup this part builds (in the lab both run on one machine)

Physical and logical backups solve different problems. Most production systems keep both.

Physical (pg_basebackup)Logical (pg_dump)
What it copiesThe whole cluster: every database, roles, all filesOne database (or the cluster with pg_dumpall), as SQL
Restore speedFast: copy files and start the serverSlow on big databases: rows are reloaded, indexes rebuilt
Single table restoreNoYes
Restore to another major versionNo, same major version onlyYes
Point-in-time recoveryYes, together with archived WAL (Part 19 and 20)No
Server must be runningYes, it connects like a clientYes

Two files make a backup self-describing. backup_label records where the WAL replay must start. backup_manifest lists every file with its size and a checksum, so pg_verifybackup can later prove that nothing is missing or damaged.

Prepare the primary server

Step 1: Create sample data to back up

On db1, as the postgres user. We need some data so we can prove later that the restore worked. This creates a database with one table of 100,000 orders.

sudo -u postgres psql
CREATE DATABASE shopdb;
\c shopdb
CREATE TABLE orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer    text          NOT NULL,
    amount      numeric(10,2) NOT NULL,
    created_at  timestamptz   NOT NULL DEFAULT now()
);
INSERT INTO orders (customer, amount)
SELECT 'customer_' || (g % 500), round((random() * 5000)::numeric, 2)
  FROM generate_series(1, 100000) AS g;
SELECT count(*) AS orders,
       pg_size_pretty(pg_database_size('shopdb')) AS db_size
  FROM orders;
Create shopdb and 100,000 orders
 orders | db_size 
--------+---------
 100000 | 16 MB
(1 row)
Output

The database holds exactly 100,000 rows. Remember that number: it is our check after every restore.

Step 2: Check the settings pg_basebackup needs

On db1, in psql as postgres. The documentation lists the requirements: a role with the REPLICATION attribute, a pg_hba.conf entry for replication connections, and enough max_wal_senders for one connection for the backup plus one for WAL streaming. wal_level must be replica or higher.

SELECT name, setting, context
  FROM pg_settings
 WHERE name IN ('wal_level', 'max_wal_senders', 'max_replication_slots',
                'summarize_wal', 'full_page_writes')
 ORDER BY name;
Check the backup-related settings
         name          | setting |  context   
-----------------------+---------+------------
 full_page_writes      | on      | sighup
 max_replication_slots | 10      | postmaster
 max_wal_senders       | 10      | postmaster
 summarize_wal         | off     | sighup
 wal_level             | replica | postmaster
(5 rows)
Output

These are the PostgreSQL 18 defaults and they are already correct for backups. A context of postmaster means the setting needs a restart; sighup means a reload is enough (Part 5). If someone set wal_level = minimal or max_wal_senders = 0, change them in /etc/postgresql/18/main/postgresql.conf and run sudo systemctl restart postgresql@18-main. We will turn on summarize_wal later for incremental backups.

Step 3: Create a dedicated backup role

On db1, in psql as postgres. You could run backups as the postgres superuser, but a separate role with only the REPLICATION attribute is safer: if its password leaks, it cannot read or change tables through a normal connection.

CREATE ROLE backup_user WITH LOGIN REPLICATION PASSWORD 'Backup#2026';
\du backup_user
Create the backup role
CREATE ROLE
       List of roles
  Role name  | Attributes  
-------------+-------------
 backup_user | Replication
Output

Undo: DROP ROLE backup_user; Use your own strong password in production.

Step 4: Allow replication connections in pg_hba.conf

On db1, in psql as postgres. A replication connection only matches pg_hba.conf lines whose database column is the keyword replication. A line with all does not cover it. Check what the Ubuntu package created:

SELECT line_number, type, database, user_name, address, auth_method
  FROM pg_hba_file_rules
 WHERE 'replication' = ANY(database);
List replication rules in pg_hba.conf
 line_number | type  |   database    | user_name |  address  |  auth_method  
-------------+-------+---------------+-----------+-----------+---------------
         130 | local | {replication} | {all}     |           | peer
         131 | host  | {replication} | {all}     | 127.0.0.1 | scram-sha-256
         132 | host  | {replication} | {all}     | ::1       | scram-sha-256
(3 rows)
Output

Line 131 already allows our lab connection from 127.0.0.1 with a password. For a real backup host, add one line for it at the end of the file:

# /etc/postgresql/18/main/pg_hba.conf
# TYPE  DATABASE     USER         ADDRESS          METHOD
host    replication  backup_user  10.0.0.13/32     scram-sha-256
Line to add on db1 for backup host bk1
sudo systemctl reload postgresql@18-main
Apply pg_hba.conf changes (reload is enough)

A remote host also needs listen_addresses to include the server's IP address, which needs a restart (Part 6). Undo: delete the line and reload again.

Step 5: Store the password and create the backup folder

On the backup host (in the lab, db1), as the postgres OS user. Backups run unattended, so nobody can type a password. A .pgpass file in the home directory supplies it. For replication connections the database field must be the word replication.

sudo -u postgres bash
cd ~
echo "127.0.0.1:5432:replication:backup_user:Backup#2026" > ~/.pgpass
chmod 600 ~/.pgpass
mkdir -p /var/lib/postgresql/backups
chmod 700 /var/lib/postgresql/backups
Run as postgres: .pgpass and backup folder

The format is host:port:database:user:password. libpq ignores the file if anyone other than the owner can read it, hence chmod 600. On a separate backup host, use db1's address (10.0.0.11) instead of 127.0.0.1 here and in every command below. Verify that the role, pg_hba.conf and password all work by opening a replication connection and running IDENTIFY_SYSTEM:

psql "host=127.0.0.1 port=5432 user=backup_user dbname=replication replication=true" -w -c "IDENTIFY_SYSTEM;"
Test a replication connection
      systemid       | timeline |  xlogpos   | dbname 
---------------------+----------+------------+--------
 7707636659200669857 |        1 | 0/11000060 | 
(1 row)
Output

The systemid is the unique identifier of this cluster. You will see the same number inside the backup manifest. Your number and WAL position will differ.

Take and verify a plain-format backup

Step 6: Run pg_basebackup

On the backup host, as postgres. A plain-format backup is a ready-to-use copy of the data directory. Ubuntu puts pg_basebackup on the PATH through its wrapper script, so you can call it directly.

pg_basebackup -h 127.0.0.1 -p 5432 -U backup_user -w \
  -D /var/lib/postgresql/backups/base_2026_11_13 \
  -Fp -X stream -c fast \
  -l "shopdb full 2026-11-13" -P -v
Take a plain-format base backup
OptionWhat it does
-h, -p, -UServer host, port and user name to connect as
-wNever prompt for a password; fail if none is available (from .pgpass). Right for scripts
-D directoryWhere to write the backup. Created if missing; if it exists it must be empty
-FpPlain format: files with the same layout as the data directory (the default)
-X streamStream the WAL generated during the backup over a second connection (the default), so the backup is self-contained
-c fastRequest an immediate checkpoint. The default, spread, can make the backup wait for minutes before copying
-l labelA label stored in backup_label. Default: pg_basebackup base backup
-PShow approximate progress
-vVerbose: print each start and finish stage
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2337"
40507/40507 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/3000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
Output

You can see every stage from How it works: the checkpoint, the WAL start point, the temporary replication slot for WAL streaming, the file copy, the WAL end point and the manifest. The command returns exit code 0 on success, which is what a script should check.

Step 7: Look inside the backup

On the backup host, as postgres. List the folder and read the label:

cd /var/lib/postgresql/backups/base_2026_11_13
ls
cat backup_label
Inspect the backup
PG_VERSION	 global        pg_multixact  pg_snapshots  pg_tblspc	postgresql.auto.conf
backup_label	 pg_commit_ts  pg_notify     pg_stat	   pg_twophase
backup_manifest  pg_dynshmem   pg_replslot   pg_stat_tmp   pg_wal
base		 pg_logical    pg_serial     pg_subtrans   pg_xact
START WAL LOCATION: 0/3000028 (file 000000010000000000000003)
CHECKPOINT LOCATION: 0/3000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-11-13 12:48:03 UTC
LABEL: shopdb full 2026-11-13
START TIMELINE: 1
Output

It looks like the data directory from Part 3, plus backup_label and backup_manifest. pg_wal holds the streamed WAL segment 000000010000000000000003. The files that only describe the running server, such as postmaster.pid, are left out, and pg_replslot is empty.

Step 8: Verify the backup with pg_verifybackup

On the backup host, as postgres. pg_verifybackup reads backup_manifest, checks that every listed file exists with the right size, recomputes the checksums, and checks that the WAL needed for recovery is present and can be parsed. Ubuntu's wrapper does not cover this tool, so use its full path:

/usr/lib/postgresql/18/bin/pg_verifybackup /var/lib/postgresql/backups/base_2026_11_13
Verify the plain backup
backup successfully verified
Output

To see what a failure looks like, we copied the backup, changed one byte in the orders table file and deleted one small file, then verified the copy:

pg_verifybackup: error: "base/16385/PG_VERSION" is present in the manifest but not on disk
pg_verifybackup: error: checksum mismatch for file "base/16385/16387"
Output (damaged copy, exit code 1)

It names each problem file and exits with code 1. Note the documentation's own warning: verification cannot run every check a real server would, so you should still do test restores. That is the next section.

Restore the backup into a second cluster

A backup you have never restored is only a hope. We will restore it into a new cluster called 18 restore on port 5433, so the original 18 main keeps running. On a real recovery you would restore onto the replacement server instead.

Step 9: Create an empty target cluster and replace its data directory

On the restore server (in the lab, db1), as a sudo user. First create the cluster. Ubuntu's tool also writes its configuration in /etc/postgresql/18/restore/ and picks port 5433.

sudo pg_createcluster 18 restore --port 5433
pg_lsclusters
Create the restore cluster
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 down   postgres /var/lib/postgresql/18/restore /var/log/postgresql/postgresql-18-restore.log
Output
sudo -u postgres bash -c 'rm -rf /var/lib/postgresql/18/restore/*'
sudo -u postgres cp -a /var/lib/postgresql/backups/base_2026_11_13/. /var/lib/postgresql/18/restore/
sudo chmod 700 /var/lib/postgresql/18/restore
sudo ls -ld /var/lib/postgresql/18/restore
Copy the backup into the empty data directory
drwx------ 19 postgres postgres 4096 Nov 13 12:48 /var/lib/postgresql/18/restore
Output
Command partWhat it does
rm -rf .../restore/*Empties the new cluster's data directory, as the documentation's restore procedure requires
cp -aCopies recursively and keeps owner, permissions and timestamps
chmod 700PostgreSQL refuses to start unless the data directory is 0700 or 0750

The files must be owned by postgres, not root, which is why the copy runs as postgres.

Step 10: Start the restored cluster and check the data

On the restore server, as a sudo user. To prove the restore returns the state at backup time, first add one row on the primary, which is not in the backup:

sudo -u postgres psql -d shopdb -c "INSERT INTO orders (customer, amount) VALUES ('after_backup', 99.00);"
On db1: a change made after the backup

The primary now has 100,001 rows. Start the restored cluster and read its log:

sudo pg_ctlcluster 18 restore start
sudo grep -E "recovery|redo|ready" /var/log/postgresql/postgresql-18-restore.log
Start the restored cluster
LOG:  starting backup recovery with redo LSN 0/3000028, checkpoint LSN 0/3000080, on timeline ID 1
LOG:  redo starts at 0/3000028
LOG:  completed backup recovery with redo LSN 0/3000028 and end LSN 0/3000120
LOG:  consistent recovery state reached at 0/3000120
LOG:  redo done at 0/3000120 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOG:  checkpoint starting: end-of-recovery immediate wait
LOG:  database system is ready to accept connections
Output (timestamps and PIDs removed from each line)

The server found backup_label ("starting backup recovery"), replayed the WAL from 0/3000028 to 0/3000120 (the exact start and end points pg_basebackup printed), reached a consistent state and opened. Afterwards it renames the label to backup_label.old. Now check the data:

sudo -u postgres psql -p 5433 -d shopdb -c "SELECT count(*) AS orders, count(*) FILTER (WHERE customer = 'after_backup') AS after_backup FROM orders;"
Count rows in the restored copy
 orders | after_backup 
--------+--------------
 100000 |            0
(1 row)
Output

All 100,000 rows are back, and the row added after the backup is not there. A base backup on its own restores the cluster as it was at the end of the backup. To replay changes made after it, up to any chosen moment, you need archived WAL, which is Parts 19 and 20.

Compressed tar backups

Plain format is easy to restore but large. Tar format writes one archive file per tablespace, and it can be compressed, which suits copying to other storage.

Step 11: Take a compressed tar backup and verify it

On the backup host, as postgres.

pg_basebackup -h 127.0.0.1 -U backup_user -w \
  -D /var/lib/postgresql/backups/tar_2026_11_13 \
  -Ft -z -X stream -c fast -l "shopdb tar 2026-11-13" -P
ls -lh /var/lib/postgresql/backups/tar_2026_11_13
Tar format, gzip compressed
OptionWhat it does
-FtTar format: base.tar for the data directory, one tar per extra tablespace (named by its OID), and pg_wal.tar for streamed WAL
-zgzip compression at the default level. Same as --compress=gzip. Use -Z / --compress for lz4 or zstd and a level, e.g. --compress=gzip:9
waiting for checkpoint
40604/40604 kB (100%), 1/1 tablespace
total 5.8M
-rw------- 1 postgres postgres 178K Nov 13 12:48 backup_manifest
-rw------- 1 postgres postgres 5.6M Nov 13 12:48 base.tar.gz
-rw------- 1 postgres postgres  18K Nov 13 12:48 pg_wal.tar.gz
Output

The same cluster took 56 MB in plain format and 5.8 MB here; our test data compresses unusually well, so expect less on real tables. Verifying tar backups is new in PostgreSQL 18. WAL parsing still works only for plain format, so add -n (--no-parse-wal) and -F t:

/usr/lib/postgresql/18/bin/pg_verifybackup -F t -n /var/lib/postgresql/backups/tar_2026_11_13
Verify a tar backup
backup successfully verified
Output

Step 12: Restore from tar files

On the restore server, as postgres (after stopping the cluster with sudo). A tar backup must be unpacked before the server can use it: base.tar into the data directory, pg_wal.tar into its pg_wal folder.

sudo pg_ctlcluster 18 restore stop
sudo -u postgres bash -c 'rm -rf /var/lib/postgresql/18/restore/*'
sudo -u postgres tar -xzf /var/lib/postgresql/backups/tar_2026_11_13/base.tar.gz -C /var/lib/postgresql/18/restore
sudo -u postgres tar -xzf /var/lib/postgresql/backups/tar_2026_11_13/pg_wal.tar.gz -C /var/lib/postgresql/18/restore/pg_wal
sudo pg_ctlcluster 18 restore start
sudo -u postgres psql -p 5433 -d shopdb -Atc "SELECT count(*) FROM orders"
Unpack and start
100001
Output

This backup was taken after the extra row was added, so the count is 100,001. With extra tablespaces you would unpack each OID-named tar into its tablespace location; the server recreates the links from the tablespace_map file inside base.tar.

Incremental backups (PostgreSQL 17 and later)

A full backup copies every file every time. An incremental backup copies only the blocks that changed since an earlier backup. To know which blocks changed, the server runs a WAL summarizer process that writes small summary files into pg_wal/summaries. You later glue the full backup and its incrementals into one normal backup with pg_combinebackup.

Step 13: Turn on summarize_wal

On db1, as a sudo user. summarize_wal defaults to off and only needs a reload.

# /etc/postgresql/18/main/postgresql.conf
# before
#summarize_wal = off		# run WAL summarizer process?
# after
summarize_wal = on		# run WAL summarizer process?
Edit postgresql.conf
sudo systemctl reload postgresql@18-main
sudo -u postgres psql -c "SELECT * FROM pg_get_wal_summarizer_state();"
Reload and check the summarizer
 summarized_tli | summarized_lsn | pending_lsn | summarizer_pid 
----------------+----------------+-------------+----------------
              1 | 0/5000000      | 0/6000060   |           2598
(1 row)
Output

A summarizer_pid means the process is running. Summaries are kept for wal_summary_keep_time, default 10 days, so take a full backup more often than that. Undo: set it back to off and reload.

Step 14: Take a full backup, change data, take an incremental

On the backup host, as postgres. The incremental needs WAL summaries covering everything since the full backup started, so take a new full backup after enabling the summarizer. Then add 2,000 rows on the primary and take the incremental.

pg_basebackup -h 127.0.0.1 -U backup_user -w -c fast \
  -D /var/lib/postgresql/backups/full_mon -l "full monday"

psql -d shopdb -c "INSERT INTO orders (customer, amount) SELECT 'tuesday_' || g, 10 FROM generate_series(1, 2000) AS g;"

pg_basebackup -h 127.0.0.1 -U backup_user -w -c fast \
  -D /var/lib/postgresql/backups/incr_tue -l "incremental tuesday" \
  -i /var/lib/postgresql/backups/full_mon/backup_manifest
tail -n 2 /var/lib/postgresql/backups/incr_tue/backup_label
du -sh --exclude=pg_wal /var/lib/postgresql/backups/full_mon /var/lib/postgresql/backups/incr_tue
Full backup, then incremental
OptionWhat it does
-i manifest, --incremental=manifestTake an incremental backup. The argument is the backup_manifest of the earlier backup it builds on (full or incremental). Needs a version 17 or later server
INCREMENTAL FROM LSN: 0/8000028
INCREMENTAL FROM TLI: 1
40M	/var/lib/postgresql/backups/full_mon
7.7M	/var/lib/postgresql/backups/incr_tue
Output

The label records which backup this one depends on. Without its WAL, the incremental is 7.7 MB against 40 MB for the full one; in this tiny test most of that is system catalog files, so on a large, mostly unchanged database the saving is much bigger. Changed tables appear as files named like INCREMENTAL.16387, which hold only the changed blocks.

Step 15: Combine the backups and restore the result

On the backup host, as postgres. List the backups from oldest to newest: the full backup first, the incremental you want to restore last.

/usr/lib/postgresql/18/bin/pg_combinebackup \
  /var/lib/postgresql/backups/full_mon \
  /var/lib/postgresql/backups/incr_tue \
  -o /var/lib/postgresql/backups/synthetic_tue
/usr/lib/postgresql/18/bin/pg_verifybackup /var/lib/postgresql/backups/synthetic_tue
Build a synthetic full backup
OptionWhat it does
backup directoriesThe full backup, then each incremental in order
-o dir, --output=dirWhere to write the synthetic full backup (required)
--copy (default), --clone, -k / --linkHow files are written. --link (new in 18) uses hard links: fast, but the output then shares files with the inputs
backup successfully verified
Output

pg_combinebackup writes a new manifest, so the result verifies like any plain backup. In a real restore you would first copy the backup folder to the target server (for example with rsync). Here it is on the same machine, so restore it exactly as in Steps 9 and 10 (stop the restore cluster, empty its data directory, copy synthetic_tue in, start), then check:

sudo -u postgres psql -p 5433 -d shopdb -c "SELECT count(*) AS orders, count(*) FILTER (WHERE customer LIKE 'tuesday_%') AS tuesday_rows FROM orders;"
Check the restored synthetic backup
 orders | tuesday_rows 
--------+--------------
 102001 |         2000
(1 row)
Output

The Tuesday rows are there, so the incremental carried the changes correctly.

Run backups in production

Step 16: Watch a running backup

On db1, in psql as postgres, while a backup runs. A backup of a large cluster can take hours. The view pg_stat_progress_basebackup shows how far it is. We slowed a test backup with -r 2M (--max-rate, which limits the transfer rate to protect disks and network) to catch it:

SELECT pid, phase,
       pg_size_pretty(backup_streamed) AS streamed,
       pg_size_pretty(backup_total)    AS total,
       round(100.0 * backup_streamed / backup_total, 1) AS pct
  FROM pg_stat_progress_basebackup;
Progress of a running backup
 pid  |          phase           | streamed | total | pct  
------+--------------------------+----------+-------+------
 3010 | streaming database files | 8434 kB  | 40 MB | 20.7
(1 row)
Output

The phase column moves through initializing, waiting for checkpoint to finish, estimating backup size, streaming database files and waiting for wal archiving to finish (plus transferring wal files when you use -X fetch). The sizes are counted before any server-side compression.

Step 17: Schedule a nightly backup

On the backup host, as postgres. This script takes a compressed tar backup, verifies it, saves the Ubuntu configuration folder next to it, and removes backups older than 7 days. Save it as /var/lib/postgresql/scripts/basebackup_nightly.sh.

#!/bin/bash
# Nightly physical backup of cluster 18 main (run as postgres)
set -euo pipefail
BACKUP_ROOT=/var/lib/postgresql/backups
TARGET="$BACKUP_ROOT/base_$(date +%Y%m%d_%H%M)"
KEEP_DAYS=7

pg_basebackup -h 127.0.0.1 -p 5432 -U backup_user -w \
  -D "$TARGET" -Ft -z -X stream -c fast \
  -l "nightly $(date +%F)"

/usr/lib/postgresql/18/bin/pg_verifybackup -F t -n -q "$TARGET"

# Ubuntu keeps the config files outside the data directory
mkdir "$TARGET-etc"
cp -a /etc/postgresql/18/main/. "$TARGET-etc"/

find "$BACKUP_ROOT" -maxdepth 1 -name "base_2*" \
  -mtime +"$KEEP_DAYS" -exec rm -rf {} +
echo "backup OK: $TARGET"
basebackup_nightly.sh
chmod 700 ~/scripts/basebackup_nightly.sh
~/scripts/basebackup_nightly.sh
Run it once by hand
backup OK: /var/lib/postgresql/backups/base_20261113_1250
Output

set -e stops the script at the first failing command, so "backup OK" only prints when the backup and the verification both succeeded. The config copy goes into a separate -etc folder because pg_verifybackup rejects extra files inside a tar backup folder. Then schedule it with crontab -e as postgres:

30 1 * * * /var/lib/postgresql/scripts/basebackup_nightly.sh >> /var/lib/postgresql/backups/basebackup.log 2>&1
crontab entry: every night at 01:30

Keeping backups on the same disk as the database protects against mistakes, not against losing the disk. Copy them to another server or object storage as well. Part 21 covers pgBackRest, which handles retention, remote storage and parallelism for you.

Step 18: Clean up the lab

sudo pg_dropcluster 18 restore --stop
sudo -u postgres rm -rf /var/lib/postgresql/backups/full_mon /var/lib/postgresql/backups/incr_tue /var/lib/postgresql/backups/synthetic_tue
pg_lsclusters
Remove the restore cluster and incremental test backups
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
Output

Keep backup_user and its pg_hba.conf entry: Part 19 builds on them. If you do not want incremental backups, set summarize_wal back to off and reload.

Common mistakes and how to fix them

pg_basebackup: error: directory "..." exists but is not empty

pg_basebackup: error: directory "/var/lib/postgresql/backups/full_mon" exists but is not empty
Error

pg_basebackup never overwrites. Use a new folder name for every backup, for example with the date and time as in the script.

FATAL: permission denied to start WAL sender

pg_basebackup: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  permission denied to start WAL sender
DETAIL:  Only roles with the REPLICATION attribute may start a WAL sender process.
Error

The role lacks REPLICATION. Fix: ALTER ROLE app_reader REPLICATION;, or better, use the dedicated backup role.

FATAL: no pg_hba.conf entry for replication connection

pg_basebackup: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  no pg_hba.conf entry for replication connection from host "127.0.0.1", user "backup_user", no encryption
Error

There is no line with replication in the database column for that host. A line with all does not count. Add one as in Step 4 and reload.

FATAL: data directory ... has invalid permissions

FATAL:  data directory "/var/lib/postgresql/18/restore" has invalid permissions
DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
Error in the restore cluster log

Copying as root or with a wide umask leaves the folder readable by others. Fix: sudo chown -R postgres:postgres /var/lib/postgresql/18/restore, then sudo chmod 700 /var/lib/postgresql/18/restore, and start again.

ERROR: incremental backups cannot be taken unless WAL summarization is enabled

pg_basebackup: error: could not initiate base backup: ERROR:  incremental backups cannot be taken unless WAL summarization is enabled
Error

Turn on summarize_wal (Step 13). If you then reuse a full backup taken before that, you get a second error:

pg_basebackup: error: could not initiate base backup: ERROR:  WAL summaries are required on timeline 1 from 0/3000028 to 0/7000028, but the summaries for that timeline and LSN range are incomplete
DETAIL:  The first unsummarized LSN in this range is 0/3000028.
Error

Take a new full backup and base the incrementals on it. The same error appears if the summaries were removed because the reference backup is older than wal_summary_keep_time.

pg_verifybackup: error: pg_waldump cannot read tar files

pg_verifybackup: error: pg_waldump cannot read tar files
pg_verifybackup: hint: You must use -n/--no-parse-wal when verifying a tar-format backup.
Error

Add -n as the hint says. Also, if pg_verifybackup or pg_combinebackup gives "command not found" on Ubuntu, call them from /usr/lib/postgresql/18/bin/.

pg_combinebackup: backups in the wrong order

pg_combinebackup: error: backup at "/var/lib/postgresql/backups/full_mon" is a full backup, but only the first backup should be a full backup
Error

List the full backup first, then the incrementals from oldest to newest.

Practice exercise

  1. Take a plain-format backup of your cluster with gzip-level-9 compression applied on the server side (hint: server-side compression is allowed with plain format), verify it, and restore it into a new cluster on port 5434.
  2. Insert 10 rows into shopdb.orders, take an incremental backup based on the full backup from task 1, combine the two and confirm the restored copy has the 10 extra rows.

Quick reference

# Check settings (psql as postgres)
SELECT name, setting, context FROM pg_settings WHERE name IN ('wal_level','max_wal_senders','summarize_wal');
# Create the backup role (psql as postgres)
CREATE ROLE backup_user WITH LOGIN REPLICATION PASSWORD 'Backup#2026';
# pg_hba.conf line for a backup host, then reload
#   host  replication  backup_user  10.0.0.13/32  scram-sha-256
sudo systemctl reload postgresql@18-main
# Password file for the postgres OS user
echo "127.0.0.1:5432:replication:backup_user:Backup#2026" > ~/.pgpass && chmod 600 ~/.pgpass
# Test the replication connection
psql "host=127.0.0.1 user=backup_user dbname=replication replication=true" -w -c "IDENTIFY_SYSTEM;"
# Plain-format backup
pg_basebackup -h 127.0.0.1 -U backup_user -w -D /var/lib/postgresql/backups/base_2026_11_13 -Fp -X stream -c fast -l "full" -P -v
# Verify a plain backup
/usr/lib/postgresql/18/bin/pg_verifybackup /var/lib/postgresql/backups/base_2026_11_13
# Restore into a test cluster
sudo pg_createcluster 18 restore --port 5433
sudo -u postgres bash -c 'rm -rf /var/lib/postgresql/18/restore/*'
sudo -u postgres cp -a /var/lib/postgresql/backups/base_2026_11_13/. /var/lib/postgresql/18/restore/
sudo chmod 700 /var/lib/postgresql/18/restore
sudo pg_ctlcluster 18 restore start
# Compressed tar backup and its verification
pg_basebackup -h 127.0.0.1 -U backup_user -w -D /var/lib/postgresql/backups/tar_2026_11_13 -Ft -z -X stream -c fast
/usr/lib/postgresql/18/bin/pg_verifybackup -F t -n /var/lib/postgresql/backups/tar_2026_11_13
# Restore from tar
tar -xzf base.tar.gz -C /var/lib/postgresql/18/restore
tar -xzf pg_wal.tar.gz -C /var/lib/postgresql/18/restore/pg_wal
# Incremental: enable summarize_wal = on in postgresql.conf, reload, then
pg_basebackup -h 127.0.0.1 -U backup_user -w -D /var/lib/postgresql/backups/incr_tue -i /var/lib/postgresql/backups/full_mon/backup_manifest
/usr/lib/postgresql/18/bin/pg_combinebackup /var/lib/postgresql/backups/full_mon /var/lib/postgresql/backups/incr_tue -o /var/lib/postgresql/backups/synthetic_tue
# Watch a running backup (psql)
SELECT phase, backup_streamed, backup_total FROM pg_stat_progress_basebackup;
# Remove the test cluster
sudo pg_dropcluster 18 restore --stop
Every command from this part, in order

Frequently asked questions

pg_basebackup vs pg_dump: which should I use?

Use both. pg_basebackup gives fast full-cluster restores and is the base for point-in-time recovery and standbys. pg_dump gives portable, per-database dumps that can restore a single table or move data to a newer major version.

Why is pg_basebackup stuck at waiting for checkpoint?

With the default --checkpoint=spread the server spreads the checkpoint over its normal schedule before copying starts, which can take minutes. Add -c fast to request an immediate checkpoint.

Can pg_basebackup back up a single database?

No. It always copies the whole cluster: every database, plus the roles and other shared objects. Use pg_dump for one database.

Does pg_basebackup run in parallel?

No, it copies files over a single connection (plus one for WAL). For parallel backup and restore of large clusters, use a tool such as pgBackRest (Part 21).

Summary and what's next

  • pg_basebackup copies the whole cluster's files while it runs, plus the WAL needed to make that copy consistent
  • It needs a REPLICATION role, a pg_hba.conf line with the replication keyword, and free max_wal_senders slots
  • Use -X stream (default) so every backup carries its own WAL, and -c fast if you cannot wait for a spread checkpoint
  • Verify every backup with pg_verifybackup (add -F t -n for tar backups) and still do regular test restores
  • On Ubuntu, back up /etc/postgresql/18/main/ separately: the config files are not in the data directory
  • Incremental backups need summarize_wal = on, a full backup taken after enabling it, and pg_combinebackup before restore
  • A base backup alone restores to the moment the backup ended

In Part 19 we set up WAL archiving with archive_mode and archive_command, so that changes made after a base backup are kept too.

PT Ganesh

An experienced PostgreSQL DBA/SQL developer with years of expertise in designing and implementing robust database solutions. Proficient in writing complex queries, developing stored procedures, and conducting performance tuning to deliver highly efficient data-driven applications that meet business requirements.

Home
SQL
PLSQL
Interview Q&A