Docker: upgrade from Postges 11/12 to a newer version

[net47]

Description

Since Postgres 11/12 is now deprecated, is there an official guide how to upgrade to a new Postgres version using Docker? Just changing the base image throws an error:

  db:
    image: docker.io/postgres:15-alpine
    container_name: synapse-db
    environment:
      - POSTGRES_DB=synapse
      - POSTGRES_USER=synapse
      - POSTGRES_PASSWORD=VerySecurePassword
      - POSTGRES_INITDB_ARGS=--encoding=UTF-8 --lc-collate=C --lc-ctype=C
    volumes:
      - db:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 5s
      timeout: 5s
      retries: 10

# docker logs synapse-db

PostgreSQL Database directory appears to contain a database; Skipping initialization

2025-01-15 07:09:06.003 UTC [1] FATAL:  database files are incompatible with server
2025-01-15 07:09:06.003 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 15.10.

Steps to reproduce

• change the image tag from docker.io/postgres:12-alpine to docker.io/postgres:15-alpine

Homeserver

self-hosted homeserver

Synapse Version

{"server_version":"1.121.1"}

Installation Method

Docker (matrixdotorg/synapse)

Database

Postgres, single server, not ported but running Postgres from the beginning, no backup restored

Workers

Single process

Platform

Docker on Debian 12

Configuration

no unusual configuration, just using this homeserver.yaml:

...
database:
  name: psycopg2
  args:
    user: synapse
    password: VerySecurePassword
    dbname: synapse
    host: db
    cp_min: 5
    cp_max: 10
user_directory:
    enabled: true
    search_all_users: true
...

Relevant log output

PostgreSQL Database directory appears to contain a database; Skipping initialization

2025-01-15 07:09:06.003 UTC [1] FATAL:  database files are incompatible with server
2025-01-15 07:09:06.003 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 15.10.

Anything else that would be useful to know?

No response

[marrobHD]

Easiest would be dumping the database and importing it with a newer postgres version.
This guide gives good hints: https://thomasbandt.com/postgres-docker-major-version-upgrade

[maxkratz]

I've collected all necessary steps for my instance(s) in a document file. I had to:

• Upgrade PSQL from v12 to v15.
• Set the password of the PSQL Synapse user again, since the old hash was MD5 and PSQL v15 wants scram.
• Upgrade Synapse to v1.122.0.

Hope this helps other people.

matrix-synapse-postgres-12-to-15-upgrade.md
matrix-synapse-postgres-12-to-15-upgrade.pdf

[davidangel]

Thanks @maxkratz, you're guide worked well for me!

[net47]

Thanks a lot @maxkratz for the guide, worked like a charm! The only thing I had to adapt is

Import the dump, as the “new” database instance is empty (because I wiped the data volume
with the step above): $ docker exec -i matrix-database-1 psql -U synapse
< dump.sql

-> change to ... exec -T ...

See: https://stackoverflow.com/questions/43099116/error-the-input-device-is-not-a-tty#comment111448577_43099210

[ImMALWARE]

@maxkrat, oh my god, man, I love you

[BitBernd]

@maxkratz Danke man