Postgresql流复制配置方法

背景：在两个或多个部署了postgresql数据库上，进行配置流复制。或基于原来的流复制架构进行扩展备用节点。出现问题可进行手动切换主库

环境如下

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’

log_filename = ‘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 roles

Role 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 relations

Schema | 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；

技术交流欢迎加入Q群：177428068