Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit1922d7c

Browse files
committed
Add SQL functions to monitor the directory contents of replication slots
This commit adds a set of functions able to look at the contents ofvarious paths related to replication slots:- pg_ls_logicalsnapdir, for pg_logical/snapshots/- pg_ls_logicalmapdir, for pg_logical/mappings/- pg_ls_replslotdir, for pg_replslot/<slot_name>/These are intended to be used by monitoring tools. Unlike pg_ls_dir(),execution permission can be granted to non-superusers. Roles members ofpg_monitor gain have access to those functions.Bump catalog version.Author: Bharath RupireddyReviewed-by: Nathan Bossart, Justin PryzbyDiscussion:https://postgr.es/m/CALj2ACWsfizZjMN6bzzdxOk1ADQQeSw8HhEjhmVXn_Pu+7VzLw@mail.gmail.com
1 parentb55f2b6 commit1922d7c

File tree

9 files changed

+250
-1
lines changed

9 files changed

+250
-1
lines changed

‎contrib/test_decoding/expected/slot.out

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,27 @@ SELECT pg_drop_replication_slot('regression_slot_t');
4848
ERROR: replication slot "regression_slot_t" does not exist
4949
SELECT pg_drop_replication_slot('regression_slot_t2');
5050
ERROR: replication slot "regression_slot_t2" does not exist
51+
-- monitoring functions for slot directories
52+
SELECT count(*) >= 0 AS ok FROM pg_ls_logicalmapdir();
53+
ok
54+
----
55+
t
56+
(1 row)
57+
58+
SELECT count(*) >= 0 AS ok FROM pg_ls_logicalsnapdir();
59+
ok
60+
----
61+
t
62+
(1 row)
63+
64+
SELECT count(*) >= 0 AS ok FROM pg_ls_replslotdir('regression_slot_p');
65+
ok
66+
----
67+
t
68+
(1 row)
69+
70+
SELECT count(*) >= 0 AS ok FROM pg_ls_replslotdir('not_existing_slot'); -- fails
71+
ERROR: replication slot "not_existing_slot" does not exist
5172
-- permanent slot has survived
5273
SELECT pg_drop_replication_slot('regression_slot_p');
5374
pg_drop_replication_slot

‎contrib/test_decoding/sql/slot.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,12 @@ end';
2828
SELECT pg_drop_replication_slot('regression_slot_t');
2929
SELECT pg_drop_replication_slot('regression_slot_t2');
3030

31+
-- monitoring functions for slot directories
32+
SELECTcount(*)>=0AS okFROM pg_ls_logicalmapdir();
33+
SELECTcount(*)>=0AS okFROM pg_ls_logicalsnapdir();
34+
SELECTcount(*)>=0AS okFROM pg_ls_replslotdir('regression_slot_p');
35+
SELECTcount(*)>=0AS okFROM pg_ls_replslotdir('not_existing_slot');-- fails
36+
3137
-- permanent slot has survived
3238
SELECT pg_drop_replication_slot('regression_slot_p');
3339

‎doc/src/sgml/func.sgml

Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27417,6 +27417,79 @@ SELECT convert_from(pg_read_binary_file('file_in_utf8.txt'), 'UTF8');
2741727417
can be granted EXECUTE to run the function.
2741827418
</para></entry>
2741927419
</row>
27420+
27421+
<row>
27422+
<entry role="func_table_entry"><para role="func_signature">
27423+
<indexterm>
27424+
<primary>pg_ls_logicalsnapdir</primary>
27425+
</indexterm>
27426+
<function>pg_ls_logicalsnapdir</function> ()
27427+
<returnvalue>setof record</returnvalue>
27428+
( <parameter>name</parameter> <type>text</type>,
27429+
<parameter>size</parameter> <type>bigint</type>,
27430+
<parameter>modification</parameter> <type>timestamp with time zone</type> )
27431+
</para>
27432+
<para>
27433+
Returns the name, size, and last modification time (mtime) of each
27434+
ordinary file in the server's <filename>pg_logical/snapshots</filename>
27435+
directory. Filenames beginning with a dot, directories, and other
27436+
special files are excluded.
27437+
</para>
27438+
<para>
27439+
This function is restricted to superusers and members of
27440+
the <literal>pg_monitor</literal> role by default, but other users can
27441+
be granted EXECUTE to run the function.
27442+
</para></entry>
27443+
</row>
27444+
27445+
<row>
27446+
<entry role="func_table_entry"><para role="func_signature">
27447+
<indexterm>
27448+
<primary>pg_ls_logicalmapdir</primary>
27449+
</indexterm>
27450+
<function>pg_ls_logicalmapdir</function> ()
27451+
<returnvalue>setof record</returnvalue>
27452+
( <parameter>name</parameter> <type>text</type>,
27453+
<parameter>size</parameter> <type>bigint</type>,
27454+
<parameter>modification</parameter> <type>timestamp with time zone</type> )
27455+
</para>
27456+
<para>
27457+
Returns the name, size, and last modification time (mtime) of each
27458+
ordinary file in the server's <filename>pg_logical/mappings</filename>
27459+
directory. Filenames beginning with a dot, directories, and other
27460+
special files are excluded.
27461+
</para>
27462+
<para>
27463+
This function is restricted to superusers and members of
27464+
the <literal>pg_monitor</literal> role by default, but other users can
27465+
be granted EXECUTE to run the function.
27466+
</para></entry>
27467+
</row>
27468+
27469+
<row>
27470+
<entry role="func_table_entry"><para role="func_signature">
27471+
<indexterm>
27472+
<primary>pg_ls_replslotdir</primary>
27473+
</indexterm>
27474+
<function>pg_ls_replslotdir</function> ( <parameter>slot_name</parameter> <type>text</type> )
27475+
<returnvalue>setof record</returnvalue>
27476+
( <parameter>name</parameter> <type>text</type>,
27477+
<parameter>size</parameter> <type>bigint</type>,
27478+
<parameter>modification</parameter> <type>timestamp with time zone</type> )
27479+
</para>
27480+
<para>
27481+
Returns the name, size, and last modification time (mtime) of each
27482+
ordinary file in the server's <filename>pg_replslot/slot_name</filename>
27483+
directory, where <parameter>slot_name</parameter> is the name of the
27484+
replication slot provided as input of the function. Filenames beginning
27485+
with a dot, directories, and other special files are excluded.
27486+
</para>
27487+
<para>
27488+
This function is restricted to superusers and members of
27489+
the <literal>pg_monitor</literal> role by default, but other users can
27490+
be granted EXECUTE to run the function.
27491+
</para></entry>
27492+
</row>
2742027493
</tbody>
2742127494
</tgroup>
2742227495
</table>

‎src/backend/catalog/system_functions.sql

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -701,6 +701,12 @@ REVOKE EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) FROM public;
701701

702702
REVOKE EXECUTEON FUNCTION pg_log_backend_memory_contexts(integer)FROM PUBLIC;
703703

704+
REVOKE EXECUTEON FUNCTION pg_ls_logicalsnapdir()FROM PUBLIC;
705+
706+
REVOKE EXECUTEON FUNCTION pg_ls_logicalmapdir()FROM PUBLIC;
707+
708+
REVOKE EXECUTEON FUNCTION pg_ls_replslotdir(text)FROM PUBLIC;
709+
704710
--
705711
-- We also set up some things as accessible to standard roles.
706712
--
@@ -715,6 +721,12 @@ GRANT EXECUTE ON FUNCTION pg_ls_tmpdir() TO pg_monitor;
715721

716722
GRANT EXECUTEON FUNCTION pg_ls_tmpdir(oid) TO pg_monitor;
717723

724+
GRANT EXECUTEON FUNCTION pg_ls_logicalsnapdir() TO pg_monitor;
725+
726+
GRANT EXECUTEON FUNCTION pg_ls_logicalmapdir() TO pg_monitor;
727+
728+
GRANT EXECUTEON FUNCTION pg_ls_replslotdir(text) TO pg_monitor;
729+
718730
GRANT pg_read_all_settings TO pg_monitor;
719731

720732
GRANT pg_read_all_stats TO pg_monitor;

‎src/backend/utils/adt/genfile.c

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,7 @@
2929
#include"mb/pg_wchar.h"
3030
#include"miscadmin.h"
3131
#include"postmaster/syslogger.h"
32+
#include"replication/slot.h"
3233
#include"storage/fd.h"
3334
#include"utils/acl.h"
3435
#include"utils/builtins.h"
@@ -720,3 +721,46 @@ pg_ls_archive_statusdir(PG_FUNCTION_ARGS)
720721
{
721722
returnpg_ls_dir_files(fcinfo,XLOGDIR"/archive_status", true);
722723
}
724+
725+
/*
726+
* Function to return the list of files in the pg_logical/snapshots directory.
727+
*/
728+
Datum
729+
pg_ls_logicalsnapdir(PG_FUNCTION_ARGS)
730+
{
731+
returnpg_ls_dir_files(fcinfo,"pg_logical/snapshots", false);
732+
}
733+
734+
/*
735+
* Function to return the list of files in the pg_logical/mappings directory.
736+
*/
737+
Datum
738+
pg_ls_logicalmapdir(PG_FUNCTION_ARGS)
739+
{
740+
returnpg_ls_dir_files(fcinfo,"pg_logical/mappings", false);
741+
}
742+
743+
/*
744+
* Function to return the list of files in the pg_replslot/<replication_slot>
745+
* directory.
746+
*/
747+
Datum
748+
pg_ls_replslotdir(PG_FUNCTION_ARGS)
749+
{
750+
text*slotname_t;
751+
charpath[MAXPGPATH];
752+
char*slotname;
753+
754+
slotname_t=PG_GETARG_TEXT_PP(0);
755+
756+
slotname=text_to_cstring(slotname_t);
757+
758+
if (!SearchNamedReplicationSlot(slotname, true))
759+
ereport(ERROR,
760+
(errcode(ERRCODE_UNDEFINED_OBJECT),
761+
errmsg("replication slot \"%s\" does not exist",
762+
slotname)));
763+
764+
snprintf(path,sizeof(path),"pg_replslot/%s",slotname);
765+
returnpg_ls_dir_files(fcinfo,path, false);
766+
}

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202111171
56+
#defineCATALOG_VERSION_NO202111231
5757

5858
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11623,6 +11623,28 @@
1162311623
proallargtypes => '{oid,text,int8,timestamptz}', proargmodes => '{i,o,o,o}',
1162411624
proargnames => '{tablespace,name,size,modification}',
1162511625
prosrc => 'pg_ls_tmpdir_1arg' },
11626+
{ oid => '9858',
11627+
descr => 'list of files in the pg_logical/snapshots directory',
11628+
proname => 'pg_ls_logicalsnapdir', procost => '10', prorows => '20',
11629+
proretset => 't', provolatile => 'v', prorettype => 'record',
11630+
proargtypes => '', proallargtypes => '{text,int8,timestamptz}',
11631+
proargmodes => '{o,o,o}', proargnames => '{name,size,modification}',
11632+
prosrc => 'pg_ls_logicalsnapdir' },
11633+
{ oid => '9859',
11634+
descr => 'list of files in the pg_logical/mappings directory',
11635+
proname => 'pg_ls_logicalmapdir', procost => '10', prorows => '20',
11636+
proretset => 't', provolatile => 'v', prorettype => 'record',
11637+
proargtypes => '', proallargtypes => '{text,int8,timestamptz}',
11638+
proargmodes => '{o,o,o}', proargnames => '{name,size,modification}',
11639+
prosrc => 'pg_ls_logicalmapdir' },
11640+
{ oid => '9860',
11641+
descr => 'list of files in the pg_replslot/slot_name directory',
11642+
proname => 'pg_ls_replslotdir', procost => '10', prorows => '20',
11643+
proretset => 't', provolatile => 'v', prorettype => 'record',
11644+
proargtypes => 'text', proallargtypes => '{text,text,int8,timestamptz}',
11645+
proargmodes => '{i,o,o,o}',
11646+
proargnames => '{slot_name,name,size,modification}',
11647+
prosrc => 'pg_ls_replslotdir' },
1162611648

1162711649
# hash partitioning constraint function
1162811650
{ oid => '5028', descr => 'hash partition CHECK constraint',

‎src/test/regress/expected/misc_functions.out

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -243,6 +243,56 @@ select count(*) > 0 from
243243
t
244244
(1 row)
245245

246+
--
247+
-- Test replication slot directory functions
248+
--
249+
CREATE ROLE regress_slot_dir_funcs;
250+
-- Not available by default.
251+
SELECT has_function_privilege('regress_slot_dir_funcs',
252+
'pg_ls_logicalsnapdir()', 'EXECUTE');
253+
has_function_privilege
254+
------------------------
255+
f
256+
(1 row)
257+
258+
SELECT has_function_privilege('regress_slot_dir_funcs',
259+
'pg_ls_logicalmapdir()', 'EXECUTE');
260+
has_function_privilege
261+
------------------------
262+
f
263+
(1 row)
264+
265+
SELECT has_function_privilege('regress_slot_dir_funcs',
266+
'pg_ls_replslotdir(text)', 'EXECUTE');
267+
has_function_privilege
268+
------------------------
269+
f
270+
(1 row)
271+
272+
GRANT pg_monitor TO regress_slot_dir_funcs;
273+
-- Role is now part of pg_monitor, so these are available.
274+
SELECT has_function_privilege('regress_slot_dir_funcs',
275+
'pg_ls_logicalsnapdir()', 'EXECUTE');
276+
has_function_privilege
277+
------------------------
278+
t
279+
(1 row)
280+
281+
SELECT has_function_privilege('regress_slot_dir_funcs',
282+
'pg_ls_logicalmapdir()', 'EXECUTE');
283+
has_function_privilege
284+
------------------------
285+
t
286+
(1 row)
287+
288+
SELECT has_function_privilege('regress_slot_dir_funcs',
289+
'pg_ls_replslotdir(text)', 'EXECUTE');
290+
has_function_privilege
291+
------------------------
292+
t
293+
(1 row)
294+
295+
DROP ROLE regress_slot_dir_funcs;
246296
--
247297
-- Test adding a support function to a subject function
248298
--

‎src/test/regress/sql/misc_functions.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,27 @@ select count(*) > 0 from
9191
where spcname='pg_default') pts
9292
join pg_database dbonpts.pts=db.oid;
9393

94+
--
95+
-- Test replication slot directory functions
96+
--
97+
CREATE ROLE regress_slot_dir_funcs;
98+
-- Not available by default.
99+
SELECT has_function_privilege('regress_slot_dir_funcs',
100+
'pg_ls_logicalsnapdir()','EXECUTE');
101+
SELECT has_function_privilege('regress_slot_dir_funcs',
102+
'pg_ls_logicalmapdir()','EXECUTE');
103+
SELECT has_function_privilege('regress_slot_dir_funcs',
104+
'pg_ls_replslotdir(text)','EXECUTE');
105+
GRANT pg_monitor TO regress_slot_dir_funcs;
106+
-- Role is now part of pg_monitor, so these are available.
107+
SELECT has_function_privilege('regress_slot_dir_funcs',
108+
'pg_ls_logicalsnapdir()','EXECUTE');
109+
SELECT has_function_privilege('regress_slot_dir_funcs',
110+
'pg_ls_logicalmapdir()','EXECUTE');
111+
SELECT has_function_privilege('regress_slot_dir_funcs',
112+
'pg_ls_replslotdir(text)','EXECUTE');
113+
DROP ROLE regress_slot_dir_funcs;
114+
94115
--
95116
-- Test adding a support function to a subject function
96117
--

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp