HighGo Database判断流复制主备角色的方法

文档用途

HighGo Database数据库流复制判断主备角色

详细信息

进行流复制主备切换之前首先要知道当前数据库的角色，以下提供五种方法判断数据库角色，测试环境为一主一备。

1.通过pg_controldata命令查看数据库的控制信息，Database cluster state字段信息可判断是主库还是备库。

主库返回in production：

[highgo@localhost~]$ pg_controldata|grep clusterDatabasecluster state:inproduction

备库返回in archive recovery：

[highgo@localhost~]$ pg_controldata|grep clusterDatabasecluster state:inarchive recovery

2.通过进程查看，ps -ef | grep wal | grep -v grep。

显示wal sender …streaming进程说明当前数据库为主库：

[highgo@localhost~]$ ps-ef|grep wal|grep-v grep highgo1062510239007:03?00:00:00postgres: wal writer process highgo1454010239010:00?00:00:00postgres: wal sender process repuser x.x.150.163(63146)streaming0/100991F8

显示wal receive …streaming说明当前数据库为备库：

[highgo@localhost~]$ ps-ef|grep wal|grep-v grep highgo1869218687010:00?00:00:00postgres: wal receiver process streaming0/100991F8

3.通过查看数据字典表pg_stat_replication。进入psql客户端，输入select * from pg_stat_replication;

主库在表中能查到记录：

[highgo@localhost~]$ psql psql(4.7.6)PSQL:Release4.7.6Connectedto: HighGoDatabaseV4.7Standard EditionRelease4.7.6-64-bitProductionType"help"forhelp.highgo=# select * from pg_stat_replication;pid|usesysid|usename|application_name|client_addr|client_hostna me|client_port|backend_start|backend_xmin|state|se nt_location|write_location|flush_location|replay_location|sync_priority|sync_state-------+----------+---------+------------------+-----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------14540|16384|repuser|walreceiver|x.x.150.163||63146|2019-01-1010:00:23.252552+08||streaming|0/100991F8|0/100991F8|0/100991F8|0/100991F8|0|async(1row)

备库在表中无记录：

highgo=# select * from pg_stat_replication;pid|usesysid|usename|application_name|client_addr|client_hostname|c lient_port|backend_start|backend_xmin|state|sent_location|write_location|flush_location|replay_location|sync_priority|sync_state-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+---------------+----------------+----------------+-----------------+---------------+------------(0rows)

4.通过系统函数pg_is_in_recovery()判断。

进入psql客户端，输入select pg_is_in_recovery();

主库返回：

highgo=# select pg_is_in_recovery();pg_is_in_recovery-------------------f(1row)

备库返回t：

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

5.通过recovery.conf配置文件判断。

进入$PGDATA目录，存在recovery.conf配置文件说明是备库：

[highgo@localhost~]$ cd $PGDATA[highgo@localhostdata]$ ls backup_label.old pg_dynshmem pg_snapshots postgresql.auto.conf base pg_hba.conf pg_stat postgresql.confdata.tar.gz pg_ident.conf pg_stat_tmp postmaster.optsglobalpg_logical pg_subtrans postmaster.pid hgdb.lic pg_multixact pg_tblspc recovery.conf hgdb_log pg_notify pg_twophase pg_clog pg_replslot PG_VERSION pg_commit_ts pg_serial pg_xlog

不存在recovery.conf或此文件后缀名是recovery.done说明是主库。

[highgo@localhost~]$ cd $PGDATA[highgo@localhostdata]$ ls backup_label.old pg_commit_ts pg_serial pg_xlog base pg_dynshmem pg_snapshots postgresql.auto.confdata.tar.gz pg_hba.conf pg_stat postgresql.confglobalpg_ident.conf pg_stat_tmp postmaster.opts hgdb.lic pg_logical pg_subtrans postmaster.pid hgdb_log pg_multixact pg_tblspc NIH pg_notify pg_twophase pg_clog pg_replslot PG_VERSION