Posgresql查询主备节点角色

一、通过查询数据库进程：
Master节点
$ps -ef |grep wal
postgres 31076 31068 0 2020 ? 00:32:28 postgres: walwriter
postgres 32200 10234 0 15:45 ? 00:00:00 postgres: walsender repuser 10.14.6.116(40564) streaming 0/C6000060

Slave节点
$ ps -ef |grep wal
postgres 5489 5483 0 Apr21 ? 00:17:36 postgres: walwriter
postgres 16185 16179 0 15:45 ? 00:00:00 postgres: walreceiver streaming 0/C6000060

二、通过视图pg_stat_replication，pg_stat_wal_receiver
主库：
postgres=# select pid,usename, application_name from pg_stat_replication ;
pid | usename | application_name
-------+----------+------------------
32200 | repuser | walreceiver

备库：
postgres=# select pid,status,receive_start_lsn from pg_stat_wal_receiver;
pid | status | receive_start_lsn
-------+-----------+-------------------
16185 | streaming | 0/C6000000
(1 row)

三、通过系统函数查询
主库：
postgres=# select * from pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
备库：postgres=# select * from pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)

四、控制文件pg_controldata $PGDATA | grep cluster
主库：
$pg_controldata /data/pgsql_5433/data/|grep cluster
Database cluster state: in production

备库：
$ pg_controldata /data/pgsql_5433/pgdata/|grep cluster
Database cluster state: in archive recovery
五、配置文件

PG12之前，通过recovery.conf文件判断， 该文件会存在于备库上
PG12以及以后，通过standy.signal文件来判断， 该文件存在于备库上

Posgresql查询主备节点角色

原文：https://www.cnblogs.com/helon/p/14867284.html