good idea, thanks Ed! i updated my post. 
2 Likes
With this change, I am seeing an overall reduction in memory usage by about 4%. Well done.
2 Likes
Thanks Lilly, did the upgrade earlier and it all seems to have gone ok 
1 Like
It’s not uncommon that rewritten tables and index are much more compact
1 Like
Uh that’s in the noise of operating Postgres (depending if you look before or after vacuum, table rebuild etc)
I haven’t noticed anything missing since then, so as far as I can tell it is all good.
1 Like
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:
-
What is the correct way to rebuild or update the Discourse container so that it actually contains PostgreSQL 18 binaries?
-
Is there a particular template or image tag that should be used in
app.yml? -
Once PostgreSQL 18 is available, what is the supported procedure for starting it against the existing
/shared/postgres_datadirectory?
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
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
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
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
OK, I guess so! Sometimes it’s not worth messing around with fixing things. Thanks.
2 Likes
Where you can’t access the admin panel because your discourse has decided to lose its mind, how do you grab the db via root@ to upload to a new instance? (maybe another guide)
Or what’s the command line to execute the same thing as if doing via admin panel?
Ok, and more to the point, need to rebuild the app to sort a certificate error that seems to just have happened (no changes pre-empted the apparent failure) - that port 443 accessible error is popping up (wizard indicates: “DNS verification failed”) - dunno if CF or DO changed something but again nothing was changed on my end - well I can’t rebuild due to maybe these 2 errors, including the one below, unlike last time which was a not enough space error, any idea how to get past this one below?
To add, I also got this exact same error before and after increasing primary disk size (accounting for postgres 15 > 18 needs)
FAILED
--------------------
Pups::ExecError: if [ -f /root/install_postgres ]; then
/root/install_postgres && rm -f /root/install_postgres
elif [ -e /shared/postgres_run/.s.PGSQL.5432 ]; then
socat /dev/null UNIX-CONNECT:/shared/postgres_run/.s.PGSQL.5432 || exit 0 && echo postgres already running stop container ; exit 1
fi
failed with return #<Process::Status: pid 17 exit 1>
Location of failure: /usr/local/lib/ruby/gems/3.4.0/gems/pups-1.4.0/lib/pups/exec_command.rb:138:in 'Pups::ExecCommand#spawn'
exec failed with the params {"tag" => "db", "cmd" => "if [ -f /root/install_postgres ]; then\n /root/install_postgres && rm -f /root/install_postgres\nelif [ -e /shared/postgres_run/.s.PGSQL.5432 ]; then\n socat /dev/null UNIX-CONNECT:/shared/postgres_run/.s.PGSQL.5432 || exit 0 && echo postgres already running stop container ; exit 1\nfi\n"}
Ah, ok I found what I think I need posted by @Lilly way back in this topic
cd /var/discourse
./launcher enter web_only
discourse backup
exit
From this I assume I can output a useable db backup that i can upload to a fresh discourse instance and bypass all the problems.
Will that command give a full backup, i.e. posts + uploads/images?
I’d recommend a database-only backup and using rsync to move over everything else. Move a Discourse site to another VPS with rsync. You can also rsync over the ssl certs so you can get the new site up before you change DNS.
Here’s how I do it: Changing Digital Ocean droplets with doctl (resize, floating ip, rename, etc) - Howto - Literate Computing Dashboard
EDIT: Sorry. I can’t tell if you’ve solved your problem or what. If I’m reading correctly , the easiest thing to do is switch back to the default, versionless template and rebuild again.
2 Likes
Did you rebuild the data (probably app in your case) container twice, as required?
(Btw the update message says to rebuild app, but I guess it meant “the database container which might be called app or data” or does it mean both?)
and a some feedback, a while back I tried to go from 13 to 15 but it failed multiple times and I remained with the .13. Template. Now that .18. was offered, I tried it again and it worked with no issues (I just had to cleanup the _new and _older directories before). So now I can run with the default unversioned data template again. So good job on making the update so robust
1 Like
I just noticed in the error log, before the PG update, I seem to have some SQL related erros in the logs:
Job exception: PG::UndefinedFunction: ERROR: function array_remove(bigint[], integer) does not exist LINE 1: ...ATE "access_control_lists" SET allowed_user_ids = array_remo...
Which is strange, it looks like this function is from PG9, and I was running 15 before. Could this be a permission or search path problem? It seems it does not occur after the DB update, but maybe its a batch timing thing… I see the function used in quite some places and even migration scripts.
Maybe that means some functions fail on older PostgreSQL despite their templates are stilll listed. MAybe I missed it, but I thought the older versions are compatible.
1 Like