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

Commit2258e76

Browse files
committed
Add contrib/pg_walinspect.
Provides similar functionality to pg_waldump, but from a SQL interfacerather than a separate utility.Author: Bharath RupireddyReviewed-by: Greg Stark, Kyotaro Horiguchi, Andres Freund, Ashutosh Sharma, Nitin Jadhav, RKN Sai KrishnaDiscussion:https://postgr.es/m/CALj2ACUGUYXsEQdKhEdsBzhGEyF3xggvLdD8C0VT72TNEfOiog%40mail.gmail.com
1 parent708007d commit2258e76

25 files changed

+1675
-204
lines changed

‎contrib/Makefile

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ SUBDIRS = \
4141
pgrowlocks\
4242
pgstattuple\
4343
pg_visibility\
44+
pg_walinspect\
4445
postgres_fdw\
4546
seg\
4647
spi\

‎contrib/pg_walinspect/.gitignore

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
# Generated subdirectories
2+
/log/
3+
/results/
4+
/tmp_check/

‎contrib/pg_walinspect/Makefile

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,23 @@
1+
# contrib/pg_walinspect/Makefile
2+
3+
MODULE_big = pg_walinspect
4+
OBJS =\
5+
$(WIN32RES)\
6+
pg_walinspect.o
7+
PGFILEDESC = "pg_walinspect - functions to inspect contents of PostgreSQL Write-Ahead Log"
8+
9+
EXTENSION = pg_walinspect
10+
DATA = pg_walinspect--1.0.sql
11+
12+
REGRESS = pg_walinspect
13+
14+
ifdefUSE_PGXS
15+
PG_CONFIG = pg_config
16+
PGXS :=$(shell$(PG_CONFIG) --pgxs)
17+
include$(PGXS)
18+
else
19+
subdir = contrib/pg_walinspect
20+
top_builddir = ../..
21+
include$(top_builddir)/src/Makefile.global
22+
include$(top_srcdir)/contrib/contrib-global.mk
23+
endif
Lines changed: 165 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,165 @@
1+
CREATE EXTENSION pg_walinspect;
2+
CREATE TABLE sample_tbl(col1 int, col2 int);
3+
-- Make sure checkpoints don't interfere with the test.
4+
SELECT lsn as wal_lsn1 FROM
5+
pg_create_physical_replication_slot('regress_pg_walinspect_slot', true, false)
6+
\gset
7+
INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
8+
SELECT pg_current_wal_lsn() AS wal_lsn2 \gset
9+
INSERT INTO sample_tbl SELECT * FROM generate_series(1, 2);
10+
-- ===================================================================
11+
-- Tests for input validation
12+
-- ===================================================================
13+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn2', :'wal_lsn1'); -- ERROR
14+
ERROR: WAL start LSN must be less than end LSN
15+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'wal_lsn2', :'wal_lsn1'); -- ERROR
16+
ERROR: WAL start LSN must be less than end LSN
17+
-- ===================================================================
18+
-- Tests for all function executions
19+
-- ===================================================================
20+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_record_info(:'wal_lsn1');
21+
ok
22+
----
23+
t
24+
(1 row)
25+
26+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2');
27+
ok
28+
----
29+
t
30+
(1 row)
31+
32+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_records_info_till_end_of_wal(:'wal_lsn1');
33+
ok
34+
----
35+
t
36+
(1 row)
37+
38+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats(:'wal_lsn1', :'wal_lsn2');
39+
ok
40+
----
41+
t
42+
(1 row)
43+
44+
SELECT COUNT(*) >= 0 AS ok FROM pg_get_wal_stats_till_end_of_wal(:'wal_lsn1');
45+
ok
46+
----
47+
t
48+
(1 row)
49+
50+
-- ===================================================================
51+
-- Test for filtering out WAL records of a particular table
52+
-- ===================================================================
53+
SELECT oid AS sample_tbl_oid FROM pg_class WHERE relname = 'sample_tbl' \gset
54+
SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2')
55+
WHERE block_ref LIKE concat('%', :'sample_tbl_oid', '%') AND resource_manager = 'Heap';
56+
ok
57+
----
58+
t
59+
(1 row)
60+
61+
-- ===================================================================
62+
-- Test for filtering out WAL records based on resource_manager and
63+
-- record_type
64+
-- ===================================================================
65+
SELECT COUNT(*) >= 1 AS ok FROM pg_get_wal_records_info(:'wal_lsn1', :'wal_lsn2')
66+
WHERE resource_manager = 'Heap' AND record_type = 'INSERT';
67+
ok
68+
----
69+
t
70+
(1 row)
71+
72+
-- ===================================================================
73+
-- Tests for permissions
74+
-- ===================================================================
75+
CREATE ROLE regress_pg_walinspect;
76+
SELECT has_function_privilege('regress_pg_walinspect',
77+
'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- no
78+
has_function_privilege
79+
------------------------
80+
f
81+
(1 row)
82+
83+
SELECT has_function_privilege('regress_pg_walinspect',
84+
'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- no
85+
has_function_privilege
86+
------------------------
87+
f
88+
(1 row)
89+
90+
SELECT has_function_privilege('regress_pg_walinspect',
91+
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- no
92+
has_function_privilege
93+
------------------------
94+
f
95+
(1 row)
96+
97+
-- Functions accessible by users with role pg_read_server_files
98+
GRANT pg_read_server_files TO regress_pg_walinspect;
99+
SELECT has_function_privilege('regress_pg_walinspect',
100+
'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
101+
has_function_privilege
102+
------------------------
103+
t
104+
(1 row)
105+
106+
SELECT has_function_privilege('regress_pg_walinspect',
107+
'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
108+
has_function_privilege
109+
------------------------
110+
t
111+
(1 row)
112+
113+
SELECT has_function_privilege('regress_pg_walinspect',
114+
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
115+
has_function_privilege
116+
------------------------
117+
t
118+
(1 row)
119+
120+
REVOKE pg_read_server_files FROM regress_pg_walinspect;
121+
-- Superuser can grant execute to other users
122+
GRANT EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
123+
TO regress_pg_walinspect;
124+
GRANT EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
125+
TO regress_pg_walinspect;
126+
GRANT EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
127+
TO regress_pg_walinspect;
128+
SELECT has_function_privilege('regress_pg_walinspect',
129+
'pg_get_wal_record_info(pg_lsn)', 'EXECUTE'); -- yes
130+
has_function_privilege
131+
------------------------
132+
t
133+
(1 row)
134+
135+
SELECT has_function_privilege('regress_pg_walinspect',
136+
'pg_get_wal_records_info(pg_lsn, pg_lsn) ', 'EXECUTE'); -- yes
137+
has_function_privilege
138+
------------------------
139+
t
140+
(1 row)
141+
142+
SELECT has_function_privilege('regress_pg_walinspect',
143+
'pg_get_wal_stats(pg_lsn, pg_lsn, boolean) ', 'EXECUTE'); -- yes
144+
has_function_privilege
145+
------------------------
146+
t
147+
(1 row)
148+
149+
REVOKE EXECUTE ON FUNCTION pg_get_wal_record_info(pg_lsn)
150+
FROM regress_pg_walinspect;
151+
REVOKE EXECUTE ON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)
152+
FROM regress_pg_walinspect;
153+
REVOKE EXECUTE ON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn, boolean)
154+
FROM regress_pg_walinspect;
155+
-- ===================================================================
156+
-- Clean up
157+
-- ===================================================================
158+
DROP ROLE regress_pg_walinspect;
159+
SELECT pg_drop_replication_slot('regress_pg_walinspect_slot');
160+
pg_drop_replication_slot
161+
--------------------------
162+
163+
(1 row)
164+
165+
DROP TABLE sample_tbl;
Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,118 @@
1+
/* contrib/pg_walinspect/pg_walinspect--1.0.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use"CREATE EXTENSION pg_walinspect" to load this file. \quit
5+
6+
--
7+
-- pg_get_wal_record_info()
8+
--
9+
CREATEFUNCTIONpg_get_wal_record_info(IN in_lsn pg_lsn,
10+
OUT start_lsn pg_lsn,
11+
OUT end_lsn pg_lsn,
12+
OUT prev_lsn pg_lsn,
13+
OUT xid xid,
14+
OUT resource_managertext,
15+
OUT record_typetext,
16+
OUT record_length int4,
17+
OUT main_data_length int4,
18+
OUT fpi_length int4,
19+
OUT descriptiontext,
20+
OUT block_reftext
21+
)
22+
AS'MODULE_PATHNAME','pg_get_wal_record_info'
23+
LANGUAGE C STRICT PARALLEL SAFE;
24+
25+
REVOKE EXECUTEON FUNCTION pg_get_wal_record_info(pg_lsn)FROM PUBLIC;
26+
GRANT EXECUTEON FUNCTION pg_get_wal_record_info(pg_lsn) TO pg_read_server_files;
27+
28+
--
29+
-- pg_get_wal_records_info()
30+
--
31+
CREATEFUNCTIONpg_get_wal_records_info(IN start_lsn pg_lsn,
32+
IN end_lsn pg_lsn,
33+
OUT start_lsn pg_lsn,
34+
OUT end_lsn pg_lsn,
35+
OUT prev_lsn pg_lsn,
36+
OUT xid xid,
37+
OUT resource_managertext,
38+
OUT record_typetext,
39+
OUT record_length int4,
40+
OUT main_data_length int4,
41+
OUT fpi_length int4,
42+
OUT descriptiontext,
43+
OUT block_reftext
44+
)
45+
RETURNS SETOF record
46+
AS'MODULE_PATHNAME','pg_get_wal_records_info'
47+
LANGUAGE C STRICT PARALLEL SAFE;
48+
49+
REVOKE EXECUTEON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn)FROM PUBLIC;
50+
GRANT EXECUTEON FUNCTION pg_get_wal_records_info(pg_lsn, pg_lsn) TO pg_read_server_files;
51+
52+
--
53+
-- pg_get_wal_records_info_till_end_of_wal()
54+
--
55+
CREATEFUNCTIONpg_get_wal_records_info_till_end_of_wal(IN start_lsn pg_lsn,
56+
OUT start_lsn pg_lsn,
57+
OUT end_lsn pg_lsn,
58+
OUT prev_lsn pg_lsn,
59+
OUT xid xid,
60+
OUT resource_managertext,
61+
OUT record_typetext,
62+
OUT record_length int4,
63+
OUT main_data_length int4,
64+
OUT fpi_length int4,
65+
OUT descriptiontext,
66+
OUT block_reftext
67+
)
68+
RETURNS SETOF record
69+
AS'MODULE_PATHNAME','pg_get_wal_records_info_till_end_of_wal'
70+
LANGUAGE C STRICT PARALLEL SAFE;
71+
72+
REVOKE EXECUTEON FUNCTION pg_get_wal_records_info_till_end_of_wal(pg_lsn)FROM PUBLIC;
73+
GRANT EXECUTEON FUNCTION pg_get_wal_records_info_till_end_of_wal(pg_lsn) TO pg_read_server_files;
74+
75+
--
76+
-- pg_get_wal_stats()
77+
--
78+
CREATEFUNCTIONpg_get_wal_stats(IN start_lsn pg_lsn,
79+
IN end_lsn pg_lsn,
80+
IN per_recordboolean DEFAULT false,
81+
OUT"resource_manager/record_type"text,
82+
OUT count int8,
83+
OUT count_percentage float4,
84+
OUT record_size int8,
85+
OUT record_size_percentage float4,
86+
OUT fpi_size int8,
87+
OUT fpi_size_percentage float4,
88+
OUT combined_size int8,
89+
OUT combined_size_percentage float4
90+
)
91+
RETURNS SETOF record
92+
AS'MODULE_PATHNAME','pg_get_wal_stats'
93+
LANGUAGE C STRICT PARALLEL SAFE;
94+
95+
REVOKE EXECUTEON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn,boolean)FROM PUBLIC;
96+
GRANT EXECUTEON FUNCTION pg_get_wal_stats(pg_lsn, pg_lsn,boolean) TO pg_read_server_files;
97+
98+
--
99+
-- pg_get_wal_stats_till_end_of_wal()
100+
--
101+
CREATEFUNCTIONpg_get_wal_stats_till_end_of_wal(IN start_lsn pg_lsn,
102+
IN per_recordboolean DEFAULT false,
103+
OUT"resource_manager/record_type"text,
104+
OUT count int8,
105+
OUT count_percentage float4,
106+
OUT record_size int8,
107+
OUT record_size_percentage float4,
108+
OUT fpi_size int8,
109+
OUT fpi_size_percentage float4,
110+
OUT combined_size int8,
111+
OUT combined_size_percentage float4
112+
)
113+
RETURNS SETOF record
114+
AS'MODULE_PATHNAME','pg_get_wal_stats_till_end_of_wal'
115+
LANGUAGE C STRICT PARALLEL SAFE;
116+
117+
REVOKE EXECUTEON FUNCTION pg_get_wal_stats_till_end_of_wal(pg_lsn,boolean)FROM PUBLIC;
118+
GRANT EXECUTEON FUNCTION pg_get_wal_stats_till_end_of_wal(pg_lsn,boolean) TO pg_read_server_files;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp