Thread

1. Wal file query

   Atul Kumar <akumar14871@gmail.com> — 2025-04-08T11:44:07Z

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

2. Re: Wal file query

   Kashif Zeeshan <kashi.zeeshan@gmail.com> — 2025-04-08T12:01:38Z

   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 <akumar14871@gmail.com> wrote:
   
   > In streaming replication What is the way to check which "WAL file" is
   > currently in use in primary  and in standby ?
   >
   >
   > Regards.
   >
   

3. Re: Wal file query

   Atul Kumar <akumar14871@gmail.com> — 2025-04-08T13:09:01Z

   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, <kashi.zeeshan@gmail.com> wrote:
   
   > 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 <akumar14871@gmail.com> wrote:
   >
   >> In streaming replication What is the way to check which "WAL file" is
   >> currently in use in primary  and in standby ?
   >>
   >>
   >> Regards.
   >>
   >
   

4. Re: Wal file query

   Ron <ronljohnsonjr@gmail.com> — 2025-04-08T13:32:06Z

   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()
   
   
   On Tue, Apr 8, 2025 at 9:09 AM Atul Kumar <akumar14871@gmail.com> wrote:
   
   > 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, <kashi.zeeshan@gmail.com> wrote:
   >
   >> 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 <akumar14871@gmail.com> wrote:
   >>
   >>> In streaming replication What is the way to check which "WAL file" is
   >>> currently in use in primary  and in standby ?
   >>>
   >>>
   >>> Regards.
   >>>
   >>
   
   -- 
   Death to <Redacted>, and butter sauce.
   Don't boil me, I'm still alive.
   <Redacted> lobster!