Check postgres replication status

Question

Can someone suggest the steps to check pgsql replication status and how to identify if the replication is not happening properly?

We use streaming replication with pgsql9.0 and pgsql9.4

http://stackoverflow.com/questions/28323355/how-to-check-the-replication-delay-in-postgresql — Vao Tsun, Apr 13 '17 at 09:25

Alexey · Answer 1 · 2019-01-12T22:31:32.037

65

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

On master:

select * from pg_stat_replication;

On replica (streaming replication in my case):

select * from pg_stat_wal_receiver;

edited Jan 12 '19 at 22:31

answered Jan 12 '19 at 22:21

Alexey

1,914
16
13

11

For vertical output replace `;` with `\x\g\x`. – x-yuri Oct 24 '20 at 06:38
4

Could you explain what we are supposed to see if everything is going well or if something is wrong ? sync_state : sync on the primary is a good sign ? – Cyril Duchon-Doris Mar 08 '22 at 09:48
1

sync_state refers to how the replication was configured. **state** is the field to check in pg_stat_replication. – gerardw Apr 21 '22 at 08:22

Reinsbrain · Answer 2 · 2019-01-15T01:49:04.300

29

On your master, pg_stat_replication provides data about ongoing replication:

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

On postgresql v10:

select client_addr, state, sent_lsn, write_lsn,
    flush_lsn, replay_lsn from pg_stat_replication;

edited Jan 15 '19 at 01:49

answered Apr 27 '17 at 15:59

Reinsbrain

2,235
2
23
35

3

Ok, what's wrong then if the above query returns 0 rows? – gvasquez Jul 17 '17 at 15:58
1

0 rows means you have no replication configured at your server. When configured correctly should get a row(s) but that doesn't mean you have replication either as the slave may not be connecting (check 'state' column) – Reinsbrain Jul 18 '17 at 00:28
mmmm...we had replication working fine and it failed, now it shows no rows, please see: https://dba.stackexchange.com/questions/180062/post-mortem-analysis-postgresql-replication-failed – gvasquez Jul 18 '17 at 19:22

truth · Answer 3 · 2018-07-10T09:29:17.570

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 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)

on postgres 10, this is not working entirely but `SELECT pg_last_xact_replay_timestamp();` gives useful information! — user39950, Jul 12 '18 at 09:06
Compare master and slave statuses in a single query: `select pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp(), 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; ` — Sergey Nemchinov, Apr 14 '20 at 12:04
To check master IP from slave, https://dba.stackexchange.com/a/45062/123063 — GypsyCosmonaut, May 27 '20 at 05:15

Check postgres replication status

3 Answers3

on server

on client

Linked