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 initdb
WARNING: using obsoleted argument syntax, try --help
WARNING: 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.conf
listen_addresses = '192.168.1.11'
wal_log_hints = on
archive_mode = on
archive_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         md5
host    replication     replica         192.168.1.12/32         md5
host    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.bak
standby_mode = on
primary_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 ~/.pgpass
192.168.1.11:5432:replication:replica:replica
192.168.1.12:5432:replication:replica:replica
192.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 ROLE
postgres=# \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:  replica
22797/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 ~/.pgpass
192.168.1.11:5432:replication:replica:replica
192.168.1.12:5432:replication:replica:replica
192.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.conf
listen_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:            1002
Catalog version number:               201707211
Database system identifier:           6782563721072319907
Database cluster state:               in production
pg_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:            1002
Catalog version number:               201707211
Database system identifier:           6782563721072319907
Database cluster state:               in archive recovery
pg_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:            1002
Catalog version number:               201707211
Database system identifier:           6782563721072319907
Database cluster state:               in archive recovery
pg_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.... done
server promoted
[root@test2 ~]# pg_controldata /var/lib/pgsql/data/
pg_control version number:            1002
Catalog version number:               201707211
Database system identifier:           6782563721072319907
Database cluster state:               in production
pg_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 = on
primary_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.conf
standby_mode = on
primary_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:            1002
Catalog version number:               201707211
Database system identifier:           6782563721072319907
Database cluster state:               in archive recovery
pg_control last modified:             Fri 17 Jan 2020 10:54:51 PM CST

切換完成