postgresql 检查 postgres 复制状态
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43388243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
check postgres replication status
提问by Dino Daniel
Can someone suggest the steps to check pgsql replication status and when to say replication is not happening properly?
有人可以建议检查 pgsql 复制状态的步骤以及何时说复制没有正确进行吗？
We use streaming replication with pgsql9.0 and pgsql9.4
我们在 pgsql9.0 和 pgsql9.4 中使用流式复制
Thanks.
谢谢。
回答by Reinsbrain
On your master, pg_stat_replication provides data about ongoing replication:
在您的 master 上， pg_stat_replication 提供有关正在进行的复制的数据：
select client_addr, state, sent_location, write_location,
flush_location, replay_location from pg_stat_replication;
On postgresql v10:
在 postgresql v10 上：
select client_addr, state, sent_lsn, write_lsn,
flush_lsn, replay_lsn from pg_stat_replication;
回答by truth
Show replication status in PostgreSQL
在 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)
回答by Alexey
I use following SQL queries to check status on Postgres v11 usually.
我通常使用以下 SQL 查询来检查 Postgres v11 上的状态。
On master:
在主人上：
select * from pg_stat_replication;
On replica(streaming replication in my case):
在副本上（在我的情况下是流式复制）：
select * from pg_stat_wal_receiver;
