Ubuntu24.4.2LTS 操作系统部署 postgresql-14 主从集群

1.环境说明

2.安装PostgreSQL 14

所有节点均需要执行

sudo apt install postgresql-14

3.主从时间同步

所有节点均需要执行

sudo apt install chrony
sudo sed -i '/pool.*ubuntu\.pool\.ntp\.org/s/^/# /' /etc/chrony/chrony.conf
sudo sed -i 's/^pool ntp\.ubuntu\.com.*$/server ntp.aliyun.com iburst/' /etc/chrony/chrony.conf
sudo systemctl restart chrony
chronyc sources

4.主节点配置

4.1主机名配置

hostnamectl set-hostname PostgreSQL-Master

4.2修改postgresql.conf配置文件

修改/etc/postgresql/14/main/postgresql.conf文件60行#listen_addresses = 'localhost’为listen_addresses = ‘*’

sudo sed -i '60s/#listen_addresses = .*/listen_addresses = '\''*'\''/' /etc/postgresql/14/main/postgresql.conf

去掉/etc/postgresql/14/main/postgresql.conf文件205行的注释 （# wal_level = replica）

sudo sed -i '205s/#wal_level = replica/wal_level = replica/' /etc/postgresql/14/main/postgresql.conf

去掉/etc/postgresql/14/main/postgresql.conf文件298行的注释 （#max_wal_senders = 10）

sudo sed -i '298s/#max_wal_senders = 10/max_wal_senders = 10/' /etc/postgresql/14/main/postgresql.conf

修改/etc/postgresql/14/main/postgresql.conf文件302行#wal_keep_size = 0为wal_keep_size = 256MB

sudo sed -i '302s/#wal_keep_size = 0/wal_keep_size = 256MB/' /etc/postgresql/14/main/postgresql.conf

修改/etc/postgresql/14/main/postgresql.conf文件245行#archive_mode = off为archive_mode = on

sudo sed -i '245s/#archive_mode = off/archive_mode = on/' /etc/postgresql/14/main/postgresql.conf

修改/etc/postgresql/14/main/postgresql.conf文件247行#archive_command = ''为archive_command = ‘cp %p /var/lib/postgresql/14/main/archive/%f’

sudo sed -i "247s|^#archive_command = '.*'|archive_command = 'cp %p /var/lib/postgresql/14/main/archive/%f'|" /etc/postgresql/14/main/postgresql.conf

4.3修改pg_hba.conf

添加允许从节点复制的访问控制规则

sudo sed -i '$a\host    replication     repl            192.168.100.0/24        md5' /etc/postgresql/14/main/pg_hba.conf

4.4创建主从复制的专用角色

sudo -u postgres psql
CREATE ROLE repl WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'PostgreSQL2025';

4.5重启PostgreSQL

sudo systemctl restart postgresql

5.从节点配置

5.1主机名配置

hostnamectl set-hostname PostgreSQL-Slave01

5.2清空并重新初始化数据库集群

systemctl stop postgresql
rm -rf /var/lib/postgresql/14/main/*

5.3使用pg_basebackup从主库拉数据

sudo -u postgres pg_basebackup -h 192.168.100.2 -D /var/lib/postgresql/14/main/ -U repl -P -R

5.4修改postgresql.conf配置文件

去掉/etc/postgresql/14/main/postgresql.conf文件325行的注释 （#hot_standby = on）

sudo sed -i '325s/#hot_standby = on/hot_standby = on/' /etc/postgresql/14/main/postgresql.conf

5.5重启PostgreSQL

sudo systemctl restart postgresql

6.验证主从同步

sudo -u postgres psql
SELECT client_addr, state, sync_state FROM pg_stat_replication;