PostgreSQL12.2 主从流复制搭建

背景介绍

操作系统版本：Red Hat Enterprise Linux Server release 7.7 (Maipo)
数据库版本： PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
主库IP(master): 10.32.144.101
备库IP(slave): 10.32.144.102
需求描述：搭建主从流复制

首先在主备服务器上安装好PostgreSQL数据库，两台服务器分别安装的数据库版本需要一致，具体安装方法这里不再介绍。
主从流复制搭建步骤如下：

1.配置hosts
在主、备服务器上都需要设置

[postgres@tc01 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.32.144.101 tc01
10.32.144.102 tc02
[postgres@tc01 ~]$

2.主库创建用户
创建用户用于复制WAL日志。

postgres=# CREATE USER repl replication LOGIN CONNECTION LIMIT 30 ENCRYPTED PASSWORD ‘123456’;
CREATE ROLE

3.配置pg_hba.conf
在主库的pg_hba.conf最后一行增加如下:
vi $PGDATA/pg_hba.conf

host replication repl tc02 md5

4.配置postgresql.conf
检查配置

netstat -an|grep PGSQL
grep ‘^\ *[a-z]’ $PGDATA/postgresql.conf|awk -F “#” ‘{print $1}’

在主库上配置如下：
vi $PGDATA/postgresql.conf

listen_addresses = ‘*’
port = 2020
############### connections ##############
max_connections = 500
superuser_reserved_connections = 10
tcp_keepalives_idle = 180
tcp_keepalives_interval = 10
tcp_keepalives_count = 3
############### memory ##############
shared_buffers = 1024MB
#huge_pages = try
######### archive ##########
archive_mode = on
archive_command = ‘cp %p /opt/pgsql/pgarch/%f’

############### master-slave ##############
wal_level = hot_standby
#hot_standby = on
max_wal_senders = 10
wal_keep_segments = 16
wal_sender_timeout = 60s

################ sync #####################
synchronous_commit = on
checkpoint_timeout = 5min

################ other #####################
dynamic_shared_memory_type = posix
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = ‘Asia/Shanghai’
datestyle = ‘iso, mdy’
timezone = ‘Asia/Shanghai’
lc_messages = ‘en_US.UTF-8’
lc_monetary = ‘en_US.UTF-8’
lc_numeric = ‘en_US.UTF-8’
lc_time = ‘en_US.UTF-8’
default_text_search_config = ‘pg_catalog.english’

5.pg_basebackup 创建备库

从库只安装数据库软件，不进行数据库初始化。根据主库相关路径配置，创建日志目录，表空间目录。
主库重载数据库配置信息：
pg_ctl reload

在slave端执行数据库备份：

rm -rf /opt/pgsql/pgdata
mkdir /opt/pgsql/pgdata
 

[postgres@tc02 ~]$ pg_basebackup -D $PGDATA -F p -X stream -v -P -h 10.32.144.101 -p 2020 -U repl -l replbackup
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/71E3940 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_5974"
52011/52011 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/75FBDF0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
[postgres@tc02 ~]$ 
复制

6.在备库上配置参数：
vi $PGDATA/postgresql.conf

hot_standby=on
max_connections=512

修改slave中data目录下的pg_hba.conf最后一行修改如下:

host replication repl tc01 md5

7.启动备库

[postgres@tc02 pgdata]$ pg_ctl status
pg_ctl: no server running
[postgres@tc02 pgdata]$ pg_ctl start
waiting for server to start....2020-05-20 00:49:03.606 CST [6155] FATAL:  data directory "/opt/pgsql/pgdata" has invalid permissions
2020-05-20 00:49:03.606 CST [6155] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
stopped waiting
pg_ctl: could not start server
Examine the log output.

chown -R postgres.postgres /opt/pgsql/pgdata
chmod 0700 /opt/pgsql/pgdata

[postgres@tc02 pgdata]$ pg_ctl start
waiting for server to start....2020-05-20 00:51:12.536 CST [6189] LOG:  starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-05-20 00:51:12.540 CST [6189] LOG:  listening on IPv4 address "0.0.0.0", port 2020
2020-05-20 00:51:12.540 CST [6189] LOG:  listening on IPv6 address "::", port 2020
2020-05-20 00:51:12.541 CST [6189] LOG:  listening on Unix socket "/tmp/.s.PGSQL.2020"
2020-05-20 00:51:12.617 CST [6190] LOG:  database system was interrupted; last known up at 2020-05-20 00:36:53 CST
2020-05-20 00:51:12.644 CST [6190] LOG:  entering standby mode
2020-05-20 00:51:12.646 CST [6190] LOG:  redo starts at 0/71E3940
2020-05-20 00:51:12.699 CST [6190] LOG:  consistent recovery state reached at 0/75FBDF0
2020-05-20 00:51:12.700 CST [6189] LOG:  database system is ready to accept read only connections
2020-05-20 00:51:12.713 CST [6194] LOG:  started streaming WAL from primary at 0/8000000 on timeline 1
done
server started
[postgres@tc02 pgdata]$ pg_ctl status
pg_ctl: server is running (PID: 6189)
/opt/pgsql/pgsoft/bin/postgres
[postgres@tc02 pgdata]$

复制

8.验证
查看进程、状态

ps -ef |grep postgres

[postgres@tc02 pgdata]$ ps -ef |grep postgres
root 5306 5258 0 May19 pts/0 00:00:00 su - postgres
postgres 5307 5306 0 May19 pts/0 00:00:00 -bash
postgres 6189 1 0 00:51 ? 00:00:00 /opt/pgsql/pgsoft/bin/postgres
postgres 6190 6189 1 00:51 ? 00:00:01 postgres: startup recovering 000000010000000000000009
postgres 6191 6189 0 00:51 ? 00:00:00 postgres: checkpointer
postgres 6192 6189 0 00:51 ? 00:00:00 postgres: background writer
postgres 6193 6189 0 00:51 ? 00:00:00 postgres: stats collector
postgres 6194 6189 1 00:51 ? 00:00:01 postgres: walreceiver streaming 0/9F6D120
postgres 6205 5307 0 00:52 pts/0 00:00:00 ps -ef
postgres 6206 5307 0 00:52 pts/0 00:00:00 grep --color=auto postgre
主库
查看主库的WAL日志发送进程是否正常

[postgres@tc01 pg_wal]$ ps -ef|grep walsender
postgres 24419 5112 0 00:51 ? 00:00:01 postgres: walsender repl 10.32.144.102(52178) streaming 0/A207368
postgres 28745 28874 0 00:54 pts/2 00:00:00 grep --color=auto walsender
[postgres@tc01 pg_wal]$

从库
查看从库WAL日志接收进程是否正常

[postgres@tc02 pg_wal]$ ps -ef|grep walreceiver
postgres 6194 6189 1 00:51 ? 00:00:03 postgres: walreceiver streaming 0/A2C6588
postgres 6249 5307 0 00:55 pts/0 00:00:00 grep --color=auto walreceiver
[postgres@tc02 pg_wal]$

9.数据测试
重启主、备数据库使配置生效
主库：

CREATE TABLE adg (c1 bigint  primary key,c2 bigint,c3 time,c4 character varying(136));
insert into adg(c1,c2,c3,c4) values (random()*10000000, random()*1000,now(),md5(random()::text));
复制

备库

test=> select count(*) from adg;
count
-------
     1
(1 row)
test=> CREATE TABLE adg1 (c1 bigint  primary key,c2 bigint,c3 time,c4 character varying(136));
ERROR:  cannot execute CREATE TABLE in a read-only transaction
test=>
复制

主库查看复制状态
流异步复制，只有主库才能查到数据

[postgres@tc01 pg_wal]$ psql
psql (12.2)
Type "help" for help.
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 24419
usesysid         | 24583
usename          | repl        --复制用户
application_name | walreceiver
client_addr      | 10.32.144.102    --从库IP
client_hostname  | tc02
client_port      | 52178
backend_start    | 2020-05-20 00:51:12.707557+08
backend_xmin     |
state            | streaming       --流复制
sent_lsn         | 0/B3770E0
write_lsn        | 0/B3770E0
flush_lsn        | 0/B3770E0
replay_lsn       | 0/B3770E0
write_lag        | 00:00:00.000221
flush_lag        | 00:00:00.000481
replay_lag       | 00:00:00.000483
sync_priority    | 0
sync_state       | async          --异步
reply_time       | 2020-05-20 01:01:08.290233+08
postgres=#

postgres=# select client_addr,sync_state from pg_stat_replication;
复制

至此，PostgreSQL12.2主从流复制安装部署完成。

区分数据库是主库还是备库可以用pg_controldata：
主库

pg_controldata |grep "Database cluster state"

[postgres@tc01 ~]$ pg_controldata |grep "Database cluster state"
Database cluster state:               in production
[postgres@tc01 ~]$
复制

备库

[postgres@tc02 ~]$ pg_controldata |grep "Database cluster state"
Database cluster state:               in archive recovery
[postgres@tc02 ~]$
复制