Clever Geek Handbook

Check postgres replication status

Can anyone suggest steps to check pgsql replication status and when to tell replication not going as expected?

We are using streaming replication with pgsql9.0 and pgsql9.4

Thank.

+7


source to share


3 answers


On your master, pg_stat_replication provides data about the current replication:



select client_addr, state, sent_location, write_location,
        flush_location, replay_location from pg_stat_replication;
+7


source


Show Replication Status in PostgreSQL

on server

postgres=# select usename,application_name,client_addr,backend_start,state,sync_state from pg_stat_replication ;

usename   | application_name |  client_addr   |         backend_start         |   state   | sync_state 
------------+------------------+----------------+-------------------------------+-----------+------------
replicator | walreceiver      | 192.168.10.132 | 2018-07-06 06:12:20.786918+03 | streaming | async
(1 row)


on the client

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
 (1 row)


postgres=# select pg_last_xlog_receive_location();
 pg_last_xlog_receive_location 
-------------------------------
 0/540C1DB8


postgres=# select pg_last_xlog_replay_location();
 pg_last_xlog_replay_location 
------------------------------
 0/540C1DB8
 (1 row)

postgres=#    SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
                  THEN 0
                ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
              END AS log_delay;
 log_delay 
-----------
 0
 (1 row)
+2


source


I usually use the following SQL queries to check the status on Postgres v11.

By master:

select * from pg_stat_replication;

On a replica (streaming replication in my case):

select * from pg_stat_wal_receiver;
0


source






More articles:


All Articles