【PgSQL流复制管理】判断主备库角色

PgSQL判断主备库角色
本实例采用5433端口
$ psql -h 127.0.0.1 -p 5433
psql (12.3)
Type "help" for help.

postgres=# \c pgdb
You are now connected to database "pgdb" as user "postgres".

第一种方式：操作系统查看WAL发送进程，WAL接收进程
主库：walsender ... streaming ...
$ ps -ef |grep [w]al
postgres 3438 3433 0 Jun09 ? 00:01:27 postgres: walwriter
postgres 3461 3433 0 Jun09 ? 00:00:51 postgres: walsender postgres ::1(60364) idle
postgres 3462 3433 0 Jun09 ? 00:01:12 postgres: walsender repuser 10.15.6.116(40610) streaming 0/C9000060
postgres 31076 31068 0 2020 ? 00:38:30 postgres: walwriter
备库：walreceiver streaming ...
$ ps -ef |grep [w]al
postgres 5489 5483 0 Apr21 ? 00:46:52 postgres: walwriter
postgres 18708 16179 0 Jun09 ? 01:16:23 postgres: walreceiver streaming 0/C9000060

第二种方式：数据库查询WAL发送进程，WAL接收进程
主库：
pgdb=# select pid,usename,application_name,client_addr,state,sync_state from pg_stat_replication;
pid | usename | application_name | client_addr | state | sync_state
------+----------+------------------+--------------+-----------+------------
3461 | postgres | pg_recvlogical | ::1 | streaming | async
3462 | repuser | walreceiver | 10.15.6.116 | streaming | async
(2 rows)

备库：
pgdb=# select pid,status,last_msg_send_time,last_msg_receipt_time,conninfo from pg_stat_wal_receiver;
pid | status | last_msg_send_time | last_msg_receipt_time |
conninfo
-------+-----------+-------------------------------+-------------------------------+---------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
18708 | streaming | 2021-09-09 13:52:42.727112+08 | 2021-09-09 13:52:42.694763+08 | user=repuser passfile=/home/postgres/.pgpass dbname=replication host=t3-db01 port=5433 fallback_application_name=walreceiver sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any
(1 row)

第三种方式：通过系统函数进行确定
主库：
pgdb=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
备库：
pgdb=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
第四种方式：查看控制文件控制信息

主库：$ pg_controldata -D /data/pgsql_5433/data/ |grep cluster
Database cluster state: in production

备库：$ pg_controldata -D /data/pgsql_5433/data/ |grep cluster
Database cluster state: in archive recovery

第五种方式：recovery.conf待验证?

流复制部署过程中，备库$PGDATA目录下会创建recovery.conf配置文件；主库无此文件或文件名后缀为recovery.done。