| 1 |
<!-- doc/src/sgml/pgwalinspect.sgml --> |
| 2 |
|
| 3 |
<sect1 id="pgwalinspect" xreflabel="pg_walinspect"> |
| 4 |
<title>pg_walinspect — 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>[email protected]</email> |
| 283 |
</para> |
| 284 |
</sect2> |
| 285 |
|
| 286 |
</sect1> |