PostgreSQL主从搭建
原创
©著作权归作者所有：来自51CTO博客作者Expect_lei的原创作品，请联系作者获取转载授权，否则将追究法律责任
主备环境说明
主机名
| IP
| 角色
| 端口
|
master
| 192.168.20.133
| Master
| 5432
|
slave
| 192.168.20.134
| Slave
| 5432
|
创建流复制
首先在主备服务器上安装好PG数据库，具体安装方法这里不再介绍。
配置hosts
在主、备服务器上都设置
[root@master ~]# cat /etc/hosts
127.0.0.1 localhost
192.168.20.133 master
192.168.20.134 slave
[可选]初始化master数据库
如果新搭建的主备环境，那么需要初始化主库。如果是已经运行的PG，那么就不需要这一步操作。
#切换到postgres账户
[root@master ~]# su - postgres
#初始化data
[postgres@master ~]$ initdb -D $PGDATA
# 启动master数据库
[postgres@master ~]$ pg_ctl start -D $PGDATA
主库创建用户
创建用户用于复制WAL日志。
postgres=# CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD '123456';
CREATE ROLE
配置pg_hba.conf
在主库的pg_hba.conf最后一行增加如下:
host replication repuser slave md5
配置postgresql.conf
在主库上配置如下：
listen_addresses = '*'
port = 5432
max_wal_senders = 10
wal_level = replica
archive_mode = on
archive_command = 'cd ./'
hot_standby = on
wal_keep_segments = 64
full_page_writes = on
wal_log_hints = on
其他可配置参数：
以下四个参数值，在备库上参数值不应小于主库上的值，否则会导致无法启动。
max_connections
max_prepared_transactions
max_locks_per_transaction
max_worker_processes
如果修改参数值大小：
- 将参数值修改更大
先修改所有备库，再修改主库 - 将参数值改小
先修改主库，再修改备库
重启数据库
systemctl restart postgresql-11
pg_basebackup 创建备库
在slave端的postgres用户下执行：
[postgres@slave ~]$ pg_basebackup -h master -U repuser -D /var/lib/pgsql/11/data/ -X stream -P
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/11000028 on timeline 3
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1734"
31203/31203 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/110000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
修改slave中data目录下的pg_hba.conf最后一行修改如下:
host replication repuser master md5
配置recovery.conf
主库：
[postgres@slave ~]$ cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.done
[postgres@slave ~]$ vi /var/lib/pgsql/11/data/recovery.done
#编辑内容如下
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=slave port=5432 user=repuser password=123456'
备库:
[postgres@slave ~]$ cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf
[postgres@slave ~]$ vi /var/lib/pgsql/11/data/recovery.conf
#编辑内容如下
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=master port=5432 user=repuser password=123456'
验证
查看进程、状态
主库
查看主库的WAL日志发送进程是否正常
[root@master data]# ps -ef|grep walsender
postgres 3274 3229 0 02:40 ? 00:00:00 postgres: walsender repuser 192.168.20.134(49896) streaming 0/180003C8
root 3293 1392 0 02:44 pts/0 00:00:00 grep --color=auto walsender
从库
查看从库WAL日志接收进程是否正常
[root@slave 11]# ps -ef|grep walreceiver
postgres 5942 5935 0 02:40 ? 00:00:00 postgres: walreceiver streaming 0/180003C8
root 6192 3073 0 02:45 pts/0 00:00:00 grep --color=auto walreceiver
主库查看复制状态
流异步复制
lei=# \x on;
Expanded display is on.
lei=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 3274
usesysid | 16774
usename | repuser --复制用户
application_name | walreceiver
client_addr | 192.168.20.134 --从库IP
client_hostname | slave
client_port | 49896
backend_start | 2019-05-30 02:40:58.253032-04
backend_xmin |
state | streaming --流复制
sent_lsn | 0/180003C8
write_lsn | 0/180003C8
flush_lsn | 0/180003C8
replay_lsn | 0/180003C8
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async --异步
测试数据
重启主、备数据库使配置生效
主库
[postgres@master ~]$ psql
psql (11.3)
Type "help" for help.
postgres=# \c lei;
You are now connected to database "lei" as user "postgres".
lei=# select * from test;
id
----
1
1
2
(3 rows)
备库
[postgres@slave ~]$ psql
psql (11.3)
Type "help" for help.
postgres=# \c lei;
You are now connected to database "lei" as user "postgres".
lei=# select * from test;
id
----
1
1
2
(3 rows)
主库插入数据
lei=# insert into test values(3);
INSERT 0 1
从库查询
lei=# select * from test;
id
----
1
1
2
3
(4 rows)
至此，PostgreSQL主从流复制安装部署完成。
附录
区分数据库是主库还是备库方法：
方法1. pg_controldata
主库
[postgres@localhost ~]$ pg_controldata
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6362107256088627972
Database cluster state: in production
备库
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6362107256088627972
Database cluster state: in archive recovery
方法2.字典表pg_stat_replication
只有主库才能查到数据
lei=# \x on;
Expanded display is on.
lei=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 3274
usesysid | 16774
usename | repuser --复制用户
application_name | walreceiver
client_addr | 192.168.20.134 --从库IP
client_hostname | slave
client_port | 49896
backend_start | 2019-05-30 02:40:58.253032-04
backend_xmin |
state | streaming --流复制
sent_lsn | 0/180003C8
write_lsn | 0/180003C8
flush_lsn | 0/180003C8
replay_lsn | 0/180003C8
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async --异步
方法3.根据PG进程
进程中显示wal sender的是主库，显示wal receiver的是备库，如下：
主库
[root@slave 11]# ps -ef|grep walsender
postgres 8625 8612 0 03:26 ? 00:00:00 postgres: walsender repuser 192.168.20.133(55306) streaming 0/1E0001B0
root 9404 3073 0 03:40 pts/0 00:00:00 grep --color=auto walsende
备库
[root@master data]# ps -ef|grep walreceiver
postgres 4306 4079 0 03:26 ? 00:00:01 postgres: walreceiver streaming 0/1E0001B0
root 4360 1392 0 03:41 pts/0 00:00:00 grep --color=auto walreceiver
方法4.通过pg_is_in_recovery函数
备库是t，主库是f。
主库
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
备库
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
