rocky9.7离线安装PG主从

rocky9.7离线安装PG主从

1. 离线安装PG18

下载rpm源:

# 配置下载源
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 禁用原来的PG
dnf -qy module disable postgresql
# 将PG依赖下载到本地
yum install -y --downloadonly --downloaddir=./     postgresql18-server postgresql18

安装:

yum localinstall -y --disablerepo="*"  *.rpm

启动数据库:

/usr/pgsql-18/bin/postgresql-18-setup initdb
systemctl enable --now postgresql-18
systemctl status postgresql-18

2. 主从规划

3. 主库配置

加入配置:

vi /var/lib/pgsql/18/data/postgresql.conf

listen_addresses = '*'

wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on

编辑访问控制:

vi /var/lib/pgsql/18/data/pg_hba.conf

host    all             all             192.168.1.130/24    scram-sha-256
host    replication     repl_user       192.168.1.133/24    scram-sha-256

重启主库：

systemctl restart postgresql-18

进入pg终端:

sudo -u postgres psql

# 创建复制用户
CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'StrongReplPass';
# 可选但推荐：创建物理复制槽，防止主库过早清理备库需要的 WAL。
SELECT pg_create_physical_replication_slot('pg_standby_slot');

开放防火墙:

firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --reload

4. 从库配置

systemctl stop postgresql-18
# 从库之前执行过/usr/pgsql-18/bin/postgresql-18-setup initdb, 清掉数据
rm -rf /var/lib/pgsql/18/data/*

用 pg_basebackup 从主库拉取数据：

sudo -u postgres pg_basebackup \
  -h 192.168.1.130 \
  -p 5432 \
  -U repl_user \
  -D /var/lib/pgsql/18/data \
  -P -v \
  --wal-method=stream \
  -R \
  --slot=pg_standby_slot

加入配置:

vi /var/lib/pgsql/18/data/postgresql.conf

primary_slot_name = 'pg_standby_slot'

启动从库:

systemctl enable --now postgresql-18

5. 验证主从

进入终端:

sudo -u postgres psql

postgres=# SELECT pid, usename, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;
 pid  |  usename  |  client_addr  |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn
------+-----------+---------------+-----------+-----------+-----------+-----------+------------
 8228 | repl_user | 192.168.1.133 | streaming | 0/3000168 | 0/3000168 | 0/3000168 | 0/3000168

state=streaming 代表主从复制就绪。