Pg_dump backup failure for remote pgsql - port & version differentials; what options exist?

When I attempt to run system backups I get: “The backup failed. Please check the logs.”
The log reports: pg_dump: [archiver (db)] connection to database "discoursedb" failed: could not connect to server: Connection refused

I believe there may be two problems at play:

  1. The remote server is running on a non-standard port.
  2. The remote PostgreSQL is running a newer PSQL version.

When I enter the app (/var/discourse/launcher enter app) and execute a manual backup, I noted that initially without the port definition, I got the exact same error:

$ pg_dump -h 123.456.789.101 -U username -W -F t discourse_db > discourse_db_backup.tar
Password:
pg_dump: [archiver (db)] connection to database "discourse_db" failed: could not connect to server: Connection refused
	Is the server running on host "123.456.789.101" and accepting
	TCP/IP connections on port 5432?

So that was easily resolved (EXCEPT I don’t know how to force Discourse to use the proper port in backups), however the next issue was a little more concerning which is we’re using a newer version of PSQL on the database server:

$ pg_dump -h 123.456.789.101 -p 45678 -U username -W -F t discourse_db > discourse_db_backup.tar
Password:
pg_dump: server version: 11.5 (Ubuntu 11.5-3.pgdg18.04+1); pg_dump version: 10.10 (Debian 10.10-1.pgdg100+1)
pg_dump: aborting because of server version mismatch

What can be done in such a situation? Is there a way to get actual system backup functional in such a case or does Discourse + the PostgreSQL database have to be backed up separately?

If the latter is the only option, what’s the PROPER way to backup at least the data? And is there a preferred cohesive mechanism available to do both at the same time without having to write a new script to do this?

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 Like

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.

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."

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 Likes

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.

Same problem happened to me. I moved the postgresql database to separate server and started getting backup error. I found the solution by deleting and reinstalling postgresql in docker on the main server.

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

Detail: Discourse yedekleme pg_dump hatası ve çözümü: pg_dump: error: server version: 12|13|14|*; pg_dump version: 12|13|14|* - Veritabanı Yönetim Sistemleri - Soru Cevap - Türkiye'nin #sorucevap sitesi!