Hi,
while setting up monitoring for a new PostgreSQL instance, I noticed that
there's no build-in way for a pg_monitor role to check the contents of
the archive_status directory. We got pg_ls_waldir() in 10, but that
only lists pg_wal - not it's subdirectory. It seems listing the
archive_status directory wasn't even really discussed (or my Google-Fu
betrayed me?). Of course, these days people should use streaming archiving
(but there're still environments where that's not an option); and of
course it's possible to create a wrapper function for
pg_ls_dir('pg_wal/archive_status') with SECURITY DEFINER set - but the
same could have been said about pg_ls_waldir(), and it didn't stop
anyone.
Without further ado, I present a patch to implement pg_ls_archive_status(),
which fills this gap. I believe the function name is long enough and we
don't need an extra wal in there. The patch is based on a very recent
master (just pulled and merged), but does not include the catversion
bump (avoid conflict on merge).
Is this relevant?
Regards,
Christoph
--
Spare Space
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9a7f683658..55bf36c5c4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20357,6 +20357,18 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
</row>
<row>
<entry>
+ <literal><function>pg_ls_archive_status()</function></literal>
+ </entry>
+ <entry><type>setof record</type></entry>
+ <entry>
+ List the name, size, and last modification time of files in the WAL
+ archive status directory. Access is granted to members of the
+ <literal>pg_monitor</literal> role and may be granted to other
+ non-superuser roles.
+ </entry>
+ </row>
+ <row>
+ <entry>
<literal><function>pg_read_file(<parameter>filename</parameter> <type>text</type> [, <parameter>offset</parameter> <type>bigint</type>, <parameter>length</parameter> <type>bigint</type> [, <parameter>missing_ok</parameter> <type>boolean</type>] ])</function></literal>
</entry>
<entry><type>text</type></entry>
@@ -20429,6 +20441,17 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup());
</para>
<indexterm>
+ <primary>pg_ls_archive_status</primary>
+ </indexterm>
+ <para>
+ <function>pg_ls_archive_status</function> returns the name, size, and
+ last modified time (mtime) of each file in the write ahead log (WAL)
+ <literal>archive_status</literal> directory. By default only superusers
+ and members of the <literal>pg_monitor</literal> role can use this
+ function. Access may be granted to others using <command>GRANT</command>.
+ </para>
+
+ <indexterm>
<primary>pg_read_file</primary>
</indexterm>
<para>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 7251552419..6d4d0b8d8c 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1150,6 +1150,7 @@ REVOKE EXECUTE ON FUNCTION lo_export(oid, text) FROM public;
REVOKE EXECUTE ON FUNCTION pg_ls_logdir() FROM public;
REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_archive_status() FROM public;
REVOKE EXECUTE ON FUNCTION pg_read_file(text) FROM public;
REVOKE EXECUTE ON FUNCTION pg_read_file(text,bigint,bigint) FROM public;
@@ -1170,6 +1171,7 @@ REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
--
GRANT EXECUTE ON FUNCTION pg_ls_logdir() TO pg_monitor;
GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_ls_archive_status() TO pg_monitor;
GRANT pg_read_all_settings TO pg_monitor;
GRANT pg_read_all_stats TO pg_monitor;
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index a97cbea248..b51137aa63 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -610,3 +610,10 @@ pg_ls_waldir(PG_FUNCTION_ARGS)
{
return pg_ls_dir_files(fcinfo, XLOGDIR);
}
+
+/* Function to return the list of files in the WAL archive_status directory */
+Datum
+pg_ls_archive_status(PG_FUNCTION_ARGS)
+{
+ return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status");
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 8e4145f42b..b85a978559 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10199,6 +10199,11 @@
provolatile => 'v', prorettype => 'record', proargtypes => '',
proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
proargnames => '{name,size,modification}', prosrc => 'pg_ls_waldir' },
+{ oid => '3996', descr => 'list of files in the archive_status directory',
+ proname => 'pg_ls_archive_status', procost => '10', prorows => '20', proretset => 't',
+ provolatile => 'v', prorettype => 'record', proargtypes => '',
+ proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
+ proargnames => '{name,size,modification}', prosrc => 'pg_ls_archive_status' },
# hash partitioning constraint function
{ oid => '5028', descr => 'hash partition CHECK constraint',
