Streaming Replication Useful Queries

How to Check Replication Status Properly

On Primary -

SELECT pid, usename, application_name, state

 --, client_addr

 , date_trunc('minute', backend_start) as backend_start

 , sent_lsn, round(((pg_current_wal_lsn()-sent_lsn)/(1024*1024.0)), 2) as mb_diff

from pg_stat_replication;

SELECT pid, state, sync_state, write_lag, flush_lag, replay_lag

FROM pg_stat_replication;

1) SELECT pg_current_wal_lsn();

2) SELECT pg_current_wal_insert_lsn();

3) WAL Switch Functions -

#SELECT pg_switch_wal();

Run this on STANDBY

SELECT 

    pg_last_wal_receive_lsn()  AS receive_lsn,

    pg_last_wal_replay_lsn()   AS replay_lsn,

    pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS byte_lag;

1) Last received LSN

SELECT pg_last_wal_receive_lsn();

        

2) Last replayed LSN

        SELECT pg_last_wal_replay_lsn();

    

3) WAL difference

    SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn());

    SELECT pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.replay_lsn) as byte_lag        FROM pg_stat_replication

Missing WAL File live scenario in PostgreSQL and how to recover from it

 

            Missing WAL File live scenario in PostgreSQL and how to recover from it

If WAL files are missing on primary side then replication will break and this error message will be displayed in the log file.

Answer - 

When a standby requests a WAL segment that the primary has already recycled or which is missing from archive storage, the standby cannot continue recovery. PostgreSQL physical replication does not support skipping missing WAL files. The only recovery is to re-initialize the standby with a fresh base backup.

I simulate this scenario by stopping the standby, generating excessive WAL on the primary, manually removing old WAL files, and restarting the standby.”

On PROD-1

On Replica -

Took the fresh pg_basebackup and built the replica again.

How to control the streaming replication

 

1. Check if WAL replay (recovery) is paused

SELECT pg_is_wal_replay_paused();

Output:

• true → WAL replay is paused

• false → WAL replay is running

---

2. Pause WAL replay (recovery)

SELECT pg_wal_replay_pause();

    - Pauses WAL replay immediately
    - Works only on a standby server (read replica)

---

3. Resume WAL replay (recovery)

SELECT pg_wal_replay_resume();

    - Resumes WAL replay if it was paused

How to change hostname

 

        

    

                                                

                                               3. It will show like below -

                                                

                                            

    

    

SSH to EC2-Servers from GitBash client