PostgreSQL 16 单虚拟机主从搭建测试（学习记录）

一、搭建环境说明本次测试基于 PostgreSQL 16 版本因只有一台虚拟机故在同一台机器上创建两个实例主库实例main、从库实例test用于学习PostgreSQL主从复制的基本搭建流程。数据库版本PostgreSQL 16Ubuntu 16.13-0ubuntu0.24.04.1主库实例main端口 5432数据目录 /var/lib/postgresql/16/main配置目录 /etc/postgresql/16/main从库实例test端口 5433数据目录 /var/lib/postgresql/16/test配置目录 /etc/postgresql/16/test操作用户postgresPostgreSQL默认系统用户二、搭建步骤完整操作记录2.1 创建从库test实例使用pg_createcluster命令创建test实例指定端口为5433避免与主库5432端口冲突操作及输出如下# 创建test实例 postgreslzy:~$ pg_createcluster 16 test -p 5433 Creating new PostgreSQL cluster 16/test ... /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/test --auth-local peer --auth-host scram-sha-256 --no-instructions The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale en_US.UTF-8. The default database encoding has accordingly been set to UTF8. The default text search configuration will be set to english. Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/16/test ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Etc/UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Warning: systemd does not know about the new cluster yet. Operations like service postgresql start will not handle it. To fix, run: sudo systemctl daemon-reload Ver Cluster Port Status Owner Data directory Log file 16 test 5433 down postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log # 查看所有实例状态 postgreslzy:~$ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log 16 test 5433 down postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log2.2 主库main配置修改修改主库的postgresql.conf和pg_hba.conf配置文件开启WAL复制相关参数允许从库连接。# 修改主库postgresql.conf配置文件 vim /etc/postgresql/16/main/postgresql.conf # 在文件末尾添加以下配置开启主从复制所需参数 listen_addresses * port 5432 wal_level replica max_wal_senders 10 max_replication_slots 10 hot_standby on wal_log_hints on # 修改主库pg_hba.conf配置文件设置访问权限 vim /etc/postgresql/16/main/pg_hba.conf # 在文件末尾添加以下配置允许本地从库连接进行复制 host replication all 127.0.0.1/32 trust2.3 重启主库使配置生效postgreslzy:~$ pg_ctlcluster 16 main restart Warning: the cluster will not be running as a systemd service. Consider using systemctl: sudo systemctl restart postgresql16-main2.4 主库创建复制专用用户登录主库创建具有REPLICATION权限的用户repl用于从库拉取主库数据进行同步。-- 登录主库默认端口5432可省略-p参数 psql -p 5432 -- 创建复制用户 CREATE ROLE repl REPLICATION LOGIN; ALTER ROLE repl PASSWORD 123456; -- 退出数据库 \q2.5 从库test配置清空从库test的数据目录使用pg_basebackup命令从主库拉取全量数据完成从库初始化配置。# 查看实例状态确认test实例处于down状态 postgreslzy:~$ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log 16 test 5433 down postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log # 清空从库数据目录确保目录为空避免数据冲突 postgreslzy:~$ rm -rf /var/lib/postgresql/16/test/* # 从主库拉取全量备份自动配置主从同步关系 postgreslzy:~$ pg_basebackup \ -h 127.0.0.1 \ -p 5432 \ -U repl \ -D /var/lib/postgresql/16/test \ -Fp -Xs -P -R Password: 38524/38524 kB (100%), 1/1 tablespace # 启动从库test postgreslzy:~$ pg_ctlcluster 16 test start Warning: the cluster will not be running as a systemd service. Consider using systemctl: sudo systemctl start postgresql16-test # 再次查看实例状态确认test实例处于online,recovery状态 postgreslzy:~$ pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log 16 test 5433 online,recovery postgres /var/lib/postgresql/16/test /var/log/postgresql/postgresql-16-test.log三、主从同步验证测试通过在主库创建表、插入数据在从库查询数据验证主从同步是否正常同时确认从库处于只读恢复状态。# 查看主库复制状态确认从库连接 postgreslzy:~$ psql -p 5432 -c SELECT * FROM pg_stat_replication; # 主库创建测试表并插入数据 postgreslzy:~$ psql -p 5432 -c CREATE TABLE test_sync(id int); INSERT INTO test_sync VALUES (100); CREATE TABLE INSERT 0 1 # 从库查询测试数据验证同步是否正常 postgreslzy:~$ psql -p 5433 -c SELECT * FROM test_sync; id ----- 100 (1 row) # 查看从库状态确认从库处于恢复模式只读 postgreslzy:~$ psql -p 5433 -c SELECT pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row)四、学习总结本次测试成功在单台虚拟机上完成了PostgreSQL 16的主从搭建核心流程为创建从库实例→配置主库复制参数→创建复制用户→从库拉取主库全量备份→启动从库→验证同步。关键要点主从实例端口需区分避免冲突主库需开启wal_levelreplica等复制参数pg_basebackup命令的-R参数可自动配置主从连接关系从库启动后处于online,recovery状态只读可正常同步主库数据。五、补充说明操作过程中出现的systemd相关警告可通过执行sudo systemctl daemon-reload更新systemd配置使系统识别新创建的test实例不影响主从同步功能。