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