Version 10.0 and later:
pg_walfile_name()
usage:
postgres=# select pg_current_wal_lsn(),
pg_walfile_name(pg_current_wal_lsn()),
pg_walfile_name_offset(pg_current_wal_lsn());
pg_current_wal_lsn | pg_walfile_name | pg_walfile_name_offset
--------------------+--------------------------+---------------------------------
2/C000840 | 00000001000000020000000C | (00000001000000020000000C,2112)
(1 row)
Note:
pg_current_wal_lsn(): Get the current wal log writing location.
pg_walfile_name(lsn pg_lsn): converts wal log location to file name.
pg_walfile_name_offset(lsn pg_lsn): Returns the converted wal log file name and offset.
Versions before 10.0:
postgres=# select pg_current_xlog_location(),
pg_xlogfile_name(pg_current_xlog_location()),
pg_xlogfile_name_offset(pg_current_xlog_location());
pg_current_xlog_location | pg_xlogfile_name | pg_xlogfile_name_offset
--------------------------+--------------------------+-------------------------------------
596/C4DA2000 | 0000000100000596000000C4 | (0000000100000596000000C4,14295040)
Note:
pg_current_xlog_location(): Get the current wal log writing location.
pg_xlogfile_name(): converts wal log location to file name.
pg_xlogfile_name_offset(): Returns the converted wal log file name and offset.
Supplement: The correspondence between postgresql lsn and wal file
System environment:
PostgreSQL 11.6
View the current LSN and wal names
postgres=# select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()); pg_current_wal_lsn | pg_walfile_name --------------------±------------------------- 4/D20001B0 | 0000000100000004000000D2
Corresponding relationship
LSN:4/D20001B0
Note: LNS consists of three parts
4: Part 2 representing walfile
D2: Represents the last two digits of the walfile file
0001B0: represents the offset
walfile:00000001 00000004 000000D2
Description: It consists of 24 characters and three parts, each part consists of 8 characters, and the representative meaning is as follows
00000001: represents the timeline of the database running. If the database is restored (main and standby switch) this value will increase.
00000004: Correspond to the second part of LSN
00000D2: Represents the last two digits of the walfile file
View the path to the walfile file
postgres=# select * from pg_ls_waldir() order by modification desc limit 5; name | size | modification --------------------------±---------±----------------------- 0000000100000004000000D2 | 16777216 | 2020-05-30 12:01:57+08 0000000100000004000000D1 | 16777216 | 2020-05-27 16:11:10+08 0000000100000004000000D0 | 16777216 | 2020-05-24 23:18:25+08 0000000100000004000000CF | 16777216 | 2020-05-14 14:17:16+08 0000000100000004000000CE | 16777216 | 2020-05-09 14:24:25+08
Note: The default walfile size of pg is 16MB. It can be modified by initdb --wal-segsize=SIZE when initializing the database.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.