begriffs open source - ai-pg/blob - full-docs/src/sgml/html/pgwalinspect.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.37. pg_walinspect — low-level WAL inspection</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="pgvisibility.html" title="F.36. pg_visibility — visibility map information and utilities" /><link rel="next" href="postgres-fdw.html" title="F.38. postgres_fdw — access data stored in external PostgreSQL servers" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.37. pg_walinspect — low-level WAL inspection</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgvisibility.html" title="F.36. pg_visibility — visibility map information and utilities">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="postgres-fdw.html" title="F.38. postgres_fdw —&#10;   access data stored in external PostgreSQL&#10;   servers">Next</a></td></tr></table><hr /></div><div class="sect1" id="PGWALINSPECT"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.37. pg_walinspect — low-level WAL inspection <a href="#PGWALINSPECT" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="pgwalinspect.html#PGWALINSPECT-FUNCS">F.37.1. General Functions</a></span></dt><dt><span class="sect2"><a href="pgwalinspect.html#PGWALINSPECT-AUTHOR">F.37.2. Author</a></span></dt></dl></div><a id="id-1.11.7.47.2" class="indexterm"></a><p>
   3   The <code class="filename">pg_walinspect</code> module provides SQL functions that
   4   allow you to inspect the contents of write-ahead log of
   5   a running <span class="productname">PostgreSQL</span> database cluster at a low
   6   level, which is useful for debugging, analytical, reporting or
   7   educational purposes. It is similar to <a class="xref" href="pgwaldump.html" title="pg_waldump"><span class="refentrytitle"><span class="application">pg_waldump</span></span></a>, but
   8   accessible through SQL rather than a separate utility.
   9  </p><p>
  10   All the functions of this module will provide the WAL information using the
  11   server's current timeline ID.
  12  </p><div class="note"><h3 class="title">Note</h3><p>
  13    The <code class="filename">pg_walinspect</code> functions are often called
  14    using an LSN argument that specifies the location at which a known
  15    WAL record of interest <span class="emphasis"><em>begins</em></span>.  However, some
  16    functions, such as
  17    <code class="function"><a class="link" href="functions-admin.html#PG-LOGICAL-EMIT-MESSAGE">pg_logical_emit_message</a></code>,
  18    return the LSN <span class="emphasis"><em>after</em></span> the record that was just
  19    inserted.
  20   </p></div><div class="tip"><h3 class="title">Tip</h3><p>
  21    All of the <code class="filename">pg_walinspect</code> functions that show
  22    information about records that fall within a certain LSN range are
  23    permissive about accepting <em class="replaceable"><code>end_lsn</code></em>
  24    arguments that are after the server's current LSN.  Using an
  25    <em class="replaceable"><code>end_lsn</code></em> <span class="quote">“<span class="quote">from the future</span>”</span>
  26    will not raise an error.
  27   </p><p>
  28    It may be convenient to provide the value
  29    <code class="literal">FFFFFFFF/FFFFFFFF</code> (the maximum valid
  30    <code class="type">pg_lsn</code> value) as an <em class="replaceable"><code>end_lsn</code></em>
  31    argument.  This is equivalent to providing an
  32    <em class="replaceable"><code>end_lsn</code></em> argument matching the server's
  33    current LSN.
  34   </p></div><p>
  35   By default, use of these functions is restricted to superusers and members of
  36   the <code class="literal">pg_read_server_files</code> role. Access may be granted by
  37   superusers to others using <code class="command">GRANT</code>.
  38  </p><div class="sect2" id="PGWALINSPECT-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.37.1. General Functions <a href="#PGWALINSPECT-FUNCS" class="id_link">#</a></h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="PGWALINSPECT-FUNCS-PG-GET-WAL-RECORD-INFO"><span class="term">
  39      <code class="function">pg_get_wal_record_info(in_lsn pg_lsn) returns record</code>
  40     </span> <a href="#PGWALINSPECT-FUNCS-PG-GET-WAL-RECORD-INFO" class="id_link">#</a></dt><dd><p>
  41       Gets WAL record information about a record that is located at or
  42       after the <em class="replaceable"><code>in_lsn</code></em> argument.  For
  43       example:
  44 </p><pre class="screen">
  45 postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');
  46 -[ RECORD 1 ]----+-------------------------------------------------
  47 start_lsn        | 0/E419E28
  48 end_lsn          | 0/E419E68
  49 prev_lsn         | 0/E419D78
  50 xid              | 0
  51 resource_manager | Heap2
  52 record_type      | VACUUM
  53 record_length    | 58
  54 main_data_length | 2
  55 fpi_length       | 0
  56 description      | nunused: 5, unused: [1, 2, 3, 4, 5]
  57 block_ref        | blkref #0: rel 1663/16385/1249 fork main blk 364
  58 </pre><p>
  59      </p><p>
  60       If <em class="replaceable"><code>in_lsn</code></em> isn't at the start of a WAL
  61       record, information about the next valid WAL record is shown
  62       instead.  If there is no next valid WAL record, the function
  63       raises an error.
  64      </p></dd><dt id="PGWALINSPECT-FUNCS-PG-GET-WAL-RECORDS-INFO"><span class="term">
  65      <code class="function">
  66       pg_get_wal_records_info(start_lsn pg_lsn, end_lsn pg_lsn)
  67       returns setof record
  68      </code>
  69     </span> <a href="#PGWALINSPECT-FUNCS-PG-GET-WAL-RECORDS-INFO" class="id_link">#</a></dt><dd><p>
  70       Gets information of all the valid WAL records between
  71       <em class="replaceable"><code>start_lsn</code></em> and <em class="replaceable"><code>end_lsn</code></em>.
  72       Returns one row per WAL record.  For example:
  73 </p><pre class="screen">
  74 postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;
  75 -[ RECORD 1 ]----+--------------------------------------------------------------
  76 start_lsn        | 0/1E913618
  77 end_lsn          | 0/1E913650
  78 prev_lsn         | 0/1E9135A0
  79 xid              | 0
  80 resource_manager | Standby
  81 record_type      | RUNNING_XACTS
  82 record_length    | 50
  83 main_data_length | 24
  84 fpi_length       | 0
  85 description      | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 33775
  86 block_ref        |
  87 </pre><p>
  88      </p><p>
  89       The function raises an error if
  90       <em class="replaceable"><code>start_lsn</code></em> is not available.
  91      </p></dd><dt id="PGWALINSPECT-FUNCS-PG-GET-WAL-BLOCK-INFO"><span class="term">
  92      <code class="function">pg_get_wal_block_info(start_lsn pg_lsn, end_lsn pg_lsn, show_data boolean DEFAULT true) returns setof record</code>
  93     </span> <a href="#PGWALINSPECT-FUNCS-PG-GET-WAL-BLOCK-INFO" class="id_link">#</a></dt><dd><p>
  94       Gets information about each block reference from all the valid
  95       WAL records between <em class="replaceable"><code>start_lsn</code></em> and
  96       <em class="replaceable"><code>end_lsn</code></em> with one or more block
  97       references.  Returns one row per block reference per WAL record.
  98       For example:
  99 </p><pre class="screen">
 100 postgres=# SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');
 101 -[ RECORD 1 ]-----+-----------------------------------
 102 start_lsn         | 0/1230278
 103 end_lsn           | 0/12302B8
 104 prev_lsn          | 0/122FD40
 105 block_id          | 0
 106 reltablespace     | 1663
 107 reldatabase       | 1
 108 relfilenode       | 2658
 109 relforknumber     | 0
 110 relblocknumber    | 11
 111 xid               | 341
 112 resource_manager  | Btree
 113 record_type       | INSERT_LEAF
 114 record_length     | 64
 115 main_data_length  | 2
 116 block_data_length | 16
 117 block_fpi_length  | 0
 118 block_fpi_info    |
 119 description       | off: 46
 120 block_data        | \x00002a00070010402630000070696400
 121 block_fpi_data    |
 122 </pre><p>
 123      </p><p>
 124       This example involves a WAL record that only contains one block
 125       reference, but many WAL records contain several block
 126       references.  Rows output by
 127       <code class="function">pg_get_wal_block_info</code> are guaranteed to
 128       have a unique combination of
 129       <em class="replaceable"><code>start_lsn</code></em> and
 130       <em class="replaceable"><code>block_id</code></em> values.
 131      </p><p>
 132       Much of the information shown here matches the output that
 133       <code class="function">pg_get_wal_records_info</code> would show, given
 134       the same arguments.  However,
 135       <code class="function">pg_get_wal_block_info</code> unnests the
 136       information from each WAL record into an expanded form by
 137       outputting one row per block reference, so certain details are
 138       tracked at the block reference level rather than at the
 139       whole-record level.  This structure is useful with queries that
 140       track how individual blocks changed over time.  Note that
 141       records with no block references (e.g.,
 142       <code class="literal">COMMIT</code> WAL records) will have no rows
 143       returned, so <code class="function">pg_get_wal_block_info</code> may
 144       actually return <span class="emphasis"><em>fewer</em></span> rows than
 145       <code class="function">pg_get_wal_records_info</code>.
 146      </p><p>
 147       The <code class="structfield">reltablespace</code>,
 148       <code class="structfield">reldatabase</code>, and
 149       <code class="structfield">relfilenode</code> parameters reference
 150       <a class="link" href="catalog-pg-tablespace.html" title="52.56. pg_tablespace"><code class="structname">pg_tablespace</code></a>.<code class="structfield">oid</code>,
 151       <a class="link" href="catalog-pg-database.html" title="52.15. pg_database"><code class="structname">pg_database</code></a>.<code class="structfield">oid</code>, and
 152       <a class="link" href="catalog-pg-class.html" title="52.11. pg_class"><code class="structname">pg_class</code></a>.<code class="structfield">relfilenode</code>
 153       respectively.  The <code class="structfield">relforknumber</code>
 154       field is the fork number within the relation for the block
 155       reference; see <code class="filename">common/relpath.h</code> for
 156       details.
 157      </p><div class="tip"><h3 class="title">Tip</h3><p>
 158        The <code class="function">pg_filenode_relation</code> function (see
 159        <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-DBLOCATION" title="Table 9.103. Database Object Location Functions">Table 9.103</a>) can help you to
 160        determine which relation was modified during original execution.
 161       </p></div><p>
 162       It is possible for clients to avoid the overhead of
 163       materializing block data.  This may make function execution
 164       significantly faster.  When <em class="replaceable"><code>show_data</code></em>
 165       is set to <code class="literal">false</code>, <code class="structfield">block_data</code>
 166       and <code class="structfield">block_fpi_data</code> values are omitted
 167       (that is, the <code class="structfield">block_data</code> and
 168       <code class="structfield">block_fpi_data</code> <code class="literal">OUT</code>
 169       arguments are <code class="literal">NULL</code> for all rows returned).
 170       Obviously, this optimization is only feasible with queries where
 171       block data isn't truly required.
 172      </p><p>
 173       The function raises an error if
 174       <em class="replaceable"><code>start_lsn</code></em> is not available.
 175      </p></dd><dt id="PGWALINSPECT-FUNCS-PG-GET-WAL-STATS"><span class="term">
 176      <code class="function">
 177       pg_get_wal_stats(start_lsn pg_lsn, end_lsn pg_lsn, per_record boolean DEFAULT false)
 178       returns setof record
 179      </code>
 180     </span> <a href="#PGWALINSPECT-FUNCS-PG-GET-WAL-STATS" class="id_link">#</a></dt><dd><p>
 181       Gets statistics of all the valid WAL records between
 182       <em class="replaceable"><code>start_lsn</code></em> and
 183       <em class="replaceable"><code>end_lsn</code></em>. By default, it returns one row per
 184       <em class="replaceable"><code>resource_manager</code></em> type. When
 185       <em class="replaceable"><code>per_record</code></em> is set to <code class="literal">true</code>,
 186       it returns one row per <em class="replaceable"><code>record_type</code></em>.
 187       For example:
 188 </p><pre class="screen">
 189 postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500')
 190            WHERE count &gt; 0 AND
 191                  "resource_manager/record_type" = 'Transaction'
 192            LIMIT 1;
 193 -[ RECORD 1 ]----------------+-------------------
 194 resource_manager/record_type | Transaction
 195 count                        | 2
 196 count_percentage             | 8
 197 record_size                  | 875
 198 record_size_percentage       | 41.23468426013195
 199 fpi_size                     | 0
 200 fpi_size_percentage          | 0
 201 combined_size                | 875
 202 combined_size_percentage     | 2.8634072910530795
 203 </pre><p>
 204      </p><p>
 205       The function raises an error if
 206       <em class="replaceable"><code>start_lsn</code></em> is not available.
 207      </p></dd></dl></div></div><div class="sect2" id="PGWALINSPECT-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.37.2. Author <a href="#PGWALINSPECT-AUTHOR" class="id_link">#</a></h3></div></div></div><p>
 208    Bharath Rupireddy <code class="email">&lt;<a class="email" href="mailto:bharath.rupireddyforpostgres@gmail.com">bharath.rupireddyforpostgres@gmail.com</a>&gt;</code>
 209   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgvisibility.html" title="F.36. pg_visibility — visibility map information and utilities">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="postgres-fdw.html" title="F.38. postgres_fdw —&#10;   access data stored in external PostgreSQL&#10;   servers">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.36. pg_visibility — visibility map information and utilities </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.38. postgres_fdw —
 210    access data stored in external <span class="productname">PostgreSQL</span>
 211    servers</td></tr></table></div></body></html>