PostgreSQL学习随笔17 Repmgr部署实验

1Repmgr
1.1配置postgresql复制
hostnamectl set-hostname node1
hostnamectl set-hostname node2

Hosts文件配置
# vim etc/hosts
192.168.213.146 node1
192.168.213.147 node2

关闭防火墙
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
SELINUX
vi etc/selinux/config
将SELINUX=enforcing 改为SELINUX=disabled
配置流复制主备,主节点配置
 
以下操作分别在主节点和备节点同时执行:
postgres配置两台数据库服务器之间的信任关系
su - postgres
ssh-keygen
ssh-copy-id postgres@备节点IP           主节点执行
ssh-copy-id postgres@主节点IP           备节点执行
主节点配置
编辑vi pg_hba.conf文件,添加如下内容:
local   replication postgres                             trust
local   repmgr      postgres                               trust
host    all         postgres      127.0.0.1/32            trust
host    replication postgres      192.168.179.0/24       md5
host    repmgr      postgres      192.168.179.0/24         md5
host    repmgr      repmgr        192.168.179.0/24          md5

编辑postgresql.conf文件,添加如下内容:
listen_addresses = '*'
port = 5432
wal_level = replica
max_wal_senders = 32
max_replication_slots = 32
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
wal_sender_timeout = 60s
wal_log_hints = on
unix_socket_directories = '.'
unix_socket_permissions = 0700
bgwriter_delay = '10ms'
datestyle = 'iso, mdy'
full_page_writes = 'True'
huge_pages = 'False'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
tcp_keepalives_idle = 0
tcp_keepalives_interval = 0
tcp_keepalives_count = 0
wal_receiver_status_interval = '1s'
wal_writer_delay = '10ms'
autovacuum_max_workers=10
maintenance_work_mem = 256MB
vacuum_cost_limit=150
max_worker_processes = 128
checkpoint_timeout = 10min
log_disconnections = on
wal_writer_delay = 10ms
log_destination = 'csvlog'
logging_collector = on
log_directory = '/opt/pg_root/pg_log'
log_filename = 'postgresql-log.%a'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 1000000
log_min_duration_statement = 500
log_statement = ddl
huge_pages = off
shared_preload_libraries = 'repmgr'
wal_log_hints=on

所有节点都需要安装repmgr工具
–解压压缩包
su - root
export PATH=/opt/pgsql/bin:$PATH
tar -zxvf repmgr-5.1.0.tar.gz
cd repmgr-5.1.0
–编译安装repmgr
./configure
make && make install
cp -R opt/soft_bak/repmgr-5.1.0 opt/repmgr510
chown postgres:postgres -R opt/repmgr510

su - postgres
cd ~
修改~/.bash_profile和~/.bashrc环境变量
vi ~/.bash_profile
vi ~/.bashrc
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=5432
export PGDATA=/opt/pg_root
export LANG=en_US.utf8
export PGHOME=/opt/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGDATABASE=postgres
alias rm='rm -i'
alias ll='ls -lh'
export PATH=$PGHOME/bin:/opt/repmgr510:$PATH:.


source ~/.bashrc
source .bash_profile

su - root
创建/etc/repmgr.conf
以root用户创建/etc/repmgr.conf
修改/etc/repmgr.conf
vi etc/repmgr.conf

node_id=1
node_name=node1
conninfo='host=192.168.213.146 user=postgres dbname=repmgr connect_timeout=2'
data_directory='/opt/pg_root'
pg_bindir='/opt/pgsql/bin'
log_level=INFO
log_status_interval=10
log_file='/opt/repmgr510/repmgrd.log'
failover=automatic
monitoring_history=yes
monitor_interval_secs=5
reconnect_attempts=10
reconnect_interval=5
follow_command='/opt/repmgr510/repmgr standby follow --upstream-node-id=%n'
promote_command='/opt/repmgr510/repmgr standby promote'

chown postgres:postgres etc/repmgr.conf

创建.pgpass文件
su - postgres
vi ~/.pgpass
*:*:*:postgres:123456
*:*:repmgr:repmgr:123456
chmod 600 home/postgres/.pgpass

#主库创建 repmgr 库存储元数据
create user repmgr superuser password '123456';
create database repmgr owner repmgr;
\c repmgr repmgr  
create extension repmgr;

启动主节点,并注册
su - postgres
pg_ctl start
repmgr primary register

启动守护进程
repmgrd --pid-file tmp/repmgrd.pid
备节点配置
编辑vi pg_hba.conf文件,添加如下内容:
host all all 0.0.0.0/0 trust
host replication all 0.0.0.0/0 trust

编辑postgresql.conf文件,添加如下内容:
listen_addresses = '*'
port = 5432
wal_level = replica
max_wal_senders = 32
max_replication_slots = 32
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
wal_sender_timeout = 60s
wal_log_hints = on
unix_socket_directories = '.'
unix_socket_permissions = 0700
bgwriter_delay = '10ms'
datestyle = 'iso, mdy'
full_page_writes = 'True'
huge_pages = 'False'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
tcp_keepalives_idle = 0
tcp_keepalives_interval = 0
tcp_keepalives_count = 0
wal_receiver_status_interval = '1s'
wal_writer_delay = '10ms'
autovacuum_max_workers=10
maintenance_work_mem = 256MB
vacuum_cost_limit=150
max_worker_processes = 128
checkpoint_timeout = 10min
log_disconnections = on
wal_writer_delay = 10ms
log_destination = 'csvlog'
logging_collector = on
log_directory = '/opt/pg_root/pg_log'
log_filename = 'postgresql-log.%a'
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 1000000
log_min_duration_statement = 500
log_statement = ddl
huge_pages = off
shared_preload_libraries = 'repmgr'
wal_log_hints=on

所有节点都需要安装repmgr工具
–解压压缩包
su - root
export PATH=/opt/pgsql/bin:$PATH
tar -zxvf repmgr-5.1.0.tar.gz
cd repmgr-5.1.0
–编译安装repmgr
./configure
make && make install
cp -R opt/soft_bak/repmgr-5.1.0 opt/repmgr510
chown postgres:postgres -R opt/repmgr510

su - postgres
cd ~
vi .bash_profile 
export PATH=$PGHOME/bin:/opt/repmgr510:$PATH:.
source .bash_profile

su - root
创建/etc/repmgr.conf
以root用户创建/etc/repmgr.conf
修改/etc/repmgr.conf
vi /etc/repmgr.conf


node_id=2
node_name=node2
conninfo='host=192.168.213.147 user=postgres dbname=repmgr connect_timeout=2'
data_directory='/opt/pg_root'
pg_bindir='/opt/pgsql/bin'
log_level=INFO
log_status_interval=10
log_file='/opt/repmgr510/repmgrd.log'
failover=automatic
monitoring_history=yes
monitor_interval_secs=5
reconnect_attempts=10
reconnect_interval=5
follow_command='/opt/repmgr510/repmgr standby follow --upstream-node-id=%n'
promote_command='/opt/repmgr510/repmgr standby promote'


chown postgres:postgres /etc/repmgr.conf


创建.pgpass文件
su - postgres
vi ~/.pgpass
*:*:*:postgres:123456
*:*:repmgr:repmgr:123456
chmod 600 /home/postgres/.pgpass


rm -rf /opt/pg_root/*
克隆检测
su - postgres
repmgr -h 192.168.213.146 -U repmgr -d repmgr standby clone --dry-run


克隆主节点
repmgr -h 192.168.213.146 -U repmgr -d repmgr standby clone --force


启动数据库
su - postgres
pg_ctl start


备节点加入集群
repmgr standby register


启动守护进程
repmgrd --pid-file /tmp/repmgrd.pid




查看集群状态,所有节点都可以通过repmgr查看集群状态
repmgr cluster show
repmgr service status


switchover（备库执行）
repmgr standby switchover -U repmgr --verbose 
复制