PostgreSQL异步主从流复制搭建
1 总体规划
|
Master库 |
Slave库 |
|
|
操作系统 |
CentOS Linux release 7.5.1804 |
CentOS Linux release 7.5.1804 |
|
处理器 |
1 |
1 |
|
内存 |
4G |
4G |
|
硬盘 |
38G |
38G |
|
主机名称 |
SHSNC-DB01 |
SHSNC-DB02 |
|
IP地址 |
192.168.1.61 |
192.168.1.62 |
具体安装步骤，可以查看《PostgreSQL数据库的安装》，不在本文的介绍范围内。
2 PostgreSQL主从异步流复制搭建
2.1 参数检查
检查主库postgresql.conf文件是否已经配置以下参数：
listen_addresses = '*' port = 5432 log_destination = 'csvlog' logging_collector = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' max_connections = 1000 max_connections = 100 max_connections = hot_standby
若以上参数没有配置，配置完后需要重启PostgreSQL数据库。
2.2 创建同步账号
在主库创建同步账号以及相应的数据库
$ psql -p 5432 -U postgres postgres psql (9.6.11) Type "help" for help. postgres=# CREATE DATABASE pocdb; CREATE DATABASE postgres=# \c pocdb You are now connected to database "pocdb" as user "postgres". pocdb=# pocdb=# CREATE USER repl ENCRYPTED PASSWORD '123456' REPLICATION; CREATE ROLE
检查创建用户的权限：
$ psql -p 5432 -U postgres postgres psql (9.6.11) Type "help" for help. postgres=# \du+ List of roles Role name | Attributes | Member of | Description -----------+------------------------------------------------------------+-----------+------------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} | repl | Replication | {} |
2.3 修改pg_hba.conf文件
在主库pg_hba.conf文件中添加相应内容，添加后关键内容如下：
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust host all all 192.168.1.0/24 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres trust #host replication postgres 127.0.0.1/32 trust #host replication postgres ::1/128 trust host replication repl 192.168.1.61/32 md5 host replication repl 192.168.1.62/32 md5
需要注意此处用户名repl是刚才创建同步的用户，不是postgres。
2.4 reload配置文件
reload主库配置文件：
$ pg_ctl -D /postgres/pgdata/ reload
server signaled
master配置成功后，slave 安装基本环境同 master ,区别在于 slave 从库不需要进行 initdb 初始化数据库
2.5 备库数据复制
在备库使用postgres主机账号进行操作：
$ pg_basebackup -h 192.168.1.61 -U repl -W -Fp -Pv -Xs -R -D /postgres/pgdata Password: pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed transaction log start point: 0/2000028 on timeline 1 pg_basebackup: starting background WAL receiver 29956/29956 kB (100%), 1/1 tablespace transaction log end point: 0/20000F8 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed
上述表示同步成功
2.6 检查recovery配置文件
在备库检查recovery.conf配置文件
$ cat recovery.conf standby_mode = 'on' primary_conninfo = 'user=repl password=123456 host=192.168.1.61 port=5432 sslmode=disable sslcompression=1'
2.7 启动备库
$ pg_ctl -D /postgres/pgdata start
server starting
2.8 搭建后验证
启动后在主库创建一个数据库并在备库查看是否已同步：
主库：
$ psql psql (9.6.11) Type "help" for help. postgres=# create database shsnc; CREATE DATABASE postgres=# \list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- chenzxdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | pocdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | shsnc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (6 rows) postgres=# \x Expanded display is on. postgres=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 29210 usesysid | 16386 usename | repl application_name | walreceiver client_addr | 192.168.1.162 ------>从备库连接上主库 client_hostname | client_port | 59590 backend_start | 2018-11-15 17:13:54.269887+08 backend_xmin | state | streaming sent_location | 0/4032A78 write_location | 0/4032A78 flush_location | 0/4032A78 replay_location | 0/4032A78 sync_priority | 0 sync_state | async postgres=#
备库：
$ psql psql (9.6.11) Type "help" for help. postgres=# \list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- chenzxdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | pocdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | shsnc | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (6 rows) postgres=#
另外，可查看pg_log查看关键字"database system is ready to accept read only connections"
$ grep "database system " ./pg_log/*csv ./pg_log/postgresql-2018-11-15_171206.csv:2018-11-15 17:12:06.999 CST,,,4324,,5bed3866.10e4,2,,2018-11-15 17:12:06 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,"" ----->连接上主库并使用read only模式打开数据库
3 搭建过程中遇到的问题QA
- pg_basebackup: could not connect to server: FATAL: number of requested standby connections exceeds max_wal_senders (currently 0)
解决方案：在主库postgres.conf文件中添加以下参数
max_connections = 1000 max_connections = 100 max_connections = hot_standby
- psql: FATAL: the database system is starting up
解决方案：在备库postgres.conf文件中添加以下参数
hot_standby = on
