PostgreSql 14 master-slave load balancing deployment record


A few days ago, I deployed PostgreSQL 主从负载均衡for an environment. Here仅仅简单记录一下命令for subsequent use. As for more configuration of the database, please configure it according to your needs.

√> This article is an original article by Stille . It has been practiced, tested, and published. If you need to reprint, please contact the author for authorization, and indicate the reprint address.

version environment

  • Server system: CentOS 7.9
  • PostgreSQL version: 14
  • Main database intranet IP:
  • Intranet IP from database:

master database


 sudo yum install -y sudo yum install -y postgresql14-server ## 主库初始化数据库/usr/pgsql-14/bin/postgresql-14-setup initdb ## 启动sudo systemctl enable postgresql-14 sudo systemctl start postgresql-14


 su - postgres psql ## 创建postgres 密码ALTER USER postgres WITH PASSWORD '123456'; # #创建从库replica 用户密码CREATE ROLE replica login replication encrypted password 'replica'; ## 检查账号SELECT usename from pg_user; # 结果如下# usename # ---------- # postgres # replica # (2 rows) # 查看权限SELECT rolname from pg_roles; # 结果如下# rolname # ---------- # postgres # replica # (2 rows) # 退出\q exit



 vi /var/lib/pgsql/14/data/pg_hba.conf ## 添加从库网段host all all trust # replication privilege. local replication all peer host replication replica md5

√> Note that needs to be modified to the IP segment of the slave library


 vi /var/lib/pgsql/14/data/postgresql.conf listen_addresses = '*' wal_level = hot_standby synchronous_commit = remote_write # synchronous_commit 参考文档可选其他on max_wal_senders = 32 #同步最大的进程数量wal_sender_timeout = 60s #流复制主机发送数据的超时时间max_connections = 100 #最大连接数,从库的max_connections必须要大于主库的

From database


 sudo yum install -y sudo yum install -y postgresql14-server ## 从主库同步数据pg_basebackup -D /var/lib/pgsql/14/data -h -p 5432 -U replica -X stream -P

!> Note that the slave library does not need the initialization steps in the main library installation process above. Modify the above to the master library IP address to synchronize the database.



i> The recovery.conf file has been removed since PostgreSQL 12, and the related configuration has been merged into postgresql.conf. Since the database is synchronized from the main database, the configuration also needs to be removed and modified.

 vi /var/lib/pgsql/14/data/postgresql.conf ## 移除或注释wal_level wal_level = xxx ## 修改或添加以下primary_conninfo = 'host= port=5432 user=replica password=replica' recovery_target_timeline = 'latest'

create standby.signal

i> Create the standby.signal file and declare the slave library.

 vi /var/lib/pgsql/14/data/standby.signal standby_mode = on ## 声明从库


!> Here are a few times I stepped on the pit.

 chown -R postgres.postgres /var/lib/pgsql/14/data

start up

 sudo systemctl enable postgresql-14 sudo systemctl start postgresql-14

test sync

master database

 ps aux |grep sender # 返回postgres: walsender replica streaming 0/7000148 su - postgres psql select application_name, state, sync_priority, sync_state from pg_stat_replication; # 返回async select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication; # 返回async

From database

 ps aux |grep receiver # 返回postgres: walreceiver streaming 0/7000148

This article is reprinted from:
This site is for inclusion only, and the copyright belongs to the original author.

Leave a Comment

Your email address will not be published.