path: root/doc/src/sgml/pgwalinspect.sgml

<!-- doc/src/sgml/pgwalinspect.sgml -->

<sect1 id="pgwalinspect" xreflabel="pg_walinspect">
 <title>pg_walinspect &mdash; low-level WAL inspection</title>

 <indexterm zone="pgwalinspect">
  <primary>pg_walinspect</primary>
 </indexterm>

 <para>
  The <filename>pg_walinspect</filename> module provides SQL functions that
  allow you to inspect the contents of write-ahead log of
  a running <productname>PostgreSQL</productname> database cluster at a low
  level, which is useful for debugging, analytical, reporting or
  educational purposes. It is similar to <xref linkend="pgwaldump"/>, but
  accessible through SQL rather than a separate utility.
 </para>

 <para>
  All the functions of this module will provide the WAL information using the
  server's current timeline ID.
 </para>

 <note>
  <para>
   The <filename>pg_walinspect</filename> functions are often called
   using an LSN argument that specifies the location at which a known
   WAL record of interest <emphasis>begins</emphasis>.  However, some
   functions, such as
   <function><link linkend="pg-logical-emit-message">pg_logical_emit_message</link></function>,
   return the LSN <emphasis>after</emphasis> the record that was just
   inserted.
  </para>
 </note>
 <tip>
  <para>
   All of the <filename>pg_walinspect</filename> functions that show
   information about records that fall within a certain LSN range are
   permissive about accepting <replaceable>end_lsn</replaceable>
   arguments that are after the server's current LSN.  Using an
   <replaceable>end_lsn</replaceable> <quote>from the future</quote>
   will not raise an error.
  </para>
  <para>
   It may be convenient to provide the value
   <literal>FFFFFFFF/FFFFFFFF</literal> (the maximum valid
   <type>pg_lsn</type> value) as an <replaceable>end_lsn</replaceable>
   argument.  This is equivalent to providing an
   <replaceable>end_lsn</replaceable> argument matching the server's
   current LSN.
  </para>
 </tip>
 <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 id="pgwalinspect-funcs">
  <title>General Functions</title>

  <variablelist>
   <varlistentry id="pgwalinspect-funcs-pg-get-wal-record-info">
    <term>
     <function>pg_get_wal_record_info(in_lsn pg_lsn) returns record</function>
    </term>

    <listitem>
     <para>
      Gets WAL record information about a record that is located at or
      after the <replaceable>in_lsn</replaceable> argument.  For
      example:
<screen>
postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');
-[ RECORD 1 ]----+-------------------------------------------------
start_lsn        | 0/E419E28
end_lsn          | 0/E419E68
prev_lsn         | 0/E419D78
xid              | 0
resource_manager | Heap2
record_type      | VACUUM
record_length    | 58
main_data_length | 2
fpi_length       | 0
description      | nunused: 5, unused: [1, 2, 3, 4, 5]
block_ref        | blkref #0: rel 1663/16385/1249 fork main blk 364
</screen>
     </para>
     <para>
      If <replaceable>in_lsn</replaceable> isn't at the start of a WAL
      record, information about the next valid WAL record is shown
      instead.  If there is no next valid WAL record, the function
      raises an error.
     </para>
    </listitem>
   </varlistentry>

    <varlistentry id="pgwalinspect-funcs-pg-get-wal-records-info">
    <term>
     <function>
      pg_get_wal_records_info(start_lsn pg_lsn, end_lsn pg_lsn)
      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 WAL record.  For example:
<screen>
postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;
-[ RECORD 1 ]----+--------------------------------------------------------------
start_lsn        | 0/1E913618
end_lsn          | 0/1E913650
prev_lsn         | 0/1E9135A0
xid              | 0
resource_manager | Standby
record_type      | RUNNING_XACTS
record_length    | 50
main_data_length | 24
fpi_length       | 0
description      | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 33775
block_ref        |
</screen>
     </para>
     <para>
      The function raises an error if
      <replaceable>start_lsn</replaceable> is not available.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="pgwalinspect-funcs-pg-get-wal-block-info">
    <term>
     <function>pg_get_wal_block_info(start_lsn pg_lsn, end_lsn pg_lsn, show_data boolean DEFAULT true) returns setof record</function>
    </term>

    <listitem>
     <para>
      Gets information about each block reference from all the valid
      WAL records between <replaceable>start_lsn</replaceable> and
      <replaceable>end_lsn</replaceable> with one or more block
      references.  Returns one row per block reference per WAL record.
      For example:
<screen>
postgres=# SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');
-[ RECORD 1 ]-----+-----------------------------------
start_lsn         | 0/1230278
end_lsn           | 0/12302B8
prev_lsn          | 0/122FD40
block_id          | 0
reltablespace     | 1663
reldatabase       | 1
relfilenode       | 2658
relforknumber     | 0
relblocknumber    | 11
xid               | 341
resource_manager  | Btree
record_type       | INSERT_LEAF
record_length     | 64
main_data_length  | 2
block_data_length | 16
block_fpi_length  | 0
block_fpi_info    |
description       | off: 46
block_data        | \x00002a00070010402630000070696400
block_fpi_data    |
</screen>
     </para>
     <para>
      This example involves a WAL record that only contains one block
      reference, but many WAL records contain several block
      references.  Rows output by
      <function>pg_get_wal_block_info</function> are guaranteed to
      have a unique combination of
      <replaceable>start_lsn</replaceable> and
      <replaceable>block_id</replaceable> values.
     </para>
     <para>
      Much of the information shown here matches the output that
      <function>pg_get_wal_records_info</function> would show, given
      the same arguments.  However,
      <function>pg_get_wal_block_info</function> unnests the
      information from each WAL record into an expanded form by
      outputting one row per block reference, so certain details are
      tracked at the block reference level rather than at the
      whole-record level.  This structure is useful with queries that
      track how individual blocks changed over time.  Note that
      records with no block references (e.g.,
      <literal>COMMIT</literal> WAL records) will have no rows
      returned, so <function>pg_get_wal_block_info</function> may
      actually return <emphasis>fewer</emphasis> rows than
      <function>pg_get_wal_records_info</function>.
     </para>
     <para>
      The <structfield>reltablespace</structfield>,
      <structfield>reldatabase</structfield>, and
      <structfield>relfilenode</structfield> parameters reference
      <link linkend="catalog-pg-tablespace"><structname>pg_tablespace</structname></link>.<structfield>oid</structfield>,
      <link linkend="catalog-pg-database"><structname>pg_database</structname></link>.<structfield>oid</structfield>, and
      <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relfilenode</structfield>
      respectively.  The <structfield>relforknumber</structfield>
      field is the fork number within the relation for the block
      reference; see <filename>common/relpath.h</filename> for
      details.
     </para>
     <tip>
      <para>
       The <function>pg_filenode_relation</function> function (see
       <xref linkend="functions-admin-dblocation"/>) can help you to
       determine which relation was modified during original execution
      </para>
     </tip>
     <para>
      It is possible for clients to avoid the overhead of
      materializing block data.  This may make function execution
      significantly faster.  When <replaceable>show_data</replaceable>
      is set to <literal>false</literal>, <structfield>block_data</structfield>
      and <structfield>block_fpi_data</structfield> values are omitted
      (that is, the <structfield>block_data</structfield> and
      <structfield>block_fpi_data</structfield> <literal>OUT</literal>
      arguments are <literal>NULL</literal> for all rows returned).
      Obviously, this optimization is only feasible with queries where
      block data isn't truly required.
     </para>
     <para>
      The function raises an error if
      <replaceable>start_lsn</replaceable> is not available.
     </para>
    </listitem>
   </varlistentry>

    <varlistentry id="pgwalinspect-funcs-pg-get-wal-stats">
    <term>
     <function>
      pg_get_wal_stats(start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean DEFAULT false)
      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>.
      For example:
<screen>
postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500')
             WHERE count > 0 LIMIT 1 AND
                   "resource_manager/record_type" = 'Transaction';
-[ RECORD 1 ]----------------+-------------------
resource_manager/record_type | Transaction
count                        | 2
count_percentage             | 8
record_size                  | 875
record_size_percentage       | 41.23468426013195
fpi_size                     | 0
fpi_size_percentage          | 0
combined_size                | 875
combined_size_percentage     | 2.8634072910530795
</screen>
     </para>
     <para>
      The function raises an error if
      <replaceable>start_lsn</replaceable> is not available.
     </para>
    </listitem>
   </varlistentry>

  </variablelist>
 </sect2>

 <sect2 id="pgwalinspect-author">
  <title>Author</title>

  <para>
   Bharath Rupireddy <email>bharath.rupireddyforpostgres@gmail.com</email>
  </para>
 </sect2>

</sect1>