PostgreSQL Backup and Restore on AlmaLinux 10: pg_dump, pg_dumpall and pg_basebackup

October 31, 2026 · by Zwiegnet Consulting · Database, Linux

PostgreSQL on AlmaLinux series, part 3. Previous: part 2, PostgreSQL Users, Roles and Privileges on AlmaLinux 10. Next up: streaming replication with a primary and a standby, and failover.

A PostgreSQL server without tested backups is an outage waiting to happen. PostgreSQL ships three backup tools, and each one answers a different question. pg_dump makes a logical backup of one database, pg_dumpall captures the cluster-wide objects (roles and tablespaces) that no single database dump contains, and pg_basebackup takes a physical copy of the entire data directory while the server keeps running. This guide shows how to use all three on AlmaLinux 10, how to restore from each, how to verify a backup, and how to schedule nightly dumps with a systemd timer.

Applies to: PostgreSQL 18 from the PGDG repository on AlmaLinux 10, as installed in part 1 (service postgresql-18, data directory /var/lib/pgsql/18/data, binaries in /usr/pgsql-18/bin). If you use the AppStream PostgreSQL 16 package instead, the service is postgresql and the data directory is /var/lib/pgsql/data; the commands are otherwise the same. Also applies to RHEL, Rocky Linux, Oracle Linux and CentOS Stream 10.

Prerequisites

  • A running PostgreSQL server on AlmaLinux 10 (lab host db1, 192.168.1.40), set up as in part 1.
  • The invdb database and roles (inv_owner, inv_read, inv_write, appsvc and friends) from part 2. Any database of your own works too.
  • Shell access with sudo on db1. Commands run as the postgres operating system user connect over the local socket with peer authentication, so no password is needed.
  • Free disk space for at least one full copy of your data. Keep backups on a different disk or host than the data directory in production.

Logical vs physical backups

ToolWhat it copiesRestore granularityGood for
pg_dumpOne database as SQL or an archiveWhole database, single schema or single tableDaily backups, moving data between major versions, restoring one table
pg_dumpallEvery database plus roles and tablespacesWhole cluster (or just the globals)Capturing roles and passwords with --globals-only
pg_basebackupThe physical data directory and the WAL needed to make it consistentWhole cluster onlyFast full restores, building standbys, point-in-time recovery

Logical dumps are portable across PostgreSQL major versions and CPU architectures, but restoring a large database means rebuilding every index. Physical backups restore quickly but only into the same major version. Most production setups use both.

Step 1: Create a backup directory

PGDG packages create /var/lib/pgsql/18/backups, owned by postgres and already carrying the right SELinux label. Use it for this guide, or mount dedicated backup storage there.

$ sudo -iu postgres
$ ls -ldZ /var/lib/pgsql/18/backups
$ mkdir -p /var/lib/pgsql/18/backups/{dumps,base}

Every command from here on runs as the postgres user unless it starts with sudo. If pg_dump is not on your PATH, use the full path such as /usr/pgsql-18/bin/pg_dump, or run export PATH=/usr/pgsql-18/bin:$PATH.

Step 2: Back up one database with pg_dump

Custom format (recommended)

The custom format (-Fc) is compressed, and pg_restore can pick objects out of it and restore in parallel. It is the best default for single-database backups.

$ pg_dump -Fc -d invdb -f /var/lib/pgsql/18/backups/dumps/invdb_$(date +%F).dump
$ ls -lh /var/lib/pgsql/18/backups/dumps/

pg_dump takes a consistent snapshot, so applications can keep reading and writing while it runs. It only blocks commands that need an exclusive lock, such as ALTER TABLE or DROP TABLE, on the tables being dumped.

Plain SQL format

A plain dump is a readable SQL script you restore with psql. It is handy for small databases, code review, or loading into another system, but it cannot be restored selectively or in parallel.

$ pg_dump -d invdb -f /var/lib/pgsql/18/backups/dumps/invdb.sql
$ pg_dump -d invdb | zstd > /var/lib/pgsql/18/backups/dumps/invdb.sql.zst

Directory format with parallel jobs

For large databases, the directory format (-Fd) is the only one that can dump in parallel. -j 4 uses four worker connections, so make sure max_connections has room for them.

$ pg_dump -Fd -j 4 -d invdb -f /var/lib/pgsql/18/backups/dumps/invdb_dir

Dump only part of a database

# One schema
$ pg_dump -Fc -d invdb -n inv -f inv_schema.dump

# One table (schema-qualified)
$ pg_dump -Fc -d invdb -t inv.items -f inv_items.dump

# Everything except a large log table's data
$ pg_dump -Fc -d invdb --exclude-table-data=inv.audit_log -f invdb_nolog.dump

# Schema definitions only, no data
$ pg_dump -d invdb --schema-only -f invdb_schema.sql

Dump from a remote client

You can also run pg_dump from another host, such as app1, with a role that can read everything in the database. Use a client package whose major version is the same as or newer than the server’s, because pg_dump cannot dump from a newer server.

$ pg_dump -h 192.168.1.40 -U inv_owner -Fc -d invdb -f invdb.dump

Step 3: Back up roles and tablespaces with pg_dumpall

Roles, role memberships, passwords and tablespaces live outside any database, so pg_dump never includes them. If you restore invdb onto a fresh server without them, every GRANT and ownership in the dump fails because the roles from part 2 do not exist. Capture them with --globals-only alongside every set of database dumps:

$ pg_dumpall --globals-only -f /var/lib/pgsql/18/backups/dumps/globals_$(date +%F).sql
$ grep -E '^(CREATE|ALTER) ROLE' /var/lib/pgsql/18/backups/dumps/globals_$(date +%F).sql

This file contains the SCRAM-SHA-256 password hashes of every role, so treat it as a secret: keep it mode 0600 and encrypt it if it leaves the server. Use --roles-only if you do not want tablespace definitions, or --no-role-passwords to leave the hashes out (the roles then need new passwords after a restore).

pg_dumpall without options dumps every database as one large plain SQL script. That works for small clusters, but per-database custom-format dumps plus a globals file are easier to restore selectively.

$ chmod 600 /var/lib/pgsql/18/backups/dumps/globals_*.sql

Step 4: Restore from a dump

Restore into a new database to test

Always test a restore into a scratch database first. The roles already exist on this server, so ownership and grants are restored as they were:

$ createdb invdb_restore
$ pg_restore -d invdb_restore -j 4 /var/lib/pgsql/18/backups/dumps/invdb_$(date +%F).dump
$ psql -d invdb_restore -c '\dt inv.*'
$ psql -d invdb_restore -c 'ANALYZE;'

PostgreSQL 18’s pg_dump does not include planner statistics by default (--no-statistics is the default), so run ANALYZE after a restore before you put load on the database.

Rebuild a database on a new server

On a freshly installed server, restore the globals first, then let pg_restore --create create the database with its original name, owner and settings:

$ psql -f globals_2026-10-31.sql postgres
$ pg_restore --create -d postgres -j 4 invdb_2026-10-31.dump

Expect a harmless role "postgres" already exists error from the globals file. To replace an existing database in place, add --clean --if-exists and stop applications first, because existing objects are dropped before they are recreated.

Restore a single table

List the archive contents, then restore only what you need. Here the table is restored into the scratch database so you can copy rows back with SQL:

$ pg_restore -l invdb_2026-10-31.dump | grep -i items
$ pg_restore -d invdb_restore -t items -n inv invdb_2026-10-31.dump

# Or build a custom list: edit invdb.list and comment out lines with ;
$ pg_restore -l invdb_2026-10-31.dump > invdb.list
$ pg_restore -d invdb_restore -L invdb.list invdb_2026-10-31.dump

Restore a plain SQL dump

$ createdb invdb_restore2
$ psql -v ON_ERROR_STOP=1 -d invdb_restore2 -f invdb.sql

ON_ERROR_STOP=1 makes psql stop at the first error instead of scrolling past hundreds of them. If you need an all-or-nothing restore, add --single-transaction (or -1) to psql or pg_restore.

Step 5: Take a physical backup with pg_basebackup

pg_basebackup connects using the replication protocol. Create a dedicated role for it instead of using postgres over the network:

$ psql -c "CREATE ROLE backup_repl WITH LOGIN REPLICATION;"
$ psql -c "\password backup_repl"

Local socket connections as postgres are already allowed for replication in the default pg_hba.conf. To run backups from another host, add a replication line in /var/lib/pgsql/18/data/pg_hba.conf for that host and reload. Replace 192.168.1.50 with your backup server:

host    replication    backup_repl    192.168.1.50/32    scram-sha-256
$ psql -c "SELECT pg_reload_conf();"
$ psql -c "SELECT database, user_name, address FROM pg_hba_file_rules WHERE 'replication' = ANY(database);"

Now take a compressed tar-format backup that streams the WAL needed for consistency (-X stream, the default) and starts with a fast checkpoint instead of waiting for the next scheduled one:

$ pg_basebackup -D /var/lib/pgsql/18/backups/base/$(date +%F) \
    -Ft -z -X stream -c fast -P -v

The target directory must be empty or not yet exist. With -Ft you get base.tar.gz, pg_wal.tar.gz and a backup_manifest. Leave out -Ft -z for a plain copy of the data directory, which is faster to restore. From a remote host, add -h 192.168.1.40 -U backup_repl.

Verify the physical backup

pg_verifybackup checks every file against the manifest. For tar-format backups WAL parsing is not supported, so add -n:

$ pg_verifybackup -n /var/lib/pgsql/18/backups/base/2026-10-31
backup successfully verified

Plain-format backups can be verified without -n, which also checks that the required WAL is present and readable.

Step 6: Restore a physical backup

A physical restore replaces the whole cluster, so every database goes back to the moment the backup finished. Stop the server and move the old data directory aside rather than deleting it:

$ sudo systemctl stop postgresql-18
$ sudo mv /var/lib/pgsql/18/data /var/lib/pgsql/18/data.broken
$ sudo -u postgres mkdir -m 700 /var/lib/pgsql/18/data
$ sudo -u postgres tar -xzf /var/lib/pgsql/18/backups/base/2026-10-31/base.tar.gz -C /var/lib/pgsql/18/data
$ sudo -u postgres tar -xzf /var/lib/pgsql/18/backups/base/2026-10-31/pg_wal.tar.gz -C /var/lib/pgsql/18/data/pg_wal
$ sudo restorecon -R /var/lib/pgsql/18/data
$ sudo systemctl start postgresql-18
$ sudo journalctl -u postgresql-18 -n 20

On startup PostgreSQL reads the backup_label file, replays the bundled WAL, and opens for connections. Check the log in /var/lib/pgsql/18/data/log/ for database system is ready to accept connections. Restoring to a later point in time needs WAL archiving and recovery.signal, which is beyond this post; the same base backup is also how part 4 seeds a streaming standby.

If you use tablespaces, each one is in its own <oid>.tar.gz and must be extracted to its original location (or remapped with --tablespace-mapping at backup time).

Step 7: Schedule nightly dumps with a systemd timer

A small script dumps the globals and each database, then deletes files older than 14 days. Create /usr/local/bin/pg-nightly-dump.sh:

#!/usr/bin/bash
set -euo pipefail
export PATH=/usr/pgsql-18/bin:$PATH
DEST=/var/lib/pgsql/18/backups/dumps
STAMP=$(date +%F_%H%M)
umask 077

pg_dumpall --globals-only -f "$DEST/globals_$STAMP.sql"

for db in $(psql -AtX -d postgres -c "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate AND datname <> 'postgres'"); do
    pg_dump -Fc -d "$db" -f "$DEST/${db}_$STAMP.dump"
    pg_restore -l "$DEST/${db}_$STAMP.dump" > /dev/null   # quick readability check
done

find "$DEST" -type f \( -name '*.dump' -o -name 'globals_*.sql' \) -mtime +14 -delete
$ sudo chmod 755 /usr/local/bin/pg-nightly-dump.sh

Create the service, /etc/systemd/system/pg-nightly-dump.service:

[Unit]
Description=Nightly PostgreSQL logical backups
After=postgresql-18.service
Requires=postgresql-18.service

[Service]
Type=oneshot
User=postgres
Group=postgres
ExecStart=/usr/local/bin/pg-nightly-dump.sh

And the timer, /etc/systemd/system/pg-nightly-dump.timer:

[Unit]
Description=Run PostgreSQL logical backups nightly

[Timer]
OnCalendar=*-*-* 01:30:00
RandomizedDelaySec=10m
Persistent=true

[Install]
WantedBy=timers.target
$ sudo systemctl daemon-reload
$ sudo systemctl enable --now pg-nightly-dump.timer
$ sudo systemctl start pg-nightly-dump.service     # run once now
$ systemctl list-timers pg-nightly-dump.timer
$ sudo journalctl -u pg-nightly-dump.service -n 20

Persistent=true runs a missed backup at the next boot. Copy the dumps off the server afterwards (rsync, object storage, or a backup system), because a backup that lives only on the database host disappears with it.

Verification checklist

  • ls -lh /var/lib/pgsql/18/backups/dumps shows fresh, non-zero dump files and a globals file each night.
  • pg_restore -l lists the archive contents without errors.
  • A test restore into invdb_restore returns the same row counts as production: compare SELECT count(*) FROM inv.items; in both databases.
  • pg_verifybackup reports backup successfully verified for each base backup.
  • Clean up test databases afterwards with dropdb invdb_restore.
  • Schedule a full restore drill on a spare host at least once a quarter.

Troubleshooting

  • pg_dump: error: aborting because of server version mismatch: the client is older than the server. Use /usr/pgsql-18/bin/pg_dump or install the matching client package.
  • permission denied for table ... during pg_dump: the dumping role cannot read every object. Dump as the owner, a superuser, or a role granted pg_read_all_data.
  • role "inv_read" does not exist during pg_restore: restore the globals file first, or use --no-owner --no-privileges to restore everything as the connecting user.
  • database "invdb" already exists with –create: connect with -d postgres, or drop the target first, or add --clean --if-exists.
  • no pg_hba.conf entry for replication connection: all in the database column does not match replication connections. Add a line with replication in that column and reload.
  • must be superuser or replication role to start walsender: the role needs the REPLICATION attribute.
  • pg_basebackup hangs at “waiting for checkpoint”: add -c fast, otherwise it waits for the next scheduled checkpoint.
  • number of requested standby connections exceeds max_wal_senders: -X stream uses an extra connection. Raise max_wal_senders (default 10) and restart.
  • Server will not start after a physical restore: check ownership (postgres:postgres), mode 0700 on the data directory, the SELinux label (restorecon -R), and that pg_wal was extracted.
  • Timer never runs: systemctl list-timers shows the next run; check journalctl -u pg-nightly-dump.service for script errors such as a missing PATH.

Quick reference

TaskCommand
Dump one databasepg_dump -Fc -d db -f db.dump
Parallel dumppg_dump -Fd -j 4 -d db -f db_dir
Dump roles and tablespacespg_dumpall --globals-only -f globals.sql
List archive contentspg_restore -l db.dump
Restore into existing databasepg_restore -d db -j 4 db.dump
Recreate database from dumppg_restore --create -d postgres db.dump
Physical backuppg_basebackup -D dir -Ft -z -X stream -c fast -P
Verify physical backuppg_verifybackup -n dir

What’s next in the PostgreSQL series

Part 4 builds on pg_basebackup to set up streaming replication between a primary and a standby, and walks through failover. Earlier parts: part 1, Install PostgreSQL on AlmaLinux 10: PGDG vs AppStream, pg_hba.conf and Remote Access, and part 2, PostgreSQL Users, Roles and Privileges on AlmaLinux 10. For an older cross-distribution install guide, see Install PostgreSQL on RHEL/CentOS/Oracle/Rocky/Alma.

Official references: PostgreSQL documentation: Backup and Restore, pg_dump, pg_dumpall, pg_restore, pg_basebackup and pg_verifybackup.

, , , , , , , , , , , , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *