PostgreSQL10.6主从复制搭建和故障切换

1、环境

操作系统版本：CentOS Linux release 8.0.1905 (Core)

PostgreSQL版本：10.6
主机：

    test1 192.168.1.11    test2 192.168.1.12    test3 192.168.1.13

2、在3台机器安装并初始化PostgreSQL

[root@test1 ~]# yum install postgresql-server -y[root@test1 ~]# postgresql-setup initdbWARNING: using obsoleted argument syntax, try --helpWARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql * Initializing database in '/var/lib/pgsql/data' * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

3、主库配置
编辑主库配置文件

[root@test1 ~]# vim /var/lib/pgsql/data/postgresql.conflisten_addresses = '192.168.1.11'wal_log_hints = onarchive_mode = onarchive_command = 'cp %p /var/lib/pgsql/pg_archive/%f' 

配置认证文件

[root@test1 ~]# vim /var/lib/pgsql/data/pg_hba.conf#追加三行host    replication     replica         192.168.1.11/32         md5host    replication     replica         192.168.1.12/32         md5host    replication     replica         192.168.1.13/32         md5

创建pg_archive目录

[root@test1 ~]# mkdir -p /var/lib/pgsql/pg_archive[root@test1 ~]# chown postgres:postgres /var/lib/pgsql/pg_archive

配置recovery.conf

[root@test1 ~]# vim /var/lib/pgsql/data/recovery.bakstandby_mode = onprimary_conninfo = 'host=192.168.1.11 port=5432 user=replica password=replica'recovery_target_timeline = 'latest'[root@test1 ~]# chown postgres:postgres /var/lib/pgsql/data/recovery.bak

新建pgpass文件

[postgres@test1 ~]$ vim ~/.pgpass192.168.1.11:5432:replication:replica:replica192.168.1.12:5432:replication:replica:replica192.168.1.13:5432:replication:replica:replica[root@test1 ~]# chown 600 /var/lib/pgsql/.pgpass

启动数据库，关闭服务

[root@test1 ~]# systemctl start postgresql[root@test1 ~]# systemctl stop firewalld.service 

创建同步用户

[root@test3 ~]# su - postgres[postgres@test3 ~]$ psql psql (10.6)Type "help" for help.postgres=# create role replica login replication encrypted password 'replica';CREATE ROLEpostgres=# \q[postgres@test1 ~]$ 

4、配置两台从库
从主库复制备份过来

[root@test2 ~]# rm -rf /var/lib/pgsql/data/*[root@test2 ~]# pg_basebackup -h 192.168.1.11 -p 5432 -U replica -F p -P -D /var/lib/pgsql/data/Password:  replica22797/22797 kB (100%), 1/1 tablespace[root@test2 ~]# chown postgres:postgres -R /var/lib/pgsql/data

重命名recovery配置文件

[root@test2 ~]# mv /var/lib/pgsql/data/recovery.bak /var/lib/pgsql/data/recovery.conf

新建pgpass文件

[root@test2 ~]# su - postgres[postgres@test2 ~]$ vim ~/.pgpass192.168.1.11:5432:replication:replica:replica192.168.1.12:5432:replication:replica:replica192.168.1.13:5432:replication:replica:replica[root@test2 ~]# chown 600 /var/lib/pgsql/.pgpass

创建pg_archive目录

[root@test2 ~]# mkdir -p /var/lib/pgsql/pg_archive[root@test2 ~]# chown postgres:postgres /var/lib/pgsql/pg_archive

修改监听ip地址，并启动服务

[root@test2 ~]# vim /var/lib/pgsql/data/postgresql.conflisten_addresses = '192.168.1.12'[root@test2 ~]# systemctl start postgresql[root@test2 ~]# systemctl stop firewalld.service

在test3重复做一次
5、测试主从同步状态
在主库查看同步节点

[postgres@test1 ~]$ psql psql (10.6)Type "help" for help.postgres=# select * from pg_stat_replication;

创建测试库，然后检查两个从库是否同步
在主库操作

postgres=# CREATE DATABASE test_db;CREATE DATABASE

查看从库
test2同步了

test3同步了

搭建完成
6、主从切换
模拟主库故障，切换至从库，然后把原来的主库设置为从库
查看同步状态
主库在in production状态

[root@test1 ~]# pg_controldata /var/lib/pgsql/data/pg_control version number:            1002Catalog version number:               201707211Database system identifier:           6782563721072319907Database cluster state:               in productionpg_control last modified:             Fri 17 Jan 2020 10:39:41 PM CST

test2从库在in archive recovery

[root@test2 ~]# pg_controldata /var/lib/pgsql/data/pg_control version number:            1002Catalog version number:               201707211Database system identifier:           6782563721072319907Database cluster state:               in archive recoverypg_control last modified:             Fri 17 Jan 2020 10:39:44 PM CST

test3从库在in archive recovery

[root@test3 ~]# pg_controldata /var/lib/pgsql/data/pg_control version number:            1002Catalog version number:               201707211Database system identifier:           6782563721072319907Database cluster state:               in archive recoverypg_control last modified:             Fri 17 Jan 2020 10:39:47 PM CST

模拟主库故障

[root@test1 ~]# systemctl stop postgresql.service

把test2提升为主库，查看test2状态，这是test2变成主库了，可以写了

[root@test2 ~]# su - postgres -c "pg_ctl promote"waiting for server to promote.... doneserver promoted[root@test2 ~]# pg_controldata /var/lib/pgsql/data/pg_control version number:            1002Catalog version number:               201707211Database system identifier:           6782563721072319907Database cluster state:               in productionpg_control last modified:             Fri 17 Jan 2020 10:48:12 PM CST

把test3指向新主库，修改recovery里面的host，然后重启

[root@test3 ~]# vim /var/lib/pgsql/data/recovery.conf standby_mode = onprimary_conninfo = 'host=192.168.1.12 port=5432 user=replica password=replica'recovery_target_timeline = 'latest'[root@test3 ~]# systemctl restart postgresql.service 

这时候test2就可以看到test3从库连过来了

把test1旧主库变成从库，指向test2

[root@test1 ~]# mv /var/lib/pgsql/data/recovery.bak  /var/lib/pgsql/data/recovery.conf[root@test1 ~]# vim /var/lib/pgsql/data/recovery.confstandby_mode = onprimary_conninfo = 'host=192.168.1.12 port=5432 user=replica password=replica'recovery_target_timeline = 'latest'[root@test1 ~]# systemctl start postgresql.service

去test2查看节点，test1连接上来了

test1的状态也变成in archive recovery了

[root@test1 ~]#  pg_controldata /var/lib/pgsql/data/pg_control version number:            1002Catalog version number:               201707211Database system identifier:           6782563721072319907Database cluster state:               in archive recoverypg_control last modified:             Fri 17 Jan 2020 10:54:51 PM CST

切换完成