环境如下
postgresql 版本为11.7
master 172.16.1.11 主库
slave 172.16.1.12 备库
建议：数据库服务器之间配置ntp进行时间定期（加到定期任务crontab -e）同步，否则可能会导致数据异常
一、搭建master服务器主库master
1、修改 master服务器 pg_hba.conf ,添加如下
TYPE DATABASE USER ADDRESS METHOD
表示允许任意网段的用户通过MD5进行认证连接
host all all 0.0.0.0/0 md5
表示允许该网段172.16.1.0 的repl 用户进行流复制
host replication repl 172.16.1.0/0 trust
host replication all ::1/128 trust
修改master服务器 postgresqlconf 参数配置如下, 为注释
listen_addresses = ‘*‘
port = 5432
max_connections = 2000
superuser_reserved_connections = 10
wal_level = logical
full_page_writes = on
wal_log_hints = off
archive_mode = on
archive_command = ‘/bin/true‘
max_wal_senders = 50
hot_standby = on
log_destination = ‘csvlog‘
logging_collector = on
log_directory = ‘log‘
logfilename = ‘postgresql-%Y-%m-%d%H%M%S‘
log_rotation_age = 1d
log_rotation_size = 10MB
log_statement = ‘mod‘
master服务器启动pg服务
postgres@MASTER > pg_ctl -D /data/postgresql/ start
waiting for server to start....2020-09-18 15:00:21.403 CST [9609] LOG: listening on IPv4 address "0.0.0.0", port 5432
2020-09-18 15:00:21.403 CST [9609] LOG: listening on IPv6 address "::", port 5432
2020-09-18 15:00:21.405 CST [9609] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-09-18 15:00:21.412 CST [9609] LOG: redirecting log output to logging collector process
2020-09-18 15:00:21.412 CST [9609] HINT: Future log output will appear in directory "log".
done
server started
master服务器修改默认用户 postgres 的密码和创建用于 流复制的用户 repl
postgres=# ALTER USER postgres WITH PASSWORD ‘p@ssw0rd‘;
ALTER ROLE
postgres=# CREATE USER repl WITH PASSWORD ‘p@ssw0rd‘ REPLICATION;
CREATE ROLE
postgres=# \du
List of rolesRole name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repl | Replication | {}
创建使用的测试数据库 pgpool 和 表 pgpool
postgres=# CREATE DATABASE pgpool ;
CREATE DATABASE
postgres=# \c pgpool
You are now connected to database "pgpool" as user "postgres".
pgpool=#
pgpool=# CREATE TABLE pgpool (id serial,age bigint,insertTime timestamp default now());
CREATE TABLE
pgpool=# insert into pgpool (age) values (1);
INSERT 0 1
pgpool=# select * from pgpool;
id | age | inserttime
----+-----+----------------------------
1 | 1 | 2020-09-18 15:07:03.329849
(1 row)
查看数据库是否为主库f 表示为主库
postgres=# select * from pg_is_in_recovery();
pg_is_in_recovery
f
(1 row)
至此master服务器主库搭建完毕
二、搭建slave服务器备库 slave
slave服务器创建数据库 data 目录
postgres@SLAVE:/date> mkdir postgresql
slave服务器使用 pg_basebackup 命令在线创建一个备库,使用该命令请确保 主库已经启动
postgres@SLAVE:/data/postgresql> pg_basebackup -h 172.16.1.11 -p 5432 -U repl -w -Fp -Xs -Pv -R -D /data/postgresql/
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
31133/31133 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/20000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
参数说明
-h 启动的主库数据库地址 -p 主库数据库端口
-U 流复制用户 -w 不使用密码验证
-Fp 备份输出正常的数据库目录 -Xs 使用流复制的方式进行复制
-Pv 输出复制过程的详细信息 -R 为备库创建recovery.conf文件
-D 指定创建的备库的数据库目录
slave服务器 在 recovery.conf 添加 application_name 为slave,配置如下
注：12版本以上无须此操作（且无此文件），默认使用IP做为名称
postgres@SLAVE:/data/postgresql> vim recovery.conf
standby_mode = ‘on‘
primary_conninfo = ‘application_name=slave user=repl passfile=‘‘/home/postgres/.pgpass‘‘ host=172.16.1.11 port=5432 sslmode=disable sslcompression=1 target_session_attrs=any‘
slave服务器修改postgresql.conf参数如下
max_connections = 2000 # 允许的最大数据库连接数
max_wal_senders = 100 # 该参数需要大于主库，否则可能导致备库无法读操作
slave服务器分配700 给数据库data目录
postgres@SLAVE:/data/postgresql> chmod 700 /data/postgresql/
slave服务器启动备库
postgres@SLAVE:/data/postgresql> pg_ctl -D /data/postgresql/ start
waiting for server to start....2020-09-18 15:20:19.966 CST [23907] LOG: listening on IPv4 address "0.0.0.0", port 5432
2020-09-18 15:20:19.966 CST [23907] LOG: listening on IPv6 address "::", port 5432
2020-09-18 15:20:19.970 CST [23907] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-09-18 15:20:20.007 CST [23907] LOG: redirecting log output to logging collector process
2020-09-18 15:20:20.007 CST [23907] HINT: Future log output will appear in directory "log".
done
server started
slave服务器连接数据库pgpool，查看数据是否同步
postgres@SLAVE:/data/postgresql> psql -h 172.16.1.12 -p 5432 -U postgres pgpool
Password for user postgres:
psql (10.3)
Type "help" for help.
pgpool=# \dt
List of relationsSchema | Name | Type | Owner
--------+--------+-------+----------
public | pgpool | table | postgres
(1 row)
pgpool=# select * from pgpool;
id | age | inserttime
----+-----+----------------------------
1 | 1 | 2020-09-18 15:07:03.329849
(1 row)
pgpool=#
参数说明
-h 备库数据库服务器地址 -p 备库数据库端口
-U 连接数据库的用户
slave服务器查看数据库是否为备库，t 表示为备库
pgpool=# select * from pg_is_in_recovery();
pg_is_in_recovery
t
(1 row)
master服务器查看数据库的状态
postgres=# select client_addr,usename,backend_start,application_name,sync_state,sync_priority FROM pg_stat_replication;
client_addr | usename | backend_start | application_name | sync_state | sync_priority
-------------+---------+-------------------------------+------------------+------------+---------------
172.16.1.12 | repl | 2020-09-18 15:20:20.066431+08 | slave | async | 0
(1 row)
postgres=#
参数说明
client_addr：备库服务器的地址 usename：使用的流复制用户
backend_start：流复制开始的时间 application_name：备库的名称
sync_state：备库与主库的同步状态 sync_priority：备库与主库变成同步状态的优先级
至此slave服务器备库slave搭建完毕
三、故障迁移思路
1、由于流复制为主备架构，默认只有主库才会写入数据，可通过命令把备库改为主库进行写入数据
在备库执行后，可在备库写入数据
[postgres@slave ~]#/postgresql/bin/pg_ctl promote -D /data/postgresql
思路：2台服务器配置流复制，通过pgpool或者keepalive进行VIP迁移。脚本通过测试连接VIP进入数据库，当出现无法连接时，主库把VIP关闭，备库执行上述命令，进行写入就数据，使得备库可写入数据，并启用VIP；