主备部署
前置条件
主备部署主要主机和备机的数据库所用的版本要完全一致,同时要求开始时需要对master机进行一次全备操作。所以空间要留充足,而且不要在业务高峰期做这件事。
主机和备机 的修改主要涉及到
- pg_hba.conf
- postgresql.conf
- ***recovery.conf(salve)或recovery.done(master)***
三个文件
这里的配置之:
master 10.0.4.119
slave 10.0.4.104
这个配置针对的数据库版本为 11.3-4
master配置
pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication all 10.0.4.104/32 md5
host replication all 10.0.4.119/32 md5
host replication all ::1/128 md5
postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
shared_buffers = 128MB # min 128kB
dynamic_shared_memory_type = windows # the default is the first option
wal_level = replica # minimal, replica, or logical
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_file_mode = 0640 # creation mode for log files,
log_timezone = 'Asia/Hong_Kong'
datestyle = 'iso, ymd'
#intervalstyle = 'postgres'
timezone = 'Asia/Hong_Kong'
lc_messages = 'Chinese (Simplified)_People''s Republic of China.936' # locale for system error message
# strings
lc_monetary = 'Chinese (Simplified)_People''s Republic of China.936' # locale for monetary formatting
lc_numeric = 'Chinese (Simplified)_People''s Republic of China.936' # locale for number formatting
lc_time = 'Chinese (Simplified)_People''s Republic of China.936' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.simple'
recovery.done
standby_mode=on
primary_conninfo= 'host=10.0.4.104 port=5432 user=postgres password=abc.123456 application_name=s1'
salve配置
数据同步
这个是最重要的一步,要是没有这一步就会报
database system identifier differs
这个卡了我好久,翻了不少资料。
解决方法其实很简单,在启动备机前要先通过命令将主机的数据同步过来,其实就是把data文件夹拷贝过来
下面这个指令是在bin目录下执行的。
./pg_basebackup.exe -h 10.0.0.119 -U postgres -D ../data -P -X fetch
配置变更
***pg_hba.conf(与maste一样)***
***postgresql.conf(与master一样)***
recovery.conf
standby_mode=on
primary_conninfo= 'host=10.0.4.119 port=5432 user=postgres password=abc.123456 application_name=s1'
主备切换
postgresql实际上是通过检测目录下是否存在_recovery.conf 来判定_是主机还是备机的
所以主备切换的过程如下
- 主机关闭或发生异常手动关闭
- 重命名主机的recovery.done为reconver.conf
- 重启主机
- 切换到备机
- 重命名备机的recovery.conf为recovery.done
- 重启备机
在切换回来的过程极为类似,就不赘述了。