PostgreSQL18 主从同步复制部署备忘录

安装postgresql-18

apt install -y postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
apt update
apt install postgresql-18

主库配置

USER="replica"
PASSWORD=$(openssl rand -base64 24)
NETWOKR="0.0.0.0/0"   //从库IP段
IP="1.1.1.1" #主库IP 

#主库
cat>>/etc/postgresql/18/main/postgresql.conf<<EOF
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 1GB
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
EOF
echo "host replication $USER $NETWOKR md5" >>/etc/postgresql/18/main/pg_hba.conf
# 创建用户（幂等）
sudo -u postgres psql -tc "SELECT 1 FROM pg_roles WHERE rolname='$USER'" | grep -q 1 || \
sudo -u postgres psql -c "CREATE ROLE $USER WITH REPLICATION LOGIN PASSWORD '$PASSWORD';"
systemctl restart postgresql
echo "REPL $USER PASSWORD: $PASSWORD"

echo -e "\n\n从库快速导入备份bash shell："
echo -e "USER=$USER\nPASSWORD=$PASSWORD"
echo "sudo -u postgres pg_basebackup -h $IP  -U  $USER -D /home/postgresql/18/main -P -R -X stream -v"

IP="1.1.1.1" #主库IP
#rm -rf /var/lib/postgresql/18/main/*   
cat>>/etc/postgresql/18/main/postgresql.conf<<EOF
listen_addresses = '*'
hot_standby = on
wal_keep_size = 2GB
max_wal_senders = 10
EOF
systemctl stop postgresql
mkdir /home/postgresql
chown -R postgres:postgres /home/postgresql
chmod 700 /home/postgresql
sudo -u postgres pg_basebackup -h $IP -U $USER -D /home/postgresql/18/main -P -R -X stream -v
systemctl start postgresql

tail -n 100 /var/log/postgresql/postgresql-18-main.log
#验证主库
sudo -u postgres psql -c "SELECT client_addr,state FROM pg_stat_replication;"

#验证从库
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"