PostgreSQL 18 update for self-hosters

News and Events Aankondigingen
83

good idea, thanks Ed! i updated my post. :slight_smile:

2 likes
84

With this change, I am seeing an overall reduction in memory usage by about 4%. Well done.

2 likes
85

Thanks Lilly, did the upgrade earlier and it all seems to have gone ok :+1:

1 like
86

It’s not uncommon that rewritten tables and index are much more compact

1 like
87

Uh that’s in the noise of operating Postgres (depending if you look before or after vacuum, table rebuild etc)

89

I haven’t noticed anything missing since then, so as far as I can tell it is all good.

1 like
90

I am running Discourse in a standalone Docker container on /var/discourse.

I attempted to upgrade the embedded PostgreSQL database from version 15 to 18. The upgrade appeared to complete, and the active data directory now reports:

/shared/postgres_data/PG_VERSION
18

However, the rebuilt Discourse container still contains only PostgreSQL 15 binaries:

/usr/lib/postgresql/15/bin/postgres
postgres (PostgreSQL) 15.18

The PostgreSQL service is configured to run:

/usr/lib/postgresql/15/bin/postmaster -D /etc/postgresql/15/main

while the actual Discourse data directory is mounted at:

/shared/postgres_data

PostgreSQL consequently fails with:

FATAL: database files are incompatible with server

DETAIL: The data directory was initialized by PostgreSQL version 18,
which is not compatible with this version 15.18
(Debian 15.18-1.pgdg12+1).

I understand that recent Discourse Docker images are supposed to include PostgreSQL 18 binaries. I changed app.yml to use the PostgreSQL 18 template and rebuilt the app, but the resulting container still has PostgreSQL 15 binaries and the service script still points to /etc/postgresql/15/main.

The relevant portion of my current service configuration is:

HOME=/var/lib/postgresql USER=postgres exec thpoff \
chpst -u postgres:postgres:ssl-cert -U postgres:postgres:ssl-cert \
/usr/lib/postgresql/15/bin/postmaster -D /etc/postgresql/15/main

My questions are:

  1. What is the correct way to rebuild or update the Discourse container so that it actually contains PostgreSQL 18 binaries?

  2. Is there a particular template or image tag that should be used in app.yml?

  3. Once PostgreSQL 18 is available, what is the supported procedure for starting it against the existing /shared/postgres_data directory?

I have not deleted or reinitialised the PostgreSQL 18 data directory. I would prefer to recover the upgraded cluster rather than restore the old PostgreSQL 15 data.

1 like
91

based on the instructions in OP that appears to have been an unnecessary step unless I’m missing something?

merely rebuilding a standard install should have pulled down the latest image and set off the migration.

unless you somehow opt out of the latest image, the new binaries were surely guaranteed? most strange …

are you sure you haven’t pinned an image?

3 likes
92

Not intentionally? I’m on the regular git repository and branch and there is nothing in my app.yml that indicates to me that I have pinned anything. I only changed to the postgres 18 template to see if it helped; yes that was an unnecessary step that didn’t help, so I can change it back.

Any suggestions on how to get the new binaries? They are really not there despite how many times I rebuild:

root@hostname-app:/usr/lib/postgresql# ls -la

total 12

drwxr-xr-x 1 root root 4096 May 21 00:47 .

drwxr-xr-x 1 root root 4096 May 21 00:48 ..

drwxr-xr-x 1 root root 4096 May 21 00:47 15

root@hostname-app:/usr/lib/postgresql#
1 like
93

To save you time and hassle I would consider using your latest backup to create a new server and it will save you a lot of work.

You can literally move a server in < 30 mins, if that - so what’s the point of messing with a container build?

1 like
94

OK, I guess so! Sometimes it’s not worth messing around with fixing things. Thanks.

2 likes