Postgresql主从容灾

一、环境
操作系统：CentOS 7
数据库版本：Postgresql 11
-主库IP：192.168.114.135
-从库IP：192.168.114.134

二、数据库部署
https://www.postgresql.org/download/linux/redhat/ 进入该官网网址选择对应版本的镜像
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
yum install postgresql11 -y
yum install postgresql11-server -y
附带安装
yum install postgresql11-libs -y
yum install postgresql11-contrib -y
yum install postgresql11-devel -y

三、主从配置
主库
1.初始化
/usr/pgsql-11/bin/postgresql-11-setup initdb
systemctl enable postgresql-11
systemctl start postgresql-11
2.创建复制用户，进行主从同步使用
su - postgres
create role repl login replication encrypted password ‘123456’;
3.主库上配置从库采用repl账号
vim /var/lib/pgsql/11/data/pg_hba.conf

vim /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = ‘*’ # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 512 # (change requires restart)
shared_buffers = 128MB # min 128kB
dynamic_shared_memory_type = posix # the default is the first option
wal_level = hot_standby # minimal, replica, or logical
archive_mode = on # enables archiving; off, on, or always
archive_command = ‘cp %p /var/lib/pgsql/11/data/pg_archive/%f’ # command to use to archive a logfile segment
max_wal_senders = 6 # max number of walsender processes
wal_keep_segments = 10240 # in logfile segments, 16MB each; 0 disables
wal_sender_timeout = 60s # in milliseconds; 0 disables
log_directory = ‘log’ # directory where log files are written

4.创建目录
mkdir /var/lib/pgsql/11/data/pg_archive/
chown -R postgres.postgres /var/lib/pgsql/11/data

5.重启服务
systemctl restart postgresql-11

从库
1.把主节点所有的数据文件都会拷贝过来
pg_basebackup -h 192.168.114.135 -U repl -D /var/lib/pgsql/11/data/ -X stream -P
2.修改配置
vim /var/lib/pgsql/11/data/postgresql.conf
listen_addresses = ‘*’ # what IP address(es) to listen on;
port = 5432 # (change requires restart)
max_connections = 1000 # (change requires restart)
shared_buffers = 128MB # min 128kB
dynamic_shared_memory_type = posix # the default is the first option
wal_level = replica # minimal, replica, or logical
archive_mode = on # enables archiving; off, on, or always
archive_command = ‘cp %p /var/lib/pgsql/12/data/pg_archive/%f’ # command to use to archive a logfile segment
wal_sender_timeout = 60s # in milliseconds; 0 disables
hot_standby = on # “on” allows queries during recovery
max_standby_streaming_delay = 30s # max delay before canceling queries
wal_receiver_status_interval = 10s # send replies at least this often
hot_standby_feedback = on # send info from standby to prevent
log_directory = ‘log’ # directory where log files are written

3.创建恢复文件recovery.conf
cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf
vim /var/lib/pgsql/11/data/recovery.conf
#调整参数
recovery_target_timeline = ‘latest’ #同步到最新数据
standby_mode = on #指明从库身份
trigger_file = ‘failover.now’
primary_conninfo = ‘host=192.168.114.135 port=5432 user=repl password=123456’ #连接到主库信息
systemctl start postgresql-11

四、验证
主库上select client_addr,sync_state from pg_stat_replication;

在主库上创建更新数据验证