1、環(huán)境
成都創(chuàng)新互聯(lián)專注于曲靖企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè),商城系統(tǒng)網(wǎng)站開(kāi)發(fā)。曲靖網(wǎng)站建設(shè)公司,為曲靖等地區(qū)提供建站服務(wù)。全流程按需網(wǎng)站策劃，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)
操作系統(tǒng)版本：CentOS Linux release 8.0.1905 (Core)
PostgreSQL版本：10.6
主機(jī)：
重慶分公司
重慶分公司
test1 192.168.1.11
test2 192.168.1.12
test3 192.168.1.132、在3臺(tái)機(jī)器安裝并初始化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.log3、主庫(kù)配置
編輯主庫(kù)配置文件
[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' 配置認(rèn)證文件
[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創(chuàng)建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啟動(dòng)數(shù)據(jù)庫(kù)，關(guān)閉服務(wù)
[root@test1 ~]# systemctl start postgresql
[root@test1 ~]# systemctl stop firewalld.service 創(chuàng)建同步用戶
[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、配置兩臺(tái)從庫(kù)
從主庫(kù)復(fù)制備份過(guò)來(lái)
[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創(chuàng)建pg_archive目錄
[root@test2 ~]# mkdir -p /var/lib/pgsql/pg_archive
[root@test2 ~]# chown postgres:postgres /var/lib/pgsql/pg_archive修改監(jiān)聽(tīng)ip地址，并啟動(dòng)服務(wù)
[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重復(fù)做一次
5、測(cè)試主從同步狀態(tài)
在主庫(kù)查看同步節(jié)點(diǎn)
[postgres@test1 ~]$ psql
psql (10.6)
Type "help" for help.
postgres=# select * from pg_stat_replication;
創(chuàng)建測(cè)試庫(kù)，然后檢查兩個(gè)從庫(kù)是否同步
在主庫(kù)操作
postgres=# CREATE DATABASE test_db;
CREATE DATABASE查看從庫(kù)
test2同步了.jpg)
test3同步了
搭建完成
6、主從切換
模擬主庫(kù)故障，切換至從庫(kù)，然后把原來(lái)的主庫(kù)設(shè)置為從庫(kù)
查看同步狀態(tài)
主庫(kù)在in production狀態(tài)
[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 CSTtest2從庫(kù)在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 CSTtest3從庫(kù)在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模擬主庫(kù)故障
[root@test1 ~]# systemctl stop postgresql.service把test2提升為主庫(kù)，查看test2狀態(tài)，這是test2變成主庫(kù)了，可以寫了
[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指向新主庫(kù)，修改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 這時(shí)候test2就可以看到test3從庫(kù)連過(guò)來(lái)了.jpg)
把test1舊主庫(kù)變成從庫(kù)，指向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查看節(jié)點(diǎn)，test1連接上來(lái)了
test1的狀態(tài)也變成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切換完成
