Pg_dump 远程 PostgreSQL 备份失败 - 端口与版本差异：有哪些可用选项？

支持 安装
1

当我尝试运行系统备份时，出现以下错误：“备份失败。请检查日志。”
日志显示：pg_dump: [archiver (db)] 连接到数据库 "discoursedb" 失败：无法连接到服务器：连接被拒绝

我认为可能存在两个问题：

  1. 远程服务器运行在非标准端口上。
  2. 远程 PostgreSQL 运行的是较新版本的 PSQL。

当我进入应用（/var/discourse/launcher enter app）并执行手动备份时，我注意到最初在没有指定端口的情况下，我得到了完全相同的错误：

$ pg_dump -h 123.456.789.101 -U username -W -F t discourse_db > discourse_db_backup.tar  
密码：  
pg_dump: [archiver (db)] 连接到数据库 "discourse_db" 失败：无法连接到服务器：连接被拒绝  
\t服务器是否在主机 "123.456.789.101" 上运行，并接受 TCP/IP 连接于端口 5432？

这个问题很容易解决（除了我不知道如何强制 Discourse 在备份时使用正确的端口），但接下来的问题更令人担忧：我们在数据库服务器上使用了较新版本的 PSQL：

$ pg_dump -h 123.456.789.101 -p 45678 -U username -W -F t discourse_db > discourse_db_backup.tar  
密码：  
pg_dump: 服务器版本：11.5 (Ubuntu 11.5-3.pgdg18.04+1)；pg_dump 版本：10.10 (Debian 10.10-1.pgdg100+1)  
pg_dump: 因服务器版本不匹配而中止

在这种情况下可以采取什么措施？是否有可能使实际系统备份在这种情况下正常工作，还是必须分别备份 Discourse 和 PostgreSQL 数据库？

如果后者是唯一的选择，那么备份数据的正确方法是什么？是否有一种首选的协调机制可以同时完成这两项任务，而无需编写新的脚本来实现？

2

I did find some discussion in another post regarding someone who has a slightly comparable situation. The big difference in my case is that we store files on the local server vs S3. I could forego backing up PostgreSQL since that is backed up independently, however I do still need to back up:

  • local content and
  • Discourse settings

I still would like a consolidated backup with the db + content + settings all in one place, but I’m guessing you don’t/won’t support that and thus I’d like to at least get content + settings into a consolidated package.

1 个赞
3

Postgres 11 isn’t supported. You can look elsewhere for how to restore between versions, but it’ll be some work to get discourse to work with pg11.

4

Interesting and odd. I had read somewhere that 11 was alright, but aside from that I have a system already deployed to 11 and have not seen any errors or problems (aside from backup) thus far… Now you have me worried…

Oh, here we go, according to this post PostgreSQL 11 "should just work."

5

Yes. I’ve got two systems deployed on pg11 too. They are working fine except I’m doing backups directly. I upgraded pg to 11 in the container. They’ll make backups but not restore them.

4 个赞
6

The Discourse backup system should simply warn and not fail if there is a PostgreSQL version mismatch. I just tried to make a backup myself and because I too am using an external PG server, no tarball was created at all.

8

我也遇到了同样的问题。我将 PostgreSQL 数据库移到了单独的服务器，然后开始收到备份错误。我通过删除主服务器上的 PostgreSQL 并重新安装来找到解决方案。

cd /var/discourse
./launcher enter app
apt-get remove postgresql-client-common
apt-get update
sudo apt-get install postgresql

详情：Discourse yedekleme pg_dump hatası ve çözümü: pg_dump: error: server version: 12|13|14|15|*; pg_dump version: 12|13|14|15|* - Veritabanı Yönetim Sistemleri - Soru Cevap