archive_ready does not work on PostgreSQL 10+

[moench-tegeder]

check_archive_ready() calls check_wal_files()

check_postgres/check_postgres.pl

Line 3826 in fd9702c

return check_wal_files('/archive_status', '.ready', 10, 15);

That's fine on PostgreSQL < 10, but on 10+ check_wal_files() calls pg_ls_wal_dir(), which lists only files in the wal dir, but not it's subdirectories.

check_postgres/check_postgres.pl

Line 8700 in fd9702c

qq{SELECT count(*) AS count FROM pg_ls_waldir() WHERE name ~ E'^[0-9A-F]{24}$extrabit\$'}; ## no critic (RequireInterpolationOfMetachars)

Thus the WHERE clause will never be true, and COUNT() always returns 0.
I've currently no idea how to fix this in an elegant way: of course one could fall back to the "old" query, but that requires SUPERUSER privileges for pg_ls_dir() (or GRANTs on that, or a wrapper...).

[mbanck]

FWIW, this was fixed in 8e6b3c2 but there has been no release yet.

[moench-tegeder]

Um, no - I rather think it was broken by that commit. With the new version and PostgreSQL10, check_wal_files() ignores the $subdir parameter (where "/archive_status" is passed in) and just uses pg_ls_waldir() - which does not list the contents of the archive_status directory, and the WHERE clause ("WHERE name ~ E'^[0-9A-F]{24}$extrabit$'" whith $extrabit being passed as ".ready") cannot succeed. There are no .ready files in the output of pg_ls_waldir().

[mbanck]

Oh I see - that is indeed the case.

pinging @ChristophBerg as he committed that.

[ChristophKaser]

It seems archive_ready does not work for the current version of postgres (13) and always finds 0 ready files. is there some workaround to get it running?

[ChristophKaser]

Oh I see - that is indeed the case.

pinging @ChristophBerg as he committed that.

I think the person to ping was @df7cb

[turnstep]

Looks like this was fixed as of 1e06b0d

[mbanck]

@turnstep how so, that commit (1e06b0d) is from 2015 - maybe it makes it possible to succesfully run check_archive_ready if one sets up an elaborate helper function (I did not check), but fact is that this check currently just reports OK on PG10+ as it does not check in pg_wal/archive_status but in pg_wal directly, please reopen.

postgres@db01:~$ touch /var/lib/postgresql/13/main/pg_wal/archive_status/000000010000000000000001.ready
postgres@db01:~$ touch /var/lib/postgresql/13/main/pg_wal/archive_status/000000010000000000000002.ready
postgres@db01:~$ LANG=C check_postgres -w 1 -c 1 --action archive_ready -H /var/run/postgresql/
POSTGRES_ARCHIVE_READY OK: DB "postgres" (host:/var/run/postgresql/) WAL ".ready" files found: 0 | time=0.02s files=0;1;1 

[mbanck]

With the above PR, I get:

postgres@db01:~$ LANG=C /tmp/check_postgres -w 1 -c 1 --action archive_ready -H /var/run/postgresql/
POSTGRES_ARCHIVE_READY CRITICAL: DB "postgres" (host:/var/run/postgresql/) WAL ".ready" files found: 2 | time=0.02s files=2;1;1