dbi Blog

While PostgreSQL 17 was released just a couple of days ago, the development of PostgreSQL 18 is already going on and the first patches got committed. One of them addresses a limitation with pg_verifybackup: Currently (up to version 17) it can only verify backups created with pg_basebackup which are in “plain” format. This means, backups taken either in “tar” or “compressed” format can only be verified once they’ve been unpacked. Starting with PostgreSQL 18 this limitation will most probably be gone.

If you do something like this up to PostgreSQL 17:

postgres@debian12-pg:/home/postgres/ [170] pg_basebackup --version
pg_basebackup (PostgreSQL) 17.0 dbi services build
postgres@debian12-pg:/home/postgres/ [170] mkdir /var/tmp/dummy
postgres@debian12-pg:/home/postgres/ [170] pg_basebackup --checkpoint=fast --format=t --pgdata=/var/tmp/dummy
postgres@debian12-pg:/home/postgres/ [170] ls -l /var/tmp/dummy
total 39620
-rw------- 1 postgres postgres   137808 Sep 30 08:36 backup_manifest
-rw------- 1 postgres postgres 23646720 Sep 30 08:36 base.tar
-rw------- 1 postgres postgres 16778752 Sep 30 08:36 pg_wal.tar

… and then you try to verify that afterwards you’ll hit this:

postgres@debian12-pg:/home/postgres/ [170] pg_verifybackup /var/tmp/dummy/
pg_verifybackup: error: "pg_wal.tar" is present on disk but not in the manifest
pg_verifybackup: error: "base.tar" is present on disk but not in the manifest
pg_verifybackup: error: "base/4/3431" is present in the manifest but not on disk
pg_verifybackup: error: "base/5/2620" is present in the manifest but not on disk
pg_verifybackup: error: "base/1/2617" is present in the manifest but not on disk
...
pg_verifybackup: error: "base/1/2682" is present in the manifest but not on disk
pg_waldump: error: could not open directory "/var/tmp/dummy/pg_wal": No such file or directory
pg_waldump: hint: Try "pg_waldump --help" for more information.
pg_verifybackup: error: WAL parsing failed for timeline 1
postgres@debian12-pg:/home/postgres/ [170] echo $?
1

The reason is, that pg_verifybackup is not able to look into the tar file and therefore fails to validate the backup.

Starting with PostgreSQL it will look like this:

postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --version
pg_basebackup (PostgreSQL) 18devel
postgres@debian12-pg:/home/postgres/ [pgdev] rm -rf /var/tmp/dummy
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --checkpoint=fast --format=t --pgdata=/var/tmp/dummy
postgres@debian12-pg:/home/postgres/ [pgdev] pg_verifybackup --no-parse-wal /var/tmp/dummy/
backup successfully verified

The “–no-parse-wal” is currently required, as pg_waldump (which is used internally to verify the WAL) is not yet able to deal with compressed or tar backups.

This works with compressed backups as well:

postgres@debian12-pg:/home/postgres/ [pgdev] rm -rf /var/tmp/dummy/
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --checkpoint=fast --format=t --compress --pgdata=/var/tmp/dummy
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --checkpoint=fast --format=t --compress=5 --pgdata=/var/tmp/dummy
postgres@debian12-pg:/home/postgres/ [pgdev] pg_verifybackup --no-parse-wal /var/tmp/dummy/
backup successfully verified

Nice to see another limitation gets removed. Commit message here.

Post Views: 183