![](/style/images/good.png)
![](/style/images/bad.png)
PostgreSQL建立Hot Standby的Replication机制
source link: https://note.qidong.name/2018/09/postgresql-hot-standby/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
PostgreSQL建立Hot Standby的Replication机制
2018-09-22 12:06:37 +08 字数:1357 标签: Linux
PostgreSQL的Replication机制,大概有三种。
类型 用户 优点 Simply streaming replication On-prem, Manual EC2 简单设置;I/O性能高;存储量大 Replicated block device RDS, Azure Postgres 数据在云环境的耐久度高 Reconstruct from WAL(and switch to streaming replication)replicationHeroku Heroku Citus Cloud 节点自动重启;允许fork和点恢复
这里用的是第一种。
安装PostgreSQL ¶
sudo apt install postgresql
Ubuntu 16.04上,PostgreSQL版本为9.5,配置文件的目录为/etc/postgresql/9.5/main/
。
以下的配置文件都在此目录下。
当然,用Docker来部署也是更灵活的,需要去library/postgres - Docker Hub寻找合适的镜像。 配置文件的位置,可以在运行时指定。
master机 ¶
先去创建一个同步专用账户repl
:
$ sudo su - postgres
$ psql
postgres=# create user repl replication password '******';
然后修改两个配置文件。
其一是postgresql.conf
:
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 10000 # (change requires restart)
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
wal_level = hot_standby # minimal, archive, hot_standby, or logical
synchronous_commit = local # synchronization level;
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
max_wal_senders = 32 # max number of walsender processes
wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables
# - Master Server -
synchronous_standby_names = 'pg_standby' # standby servers that provide sync rep
hot_standby = on # "on" allows queries during recovery
以上配置仅是和热备份相关的片段。
其中,'pg_standby'
需要在/etc/hosts
中配置为slave的IP。
其二是pg_hba.conf
文件:
# TYPE DATABASE USER ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication repl 0.0.0.0/0 md5
最后一行就是允许repl
的访问连接。
完成修改后,重启服务。
sudo systemctl restart postgresql.service
slave机 ¶
修改前,先停止服务。
sudo systemctl stop postgresql.service
先做首次备份(需要输入前面repl
用户的密码):
rm -rf /var/lib/postgresql/9.5/main/
pg_basebackup -h pg_master -U repl -p 5432 -F p -P -x -R -D /var/lib/postgresql/9.5/main/ -l 20180907
然后修改一个配置,postgresql.conf
:
listen_addresses = 'localhost,pg_master'
port = 5432
max_connections = 10000
wal_level = hot_standby
max_wal_senders = 8
wal_keep_segments = 16
hot_standby = on
最后新增一个配置,recovery.conf
:
standby_mode = 'on'
primary_conninfo = 'host=pg_master port=5432 user=repl password=******** sslmode=disable sslcompression=1 application_name=pg_standby'
recovery_target_timeline = 'latest'
完成修改后,启动服务。
sudo systemctl start postgresql.service
测试 ¶
在master上建表:
$ sudo su - postgres
$ psql
postgres=# create database test;
CREATE DATABASE
然后,在slave上查看结果:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | 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
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
一些问题 ¶
端口 ¶
如果在配置时修改了默认端口5432
,比如改成65432
,
则需要在pg_basebackup
同步时添加-p 65432
参数,否则备份失败。
也需要在所有使用的地方注意,否则会找不到数据库服务。
一个错误 ¶
FATAL: hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 10000)
以上是导致slave的PostgreSQL起不来的一个原因。
在postgresql.conf
中,max_connections
不应该小于master。
由于master设为10000
,这里至少需要设为10000
。
一个警告 ¶
postgres=# create database test;
^CCancel request sent
WARNING: canceling wait for synchronous replication due to user request
DETAIL: The transaction has already committed locally, but might not have been replicated to the standby.
执行写操作时,总是无法返回;Ctrl
+c
取消后,又收到警告。
而slave上,明明已经同步完成。
可以通过配置postgresql.conf
,修改一行解决。
synchronous_commit = local # synchronization level;
这表示每次commit设置为仅保证本地完成,不等待远程同步。 这相当于在CAP中取AP,比较适合当前的使用场景。
参考 ¶
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK