PostgreSQL 10（八）主从流复制

#安装配置
mkdir -p /data/postgres/v10.10
groupadd postgres && useradd -m -d /data/postgres/home -g postgres postgres
mkdir -p /data/pgbackup/{6401,6402}/archive
cd /data/postgres/home
wget https://ftp.postgresql.org/pub/source/v10.10/postgresql-10.10.tar.gz
tar -zxvf postgresql-10.10.tar.gz -C /data/postgres/v10.10 --strip-components 1
cd /data/postgres/v10.10
./configure --prefix=/data/postgres/v10.10
make && make install
chown -R postgres:postgres /data/postgres

su postgres
echo 'export PATH=$PATH:/data/postgres/v10.10/bin' >> ~/.bashrc
source ~/.bashrc

# Master 初始化数据
initdb -D /data/pg6401

# Master 参数配置
mv /data/pg6401/postgresql.conf /data/pg6401/postgresql.conf.bak

cat << EOF > /data/pg6401/postgresql.conf
port = 6401
listen_addresses = '*'
cluster_name = 'kkcluster'
hot_standby = on
max_wal_senders = 10
wal_keep_segments = 64
wal_level = replica
archive_mode = on
archive_timeout = 86400
archive_command = 'test ! -f /data/pgbackup/6401/archive/%f && cp %p /data/pgbackup/6401/archive/%f'
EOF

# Master 创建同步配置配置文件
cat << EOF > /data/pg6401/recovery.done
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'application_name=slave port=6402 user=replica password=replica sslmode=disable sslcompression=1 target_session_attrs=any'
restore_command = 'cp /data/pgbackup/6401/archive/%f %p'
archive_cleanup_command = '/data/postgres/v10.10/bin/pg_archivecleanup /data/pgbackup/6401/archive %r'
EOF

# Master 配置访问规则
echo "host    all             all             0.0.0.0/0               md5" >> /data/pg6401/pg_hba.conf

# Master 启动服务
pg_ctl -D /data/pg6401 -l /data/pg6401/logfile.log start

# Master 创建复制账号
psql -p 6401 -c "CREATE ROLE replica login replication encrypted PASSWORD 'replica';"

# Standby 备份 Master 初始化数据
mkdir -p /data/pg6402 && chown postgres:postgres /data/pg6402
pg_basebackup -F p -X stream -v -P -R -D /data/pg6402 -p 6401 -U replica -W
chmod -R 700 /data/pg6402

# Standby 更改相关参数
mv -f /data/pg6402/recovery.done /data/pg6402/recovery.conf
sed -i "s/6401/6402/g;s/port=6402/port=6401/g" /data/pg6402/recovery.conf
sed -i "s/6401/6402/g" /data/pg6402/postgresql.conf

# Standby 启动服务
pg_ctl -D /data/pg6402 -l /data/pg6402/logfile.log start

# Master && Standby 检查是否同步
psql -p 6401 -c "select * from pg_stat_replication;"
psql -p 6402 -c "select * from pg_stat_replication;"

# Master && Standby 发送/接收进程
ps -aef | grep -E 'sender|receiver'

# Master && Standby 集群状态
pg_controldata -D /data/pg6401 | grep -E 'cluster|checkpoint'
pg_controldata -D /data/pg6402 | grep -E 'cluster|checkpoint'

# 主备切换
psql -p 6401 -c "checkpoint;"
psql -p 6401 -c "select pg_switch_wal();"
psql -p 6401 -c "checkpoint;"
pg_ctl -D /data/pg6401 -l /data/pg6401/logfile.log stop
pg_ctl -D /data/pg6402 promote
mv -f /data/pg6401/recovery.done /data/pg6401/recovery.conf
pg_ctl -D /data/pg6401 -l /data/pg6401/logfile.log start

# 再次切换
psql -p 6402 -c "checkpoint;"
psql -p 6402 -c "select pg_switch_wal();"
psql -p 6402 -c "checkpoint;"
pg_ctl -D /data/pg6402 -l /data/pg6402/logfile.log stop
pg_ctl -D /data/pg6401 promote
mv -f /data/pg6402/recovery.done /data/pg6402/recovery.conf
pg_ctl -D /data/pg6402 -l /data/pg6402/logfile.log start