PostgreSQL Backup, Restore and Recovery

/ home / computer / postgresql / postgresql-for-mysql-admins / .

Table of Contents

Chapter Contents

• Logical database backup (SQL dump)
  • Logical database backup with pg_dump
  • Logical database restore with psql
  • Logical cluster-wide database backup with pg_dumpall
  • Logical database restore from archive with pg_restore
  • Restore post processing
• Physical database backup
  • Local physical backup
  • Remote physical backup
  • Monitor Backup
  • Restore physical backup
  • Restore physical backup for tarball
  • Tablespaces
• Point-in-Time-Recovery
• Other backup related stuff
  • Backup from Standby
  • Incremental backups
  • Transportable Tablespaces (TTS)
  • Backup methods
• Testing your restore!

Logical database backup (SQL dump)

• mysqldump/mysql vs. pg_dump/psql/pg_dumpall/pg_restore

Difference to MySQL: pg_dump backup CANNOT be used as base for a Point-in-Time-Recovery! Use physical backup instead.

Used for major upgrades or change of architectures (32-bit/64-bit, endianess, etc.).

Sources:

• SQL Dump

Logical database backup with pg_dump

• Only one single database that means a “partial” instance backup.
• Local or remote.
• Consistent snapshot of the database at the begin time.
• Non blocking (except exclusive locks like DDL).
• Dump is relative to template0. This means that any languages, procedures, etc. added via template1 will also be dumped by pg_dump.
• Does not dump cluster-wide information (such as roles or tablespaces).
• Unlike MySQL pg_dump does NOT create INSERT but COPY statements (similar to LOAD DATA INFILE).
• The creation statements are split apart into: CREATE TABLE, CREATE SEQUENCE (similar to AUTO_INCREMENT) and CREATE INDEX statements.

# \l+ \dn+ \dnS+
$ pg_dump <db> > db_dump.sql
$ pg_dump --dbname=<db> > db_dump.sql
$ pg_dump --user=... --host=-... --port=... <db> > db_dump.sql
$ pg_dump <db> --schema=<schema> > db_schema_dump.sql
$ pg_dump <db> --schema=<schema1> --schema=<schema2> > db_schema_dump.sql
$ pg_dump <db> --schema='<schema?>' > db_schema_dump.sql
$ pg_dump <db> --schema=<schema> --table=<test*> > db_schema_table_dump.sql
$ pg_dump <db> | gzip > db_dump.sql.gz

Dump data as INSERT commands (rather than COPY):

$ pg_dump <db> --inserts --rows-per-insert=100 > db_dump.sql

To dump roles and tablespaces use pg_dumpall.

Sources:

• https://www.postgresql.org/docs/current/app-pgdump.html

---

Logical database restore with psql

• Restore text files created with pg_dump.
• Database must exist.
• All users (object owners or grantees) must exist.
• After restore a VACUUM ANALYZE or at least an ANALYZE is recommended.

$ createdb --template=template0 <db>
$ psql --no-psqlrc <db> < db_dump.sql
$ psql --no-psqlrc --set ON_ERROR_STOP=on <db> < db_dump.sql
$ psql --no-psqlrc --single-transaction <db> < db_dump.sql
$ psql --no-psqlrc --single-transaction <db> --quiet < db_dump.sql
$ pg_dump --host=... <db> | psql --no-psqlrc --host=... <db>
$ zcat db_dump.sql.gz | psql <db>

postgres=# SHOW autovacuum;
 autovacuum 
------------
 on

dbname=# ANALYZE (/* VERBOSE, */ SKIP_LOCKED);
dbname=# VACUUM /* VERBOSE */ ANALYZE;

dbname=# SELECT schemaname, relname, last_autoanalyze, last_analyze
  FROM pg_stat_all_tables
 WHERE schemaname NOT LIKE 'pg_%' AND schemaname != 'information_schema'
;
 schemaname | relname  | last_autoanalyze | last_analyze 
------------+----------+------------------+--------------
 public     | test     |                  | 2026-06-24 17:54:47.048257+02
 public     | products |                  | 
 public     | archived |                  | 

Sources:

• https://www.postgresql.org/docs/current/app-psql.html
• https://www.postgresql.org/docs/current/populate.html#POPULATE-PG-DUMP

---

Logical cluster-wide database backup with pg_dumpall

• All databases.
• Cluster-wide data such as roles and tablespace definitions.
• No consistency among databases!
• Restore is done again with psql.

$ pg_dumpall --clean --statistics > full_dump.sql
$ cat full_dump.sql | psql --no-psqlrc
$ psql --no-psqlrc --file=full_dump.sql

Dump roles and tablespaces:

$ pg_dumpall --globals-only > globals_dump.sql
$ pg_dumpall --roles-only > roles_dump.sql
$ pg_dumpall --tablespaces-only > tablespaces_dump.sql

Sources:

• https://www.postgresql.org/docs/current/app-pg-dumpall.html

---

Logical database restore from archive with pg_restore

• Restore non-plain-text archive file (--format={custom|directory|tar}) created by pg_dump.
• Partial (selectively) restore of a database.
• Rename a database.
• Testing database migrations.
• Manual selection and reorder of database items.
• Compressed by default thus slightly slower during backup…

$ pg_dump --format=custom <db> > db.dump
$ dropdb <db>
$ pg_restore --clean --if-exists --create --dbname=postgres db.dump

Sources:

• https://www.postgresql.org/docs/current/app-pgrestore.html
• https://www.postgresql.org/docs/current/populate.html#POPULATE-PG-DUMP

---

Restore post processing

After a restore with psql/pg_restore all tables should be vacuumed:

$ vacuumdb --all
$ vacuumdb --dbname=<db>
$ vacuumdb --schema=<schema> --analyze <db>
$ vacuumdb --schema=<schema> --analyze-only <db>

Sources:

• https://www.postgresql.org/docs/current/app-vacuumdb.html
• https://www.postgresql.org/docs/current/populate.html#POPULATE-ANALYZE

---

Physical database backup

• xtrabackup/ mysqlbackup vs. pg_basebackup

Used for:

• Basebackup can be used for PiTR.
• Basebackup can be used as starting point for log-shipping or streaming replication standby server.
• Basebackup is base for incremental backups (v17).
• Backups from standby servers are possibly (backup slave/backup standby)
• Basebackup can be done remote.
• Partial backup/restore is NOT possible only whole database cluster.
• Backup is made over a regular PostgreSQL connection using the replication protocol.
• REPLICATION permission or superuser is needed and pg_hba.conf must be adapted.
• CHECKPOINT before backup can take quite some time…

Local physical backup

postgres=# SHOW max_wal_senders;
 max_wal_senders 
-----------------
 10

postgres=# SHOW wal_level;
 wal_level 
-----------
 replica

$ pg_basebackup --format=plain --pgdata=/mnt/backup/basebackup

Remote physical backup

Create a backup user:

postgres=# CREATE ROLE backupuser WITH LOGIN PASSWORD 'secret' REPLICATION;
CREATE ROLE

Allow backup user access from remote:

# pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     backupuser      10.223.125.0/24         scram-sha-256

Perform the remote backup:

$ pg_basebackup --user=backupuser --host=10.223.125.193 --format=plain --pgdata=/mnt/backup/basebackup
Password: 

Perform backup as tarball:

$ pg_basebackup --format=tar --wal-method=stream --progress --verbose --pgdata=/mnt/backup/basebackup
$ ll /mnt/backup/basebackup1/
total 67040
-rw------- 1 postgres postgres   365217 Jul 21 08:26 backup_manifest
-rw------- 1 postgres postgres 51497984 Jul 21 08:26 base.tar
-rw------- 1 postgres postgres 16778752 Jul 21 08:26 pg_wal.tar

Perform backup as tarball and compress:

$ pg_basebackup --format=tar --wal-method=fetch --progress --verbose --pgdata=- | gzip > /mnt/backup/basebackup.tar.gz
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/E000028 on timeline 1
66676/66676 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/E000120
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed

Initiate backup remote:

# GRANT pg_write_server_files TO backupuser;
$ pg_basebackup --user=backupuser --host=10.223.125.193 --wal-method=fetch --target=server:/mnt/backup/basebackup

Monitor Backup

postgres=> SELECT * FROM pg_stat_progress_basebackup;
 pid  |              phase               | backup_total | backup_streamed | tablespaces_total | tablespaces_streamed 
------+----------------------------------+--------------+-----------------+-------------------+----------------------
 4420 | waiting for checkpoint to finish |              |               0 |                 0 |                    0

Source:

• https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE

Restore physical backup

$ sudo systemctl stop postgresql
$ rm -rf /var/lib/postgresql/18/main/*
$ cp --archive /mnt/backup/basebackup/* /var/lib/postgresql/18/main/
$ sudo systemctl start postgresql

Restore physical backup from tarball

$ pg_basebackup --user=backupuser --host=10.223.125.193 --pgdata=/mnt/backup/basebackup --format=tar
$ tar -xzf /mnt/backup/basebackup/base.tar.gz -C /var/lib/postgresql/18/main/

Sources:

• https://www.postgresql.org/docs/current/app-pgbasebackup.html
• https://www.postgresql.org/docs/current/progress-reporting.html#BASEBACKUP-PROGRESS-REPORTING

---

Tablespaces

• Locate files in other locations in the file system.
• Used in 2 ways:
  • Volume runs out of space and cannot be extended.
  • Put heavily used data on fast (expensive) disks and rarely used (archived) data on slow (cheap) disks.
• There is usually not much point in making more than one tablespace per logical file system.
• Try to avoid separate tablespaces to avoid problems in backup/restore, etc.

Checks for tablespaces:

postgres=# SHOW data_directory;   -- $PGDATA
       data_directory        
-----------------------------
 /var/lib/postgresql/18/main

postgres=# SHOW default_tablespace;
 default_tablespace 
--------------------
 
(1 row)

postgres=# SHOW temp_tablespaces;
 temp_tablespaces 
------------------
 
(1 row)

postgres=# \db+
                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description 
------------+----------+----------+-------------------+---------+--------+-------------
 pg_default | postgres |          |                   |         | 48 MB  | 
 pg_global  | postgres |          |                   |         | 548 kB | 

postgres=# SELECT spcname, spcowner::regrole, pg_tablespace_location(oid), spcacl, spcoptions FROM pg_tablespace;
  spcname   | spcowner | pg_tablespace_location | spcacl | spcoptions 
------------+----------+------------------------+--------+------------
 pg_default | postgres |                        |        | 
 pg_global  | postgres |                        |        | 

postgres=# SELECT c.relname AS object_name, c.relkind AS type, t.spcname AS tablespace_name
  FROM pg_class c
  LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid
  JOIN pg_namespace n ON c.relnamespace = n.oid
 WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
   AND c.reltablespace != 0
   AND t.spcname NOT IN ('pg_global')
;
 object_name | type | tablespace_name 
-------------+------+-----------------
(0 rows)

Sources:

• https://www.postgresql.org/docs/current/manage-ag-tablespaces.html
• https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TABLESPACE
• https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TEMP-TABLESPACES
• https://www.cybertec-postgresql.com/en/when-to-use-tablespaces-in-postgresql/#when-should-i-create-a-tablespace
• https://learnomate.org/move-postgresql-tablespaces-guide/

---

Point-in-Time-Recovery

• MySQL binary log + InnoDB transaction log is similar to PostgreSQL write ahead log (WAL).
• Located in ${PGDATA}/pg_wal/.
• Records every change in the database.
• Primarily for crash-safety purposes.
• Restore backup and then replaying backed-up WAL.
  • No perfectly consistent file system backup as starting point is needed?!? Inconsistencies will be corrected by log replay.
  • WAL file archiving.
  • Point-in-Time-Recovery (PiTR)
  • Feed for warm standby system.
• Logical backups CANNOT be used for PiTR (in contrary to MySQL)!
• Requires WAL archiving.

Sources:

• https://www.postgresql.org/docs/current/continuous-archiving.html
• https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY
• https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-LOWLEVEL-BASE-BACKUP
• https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LEVEL
• https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-MODE

Write Ahead Log (WAL)

• WAL records ➜ WAL segment files (16 MB)
• After a CHECKPOINT WAL segments are not needed any more and are recycled (renamed).
• Enable WAL archiving requires database instance restart.
• After enabling WAL archiving do a full pg_basebackup again. Now you are ready for PiTR!
• Do NOT forget to backup your WAL archives to a remote machine in case your file system gets corrupted!
• archive_timeout and WAL remote copy should match to your RPO!

postgres=# SHOW wal_level;
 wal_level 
-----------
 replica

postgres=# SHOW archive_mode;
 archive_mode 
--------------
 off

postgres=# SHOW archive_timeout;   -- RPO!
 archive_timeout 
-----------------
 0

Enable WAL archiving

#
# postgresql.conf
#
archive_mode = on
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
archive_timeout = '15 min'

After restart:

postgres=# SELECT name, setting, context
  FROM pg_settings
 WHERE name IN ('wal_level', 'archive_mode', 'archive_timeout', 'archive_command')
;
      name       |                          setting                                         |  context   
-----------------+--------------------------------------------------------------------------+------------
 archive_command | test ! -f /mnt/backup/wal_archive/%f && cp %p /mnt/backup/wal_archive/%f | sighup
 archive_mode    | on                                                                       | postmaster
 archive_timeout | 900                                                                      | sighup
 wal_level       | replica                                                                  | postmaster

Monitor WAL archiving

postgres=# select * from pg_stat_archiver;
 archived_count |    last_archived_wal     |      last_archived_time       | failed_count |     last_failed_wal      |       last_failed_time        |          stats_reset          
----------------+--------------------------+-------------------------------+--------------+--------------------------+-------------------------------+-------------------------------
              3 | 000000010000000000000014 | 2026-07-21 10:24:35.254709+00 |            3 | 000000010000000000000012 | 2026-07-21 10:08:14.335224+00 | 2026-07-20 18:44:11.680715+00

Forcing a WAL switch

postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/11000180

Proceed a Point-in-Time-Recovery (until time or until end)

1. Stop the server, if it is still running.

   $ sudo systemctl stop postgresql
   $ kill -s SIGKILL <pid>
   

2. Copy the whole instance data directory to a temporary location (optional).

3. Copy the WAL files in pg_wal/ to a temporary location (/var/tmp/wal_archive/).

   $ ll /mnt/backup/wal_archive/0* /var/lib/postgresql/18/main/pg_wal/0*
   -rw------- 1 postgres postgres 16777216 Jul 21 10:09 /mnt/backup/wal_archive/000000010000000000000012
   -rw------- 1 postgres postgres 16777216 Jul 21 10:09 /mnt/backup/wal_archive/000000010000000000000013
   -rw------- 1 postgres postgres 16777216 Jul 21 10:24 /mnt/backup/wal_archive/000000010000000000000014
   -rw------- 1 postgres postgres 16777216 Jul 21 12:24 /mnt/backup/wal_archive/000000010000000000000015
   -rw------- 1 postgres postgres 16777216 Jul 21 12:27 /mnt/backup/wal_archive/000000010000000000000016
   -rw------- 1 postgres postgres 16777216 Jul 21 12:33 /mnt/backup/wal_archive/000000010000000000000017
   -rw------- 1 postgres postgres 16777216 Jul 21 12:33 /mnt/backup/wal_archive/000000010000000000000018
   -rw------- 1 postgres postgres      341 Jul 21 12:33 /mnt/backup/wal_archive/000000010000000000000018.00000028.backup
   -rw------- 1 postgres postgres 16777216 Jul 21 12:39 /mnt/backup/wal_archive/000000010000000000000019
   -rw------- 1 postgres postgres 16777216 Jul 21 12:40 /mnt/backup/wal_archive/00000001000000000000001A
   -rw------- 1 postgres postgres      341 Jul 21 12:33 /var/lib/postgresql/18/main/pg_wal/000000010000000000000018.00000028.backup
   -rw------- 1 postgres postgres 16777216 Jul 21 12:40 /var/lib/postgresql/18/main/pg_wal/00000001000000000000001B
   -rw------- 1 postgres postgres 16777216 Jul 21 12:39 /var/lib/postgresql/18/main/pg_wal/00000001000000000000001C
   -rw------- 1 postgres postgres 16777216 Jul 21 12:40 /var/lib/postgresql/18/main/pg_wal/00000001000000000000001D
   $ cp -i /var/lib/postgresql/18/main/pg_wal/0* /var/tmp/wal_archive/
   

4. Clean-up the contents of the data directory:

   $ rm -rf /var/lib/postgresql/18/main/*
   

5. Restore the database files and ownership of the files:

   $ cp --archive /mnt/backup/basebackup/* /var/lib/postgresql/18/main/
   $ chown -R postgres: /var/lib/postgresql/18/main
   

6. Remove the files present in pg_wal:

   $ rm -f /var/lib/postgresql/18/main/pg_wal/0*
   

7. Copy the unarchived WAL segment files into pg_wal/ or better into the WAL archive directory:

   $ cp /var/tmp/0* /var/lib/postgresql/18/main/pg_wal/
   $ chown -R postgres: /var/lib/postgresql/18/main/pg_wal/
   
   $ cp /var/tmp/0* /mnt/backup/wal_archive/
   $ chown -R postgres: /mnt/backup/wal_archive/
   

8. Temporarily modify pg_hba.conf to prevent ordinary users from connecting:

   $ cp pg_hba.conf pg_hba.conf.orig
   
   # pg_hba.conf
   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   local   all             postgres                                peer
   

9. Set recovery configuration settings in postgresql.conf:

   restore_command = 'cp /mnt/backup/wal_archive/%f %p'
   listen_address = ''
   

10. Specify stopping point with recovery_target_* if wanted, no recovery_target_* means until end.

    recovery_target_time = '2026-07-21 14:25:37'
    

11. Create a file recovery.signal in the data directory. This file will be removed by the server upon completion.
    If you forget to create this file, the database is recovered to a much older state (backup) and will not go into recovery mode.

    $ touch /var/lib/postgresql/18/main/recovery.signal
    $ chown postgres: /var/lib/postgresql/18/main/recovery.signal
    

12. Start the Server it will go into recovery mode and proceed the archived WAL files.

    LOG:  starting PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
    LOG:  listening on IPv6 address "::1", port 5432
    LOG:  listening on IPv4 address "127.0.0.1", port 5432
    LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
    LOG:  database system was interrupted; last known up at 2026-07-21 12:33:39 UTC
    cp: cannot stat '/mnt/backup/wal_archive/00000002.history': No such file or directory
    LOG:  starting backup recovery with redo LSN 0/18000028, checkpoint LSN 0/18003C08, on timeline ID 1
    LOG:  restored log file "000000010000000000000018" from archive
    LOG:  starting archive recovery
    LOG:  redo starts at 0/18000028
    LOG:  restored log file "000000010000000000000019" from archive
    LOG:  completed backup recovery with redo LSN 0/18000028 and end LSN 0/180042E0
    LOG:  consistent recovery state reached at 0/180042E0
    LOG:  database system is ready to accept read-only connections
    LOG:  restored log file "00000001000000000000001A" from archive
    cp: cannot stat '/mnt/backup/wal_archive/00000001000000000000001B': No such file or directory
    cp: cannot stat '/mnt/backup/wal_archive/00000001000000000000001B': No such file or directory
    LOG:  redo done at 0/1A0572E8 system usage: CPU: user: 0.01 s, system: 0.01 s, elapsed: 0.11 s
    LOG:  last completed transaction was at log time 2026-07-21 12:39:46.871393+00
    LOG:  restored log file "00000001000000000000001A" from archive
    cp: cannot stat '/mnt/backup/wal_archive/00000002.history': No such file or directory
    LOG:  selected new timeline ID: 2
    cp: cannot stat '/mnt/backup/wal_archive/00000001.history': No such file or directory
    LOG:  archive recovery complete
    LOG:  checkpoint starting: end-of-recovery immediate wait
    LOG:  checkpoint complete: wrote 147 buffers (0.9%), wrote 7 SLRU buffers; 0 WAL file(s) added, 2 removed, 7 recycled; write=0.014 s, sync=0.016 s, total=0.116 s; sync files=10, longest=0.006 s, average=0.002 s; distance=49152 kB, estimate=49152 kB; lsn=0/1B000028, redo lsn=0/1B000028
    LOG:  database system is ready to accept connections
    

13. Check the server error log.

14. Check the server if data are recovered correctly. Verify this: SELECT pg_last_xact_replay_timestamp();

15. If a recovery target was specified, promote database to primary:

    postgres=# SELECT pg_is_in_recovery();
     pg_is_in_recovery 
    -------------------
     t
    
    postgres=# select pg_promote();
     pg_promote 
    ------------
     t
    

16. Reset pg_hba_conf and postgres.conf.

17. Restart the database instance.

Source:

• https://www.postgresql.org/docs/current/runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET

Proceed a Point-in-Time-Recovery (until Oops-Query, XID or LSN)

Technically it seems to be possible to recover until a specific transaction ID or LSN cause by an Oops-Query:

recovery_target_xid = '36798'
recovery_target_lsn = '0/250000A0'
recovery_target_inclusive = off
# SELECT pg_create_restore_point('before_major_update')
recovery_target_name = 'before_major_update'

But practically it is quite difficult to find this XID or LSN from WAL files… And: DDL seems to be easier than DML (which is more common?).

postgres=# SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn(), pg_current_wal_flush_lsn(), pg_current_xact_id();
 pg_current_wal_lsn | pg_current_wal_insert_lsn | pg_current_wal_flush_lsn | pg_current_xact_id 
--------------------+---------------------------+--------------------------+--------------------
 0/2D07EBD8         | 0/2D07EBD8                | 0/2D07EBD8               |              57234

postgres=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 00000001000000000000002D

As of this writing only the date/time and named restore point options are very usable, since there are no tools to help you identify with any accuracy which transaction ID to use. [ 1 ]

See my blog article about this topic: PostgreSQL Point-in-Time Recovery with Oops-Queries

Also Debezium or some other CDC Tool can help in this matter…

Sources:

• https://www.postgresql.org/docs/devel/logicaldecoding-explanation.html
• https://wiki.postgresql.org/wiki/Logical_Decoding_Plugins
• https://dba.stackexchange.com/questions/345256/is-using-transaction-xid-or-lsn-the-more-accurate-and-precise-way-to-perform-pit

WAL Troubleshooting

We somehow messed up the WALs:

LOG:  restored log file "000000010000000000000025" from archive
LOG:  invalid record length at 0/250000A0: expected at least 24, got 0
LOG:  redo done at 0/25000028 system usage: CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.15 s
LOG:  last completed transaction was at log time 2026-07-21 14:25:38.760713+00
LOG:  restored log file "000000010000000000000025" from archive
LOG:  selected new timeline ID: 2
LOG:  archive recovery complete
LOG:  checkpoint starting: end-of-recovery immediate wait

The problem could be reproduced with pg_waldump:

$ /usr/lib/postgresql/18/bin/pg_waldump 000000010000000000000025
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/25000028, prev 0/24051518, desc: CHECKPOINT_SHUTDOWN redo 0/25000028; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:36798; oid 21518; multi 1; offset 0; oldest xid 744 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
pg_waldump: error: error in WAL record at 0/25000028: invalid record length at 0/250000A0: expected at least 24, got 0

The better WAL looked as follows:

$ /usr/lib/postgresql/18/bin/pg_waldump 000000010000000000000025
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/25000028, prev 0/24051518, desc: CHECKPOINT_SHUTDOWN redo 0/25000028; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:36798; oid 21518; multi 1; offset 0; oldest xid 744 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/250000A0, prev 0/25000028, desc: RUNNING_XACTS nextXid 36798 latestCompletedXid 36797 oldestRunningXid 36798
rmgr: XLOG        len (rec/tot):     49/  3869, tx:          0, lsn: 0/250000D8, prev 0/250000A0, desc: FPI_FOR_HINT , blkref #0: rel 1663/21506/2619 blk 0 FPW
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/25000FF8, prev 0/250000D8, desc: RUNNING_XACTS nextXid 36798 latestCompletedXid 36797 oldestRunningXid 36798
rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/25001030, prev 0/25000FF8, desc: SWITCH 

$ /usr/lib/postgresql/18/bin/pg_waldump 000000010000000000000026
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/26000028, prev 0/25001030, desc: CHECKPOINT_SHUTDOWN redo 0/26000028; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:36798; oid 21518; multi 1; offset 0; oldest xid 744 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
rmgr: Sequence    len (rec/tot):     99/    99, tx:      36798, lsn: 0/260000A0, prev 0/26000028, desc: LOG rel 1663/21506/21507, blkref #0: rel 1663/21506/21507 blk 0
rmgr: Heap        len (rec/tot):     54/  3022, tx:      36798, lsn: 0/26000108, prev 0/260000A0, desc: INSERT off: 32, flags: 0x00, blkref #0: rel 1663/21506/21508 blk 402 FPW
rmgr: Btree       len (rec/tot):     53/  5533, tx:      36798, lsn: 0/26000CD8, prev 0/26000108, desc: INSERT_LEAF off: 272, blkref #0: rel 1663/21506/21516 blk 98 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:      36798, lsn: 0/26002290, prev 0/26000CD8, desc: COMMIT 2026-07-21 15:13:24.490701 UTC
...

I think, the reason for this problem was, that we took the WAL’s directly from a living PostgreSQL database instance:

Can give wrong results when the server is running. [ 2 ]

---

Examining WAL

Looking what is in the WAL:

$ PATH="${PATH}:/usr/lib/postgresql/18/bin"
$ pg_waldump 000000010000000000000026
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/26000028, prev 0/25001030, desc: CHECKPOINT_SHUTDOWN redo 0/26000028; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:36798; oid 21518; multi 1; offset 0; oldest xid 744 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown
rmgr: Sequence    len (rec/tot):     99/    99, tx:      36798, lsn: 0/260000A0, prev 0/26000028, desc: LOG rel 1663/21506/21507, blkref #0: rel 1663/21506/21507 blk 0
rmgr: Heap        len (rec/tot):     54/  3022, tx:      36798, lsn: 0/26000108, prev 0/260000A0, desc: INSERT off: 32, flags: 0x00, blkref #0: rel 1663/21506/21508 blk 402 FPW
rmgr: Btree       len (rec/tot):     53/  5533, tx:      36798, lsn: 0/26000CD8, prev 0/26000108, desc: INSERT_LEAF off: 272, blkref #0: rel 1663/21506/21516 blk 98 FPW
rmgr: Transaction len (rec/tot):     34/    34, tx:      36798, lsn: 0/26002290, prev 0/26000CD8, desc: COMMIT 2026-07-21 15:13:24.490701 UTC

Translating relation (rel) information:

postgres=# SELECT oid, spcname AS tablespace, spcowner::regrole AS owner FROM pg_tablespace WHERE oid = 1663;
 oid  | tablespace |  owner   
------+------------+----------
 1663 | pg_default | postgres
(1 row)

postgres=# SELECT oid, datname AS database FROM pg_database WHERE oid = 21506;
  oid  | database 
-------+----------
 21506 | test
(1 row)

postgres=# \connect test
You are now connected to database "test" as user "postgres".
test=# SELECT c.oid, ns.nspname AS schema, c.relname AS object_name
     , CASE c.relkind
           WHEN 'r' THEN 'Ordinary Table'
           WHEN 'i' THEN 'Index'
           WHEN 'S' THEN 'Sequence'
           WHEN 'v' THEN 'View'
           WHEN 'm' THEN 'Materialized View'
           WHEN 'c' THEN 'Composite Type'
           WHEN 't' THEN 'TOAST Table'
           WHEN 'f' THEN 'Foreign Table'
           WHEN 'p' THEN 'Partitioned Table'
           WHEN 'I' THEN 'Partitioned Index'
           ELSE CONCAT('Unknown (', c.relkind, ')')
       END AS object_type
     , pg_size_pretty(pg_relation_size(c.oid)) AS object_size
  FROM pg_class AS c
  JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
 WHERE c.oid IN (21507, 21508, 21516)
;
  oid  | schema | object_name |  object_type   | object_size 
-------+--------+-------------+----------------+-------------
 21507 | public | test_id_seq | Sequence       | 8192 bytes
 21508 | public | test        | Ordinary Table | 18 MB
 21516 | public | test_pkey   | Index          | 4424 kB
(3 rows)

Finding time in WAL:

$ pg_waldump 000000010000000000000026 | head | grep COMMIT
rmgr: Transaction len (rec/tot):     34/    34, tx:      36798, lsn: 0/26002290, prev 0/26000CD8, desc: COMMIT 2026-07-21 15:13:24.490701 UTC
rmgr: Transaction len (rec/tot):     34/    34, tx:      36799, lsn: 0/26002370, prev 0/26002330, desc: COMMIT 2026-07-21 15:13:24.542072 UTC

$ pg_waldump 000000010000000000000026 | tail  | grep COMMIT
rmgr: Transaction len (rec/tot):     34/    34, tx:      42278, lsn: 0/2618B6D0, prev 0/2618B690, desc: COMMIT 2026-07-21 15:17:57.915617 UTC
rmgr: Transaction len (rec/tot):     34/    34, tx:      42279, lsn: 0/2618B7B0, prev 0/2618B770, desc: COMMIT 2026-07-21 15:17:57.987780 UTC
rmgr: Transaction len (rec/tot):     34/    34, tx:      42280, lsn: 0/2618B890, prev 0/2618B850, desc: COMMIT 2026-07-21 15:17:58.042972 UTC

Source:

• https://www.postgresql.org/docs/current/pgwaldump.html

---

pg_walinspect

Similar functionality like pg_waldump but on recent WAL.

WAL File Naming:

TTTTTTTTXXXXXXXX000000YY 0000000100000005000000A7

• T - timeline
• X - sequence number higher part
• 0 - 6 leading zeros
• Y - sequence number lower part

postgres=# CREATE EXTENSION pg_walinspect;
CREATE EXTENSION

postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 5/A703A590

postgres=# SELECT pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 0000000100000005000000A7

test=# INSERT INTO test VALUES (11, 'Some text', CURRENT_TIMESTAMP);
INSERT 0 1

test=# SELECT pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 5/A703E370

postgres=# SELECT '5/A703E370'::pg_lsn - '5/A703A590'::pg_lsn size_bytes;
 size_bytes 
------------
      15840

postgres=# \dx+ pg_walinspect
         Objects in extension "pg_walinspect"
                  Object description                   
-------------------------------------------------------
 function pg_get_wal_block_info(pg_lsn,pg_lsn,boolean)
 function pg_get_wal_record_info(pg_lsn)
 function pg_get_wal_records_info(pg_lsn,pg_lsn)
 function pg_get_wal_stats(pg_lsn,pg_lsn,boolean)

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_walinspect';
     name      | default_version | installed_version |                           comment                           
---------------+-----------------+-------------------+-------------------------------------------------------------
 pg_walinspect | 1.1             | 1.1               | functions to inspect contents of PostgreSQL Write-Ahead Log

postgres=# SELECT * FROM pg_get_wal_record_info('5/A703E370');
ERROR:  could not find a valid record after 5/A703E370

postgres=# SELECT * FROM pg_get_wal_record_info('5/A703A590');
 start_lsn  |  end_lsn   |  prev_lsn  |  xid  | resource_manager | record_type | record_length | main_data_length | fpi_length |     description      |                     block_ref                                         
------------+------------+------------+-------+------------------+-------------+---------------+------------------+------------+----------------------+-------------------------------------------------------------------------------------------
 5/A703A590 | 5/A703C5D8 | 5/A703A558 | 26338 | Heap             | INSERT      |          8234 |                3 |       8180 | off: 89, flags: 0x01 | blkref #0: rel 1663/24576/24578 fork main blk 632795 (FPW); hole: offset: 380, length: 12

postgres=# SELECT * FROM pg_get_wal_record_info('5/A703C5D8');
 start_lsn  |  end_lsn   |  prev_lsn  |  xid  | resource_manager | record_type | record_length | main_data_length | fpi_length | description |                                       block_ref                                        
------------+------------+------------+-------+------------------+-------------+---------------+------------------+------------+-------------+----------------------------------------------------------------------------------------
 5/A703C5D8 | 5/A703E310 | 5/A703A590 | 26338 | Btree            | INSERT_LEAF |          7453 |                2 |       7400 | off: 13     | blkref #0: rel 1663/24576/24588 fork main blk 1 (FPW); hole: offset: 1496, length: 792

postgres=# SELECT * FROM pg_get_wal_record_info('5/A703E310');
 start_lsn  |  end_lsn   |  prev_lsn  |  xid  | resource_manager | record_type | record_length | main_data_length | fpi_length |          description          | block_ref 
------------+------------+------------+-------+------------------+-------------+---------------+------------------+------------+-------------------------------+-----------
 5/A703E310 | 5/A703E338 | 5/A703C5D8 | 26338 | Transaction      | COMMIT      |            34 |                8 |          0 | 2026-07-23 17:46:13.714192+02 | 

postgres=# SELECT * FROM pg_get_wal_record_info('5/A703E338');
 start_lsn  |  end_lsn   |  prev_lsn  | xid | resource_manager |  record_type  | record_length | main_data_length | fpi_length |                          description                          | block_ref 
------------+------------+------------+-----+------------------+---------------+---------------+------------------+------------+---------------------------------------------------------------+-----------
 5/A703E338 | 5/A703E370 | 5/A703E310 |   0 | Standby          | RUNNING_XACTS |            50 |               24 |          0 | nextXid 26339 latestCompletedXid 26338 oldestRunningXid 26339 | 

postgres=# SELECT * FROM pg_get_wal_record_info('5/A703E370');
 start_lsn  |  end_lsn   |  prev_lsn  | xid | resource_manager |   record_type   | record_length | main_data_length | fpi_length |    description    | block_ref 
------------+------------+------------+-----+------------------+-----------------+---------------+------------------+------------+-------------------+-----------
 5/A703E370 | 5/A703E390 | 5/A703E338 |   0 | XLOG             | CHECKPOINT_REDO |            30 |                4 |          0 | wal_level replica | 

postgres=# select pg_get_wal_records_info('5/A703A590', '5/A703E370');
                                                                               pg_get_wal_records_info                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (5/A703A590,5/A703C5D8,5/A703A558,26338,Heap,INSERT,8234,3,8180,"off: 89, flags: 0x01","blkref #0: rel 1663/24576/24578 fork main blk 632795 (FPW); hole: offset: 380, length: 12")
 (5/A703C5D8,5/A703E310,5/A703A590,26338,Btree,INSERT_LEAF,7453,2,7400,"off: 13","blkref #0: rel 1663/24576/24588 fork main blk 1 (FPW); hole: offset: 1496, length: 792")
 (5/A703E310,5/A703E338,5/A703C5D8,26338,Transaction,COMMIT,34,8,0,"2026-07-23 17:46:13.714192+02",)
 (5/A703E338,5/A703E370,5/A703E310,0,Standby,RUNNING_XACTS,50,24,0,"nextXid 26339 latestCompletedXid 26338 oldestRunningXid 26339",)
(4 rows)

postgres=# SELECT start_lsn, end_lsn, prev_lsn, block_id, reltablespace, reldatabase, relfilenode, relforknumber, relblocknumber, xid, resource_manager, record_type, record_length, main_data_length, block_data_length, block_fpi_length, block_fpi_info, description, block_data
  FROM pg_get_wal_block_info('5/A703A590', '5/A703E370');
 start_lsn  |  end_lsn   |  prev_lsn  | block_id | reltablespace | reldatabase | relfilenode | relforknumber | relblocknumber |  xid  | resource_manager | record_type | record_length | main_data_length | block_data_length | block_fpi_length |  block_fpi_info  |     description      | block_data 
------------+------------+------------+----------+---------------+-------------+-------------+---------------+----------------+-------+------------------+-------------+---------------+------------------+-------------------+------------------+------------------+----------------------+------------
 5/A703A590 | 5/A703C5D8 | 5/A703A558 |        0 |          1663 |       24576 |       24578 |             0 |         632795 | 26338 | Heap             | INSERT      |          8234 |                3 |                 0 |             8180 | {HAS_HOLE,APPLY} | off: 89, flags: 0x01 | 
 5/A703C5D8 | 5/A703E310 | 5/A703A590 |        0 |          1663 |       24576 |       24588 |             0 |              1 | 26338 | Btree            | INSERT_LEAF |          7453 |                2 |                 0 |             7400 | {HAS_HOLE,APPLY} | off: 13              | 
(2 rows)

postgres=# SELECT * FROM pg_get_wal_stats('5/A703A590', '5/A703E370', true);
 resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage 
------------------------------+-------+------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
 Transaction/COMMIT           |     1 |               25 |          34 |     17.801047120418847 |        0 |                   0 |            34 |      0.21558556844841797
 Standby/RUNNING_XACTS        |     1 |               25 |          50 |      26.17801047120419 |        0 |                   0 |            50 |       0.3170376006594382
 Heap/INSERT                  |     1 |               25 |          54 |     28.272251308900522 |     8180 |  52.503209242618745 |          8234 |        52.20975207659628
 Btree/INSERT_LEAF            |     1 |               25 |          53 |      27.74869109947644 |     7400 |  47.496790757381255 |          7453 |        47.25762475429586
(4 rows)

postgres=# SELECT * FROM pg_stat_wal;
 wal_records | wal_fpi | wal_bytes | wal_buffers_full |          stats_reset          
-------------+---------+-----------+------------------+-------------------------------
         287 |      37 |    257197 |             3071 | 2026-07-20 18:05:24.662434+02

postgres=# SELECT * FROM pg_stat_bgwriter;
 buffers_clean | maxwritten_clean | buffers_alloc |          stats_reset          
---------------+------------------+---------------+-------------------------------
             0 |                0 |          1184 | 2026-07-20 18:05:24.662434+02

postgres=# SELECT * FROM pg_stat_archiver;
 archived_count | last_archived_wal | last_archived_time | failed_count | last_failed_wal | last_failed_time |          stats_reset          
----------------+-------------------+--------------------+--------------+-----------------+------------------+-------------------------------
              0 |                   |                    |            0 |                 |                  | 2026-07-20 18:05:24.662434+02

Sources:

• https://www.postgresql.org/docs/current/pgwalinspect.html
• https://www.crunchydata.com/blog/postgres-wal-files-and-sequuence-numbers
• https://pgpedia.info/hacks/replication/get-current-wal-filename.html
• https://www.crunchydata.com/blog/the-rest-is-history-investigations-of-wal-history-files
• https://www.percona.com/blog/see-whats-inside-a-wal-file-using-pg_waldump-and-pg_walinspect/
• https://www.cybertec-postgresql.com/en/postgresql-recovery-internals/
• https://dev.to/misachi/fun-with-postgres-recovery-f5e

---

WAL archive clean-up

Under normal circumstances WALs are cleaned-up automatically (heavy load, failing archive_command or a high wal_keep_size).

postgres=# SHOW max_wal_size;
 max_wal_size 
--------------
 1GB

Archived WALs must be cleaned-up as well:

test=# SHOW archive_cleanup_command;
 archive_cleanup_command 
-------------------------
 pg_archivecleanup -d /mnt/backup/wal_archive %r 2>>cleanup.log

Source:

• https://www.postgresql.org/docs/current/pgarchivecleanup.html

---

WAL Monitoring

postgres=# SELECT pg_size_pretty(COUNT(*)::BIGINT * (select setting from pg_settings where name = 'wal_segment_size')::BIGINT) AS wal_size
  FROM pg_ls_dir('pg_wal')
 WHERE pg_ls_dir ~ '^[0-9A-F]{24}$'
;
 wal_size 
----------
 176 MB

postgres=# SELECT * FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}$';
        pg_ls_dir         
--------------------------
 000000010000000000000036
 000000010000000000000037
 000000010000000000000039
 00000001000000000000003A
 000000010000000000000038

postgres=# SELECT * FROM pg_stat_archiver;
 archived_count |    last_archived_wal     |      last_archived_time       | failed_count |     last_failed_wal      |       last_failed_time        |          stats_reset          
----------------+--------------------------+-------------------------------+--------------+--------------------------+-------------------------------+-------------------------------
             40 | 000000010000000000000036 | 2026-07-22 10:01:53.280686+00 |           42 | 000000010000000000000025 | 2026-07-21 15:20:59.975416+00 | 2026-07-20 18:44:11.680715+00

WAL since the beginning of all time? Does NOT make sense?

postgres=# SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0'));
 pg_size_pretty 
----------------
 880 MB

• ‘0/0’ – This indicates the starting Log Sequence Number (LSN). Using 0/0 tells PostgreSQL to start from the earliest WAL record available.
• NULL – This tells PostgreSQL to continue scanning until the latest WAL record.

$ PGDATA='/var/lib/postgresql/18/main'
$ du -sh ${PGDATA}/pg_wal/
89M     /var/lib/postgresql/18/main/pg_wal/
ls -la ${PGDATA}/pg_wal/????????????????????????
-rw------- 1 postgres postgres 16777216 Jul 22 10:01  /var/lib/postgresql/18/main/pg_wal/000000010000000000000036
-rw------- 1 postgres postgres 16777216 Jul 22 09:11  /var/lib/postgresql/18/main/pg_wal/000000010000000000000037
-rw------- 1 postgres postgres 16777216 Jul 22 09:16  /var/lib/postgresql/18/main/pg_wal/000000010000000000000038
-rw------- 1 postgres postgres 16777216 Jul 22 09:31  /var/lib/postgresql/18/main/pg_wal/000000010000000000000039
-rw------- 1 postgres postgres 16777216 Jul 22 09:46  /var/lib/postgresql/18/main/pg_wal/00000001000000000000003A

postgres=# SELECT name, setting, unit FROM pg_settings WHERE name IN ('wal_keep_size', 'max_wal_size', 'min_wal_size');
     name      | setting | unit 
---------------+---------+------
 max_wal_size  | 1024    | MB
 min_wal_size  | 80      | MB
 wal_keep_size | 0       | MB

postgres=# SELECT * FROM pg_ls_dir('/mnt/backup/wal_archive') WHERE pg_ls_dir ~ '^[0-9A-F]{24}$';
        pg_ls_dir         
--------------------------
 000000010000000000000032
 00000001000000000000003F
 000000010000000000000024
...
 00000001000000000000003A
 000000010000000000000038
 00000001000000000000002A
(35 rows)

Sources:

• https://www.cybrosys.com/research-and-development/postgres/the-ultimate-guide-to-postgresql-wal-inspection-with-pgwalinspect
• https://brtkwr.com/posts/2025-11-25-postgresql-wal-monitoring/
• https://www.interdb.jp/pg/pgsql09.html

check_postgres from bucardo

Output for: Nagios, MRTG, Cacti.

$ check_postgres.pl --action=archive_ready
POSTGRES_ARCHIVE_READY OK: DB "postgres" WAL ".ready" files found: 0 | time=0.03s files=0;10;15 
$ check_postgres.pl --action=wal_files
POSTGRES_WAL_FILES WARNING: DB "postgres" WAL files found: 11 | time=0.02s files=11;10;15 

Not really maintained any more!

Sources:

• https://bucardo.org/check_postgres/
• https://github.com/bucardo/check_postgres

---

Other backup related stuff

Other backup tools / advanced backup tools

• pgBackRest https://pgbackrest.org/
• barman https://pgbarman.org/
• WAL-G https://www.postgresql.org/about/news/wal-g-300-released-2826/ https://supabase.com/blog/continuous-postgresql-backup-walg https://github.com/wal-g/wal-g
• Databasus https://databasus.com/ https://github.com/databasus/databasus

Backup from Standby

TODO: We do later when we understood how Standby works.

• https://www.postgresql.org/docs/current/app-pgbasebackup.html

Incremental backups

We do later. Not so relevant in practice!

Sources:

• https://www.postgresql.org/docs/current/app-pgcombinebackup.html
• https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-INCREMENTAL-BACKUP

Partial backup/restore

Partial backup/restore is currently not possible with physical backup but only with logical backup. See also TTS.

Transportable Tablespaces (TTS)

• Actually NOT possible with Vanilla PostgreSQL?!?

Sources:

• https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.TransportableDB.html
• https://www.dbi-services.com/blog/can-i-do-it-with-postgresql-8-transportable-tablespaces/
• https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/transport-postgresql-databases-between-two-amazon-rds-db-instances-using-pg-transport.html
• https://repost.aws/questions/QU0cBqzrHGTXyk6bHsH8qzdA/is-rds-pg-transport-available-on-on-premise-postgresql
• https://github.com/awsdocs/amazon-rds-user-guide/issues/95
• https://www.dbi-services.com/blog/can-i-do-it-with-postgresql-8-transportable-tablespaces/

---

Other Backup methods

• File system copy
• Snapshots

Source:

• https://www.postgresql.org/docs/current/backup-file.html

---

Testing your restore!

• See MySQL trainings…
• Verify
• Restore tests still must take place

Source:

• https://www.postgresql.org/docs/current/app-pgverifybackup.html