codekingpro/portable-devtools
115k
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 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="postgres-fdw.html" title="F.38. postgres_fdw — access data stored in external PostgreSQL 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 that4 allow you to inspect the contents of write-ahead log of5 a running <span class="productname">PostgreSQL</span> database cluster at a low6 level, which is useful for debugging, analytical, reporting or7 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>, but8 accessible through SQL rather than a separate utility.9 </p><p>10 All the functions of this module will provide the WAL information using the11 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 called14 using an LSN argument that specifies the location at which a known15 WAL record of interest <span class="emphasis"><em>begins</em></span>. However, some16 functions, such as17 <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 just19 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 show22 information about records that fall within a certain LSN range are23 permissive about accepting <em class="replaceable"><code>end_lsn</code></em>24 arguments that are after the server's current LSN. Using an25 <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 value29 <code class="literal">FFFFFFFF/FFFFFFFF</code> (the maximum valid30 <code class="type">pg_lsn</code> value) as an <em class="replaceable"><code>end_lsn</code></em>31 argument. This is equivalent to providing an32 <em class="replaceable"><code>end_lsn</code></em> argument matching the server's33 current LSN.34 </p></div><p>35 By default, use of these functions is restricted to superusers and members of36 the <code class="literal">pg_read_server_files</code> role. Access may be granted by37 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 or42 after the <em class="replaceable"><code>in_lsn</code></em> argument. For43 example:44</p><pre class="screen">45postgres=# SELECT * FROM pg_get_wal_record_info('0/E419E28');46-[ RECORD 1 ]----+-------------------------------------------------47start_lsn | 0/E419E2848end_lsn | 0/E419E6849prev_lsn | 0/E419D7850xid | 051resource_manager | Heap252record_type | VACUUM53record_length | 5854main_data_length | 255fpi_length | 056description | nunused: 5, unused: [1, 2, 3, 4, 5]57block_ref | blkref #0: rel 1663/16385/1249 fork main blk 36458</pre><p>59 </p><p>60 If <em class="replaceable"><code>in_lsn</code></em> isn't at the start of a WAL61 record, information about the next valid WAL record is shown62 instead. If there is no next valid WAL record, the function63 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 record68 </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 between71 <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">74postgres=# SELECT * FROM pg_get_wal_records_info('0/1E913618', '0/1E913740') LIMIT 1;75-[ RECORD 1 ]----+--------------------------------------------------------------76start_lsn | 0/1E91361877end_lsn | 0/1E91365078prev_lsn | 0/1E9135A079xid | 080resource_manager | Standby81record_type | RUNNING_XACTS82record_length | 5083main_data_length | 2484fpi_length | 085description | nextXid 33775 latestCompletedXid 33774 oldestRunningXid 3377586block_ref |87</pre><p>88 </p><p>89 The function raises an error if90 <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 valid95 WAL records between <em class="replaceable"><code>start_lsn</code></em> and96 <em class="replaceable"><code>end_lsn</code></em> with one or more block97 references. Returns one row per block reference per WAL record.98 For example:99</p><pre class="screen">100postgres=# SELECT * FROM pg_get_wal_block_info('0/1230278', '0/12302B8');101-[ RECORD 1 ]-----+-----------------------------------102start_lsn | 0/1230278103end_lsn | 0/12302B8104prev_lsn | 0/122FD40105block_id | 0106reltablespace | 1663107reldatabase | 1108relfilenode | 2658109relforknumber | 0110relblocknumber | 11111xid | 341112resource_manager | Btree113record_type | INSERT_LEAF114record_length | 64115main_data_length | 2116block_data_length | 16117block_fpi_length | 0118block_fpi_info |119description | off: 46120block_data | \x00002a00070010402630000070696400121block_fpi_data |122</pre><p>123 </p><p>124 This example involves a WAL record that only contains one block125 reference, but many WAL records contain several block126 references. Rows output by127 <code class="function">pg_get_wal_block_info</code> are guaranteed to128 have a unique combination of129 <em class="replaceable"><code>start_lsn</code></em> and130 <em class="replaceable"><code>block_id</code></em> values.131 </p><p>132 Much of the information shown here matches the output that133 <code class="function">pg_get_wal_records_info</code> would show, given134 the same arguments. However,135 <code class="function">pg_get_wal_block_info</code> unnests the136 information from each WAL record into an expanded form by137 outputting one row per block reference, so certain details are138 tracked at the block reference level rather than at the139 whole-record level. This structure is useful with queries that140 track how individual blocks changed over time. Note that141 records with no block references (e.g.,142 <code class="literal">COMMIT</code> WAL records) will have no rows143 returned, so <code class="function">pg_get_wal_block_info</code> may144 actually return <span class="emphasis"><em>fewer</em></span> rows than145 <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>, and149 <code class="structfield">relfilenode</code> parameters reference150 <a class="link" href="catalog-pg-tablespace.html" title="53.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="53.15. pg_database"><code class="structname">pg_database</code></a>.<code class="structfield">oid</code>, and152 <a class="link" href="catalog-pg-class.html" title="53.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 block155 reference; see <code class="filename">common/relpath.h</code> for156 details.157 </p><div class="tip"><h3 class="title">Tip</h3><p>158 The <code class="function">pg_filenode_relation</code> function (see159 <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-DBLOCATION" title="Table 9.97. Database Object Location Functions">Table 9.97</a>) can help you to160 determine which relation was modified during original execution.161 </p></div><p>162 It is possible for clients to avoid the overhead of163 materializing block data. This may make function execution164 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 omitted167 (that is, the <code class="structfield">block_data</code> and168 <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 where171 block data isn't truly required.172 </p><p>173 The function raises an error if174 <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 record179 </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 between182 <em class="replaceable"><code>start_lsn</code></em> and183 <em class="replaceable"><code>end_lsn</code></em>. By default, it returns one row per184 <em class="replaceable"><code>resource_manager</code></em> type. When185 <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">189postgres=# SELECT * FROM pg_get_wal_stats('0/1E847D00', '0/1E84F500')190 WHERE count > 0 AND191 "resource_manager/record_type" = 'Transaction'192 LIMIT 1;193-[ RECORD 1 ]----------------+-------------------194resource_manager/record_type | Transaction195count | 2196count_percentage | 8197record_size | 875198record_size_percentage | 41.23468426013195199fpi_size | 0200fpi_size_percentage | 0201combined_size | 875202combined_size_percentage | 2.8634072910530795203</pre><p>204 </p><p>205 The function raises an error if206 <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"><<a class="email" href="mailto:bharath.rupireddyforpostgres@gmail.com">bharath.rupireddyforpostgres@gmail.com</a>></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 — access data stored in external PostgreSQL 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 16.3 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>