PostgreSQL 因主库表空间错乱_缺少软链接、从库配置问题，导致主从复制故障【修复过程】

修复后目录结构/var/lib/pgsql/15/ ├── data/# PGDATA 主目录│ ├── pg_tblspc/ │ │ └──24610-/var/lib/pgsql/15/pg_tps# 符号链接│ ├── base/# 默认表空间数据│ ├── global/# 系统表│ └──...其他系统目录 ├── pg_tps/# 表空间 mydemotbs 的实际存储位置│ └── PG_15_202209061/ │ ├──16388/# 某个数据库的数据│ └──24847/# db_666 的数据└── archive/# WAL 归档一、前提pg_basebackup-h192.168.x.x-Urep-D/var/lib/pgsql/15/data-Fp-Xs-P-R执行以上命令从主库拉取数据搭建主从复制总是失败。从库日志报错2026-07-0720:00:46 CST[13082]:user,db,app,clientLOG: database system was interrupted;last known up at2026-07-0719:53:31 CST cp: cannotstat‘/var/lib/pgsql/15/archive/00000011.history’: No suchfileor directory2026-07-0720:00:46 CST[13082]:user,db,app,clientFATAL: recovery target timeline17does not exist二、根据日志报错先排查修复主库报错主库日志报错2026-07-0717:38:57 CST[17670]:user,db,app,clientDETAIL: The database subdirectorypg_tblspc/24610/PG_15_202209061/24847is missing.排查过程现象表空间文件 /var/lib/pgsql/15/pg_tps 和 /var/lib/pgsql/15//var/lib/pgsql/15/ 之间缺少软链接且各个表空间文件下都有重复数据后续会导致严重问题。1、停止主库systemctl stop postgresql-152、删除各表空间目录下多余文件和目录只保留数据最全的表空间文件3、根据日志报错进入数据目录创建软链接cd/var/lib/pgsql/15/dataln-s/var/lib/pgsql/15/pg_tps pg_tblspc/24610# 软链接结果$ ll data/pg_tblspc lrwxrwxrwx1postgres postgres24Jul719:3924610-/var/lib/pgsql/15/pg_tps4、重启主库验证systemctl start postgresql-15# 查看日志tail-50/var/lib/pgsql/15/data/log/*.log# 验证 db_666psql-Upostgres-c\cdb_666psql-Upostgres-cSELECT count(*)FROM pg_tables WHERE schemanamepublic三、修复从库1、从库同目录创建表空间文件mkdir-p/var/lib/pgsql/15/pg_tps2、清理从库旧数据rm-rf/var/lib/pgsql/15/data/*rm-rf/var/lib/pgsql/15/archive/*3、从主库拉取数据pg_basebackup-h192.168.x.x-Urep-D/var/lib/pgsql/15/data-Fp-Xs-P-R4、修改postgresql.conf# 设置为空restore_command5、重启从库报错systemctl start postgresql-15.service从库日志报错2026-07-0720:00:46 CST[13082]:user,db,app,clientLOG: database system was interrupted;last known up at2026-07-0719:53:31 CST cp: cannotstat‘/var/lib/pgsql/15/archive/00000011.history’: No suchfileor directory2026-07-0720:00:46 CST[13082]:user,db,app,clientFATAL: recovery target timeline17does not exist6、排查过程1. 查看主库当前的 timeline# 在从库执行# 1. 查看 postgresql.auto.conf 内容[postgrespg05 data]$catpostgresql.auto.conf l# Do not edit this file manually!# It will be overwritten by the ALTER SYSTEM command.checkpoint_completion_target0.9primary_conninfouserrep password123456 channel_bindingprefer host192.168.6.9 port5432 sslmodeprefer sslcompression0 sslsni1 ssl_min_protocol_versionTLSv1.2 gssencmodeprefer krbsrvnamepostgres target_session_attrsany# 2. .conf 里是否有 restore_commandgreprestore_commandpostgresql.confgreprecovery_target_timelinepostgresql.conf# 3. 查看 standby.signal 是否存在[postgrespg05 data]$ls-lastandby.signal -rw-r-----1postgres postgres0Jul719:54 standby.signal# 4. 查看 recovery.signalPG12 如果有的话[postgrespg05 data]$ls-larecovery.signal ls: cannot access recovery.signal: No suchfileor directory# 5. 检查 postgresql.conf 里是否有 restore_command[postgrespg05 data]$greprestore_commandpostgresql.conf restore_command# command to use to restore an archived logfile segment[postgrespg05 data]$greprecovery_target_timelinepostgresql.conf#recovery_target_timeline latest # current, latest, or timeline ID# 1. 查找所有包含 restore_command 的配置文件[postgrespg05 data]$grep-rrestore_command/var/lib/pgsql/15/data/2/dev/null postgresql.conf 中 restore_command 的实际生效值 psql-Upostgres-cSHOW restore_command;2/dev/null||echo无法连接/var/lib/pgsql/15/data/pg_rman_recovery.conf:restore_commandcp /var/lib/pgsql/15/archive/%f %p/var/lib/pgsql/15/data/postgresql.conf-bak20260706:#restore_command # command to use to restore an archived logfile segment/var/lib/pgsql/15/data/postgresql.conf-bak20251025:#restore_command # command to use to restore an archived logfile segment/var/lib/pgsql/15/data/postgresql.conf: restore_command# command to use to restore an archived logfile segment# 2. 检查是否使用了 include 指令引入了外部配置[postgrespg05 data]$grep^includepostgresql.conf includepg_rman_recovery.conf# added by pg_rman 1.3.15# 3. 查看 postgresql.conf 中 restore_command 的实际生效值[postgrespg05 data]$ psql-Upostgres-cSHOW restore_command;2/dev/null||echo无法连接结果无法连接根本原因# postgresql.conf 里有一行includepg_rman_recovery.conf# added by pg_rman 1.3.15# 而这个 pg_rman_recovery.conf 文件里设置了restore_commandcp /var/lib/pgsql/15/archive/%f %p这就是问题所在 虽然你在 postgresql.conf 里设置了 restore_command但 include 指令在后面引用了 pg_rman_recovery.conf覆盖了你前面的设置。# PostgreSQL 的配置加载顺序是postgresql.conf → include 的文件 → postgresql.auto.conf。所以 pg_rman_recovery.conf 里的 restore_command 最终生效了。7、修复步骤cd/var/lib/pgsql/15/data# 备份 postgresql.confcppostgresql.conf postgresql.conf.bak# 注释掉 include 行sed-is/^include/#include/gpostgresql.conf# 或者直接编辑vipostgresql.conf# 找到下面这行在前面加 # 注释掉# include pg_rman_recovery.conf # added by pg_rman 1.3.15# 重命名或删除 pg_rman_recovery.conf可选## 重命名让 PostgreSQL 找不到它mvpg_rman_recovery.conf pg_rman_recovery.conf.disabled# 重置 postgresql.auto.confcatpostgresql.auto.confEOF primary_conninfo userrep password123456 host192.168.6.9 port5432 sslmodeprefer recovery_target_timeline latest EOF# 删除所有 .history 文件cd/var/lib/pgsql/15/datafind.-name*.history-typef-deleterm-fpg_wal/*.history# 清空 archive 目录rm-rf/var/lib/pgsql/15/archive/*# 启动从库systemctl start postgresql-15# 查看日志,验证修复是否成功tail-100/var/lib/pgsql/15/data/log/*.log# 启动成功后应该看到类似这样的日志LOG: database system was shut down at... LOG: entering standby mode LOG: started streaming WAL from primary at... LOG: consistent recovery state reached at...补充关于 pg_rmanpg_rman 是一个 PostgreSQL 备份工具它生成的 pg_rman_recovery.conf 文件通常用于从归档恢复。但在流复制从库场景下不需要这个配置因为从库通过流复制直接从主库接收 WAL。 如果以后要用 pg_rman 做备份恢复记得恢复完成后要把 include 行重新打开。8、验证主从复制1. 检查从库# 查看从库日志tail-50/var/lib/pgsql/15/data/log/*.log# 检查是否处于恢复模式psql-Upostgres-cSELECT pg_is_in_recovery();# 应返回 t# 查看流复制状态psql-Upostgres-cSELECT * FROM pg_stat_wal_receiver;# 在主库查看复制连接psql-Upostgres-cSELECT application_name, state, sync_state, write_lag FROM pg_stat_replication;2. 主库最终确认# 主库健康检查psql-Upostgres-c\l|grepdb_666 psql-Upostgres-cSELECT version();SELECT application_name, client_addr, state, sync_state FROM pg_stat_replication;application_name|client_addr|state|sync_state ------------------------------------------------------- walreceiver|192.168.x.x|streaming|async# 检查磁盘使用情况确保没有不必要的副本du-sh/var/lib/pgsql/15/data/pg_tblspc/24610/du-sh/var/lib/pgsql/15/pg_tps/du-sh/var/lib/pgsql/15/data/pg_tps/2/dev/null# 如果存在可以删除