Function for listing pg_wal/summaries directory
Hi,
While WAL summaries feature and some support functions have been added
in version 17, merely listing the contents of the pg_wal/summaries
directory is missing. As discussed in the pg_ls_archive_status()
discussion, it would be convenient to add pg_ls_summariesdir() that
lists the contents of the pg_wal/summaries directory with the pg_monitor
role.
This patch is based on a very recent master and does not include the
catversion bump.
Best,
Yushi
Attachments:
ls_summariesdir.difftext/x-diff; name=ls_summariesdir.diffDownload
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index d6acdd3059..1b7f5c3a12 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -30530,6 +30530,30 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_ls_summariesdir</primary>
+ </indexterm>
+ <function>pg_ls_summariesdir</function> ()
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>name</parameter> <type>text</type>,
+ <parameter>size</parameter> <type>bigint</type>,
+ <parameter>modification</parameter> <type>timestamp with time zone</type> )
+ </para>
+ <para>
+ Returns the name, size, and last modification time (mtime) of each
+ ordinary file in the server's WAL summary directory
+ (<filename>pg_wal/summaries</filename>). Filenames beginning
+ with a dot, directories, and other special files are excluded.
+ </para>
+ <para>
+ This function is restricted to superusers and members of
+ the <literal>pg_monitor</literal> role by default, but other users can
+ be granted EXECUTE to run the function.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index b0d0de051e..fd6b606ae9 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -700,6 +700,8 @@ REVOKE EXECUTE ON FUNCTION pg_ls_waldir() FROM public;
REVOKE EXECUTE ON FUNCTION pg_ls_archive_statusdir() FROM public;
+REVOKE EXECUTE ON FUNCTION pg_ls_summariesdir() FROM public;
+
REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir() FROM public;
REVOKE EXECUTE ON FUNCTION pg_ls_tmpdir(oid) FROM public;
@@ -770,6 +772,8 @@ GRANT EXECUTE ON FUNCTION pg_ls_waldir() TO pg_monitor;
GRANT EXECUTE ON FUNCTION pg_ls_archive_statusdir() TO pg_monitor;
+GRANT EXECUTE ON FUNCTION pg_ls_summariesdir() TO pg_monitor;
+
GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor;
GRANT EXECUTE ON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor;
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index 24b95c32b7..d84ed8970e 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -689,6 +689,15 @@ pg_ls_archive_statusdir(PG_FUNCTION_ARGS)
return pg_ls_dir_files(fcinfo, XLOGDIR "/archive_status", true);
}
+/*
+ * Function to return the list of files in the WAL summary directory.
+ */
+Datum
+pg_ls_summariesdir(PG_FUNCTION_ARGS)
+{
+ return pg_ls_dir_files(fcinfo, XLOGDIR "/summaries", true);
+}
+
/*
* Function to return the list of files in the PG_LOGICAL_SNAPSHOTS_DIR
* directory.
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 77f54a79e6..a3035559b6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12173,6 +12173,12 @@
provolatile => 'v', prorettype => 'record', proargtypes => '',
proallargtypes => '{text,int8,timestamptz}', proargmodes => '{o,o,o}',
proargnames => '{name,size,modification}', prosrc => 'pg_ls_waldir' },
+{ oid => '5101', descr => 'list of files in the summaries directory',
+ proname => 'pg_ls_summariesdir', 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_summariesdir' },
{ oid => '5031', descr => 'list of files in the archive_status directory',
proname => 'pg_ls_archive_statusdir', procost => '10', prorows => '20',
proretset => 't', provolatile => 'v', prorettype => 'record',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index 5f7bf6b8af..36b1201f9f 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -412,6 +412,12 @@ select count(*) >= 0 as ok from pg_ls_archive_statusdir();
t
(1 row)
+select count(*) >= 0 as ok from pg_ls_summariesdir();
+ ok
+----
+ t
+(1 row)
+
-- pg_read_file()
select length(pg_read_file('postmaster.pid')) > 20;
?column?
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 1e90d60af3..b7495d70eb 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -163,6 +163,7 @@ select (w).size = :segsize as ok
from (select pg_ls_waldir() w) ss where length((w).name) = 24 limit 1;
select count(*) >= 0 as ok from pg_ls_archive_statusdir();
+select count(*) >= 0 as ok from pg_ls_summariesdir();
-- pg_read_file()
select length(pg_read_file('postmaster.pid')) > 20;
On Fri, Oct 04, 2024 at 11:32:08AM +0900, btogiwarayuushi wrote:
While WAL summaries feature and some support functions have been added in
version 17, merely listing the contents of the pg_wal/summaries directory is
missing.
Could you explain why you feel the existing support functions are
insufficient?
--
nathan
On Fri, Oct 04, 2024 at 10:02:11AM -0500, Nathan Bossart wrote:
Could you explain why you feel the existing support functions are
insufficient?
Because it is not possible to outsource the scan of pg_wal/summaries/
to a different role, no?
On HEAD, one would require a full access to the data folder, as
pg_ls_waldir() does not recurse. Perhaps, rather than introducing a
new function, we should just extend pg_ls_dir_files() so as it can
optionally recurse, then use this option in the existing
pg_ls_waldir() to show stats for pg_wal/ and pg_wal/summary/? You'd
optinally need to print the relative path of the file in the output of
the function, meaning that any summary file in should be shown as
summaries/blah.summary. Just an idea for the bucket of ideas.
This would be unconsistent with the existing
pg_ls_archive_statusdir(), though, so using a new function may be just
better and simpler.
--
Michael
On Mon, Oct 07, 2024 at 10:07:10AM +0900, Michael Paquier wrote:
On Fri, Oct 04, 2024 at 10:02:11AM -0500, Nathan Bossart wrote:
Could you explain why you feel the existing support functions are
insufficient?Because it is not possible to outsource the scan of pg_wal/summaries/
to a different role, no?
I was under the impression that you could do this with
pg_available_wal_summaries() [0]https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-WAL-SUMMARY.
[0]: https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-WAL-SUMMARY
--
nathan
On 2024/10/07 23:35, Nathan Bossart wrote:
On Mon, Oct 07, 2024 at 10:07:10AM +0900, Michael Paquier wrote:
On Fri, Oct 04, 2024 at 10:02:11AM -0500, Nathan Bossart wrote:
Could you explain why you feel the existing support functions are
insufficient?Because it is not possible to outsource the scan of pg_wal/summaries/
to a different role, no?I was under the impression that you could do this with
pg_available_wal_summaries() [0].[0] https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-WAL-SUMMARY
One benefit of supporting something like pg_ls_summariesdir() is that
it allows us to view the last modification time of each WAL summary file
and estimate when they'll be removed based on wal_summary_keep_time.
Of course, we could also extend the existing function to report
the last modification time if this use case is valid, though.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
On Tue, Oct 08, 2024 at 12:41:16PM +0900, Fujii Masao wrote:
One benefit of supporting something like pg_ls_summariesdir() is that
it allows us to view the last modification time of each WAL summary file
and estimate when they'll be removed based on wal_summary_keep_time.Of course, we could also extend the existing function to report
the last modification time if this use case is valid, though.
My argument is about knowing the size of each file, for monitoring of
disk space. The retention can be controlled by a GUC based on time,
and this function requires knowing about the file name format.
--
Michael
On Tue, Oct 08, 2024 at 01:19:52PM +0900, Michael Paquier wrote:
On Tue, Oct 08, 2024 at 12:41:16PM +0900, Fujii Masao wrote:
One benefit of supporting something like pg_ls_summariesdir() is that
it allows us to view the last modification time of each WAL summary file
and estimate when they'll be removed based on wal_summary_keep_time.Of course, we could also extend the existing function to report
the last modification time if this use case is valid, though.My argument is about knowing the size of each file, for monitoring of
disk space. The retention can be controlled by a GUC based on time,
and this function requires knowing about the file name format.
Okay. I have no problem with adding something like pg_ls_summariesdir(),
but I guess I was hopeful we could just add any missing information to the
existing WAL summarization information functions. A new pg_ls_*dir()
function would indeed fit nicely with the existing suite of generic file
access functions.
The patch posted upthread looks reasonable to me, so I'll go commit it soon
unless there is any feedback. IMHO we should consider alphabetizing the
table in the docs [0]https://www.postgresql.org/docs/devel/functions-admin.html#FUNCTIONS-ADMIN-GENFILE, too.
[0]: https://www.postgresql.org/docs/devel/functions-admin.html#FUNCTIONS-ADMIN-GENFILE
--
nathan
On 2024/10/08 23:36, Nathan Bossart wrote:
On Tue, Oct 08, 2024 at 01:19:52PM +0900, Michael Paquier wrote:
On Tue, Oct 08, 2024 at 12:41:16PM +0900, Fujii Masao wrote:
One benefit of supporting something like pg_ls_summariesdir() is that
it allows us to view the last modification time of each WAL summary file
and estimate when they'll be removed based on wal_summary_keep_time.Of course, we could also extend the existing function to report
the last modification time if this use case is valid, though.My argument is about knowing the size of each file, for monitoring of
disk space. The retention can be controlled by a GUC based on time,
and this function requires knowing about the file name format.Okay. I have no problem with adding something like pg_ls_summariesdir(),
but I guess I was hopeful we could just add any missing information to the
existing WAL summarization information functions. A new pg_ls_*dir()
function would indeed fit nicely with the existing suite of generic file
access functions.The patch posted upthread looks reasonable to me, so I'll go commit it soon
unless there is any feedback.
Thanks! The patch looks good to me, too.
IMHO we should consider alphabetizing the
table in the docs [0], too.
+1
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
On Fri, Oct 11, 2024 at 11:09:30AM +0900, Fujii Masao wrote:
On 2024/10/08 23:36, Nathan Bossart wrote:
The patch posted upthread looks reasonable to me, so I'll go commit it soon
unless there is any feedback.Thanks! The patch looks good to me, too.
Committed.
--
nathan