postgresql 主备配置_pg数据库主备配置

主备服务器信息

主：192.168.9.113  centos7.9

备：192.168.9.114  centos7.9

一、主节点配置

1.安装postgresql-14服务

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
 
# Install PostgreSQL:
sudo yum install -y postgresql14-server
 
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

2.创建用户复制的专用用户和修改postgres用户密码

su - postgres
psql
ALTER USER postgres with encrypted password 'postgres'; #修改postgres密码
create user repl login replication encrypted password 'repl'; #创建主从复制的专用用户repl

3.修改主配置文件postgresql.conf

vim $PGDATA/postgresql.conf
 
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10     # 最大发送进程，默认 10，读写分离一写多服务读，请设置为读数据库的数量
synchronous_commit = on  # 将事务提交方式改为本地提交，默认为 on，在 on 模式下事务需要等备份数据库一起提交，这里改为 local 让备份数据库不影响主库，如若是先配置读写分离，请设置为默认 on

修改主配置文件pg_hba.conf
vim $PGDATA/pg_hba.conf
 
host all all 0.0.0.0/0 scram-sha-256 #允许其他主机连接该数据库，记住防火墙也要开放5432端口才能连接，或者直接关闭防火墙
host all all 192.168.9.0/24 trust
host replication repl 192.168.9.0/24 md5  #其中repl就是创建的用户

4.重启主节点

systemctl restart postgresql-14

主节点就配置完成了

二、从节点配置

 1.安装postgresql-14服务

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
 
# Install PostgreSQL:
sudo yum install -y postgresql14-server
 
# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

2.停掉服务并删除data目录下的数据

systemctl stop postgresql-14
su - postgres
rm -rf $PGDATA/*

3.远程拉取主节点数据

pg_basebackup -h 192.168.9.113 -D $PGDATA -U repl -P -v  -R -X stream -C -S pgstandby1 

复制后从节点的配置文件会和主节点的一样，并且一并复制的还有数据库文件，里面的数据也一样，意味着主节点有什么用户数据从节点也有。

• -h –指定作为主服务器的主机。
• -D –指定数据目录。
• -U –指定连接用户。
• -P –启用进度报告。
• -v –启用详细模式。
• -R–启用恢复配置的创建：创建一个standby.signal文件，并将连接设置附加到数据目录下的postgresql.auto.conf。
• -X–用于在备份中包括所需的预写日志文件（WAL文件）。流的值表示在创建备份时流式传输WAL。
• -C –在开始备份之前，允许创建由-S选项命名的复制插槽。
• -S –指定复制插槽名称。
  

-bash-4.2$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl password=repl channel_binding=prefer host=192.168.9.113 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'pgstandby1'

备份过程完成后，会在data目录下创建了一个standby.signal，并将primary_conninfo写入postgresql.auto.conf。

如果postgresql.conf中的hot_standby参数设置为on（默认值），并且数据目录中存在Standby.signal文件，则replication slave将在“热备”模式下运行。

sudo systemctl start postgresql-14 #启动备节点

4.查看主节点复制插槽

SELECT * FROM pg_replication_slots;

主节点信息

su - postgres
-bash-4.2$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;"

从节点信息

# su - postgres
-bash-4.2$ psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"

三、主备切换

1.停止主库

su - postgres
pg_ctl stop -m fast

2.启动备库对外提供服务

su - postgres
pg_ctl promote
 
pg_controldata #查看状态为in production对外提供服务，另外standby.signal文件自动删除

3.将旧主改为新备库，重新同步

#重建standby.signal文件
su - postgres
cd $PGDATA
touch standby.signal
 
#修改postgresql.auto.conf
vim postgresql.auto.conf
 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repl password=repl channel_binding=prefer host=192.168.9.114 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'pgstandby1'
 
 
#新主库若没有配置复制槽，需要配置下
SELECT * FROM pg_create_physical_replication_slot(‘pgstandby1’);
SELECT slot_name, slot_type, active FROM pg_replication_slots;
 
#启动备库
pg_ctl start
 
pg_controldata #查看状态为in archive recovery

四、一些前辈的优化操作

vim /etc/sysctl.conf
 
net.core.wmem_default=262144
fs.file-max=76724600
vm.mmap_min_addr=65536
net.core.somaxconn=4096
net.core.wmem_max=4194304
net.core.netdev_max_backlog=10000
# 信号量，ipcs -l 或 或 -u 查看，每 16 个进程一组，每组信号量需要 17 个信号量。
# 不重新修改该值。四个参数： SEMMSL SEMMNS SEMOPM SEMMNI。SEMMSL * SEMMNI = SEMMNS (参数1 * 参数4 = 参数2)，SEMMSL = SEMOPM (参数1 = 参数3)。
# 参数1(SEMMSL): max semaphores per array, 信号量，表示每个信号集中的最大信号量数目，信号量是多线程环境下使用的一种设施,它负责协调各个线程, 以保证它们能够正确、合理的使用公共资源。信号量设置时，最小250，对于processes参数设置较大的系统建议设置为processes+10。
# 参数2(SEMMNS): max semaphores system wide, 所有信号的最大数量、即系统范围内的最大信号量总数目，SEMMSL * SEMMNI。
# 参数3(SEMOPM): max ops per semop call, 代表调用单个信号集中最大信号数量，即每个信号发生时的最大系统操作数目，大于17，一般设置至少100，或者等于SEMMSL。
# 参数4(SEMMNI): max number of arrays, 代表是组（信号集的最大值），即系统范围内的最大信号集总数目。最少128。
# kernel.sem = 4096 2147483647 2147483646 512000 官方是给的这个设置。如果不设置，系统默认值（ipcs -sl 命令可查看）为 ：250 3200 32 128。我没有使用官方的设置，也没有使用默认值，我调整了一下
kernel.sem = 4096 83886080 4096 20480
net.core.rmem_max=4194304
vm.overcommit_ratio=90
net.ipv4.tcp_tw_reuse=1
net.core.rmem_default=262144
net.ipv4.ip_local_port_range=40000 65535
#net.netfilter.nf_conntrack_max=1200000
net.ipv4.tcp_rmem=8192 87380 16777216
net.ipv4.tcp_max_syn_backlog=4096
fs.nr_open=20480000
net.ipv4.tcp_wmem=8192 87380 16777216
vm.dirty_writeback_centisecs=100
vm.dirty_ratio=95
# 所有共享内存段相加大小限制( 建议内存的 80% ，单位 page ，查看 page 大小 getconf PAGE_SIZE)
# 设置时根据自己的实际内存大小进行计算和设置，例如：page 大小 4096，8G 内存的 80% 设置为：8*1024*1024*1024/4096*80%=1677721
kernel.shmall = 1677721 #Mem*80%
# 最大单个共享内存段大小(建议为大于shared_buffer值，单位bytes。需要计算。一般建议设置为内存的50%。例如：8G内存，则参数设置为：8*1024*1024*1024*50%=4294967296。如果因为该参数导致启动失败时，可以尝试调整为75%
# kernel.shmmax = 4294967296 # 50%
kernel.shmmax = 6442450944 #Mem*75%
# 系统范围内共享内存段的最大数量（一共能生成多少共享内存段），每个PG数据库集群至少2个共享内存段，不重新修改该值
# kernel.shmmni = 819200 官方设置819200，我设置该值会报错 Invalid argument，我调整为默认值4096
kernel.shmmni = 4096 #default 4096
vm.swappiness=0
net.ipv4.tcp_mem=8388608 12582912 16777216
vm.dirty_background_bytes=409600000
#net.nf_conntrack_max=1200000
net.ipv4.tcp_max_tw_buckets=262144
fs.aio-max-nr = 1048576

注意：net.nf_conntrack_max & net.netfilter.nf_conntrack_max 这两个参数的优化如果开启的话，我会遇到报错：
sysctl: cannot stat /proc/sys/net/netfilter/nf_conntrack_max: No such file or directory
sysctl: cannot stat /proc/sys/net/nf_conntrack_max: No such file or directory
可能是 conntrack没有加载，lsmod |grep conntrack ，如果 返回 为空，表示没有加载
执行命令加载： modprobe ip_conntrack
重新执行 sysctl -p 即可。也可以忽略该错误、或者注释掉 net.nf_conntrack_max & net.netfilter.nf_conntrack_max 这两个参数的优化。

关于ip_conntrack：
ip_conntract就是linux NAT的一个跟踪连接条目的模块，用来跟踪和记录连接状态，是netfilter的一部份。ip_conntrack模块会使用一个哈希表记录 tcp 通讯协议的 established connection 记录。当这个哈希表满了的时候，便会导致 nf_conntrack: table full, dropping packet 错误。

我直接注释掉了 net.nf_conntrack_max & net.netfilter.nf_conntrack_max 的优化。
OS limits配置:

echo '
#for PostgreSQL
postgres soft core unlimited
postgres hard nproc unlimited
postgres soft nproc unlimited
postgres hard memlock unlimited
postgres hard nofile 1024000
postgres soft memlock unlimited
postgres soft nofile 1024000
' >> /etc/security/limits.conf

参考文档：

Centos7部署PostgreSQL 12集群（主备）_sunny05296的博客-CSDN博客_postgresql主备