PostgreSQL 18 update for self-hosters

[Moin]

@NateDhaliwal

Fixed with ChatGPT’s help. I’m no Linux/Postgres wizard.

Do you have a few more details? I am seeing the same

[Moin]

For those also seeing the same this is the summary (AI) of what I did:
Temporarily start PG15 → Dump the database → Use PG18 → Restore the dump

[merefield (Robert)]

So yeah this upgrade is image based.

So even if you choose to install an old version of Discourse, the image will still force an 18 upgrade?

Got it.

[NateDhaliwal]

I see you’ve fixed it, but the method ChatGPT gave, while fixing the error, it still wiped my database, so essentially no accounts, no topics or posts.

Thankfully, it was a new-ish dev forum, so not too much lost.

[Moin]

It took great care to ensure that this didn’t happen today. After each step, it told me that this wouldn’t delete anything, and I had the impression we ensured three times that all data was migrated before we could agree on deleting the stuff we no longer needed.

But if it had gone wrong, the only data I would have missed would have been the theme component configuration.

[Saiano]

Hi, I have a forum with a fairly large database (around 80 GB). For the upgrade from version 13 to 15, I switched servers, performed a fresh installation, and restored the data.
Do you still recommend this approach? (I tried the direct upgrade but encountered collation errors)

[Andrew_Rowe]

@Saiano

For the upgrade from version 13 to 15, I switched servers, performed a fresh installation, and restored the data.
Do you still recommend this approach?

earlier in the topic staff stated:

@chrisr

@pfaffman

it’s much safer than upgrading a database major version.

True, but we’re aiming for a simple transparent upgrade mechanism. There are merits both ways.

I’d say do whatever you’re comfortable with and don’t be afraid to customise the templates in discourse_docker as your needs require. Obviously the key is to test first and have a rollback plan.

So, yes it is an option

you already said:

@Saiano

I tried the direct upgrade but encountered collation errors

Collation errors? What version is the existing Discourse?

Maybe post what errors you encountered / console output

@Saiano

a fairly large database (around 80 GB)

but you didn’t run out of space (recommended 2 x existing database size) ?

[Saiano]

Hi, I have plenty of space (250 GB free). The error was “collation mismatch,” but I think I don’t have the UTF-8 strings in the app.yml file.

[chrisr (Chris Rendle-Short)]

@merefield

So even if you choose to install an old version of Discourse, the image will still force an 18 upgrade?

We try to provide sensible defaults in the discourse_docker images but it’s not possible to account for every possible use case. Feel free to customise your images to hold versions back if you prefer.

To a certain extent the dependency versions reflect our hosting requirements - we use the base image internally. That means it shouldn’t get too stale, but also means there are only so many permutations we can maintain.

@Saiano

Hi, I have a forum with a fairly large database (around 80 GB). For the upgrade from version 13 to 15, I switched servers, performed a fresh installation, and restored the data.
Do you still recommend this approach? (I tried the direct upgrade but encountered collation errors)

That’s a perfectly valid method if you’re more comfortable with it.

@Saiano

he error was “collation mismatch,”

If the warning is generated while preparing to dump the old DB then this is nothing to worry about. We are only running the server against the old data directory for the purpose of running pg_dump. When the dump is restored into the new server indexes are recreated.

The reason you’re seeing this is that in the last few days we have released a new version of the base image which upgrades from Debian Bookworm to Trixie, changing the glibc version. Locales based on the libc provider (which you were probably using) are not stable across glibc upgrades, so when the upgrade script starts a Postgres server to dump your old data it displays collation mismatch warnings.

The collation mismatch is the whole reason we are doing the dump-and-restore instead of running pg_upgrade. Once your DB is using C.UTF-8 with the builtin provider upgrades to glibc will no longer affect collations.

[Jonathan5]

@chrisr

If the warning is generated while preparing to dump the old DB then this is nothing to worry about.

I performed the upgrade tonight and it went according to plan. I got the same database collation warnings, but it sounds like we should ignore them. Thanks.

[Saiano]

I tried to add the following to app.yml

app.yml
templates:

• “templates/postgres.15.template.yml”
• “templates/redis.template.yml”
• “templates/web.template.yml”
• “templates/web.ratelimited.template.yml”

to postpone the upgrade, but I get this error

Errno::ENOENT: No such file or directory @ rb_sysopen - /etc/postgresql/15/main/postgresql.conf
Location of failure: /usr/local/lib/ruby/gems/3.4.0/gems/pups-1.4.0/lib/pups/replace_command.rb:11:in ‘IO.read’
replace failed with the params {“filename” => “/etc/postgresql/15/main/postgresql.conf”, “from” => “data_directory = ‘/var/lib/postgresql/15/main’”, “to” => “data_directory = ‘/shared/postgres_data’”}
bootstrap failed with exit code 1
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.

[pfaffman (Jay Pfaffman)]

Looks like it’s the wrong base container, I think. You did a ./launcher rebuild? That should to a git pull, but you might try running a git pull to see if that changes things.