convert pg_LSN to wAL postgresql

Question

I have configured the streaming replication using the replication slot. I have checked the view pg_replication_slots which contain

restart_lsn pg_lsn

The address (LSN) of oldest WAL which still might be required by the consumer of this slot and thus won't be automatically removed during checkpoints.

I want to find the minimum WAL required by my standby before the master archive the same.

I thought of converting the LSN to WAL but I am not able to find any command. Please help me with this.

Answer 1

Conversion of LSN to WAL file name could be done using this formula:

(0x100000000 * xlog_master + offset_master) - (0x100000000 * xlog_replica + offset_replica)

On any PostgreSQL instance run following (ideally running the same major version):

PostgreSQL >=10:

psql -t -c "select pg_walfile_name('9F81/724931C8');"

PostgreSQL < 10:

psql -t -c "select pg_xlogfile_name('9F81/724931C8');"

will give you a number like:

0000000100009F8100000072

which assumes that your server is running on Timeline 1 (first 8 digits 00000001)

You can use pg_controldata to find out on which timeline is your server running

$ /usr/lib/postgresql/10/bin/pg_controldata /var/lib/postgresql/10/main/  | grep TimeLine
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1