foreword
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: 10.0.0.2
- Intranet IP from database: 10.0.0.3
master database 10.0.0.2
Install
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install -y postgresql14-server ## 主库初始化数据库/usr/pgsql-14/bin/postgresql-14-setup initdb ## 启动sudo systemctl enable postgresql-14 sudo systemctl start postgresql-14
initialization
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
configure
pg_hba.conf
vi /var/lib/pgsql/14/data/pg_hba.conf ## 添加从库网段host all all 0.0.0.0/0 trust # replication privilege. local replication all peer host replication replica 10.0.0.3/24 md5
√> Note that 10.0.0.3/24 needs to be modified to the IP segment of the slave library
postgresql.conf
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 10.0.0.3
Install
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm sudo yum install -y postgresql14-server ## 从主库同步数据pg_basebackup -D /var/lib/pgsql/14/data -h 10.0.0.2 -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 10.0.0.2 to the master library IP address to synchronize the database.
configure
postgresql.conf
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=10.0.0.2 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 ## 声明从库
permission
!> 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 10.0.0.2
ps aux |grep sender # 返回postgres: walsender replica 10.0.0.2(56192) 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 10.0.0.3
ps aux |grep receiver # 返回postgres: walreceiver streaming 0/7000148
This article is reprinted from: https://www.ioiox.com/archives/154.html
This site is for inclusion only, and the copyright belongs to the original author.