Fix replication issues for Content Store and Publishing API

[Tetrino]

Running these tools locally works fine, but if you wish to replicate the database from the AWS server, said database wouldn't build due to a Postgres version mismatch. Essentially, the Docker environment version of Postgres is too old.

docker compose -f [...] run --rm -T postgres-13 /usr/bin/pg_restore -h postgres-13 -U postgres -d content-store --no-owner --no-privileges
pg_restore: error: unsupported version (1.15) in file header                                                                                                                                ]   0% ETA 14:28:41
3.06MiB 0:00:03 [ 819KiB/s] [>                                                                                                                                                              ]   0%             
docker compose -f [...] down
[+] Running 2/2
 ✔ Container govuk-docker-postgres-13-1  Removed                                                                                                                                                          0.1s 
 ✔ Network govuk-docker_default          Removed  

This commit modifies the Docker environment for both Content Store and Publishing API so that they now use Postgres-16 rather than -13, allowing compatibility with the databases found on production.

Publishing API and Content Store have both had their envrioments tested locally and pass with flying colours with this change.

[kevindew]

This does look like quite a newer engine than we have for either in production.

Content Store is running on PostgreSQL 14 and Publishing API is still on 13 (I'm surprised cloning Publishing API is a problem given this, although I could understand Content Store)

[Tetrino]

I agree it is extremely strange, it is however the only solution I found as trying to bring it up to 14 produced the same error. 16 was seemingly added for an upcoming feature so it was the first I went to.

I will look more into it tomorrow to see if it's some kind of config issue.

[kevindew]

Yeah we in AI added Postgres 16, because our application, GOV.UK Chat, uses Postgres 16.

Hmm, it's probably worth investigating what version of pg_dump is being used to generate these dumps in the helm charts as it should be the version that matches the database version. Otherwise we'd have a bit of a weird situation if we're developing against much newer versions of the database than what the applications use.

[KludgeKML]

Just commenting because I've also been bitten by this (one of our devs couldn't replicate local links manager data locally, whereas I could - but it turned out this was because govuk-docker had cached an earlier backup which was made with an appropriate version of pg_dump)

It looks like the backup jobs are done using the toolbox image: https://github.com/alphagov/govuk-helm-charts/blob/ad4a0e0656ffd0bfea0dcad74bd29dc4fc985fc1/charts/db-backup/values.yaml#L5

...and the dockerfile for that just gets the latest version of postgres-client https://github.com/alphagov/govuk-infrastructure/blob/main/images/toolbox/Dockerfile#L22

...so presumably that's making images for the latest version, but perhaps there's some setting somewhere to make it make backwards-compatible dumps?

[Tetrino]

I'll close this for now and take a look at the dumping process to see if there is anything that can be done there. 👍

[kevindew]

Just commenting because I've also been bitten by this (one of our devs couldn't replicate local links manager data locally, whereas I could - but it turned out this was because govuk-docker had cached an earlier backup which was made with an appropriate version of pg_dump)

It looks like the backup jobs are done using the toolbox image: https://github.com/alphagov/govuk-helm-charts/blob/ad4a0e0656ffd0bfea0dcad74bd29dc4fc985fc1/charts/db-backup/values.yaml#L5

...and the dockerfile for that just gets the latest version of postgres-client https://github.com/alphagov/govuk-infrastructure/blob/main/images/toolbox/Dockerfile#L22

...so presumably that's making images for the latest version, but perhaps there's some setting somewhere to make it make backwards-compatible dumps?

Oh nice digging. Yeah that must be just using whatever Ubuntu 24 has. Hmm if there's not willingness to change that to use different versions of postgres then we might have to flag https://github.com/alphagov/govuk-docker/blob/main/bin/replicate-postgresql.sh to use postgres latest - all would feel a bit fragile.