恢复PostgreSQL数据库备份时频繁出现文件无法打开错误求助

问题重现 #

之前可正常从Render本地恢复数据库备份，现在执行恢复操作时在重建索引阶段出现大量文件找不到的错误。执行的备份与恢复命令如下：

$ PGPASSWORD="###" pg_dump -h ###.render.com -O -U app_production app_production >dump.sql
$ psql -U app_production -f dump.sql app_production

错误示例：

(...)
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ERROR:  could not open file "base/16388/18035": No such file or directory
CONTEXT:  writing block 0 of relation base/16388/18035
parallel worker
ERROR:  could not open file "base/16388/18035": No such file or directory
CONTEXT:  writing block 1 of relation base/16388/18035
(...)

已尝试重装Homebrew、PostgreSQL，更换不同备份文件，问题仍间歇性出现（有时少量错误，有时大量错误），系统为macOS Ventura。

可能的原因与解决方案 #

1. 并行索引构建权限/资源问题 #

PostgreSQL默认启用并行索引构建，macOS Ventura下并行worker进程可能因权限或资源限制无法写入数据文件。解决方法：

• 恢复时临时禁用并行执行：
  在执行psql -f dump.sql前，先连接数据库执行：

  psql -U app_production app_production -c "SET max_parallel_workers_per_gather = 0; SET max_parallel_workers = 0;"
  

  再执行恢复命令：

  psql -U app_production -f dump.sql app_production
  

2. 本地与远程PostgreSQL版本不兼容 #

Render上的PostgreSQL版本与本地安装的版本差异可能导致文件格式不兼容。解决方法：

• 查看Render远程数据库版本：

  PGPASSWORD="###" psql -h ###.render.com -U app_production app_production -c "SELECT version();"
  

• 安装对应版本的PostgreSQL（以14版本为例）：

  brew install postgresql@14
  brew services stop postgresql
  brew services start postgresql@14
  brew link --force postgresql@14
  

3. 本地PostgreSQL数据目录权限损坏 #

Homebrew安装的PostgreSQL数据目录权限可能因系统更新或操作失误被修改。解决方法：

• 停止PostgreSQL服务：

  brew services stop postgresql
  

• 检查并修复数据目录权限（默认路径为/usr/local/var/postgres）：

  chown -R $(whoami) /usr/local/var/postgres
  chmod -R 700 /usr/local/var/postgres
  

• 重启服务后重新尝试恢复：

  brew services start postgresql
  

4. 使用pg_restore替代psql导入纯文本备份 #

纯文本SQL备份在大数据库恢复时容易出现问题，使用自定义格式备份+pg_restore更稳定：

• 重新生成自定义格式备份：

  PGPASSWORD="###" pg_dump -h ###.render.com -O -U app_production -Fc app_production >dump.dump
  

• 使用pg_restore恢复：

  pg_restore -U app_production -d app_production dump.dump
  

5. macOS Ventura隐私/系统限制 #

Ventura的系统完整性保护（SIP）或隐私设置可能阻止PostgreSQL写入默认数据目录。解决方法：

• 创建自定义数据目录：

  mkdir ~/postgres-data
  

• 初始化新的数据库集群：

  initdb -D ~/postgres-data
  

• 修改postgresql.conf配置文件，设置data_directory为新路径：

  data_directory = '/Users/你的用户名/postgres-data'
  

• 重启PostgreSQL服务后重新尝试恢复。

内容的提问来源于stack exchange，提问作者svoop