Subject: Re: Wal file query

From: Kashif Zeeshan <[email-masked]>
Date: Tue, 8 Apr 2025 17:01:38 +0500

Hi Atul

Start by looking at the current WAL LSN and insert LSN. The
pg_current_wal_lsn is the location of the last write. The
pg_current_wal_insert_lsn is the logical location and reflects data in the
buffer that has not been written to disk. There is also a flush value that
shows what has been written to durable storage.

[postgres] # select pg_current_wal_lsn(), pg_current_wal_insert_lsn();
 pg_current_wal_lsn | pg_current_wal_insert_lsn
--------------------+---------------------------
 76/7D000000        | 76/7D000028
(1 row)

Although you can guess the name of the WAL file based on the above
output, it is best to use the pg_walfile_name function.

[postgres] # select pg_walfile_name('76/7D000028');
     pg_walfile_name
--------------------------
 00000001000000760000007D
(1 row)

For details visit following link :
https://www.crunchydata.com/blog/postgres-wal-files-and-sequuence-numbers

Thanks

Kashif Zeeshan

On Tue, Apr 8, 2025 at 4:44 PM Atul Kumar <[email-masked]> wrote:

> In streaming replication What is the way to check which "WAL file" is
> currently in use in primary  and in standby ?
>
>
> Regards.
>

---

From: Atul Kumar <[email-masked]>
Date: Tue, 8 Apr 2025 18:39:01 +0530

I got the error:

Wal control functions cannot be executed during recovery.

I need the solution that should in when streaming replication is
configured.

My postgres version is 13.20

Regards.

On Tue, 8 Apr 2025, 17:32 Kashif Zeeshan, <[email-masked]> wrote:

> pg_current_wal_insert_lsn is the logical location and reflects data in
> the buffer that has not been written to disk. There is also a flush value
> that shows what has been written to durable storage.
>
> Although you can guess the name of the WAL file based on the above output, it is best to use the pg_walfile_name function.
>
>
> For details visit following link : https://www.crunchydata.com/blog/postgres-wal-files-and-sequuence-numbers
>
>
> Thanks
>
> Kashif Zeeshan
>
>
>

---

From: Ron Johnson <[email-masked]>
Date: Tue, 8 Apr 2025 09:32:06 -0400

You cannot connect to the Primary while connected to the Replica, except
via postgres_fdw.  Even then, it might not work, since the replica
replicates _fdw definitions.

These exist on the primary:

pg_current_wal_lsn()
pg_replication_slots
pg_stat_replication

These exist on the replica:

pg_last_wal_receive_lsn()
pg_last_wal_replay_lsn()
pg_stat_wal_receiver
pg_get_wal_replay_pause_state()

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!