Backups fail with Postgres 16 database (and all PG>13)

Support
1

Backups no longer work for a postgres 16 database due to this commit that installs postgresql-client-${PG_MAJOR} rather than postgresql-client

Is this solving a problem? Wasn’t the OS-provided psql client working just fine with all versions of postgres?

I was surprised that this client was using PG16 on a Digital Ocean database, but I thought I knew that some CDCK hosting was using PG15 now.

Is there some better way to get a working backup than something like this:

run:
  - exec:
      cd: /var/www/discourse
      cmd: 
        - apt-get update && apt-get remove -y postgresql-client-13 && apt-get install -y postgresql-client-16

Maybe I can set PG_MAJOR in an ENV variable?

1 Like
2

Yep, that seems to be the case.

From the PR:

I discovered this bug when a Discourse backup was taken using pg_dump versioned 17.2 which cannot be restored onto postgres clusters < 17.2.

Seems like you’re between a rock and a hard place here (PostgreSQL: Documentation: 17: pg_dump)

Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump’s version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 9.2 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump’s output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server.

3

Oops. And I thought I read the PR. Given that I am a native English speaker with a PhD, you’d think I could do better. :person_shrugging:

No. I can’t, since this is what builds the base image.

So, it looks like my fix is about as good as it gets, though if I were more clever I’d expunge the apt stuff that I pull in with the apt-get update.

I imagine that some others will have this problem, as it’s often hard to convince various humans and systems that you want PG13 rather than something more recent. And it seem like it was a pretty long time ago that someone who knows said that Discourse was working with PG15.

Thanks, Richard!

1 Like
4

Do the backups fail silently??

(I see my ordinary installation is using version 13, so I take it that this situation is a bit special, although perhaps not terribly rare.)

5

They do not. So it’s pretty obvious, and should happen only to people who know enough to manage their own postgres.

1 Like
6

Hi all, I ran into this issue today. The symptom was we’d been getting alerts for 6 days or so that backups were failing, and the key log lines appeared to be:

[2025-06-14 03:30:20] pg_dump: error: aborting because of server version mismatch
[2025-06-14 03:30:20] pg_dump: detail: server version: 16.9; pg_dump version: 15.12 (Debian 15.12-1.pgdg120+1)

I run Discourse on ubuntu running on a digital ocean droplet, using the recommended install guide. But I use Digital Ocean’s Managed Postgres database rather than a postgres container. Looking at my database, it’s running pg 16. I don’t think they upgraded it recently (and I wouldn’t expect a major version upgrade to be automatic anyway), but I’ve emailed their support to double check.

Anyway, my research led me to this page. I wasn’t sure where to put the YAML that @pfaffman posted, so I ran the commands manually, and thought I’d share for anyone else stumbling upon this:

  • cd /var/discourse
  • launcher enter app
  • apt list --installed | grep postgres # to confirm that the version currently installed is 15
  • apt-get update
  • apt-get remove postgresql-client-15
  • apt-get install postgresql-client-16

I then triggered a manual backup on the admin backups page.

This seems to have temporarily fixed the problem, but as @pfaffman has pointed out, I expect that the next time I upgrade discourse, it will revert back to postgresql-client-15, and backups will stop working again, requiring the above manual intervention.

Is there any solution to this issue apart from having to repeat these steps every time I upgrade discourse?

7

I think somewhere here i provide the stuff to put those commands on your app.yml.

You can look at the postgres templates for examples, maybe.

8

Thanks, but I’m not sure I follow. I’m under the impression that the YAML you mentioned in the original post above was simply a way to reduce the commands from the handful I posted above to a single one, but that you still had to manually run it each time you upgrade discourse. Have I misinterpreted?