pg_walfile_name_offset()

A function returning the name and byte offset of an LSN

pg_walfile_name_offset() is a system function for obtaining the name of the WAL file corresponding to the provided LSN together with the byte offset of the record within that file.

pg_walfile_name_offset() was added in PostgreSQL 8.2 .

Usage

pg_walfile_name_offset ( lsn pg_lsn ) → record ( file_name text, file_offset integer )

pg_walfile_name_offset() can only be executed on a primary server (i.e. not a read-only standby).

Change history

Examples

Basic usage example for pg_walfile_name_offset():

postgres=# SELECT * FROM pg_walfile_name_offset('0/01000028');
        file_name         | file_offset
--------------------------+-------------
 000000010000000000000001 |          40
(1 row)

Obtaining the current WAL file name and byte offset:

postgres=# SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());
        file_name         | file_offset
--------------------------+-------------
 000000010000000000000003 |        9456
(1 row)

Attempting to execute pg_walfile_name_offset() on a standby:

postgres=# SELECT * FROM pg_walfile_name_offset('0/01000028');
ERROR:  recovery is in progress
HINT:  pg_walfile_name_offset() cannot be executed during recovery.

Categories

System function, WAL

See also

pg_walfile_name()