v18-0004-pg_walinspect-docs.patch
application/x-patch
Filename: v18-0004-pg_walinspect-docs.patch
Type: application/x-patch
Part: 3
Patch
Format: format-patch
Series: patch v18-0004
Subject: pg_walinspect docs
| File | + | − |
|---|---|---|
| doc/src/sgml/contrib.sgml | 1 | 0 |
| doc/src/sgml/filelist.sgml | 1 | 0 |
| doc/src/sgml/pgwalinspect.sgml | 244 | 0 |
From e146d961755294f8064d6afd0c24a6c6d77e11b4 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Thu, 7 Apr 2022 06:58:45 +0000
Subject: [PATCH v18] pg_walinspect docs
---
doc/src/sgml/contrib.sgml | 1 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/pgwalinspect.sgml | 244 +++++++++++++++++++++++++++++++++
3 files changed, 246 insertions(+)
create mode 100644 doc/src/sgml/pgwalinspect.sgml
diff --git a/doc/src/sgml/contrib.sgml b/doc/src/sgml/contrib.sgml
index 1e42ce1a7f..4e7b87a42f 100644
--- a/doc/src/sgml/contrib.sgml
+++ b/doc/src/sgml/contrib.sgml
@@ -131,6 +131,7 @@ CREATE EXTENSION <replaceable>module_name</replaceable>;
&pgsurgery;
&pgtrgm;
&pgvisibility;
+ &pgwalinspect;
&postgres-fdw;
&seg;
&sepgsql;
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 7dea670969..1e82cb2d3d 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -148,6 +148,7 @@
<!ENTITY pgsurgery SYSTEM "pgsurgery.sgml">
<!ENTITY pgtrgm SYSTEM "pgtrgm.sgml">
<!ENTITY pgvisibility SYSTEM "pgvisibility.sgml">
+<!ENTITY pgwalinspect SYSTEM "pgwalinspect.sgml">
<!ENTITY postgres-fdw SYSTEM "postgres-fdw.sgml">
<!ENTITY seg SYSTEM "seg.sgml">
<!ENTITY contrib-spi SYSTEM "contrib-spi.sgml">
diff --git a/doc/src/sgml/pgwalinspect.sgml b/doc/src/sgml/pgwalinspect.sgml
new file mode 100644
index 0000000000..e44874b87b
--- /dev/null
+++ b/doc/src/sgml/pgwalinspect.sgml
@@ -0,0 +1,244 @@
+<!-- doc/src/sgml/pgwalinspect.sgml -->
+
+<sect1 id="pgwalinspect" xreflabel="pg_walinspect">
+ <title>pg_walinspect</title>
+
+ <indexterm zone="pgwalinspect">
+ <primary>pg_walinspect</primary>
+ </indexterm>
+
+ <para>
+ The <filename>pg_walinspect</filename> module provides functions that allow
+ you to inspect the contents of write-ahead log of <productname>PostgreSQL</productname>
+ database cluster at a low level, which is useful for debugging or analytical
+ or reporting or educational purposes.
+ </para>
+
+ <para>
+ All the functions of this module will provide the WAL information using the
+ current server's timeline ID.
+ </para>
+
+ <para>
+ By default, use of these functions is restricted to superusers and members of
+ the <literal>pg_read_server_files</literal> role. Access may be granted by
+ superusers to others using <command>GRANT</command>.
+ </para>
+
+ <sect2>
+ <title>General Functions</title>
+
+ <variablelist>
+ <varlistentry>
+ <term>
+ <function>
+ pg_get_wal_record_info(in_lsn pg_lsn,
+ start_lsn OUT pg_lsn,
+ end_lsn OUT pg_lsn,
+ prev_lsn OUT pg_lsn,
+ xid OUT xid,
+ resource_manager OUT text,
+ record_type OUT text,
+ record_length OUT int4,
+ main_data_length OUT int4,
+ fpi_length OUT int4,
+ description OUT text,
+ block_ref OUT text)
+ </function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets WAL record information of a given LSN. If the given LSN isn't
+ containing a valid WAL record, it gives the information of the next
+ available valid WAL record. This function emits an error if a future (the
+ LSN database system doesn't know about) <replaceable>in_lsn</replaceable>
+ is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>
+ pg_get_wal_records_info(start_lsn pg_lsn,
+ end_lsn pg_lsn,
+ start_lsn OUT pg_lsn,
+ end_lsn OUT pg_lsn,
+ prev_lsn OUT pg_lsn,
+ xid OUT xid,
+ resource_manager OUT text,
+ record_type OUT text,
+ record_length OUT int4,
+ main_data_length OUT int4,
+ fpi_length OUT int4,
+ description OUT text,
+ block_ref OUT text)
+ returns setof record
+ </function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets information of all the valid WAL records between
+ <replaceable>start_lsn</replaceable> and <replaceable>end_lsn</replaceable>.
+ Returns one row per each valid WAL record. This function emits an error
+ if a future (the LSN database system doesn't know about)
+ <replaceable>start_lsn</replaceable> or <replaceable>end_lsn</replaceable>
+ is specified. For example, usage of the function is as follows:
+<screen>
+postgres=# select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length, main_data_length, fpi_length, description from pg_get_wal_records_info('0/14FBA30', '0/15011D7');
+ start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | main_data_length | fpi_length | description
+-----------+-----------+-----------+-----+------------------+---------------+---------------+------------------+------------+---------------------------------------------------------
+ 0/14FBA30 | 0/14FBA67 | 0/14FB9F8 | 0 | Heap2 | PRUNE | 56 | 8 | 0 | latestRemovedXid 0 nredirected 0 ndead 1
+ 0/14FBA68 | 0/14FBA9F | 0/14FBA30 | 0 | Standby | RUNNING_XACTS | 50 | 24 | 0 | nextXid 723 latestCompletedXid 722 oldestRunningXid 723
+ 0/14FBAA0 | 0/14FBACF | 0/14FBA68 | 0 | Storage | CREATE | 42 | 16 | 0 | base/5/16390
+ 0/14FBAD0 | 0/14FC117 | 0/14FBAA0 | 723 | Heap | INSERT | 1582 | 3 | 1528 | off 8 flags 0x01
+ 0/14FC118 | 0/14FD487 | 0/14FBAD0 | 723 | Btree | INSERT_LEAF | 4973 | 2 | 4920 | off 244
+ 0/14FD488 | 0/14FEFCF | 0/14FC118 | 723 | Btree | INSERT_LEAF | 6953 | 2 | 6900 | off 126
+ 0/14FEFD0 | 0/14FF027 | 0/14FD488 | 723 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
+ 0/14FF028 | 0/14FF06F | 0/14FEFD0 | 723 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 155
+ 0/14FF070 | 0/14FF0B7 | 0/14FF028 | 723 | Btree | INSERT_LEAF | 72 | 2 | 0 | off 134
+ 0/14FF0B8 | 0/14FF18F | 0/14FF070 | 723 | Heap | INSERT | 211 | 3 | 0 | off 9 flags 0x00
+ 0/14FF190 | 0/14FF1CF | 0/14FF0B8 | 723 | Btree | INSERT_LEAF | 64 | 2 | 0 | off 244
+ 0/14FF1D0 | 0/15010B7 | 0/14FF190 | 723 | Btree | SPLIT_L | 7885 | 10 | 4136 | level 0, firstrightoff 120, newitemoff 47, postingoff 0
+ 0/15010B8 | 0/150117F | 0/14FF1D0 | 723 | Btree | INSERT_UPPER | 197 | 2 | 136 | off 2
+ 0/1501180 | 0/15011D7 | 0/15010B8 | 723 | Heap2 | MULTI_INSERT | 85 | 6 | 0 | 1 tuples flags 0x02
+(14 rows)
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>
+ pg_get_wal_records_info_till_end_of_wal(start_lsn pg_lsn,
+ start_lsn OUT pg_lsn,
+ end_lsn OUT pg_lsn,
+ prev_lsn OUT pg_lsn,
+ xid OUT xid,
+ resource_manager OUT text,
+ record_type OUT text,
+ record_length OUT int4,
+ main_data_length OUT int4,
+ fpi_length OUT int4,
+ description OUT text,
+ block_ref OUT text)
+ returns setof record
+ </function>
+ </term>
+
+ <listitem>
+ <para>
+ This function is same as <function>pg_get_wal_records_info()</function>
+ except that it gets information of all the valid WAL records from
+ <replaceable>start_lsn</replaceable> till end of WAL.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>
+ pg_get_wal_stats(start_lsn pg_lsn,
+ end_lsn pg_lsn,
+ per_record boolean DEFAULT false,
+ "resource_manager/record_type" OUT text,
+ count OUT int8,
+ count_percentage OUT float4,
+ record_length OUT int8,
+ record_length_percentage OUT float4,
+ fpi_length OUT int8,
+ fpi_length_percentage OUT float4,
+ combined_size OUT int8,
+ combined_size_percentage OUT float4)
+ returns setof record
+ </function>
+ </term>
+
+ <listitem>
+ <para>
+ Gets statistics of all the valid WAL records between
+ <replaceable>start_lsn</replaceable> and
+ <replaceable>end_lsn</replaceable>. By default, it returns one row per
+ <replaceable>resource_manager</replaceable> type. When
+ <replaceable>per_record</replaceable> is set to <literal>true</literal>,
+ it returns one row per <replaceable>record_type</replaceable>. This
+ function emits an error if a future (the LSN database system doesn't know
+ about) <replaceable>start_lsn</replaceable> or <replaceable>end_lsn</replaceable>
+ is specified. For example, usage of the function is as follows:
+<screen>
+postgres=# select * from pg_get_wal_stats('0/12FBA30', '0/15011D7') where count > 0;
+ resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
+------------------------------+-------+------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
+ XLOG | 12 | 0.13002492 | 1024 | 2.3833392e-05 | 352 | 0.06136488 | 1376 | 3.2021846e-05
+ Transaction | 188 | 2.0370572 | 62903 | 0.0014640546 | 0 | 0 | 62903 | 0.0014638591
+ Storage | 13 | 0.14086033 | 546 | 1.2708038e-05 | 0 | 0 | 546 | 1.2706342e-05
+ Database | 2 | 0.02167082 | 84 | 1.955083e-06 | 0 | 0 | 84 | 1.954822e-06
+ Standby | 219 | 2.3729548 | 15830 | 0.00036844003 | 0 | 0 | 15830 | 0.00036839084
+ Heap2 | 1905 | 20.641457 | 384619 | 0.0089519285 | 364472 | 63.53915 | 749091 | 0.017432613
+ Heap | 1319 | 14.291906 | 621997 | 0.014476853 | 145232 | 25.318592 | 767229 | 0.017854715
+ Btree | 5571 | 60.36407 | 4295405999 | 99.9747 | 63562 | 11.0808935 | 4295469561 | 99.96284
+(8 rows)
+</screen>
+
+With <replaceable>per_record</replaceable> passed as <literal>true</literal>:
+
+<screen>
+postgres=# select * from pg_get_wal_stats('0/14FBA30', '0/15011D7', true) where count > 0;
+ resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
+------------------------------+-------+------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
+ Storage/CREATE | 1 | 7.142857 | 42 | 0.8922881 | 0 | 0 | 42 | 0.18811305
+ Standby/RUNNING_XACTS | 1 | 7.142857 | 50 | 1.0622478 | 0 | 0 | 50 | 0.2239441
+ Heap2/PRUNE | 1 | 7.142857 | 56 | 1.1897174 | 0 | 0 | 56 | 0.2508174
+ Heap2/MULTI_INSERT | 2 | 14.285714 | 170 | 3.6116421 | 0 | 0 | 170 | 0.76140994
+ Heap/INSERT | 2 | 14.285714 | 265 | 5.629913 | 1528 | 8.671964 | 1793 | 8.030636
+ Btree/INSERT_LEAF | 5 | 35.714287 | 314 | 6.6709156 | 11820 | 67.08286 | 12134 | 54.346756
+ Btree/INSERT_UPPER | 1 | 7.142857 | 61 | 1.2959422 | 136 | 0.77185017 | 197 | 0.8823398
+ Btree/SPLIT_L | 1 | 7.142857 | 3749 | 79.64733 | 4136 | 23.473326 | 7885 | 35.315987
+(8 rows)
+</screen>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>
+ <function>
+ pg_get_wal_stats_till_end_of_wal(start_lsn pg_lsn,
+ per_record boolean DEFAULT false,
+ "resource_manager/record_type" OUT text,
+ count OUT int8,
+ count_percentage OUT float4,
+ record_length OUT int8,
+ record_length_percentage OUT float4,
+ fpi_length OUT int8,
+ fpi_length_percentage OUT float4,
+ combined_size OUT int8,
+ combined_size_percentage OUT float4)
+ returns setof record
+ </function>
+ </term>
+
+ <listitem>
+ <para>
+ This function is same as <function>pg_get_wal_stats()</function> except
+ that it gets statistics of all the valid WAL records from
+ <replaceable>start_lsn</replaceable> till end of WAL.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
+
+ <sect2>
+ <title>Author</title>
+
+ <para>
+ Bharath Rupireddy <email>bharath.rupireddyforpostgres@gmail.com</email>
+ </para>
+ </sect2>
+
+</sect1>
--
2.25.1