doc/src/sgml/pgwalinspect.sgml

   1 <!-- doc/src/sgml/pgwalinspect.sgml -->
   2
   3 <sect1 id="pgwalinspect" xreflabel="pg_walinspect">
   4  <title>pg_walinspect &mdash; low-level WAL inspection</title>
   5
   6  <indexterm zone="pgwalinspect">
   7   <primary>pg_walinspect</primary>
   8  </indexterm>
   9
  10  <para>
  11   The <filename>pg_walinspect</filename> module provides SQL functions that
  12   allow you to inspect the contents of write-ahead log of
  13   a running <productname>PostgreSQL</productname> database cluster at a low
  14   level, which is useful for debugging, analytical, reporting or
  15   educational purposes. It is similar to <xref linkend="pgwaldump"/>, but
  16   accessible through SQL rather than a separate utility.
  17  </para>
  18
  19  <para>
  20   All the functions of this module will provide the WAL information using the
  21   server's current timeline ID.
  22  </para>
  23
  24  <note>
  25   <para>
  26    The <filename>pg_walinspect</filename> functions are often called
  27    using an LSN argument that specifies the location at which a known
  28    WAL record of interest <emphasis>begins</emphasis>.  However, some
  29    functions, such as
  30    <function><link linkend="pg-logical-emit-message">pg_logical_emit_message</link></function>,
  31    return the LSN <emphasis>after</emphasis> the record that was just
  32    inserted.
  33   </para>
  34  </note>
  35  <tip>
  36   <para>
  37    All of the <filename>pg_walinspect</filename> functions that show
  38    information about records that fall within a certain LSN range are
  39    permissive about accepting <replaceable>end_lsn</replaceable>
  40    arguments that are after the server's current LSN.  Using an
  41    <replaceable>end_lsn</replaceable> <quote>from the future</quote>
  42    will not raise an error.
  43   </para>
  44   <para>
  45    It may be convenient to provide the value
  46    <literal>FFFFFFFF/FFFFFFFF</literal> (the maximum valid
  47    <type>pg_lsn</type> value) as an <replaceable>end_lsn</replaceable>
  48    argument.  This is equivalent to providing an
  49    <replaceable>end_lsn</replaceable> argument matching the server's
  50    current LSN.
  51   </para>
  52  </tip>
  53  <para>
  54   By default, use of these functions is restricted to superusers and members of
  55   the <literal>pg_read_server_files</literal> role. Access may be granted by
  56   superusers to others using <command>GRANT</command>.
  57  </para>
  58
  59  <sect2 id="pgwalinspect-funcs">
  60   <title>General Functions</title>
  61
  62   <variablelist>
  63    <varlistentry id="pgwalinspect-funcs-pg-get-wal-record-info">
  64     <term>
  65      <function>pg_get_wal_record_info(in_lsn pg_lsn) returns record</function>
  66     </term>
  67
  68     <listitem>
  69      <para>
  70       Gets WAL record information about a record that is located at or
  71       after the <replaceable>in_lsn</replaceable> argument.  For
  72       example:
  73 <screen>
  74 postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');
  75 -[ RECORD 1 ]----+-------------------------------------------------
  76 start_lsn        | 0/E419E28
  77 end_lsn          | 0/E419E68
  78 prev_lsn         | 0/E419D78
  79 xid              | 0
  80 resource_manager | Heap2
  81 record_type      | VACUUM
  82 record_length    | 58
  83 main_data_length | 2
  84 fpi_length       | 0
  85 description      | nunused: 5, unused: [1, 2, 3, 4, 5]
  86 block_ref        | blkref #0: rel 1663/16385/1249 fork main blk 364
  87 </screen>
  88      </para>
  89      <para>
  90       If <replaceable>in_lsn</replaceable> isn't at the start of a WAL
  91       record, information about the next valid WAL record is shown
  92       instead.  If there is no next valid WAL record, the function
  93       raises an error.
  94      </para>
  95     </listitem>
  96    </varlistentry>
  97
  98     <varlistentry id="pgwalinspect-funcs-pg-get-wal-records-info">
  99     <term>
 100      <function>
 101       pg_get_wal_records_info(start_lsn pg_lsn, end_lsn pg_lsn)
 102       returns setof record
 103      </function>
 104     </term>
 105
 106     <listitem>
 107      <para>
 108       Gets information of all the valid WAL records between
 109       <replaceable>start_lsn</replaceable> and <replaceable>end_lsn</replaceable>.
 110       Returns one row per WAL record.  For example:
 111 <screen>
 112 postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;
 113 -[ RECORD 1 ]----+--------------------------------------------------------------
 114 start_lsn        | 0/1E913618
 115 end_lsn          | 0/1E913650
 116 prev_lsn         | 0/1E9135A0
 117 xid              | 0
 118 resource_manager | Standby
 119 record_type      | RUNNING_XACTS
 120 record_length    | 50
 121 main_data_length | 24
 122 fpi_length       | 0
 123 description      | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 33775
 124 block_ref        |
 125 </screen>
 126      </para>
 127      <para>
 128       The function raises an error if
 129       <replaceable>start_lsn</replaceable> is not available.
 130      </para>
 131     </listitem>
 132    </varlistentry>
 133
 134    <varlistentry id="pgwalinspect-funcs-pg-get-wal-block-info">
 135     <term>
 136      <function>pg_get_wal_block_info(start_lsn pg_lsn, end_lsn pg_lsn, show_data boolean DEFAULT true) returns setof record</function>
 137     </term>
 138
 139     <listitem>
 140      <para>
 141       Gets information about each block reference from all the valid
 142       WAL records between <replaceable>start_lsn</replaceable> and
 143       <replaceable>end_lsn</replaceable> with one or more block
 144       references.  Returns one row per block reference per WAL record.
 145       For example:
 146 <screen>
 147 postgres=# SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');
 148 -[ RECORD 1 ]-----+-----------------------------------
 149 start_lsn         | 0/1230278
 150 end_lsn           | 0/12302B8
 151 prev_lsn          | 0/122FD40
 152 block_id          | 0
 153 reltablespace     | 1663
 154 reldatabase       | 1
 155 relfilenode       | 2658
 156 relforknumber     | 0
 157 relblocknumber    | 11
 158 xid               | 341
 159 resource_manager  | Btree
 160 record_type       | INSERT_LEAF
 161 record_length     | 64
 162 main_data_length  | 2
 163 block_data_length | 16
 164 block_fpi_length  | 0
 165 block_fpi_info    |
 166 description       | off: 46
 167 block_data        | \x00002a00070010402630000070696400
 168 block_fpi_data    |
 169 </screen>
 170      </para>
 171      <para>
 172       This example involves a WAL record that only contains one block
 173       reference, but many WAL records contain several block
 174       references.  Rows output by
 175       <function>pg_get_wal_block_info</function> are guaranteed to
 176       have a unique combination of
 177       <replaceable>start_lsn</replaceable> and
 178       <replaceable>block_id</replaceable> values.
 179      </para>
 180      <para>
 181       Much of the information shown here matches the output that
 182       <function>pg_get_wal_records_info</function> would show, given
 183       the same arguments.  However,
 184       <function>pg_get_wal_block_info</function> unnests the
 185       information from each WAL record into an expanded form by
 186       outputting one row per block reference, so certain details are
 187       tracked at the block reference level rather than at the
 188       whole-record level.  This structure is useful with queries that
 189       track how individual blocks changed over time.  Note that
 190       records with no block references (e.g.,
 191       <literal>COMMIT</literal> WAL records) will have no rows
 192       returned, so <function>pg_get_wal_block_info</function> may
 193       actually return <emphasis>fewer</emphasis> rows than
 194       <function>pg_get_wal_records_info</function>.
 195      </para>
 196      <para>
 197       The <structfield>reltablespace</structfield>,
 198       <structfield>reldatabase</structfield>, and
 199       <structfield>relfilenode</structfield> parameters reference
 200       <link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.<structfield>oid</structfield>,
 201       <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>, and
 202       <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relfilenode</structfield>
 203       respectively.  The <structfield>relforknumber</structfield>
 204       field is the fork number within the relation for the block
 205       reference; see <filename>common/relpath.h</filename> for
 206       details.
 207      </para>
 208      <tip>
 209       <para>
 210        The <function>pg_filenode_relation</function> function (see
 211        <xref linkend="functions-admin-dblocation"/>) can help you to
 212        determine which relation was modified during original execution.
 213       </para>
 214      </tip>
 215      <para>
 216       It is possible for clients to avoid the overhead of
 217       materializing block data.  This may make function execution
 218       significantly faster.  When <replaceable>show_data</replaceable>
 219       is set to <literal>false</literal>, <structfield>block_data</structfield>
 220       and <structfield>block_fpi_data</structfield> values are omitted
 221       (that is, the <structfield>block_data</structfield> and
 222       <structfield>block_fpi_data</structfield> <literal>OUT</literal>
 223       arguments are <literal>NULL</literal> for all rows returned).
 224       Obviously, this optimization is only feasible with queries where
 225       block data isn't truly required.
 226      </para>
 227      <para>
 228       The function raises an error if
 229       <replaceable>start_lsn</replaceable> is not available.
 230      </para>
 231     </listitem>
 232    </varlistentry>
 233
 234     <varlistentry id="pgwalinspect-funcs-pg-get-wal-stats">
 235     <term>
 236      <function>
 237       pg_get_wal_stats(start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean DEFAULT false)
 238       returns setof record
 239      </function>
 240     </term>
 241
 242     <listitem>
 243      <para>
 244       Gets statistics of all the valid WAL records between
 245       <replaceable>start_lsn</replaceable> and
 246       <replaceable>end_lsn</replaceable>. By default, it returns one row per
 247       <replaceable>resource_manager</replaceable> type. When
 248       <replaceable>per_record</replaceable> is set to <literal>true</literal>,
 249       it returns one row per <replaceable>record_type</replaceable>.
 250       For example:
 251 <screen>
 252 postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500')
 253            WHERE count > 0 AND
 254                  "resource_manager/record_type" = 'Transaction'
 255            LIMIT 1;
 256 -[ RECORD 1 ]----------------+-------------------
 257 resource_manager/record_type | Transaction
 258 count                        | 2
 259 count_percentage             | 8
 260 record_size                  | 875
 261 record_size_percentage       | 41.23468426013195
 262 fpi_size                     | 0
 263 fpi_size_percentage          | 0
 264 combined_size                | 875
 265 combined_size_percentage     | 2.8634072910530795
 266 </screen>
 267      </para>
 268      <para>
 269       The function raises an error if
 270       <replaceable>start_lsn</replaceable> is not available.
 271      </para>
 272     </listitem>
 273    </varlistentry>
 274
 275   </variablelist>
 276  </sect2>
 277
 278  <sect2 id="pgwalinspect-author">
 279   <title>Author</title>
 280
 281   <para>
 282    Bharath Rupireddy <email>bharath.rupireddyforpostgres@gmail.com</email>
 283   </para>
 284  </sect2>
 285
 286 </sect1>