I had a set of services built with Docker Compose. In this set, I had a PostgreSQL service coupled with a PgBouncer instance that pooled requests from the other services of my application to the database. The PostgreSQL was at version 13 and I wanted to upgrade to the next version, at the time of this writing, 14.
The Docker Compose service settings initially looked like this:
db: image: postgres:13-alpine volumes: - pg_data:/var/lib/postgresql/data/ env_file: - ./.env networks: - app-network pgb: image: edoburu/pgbouncer:1.15.0 depends_on: - db networks: - app-network env_file: - ./.env ...
The next version tag for the
db service was
14-alpine. So I changed the
db: image: postgres:14-alpine volumes: - pg_data:/var/lib/postgresql/data/ env_file: - ./.env networks: - app-network ...
Once the new configuration is applied and the services are re-built and restarted I get the following error message:
FATAL: database files are incompatible with server DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14
The solution was to remove the database volume
pg_data, re-build the service, and restore the database backup.
The next problem, I encountered was the incompatibility of the authentication method. The PgBouncer service I was running used
md5 and I had these errors from the pgb service:
ERROR: email@example.com:5432 cannot do SCRAM authentication: wrong password type LOG: firstname.lastname@example.org:53604 closing because: server login failed: wrong password type (age=0s) WARNING: email@example.com:53604 pooler error: server login failed: wrong password type
My solution was to change the authentication method on the PostgreSQL side, in my service db. To do that I needed to set the server setting
md5. This can be done by adding the
command line to the service settings in the file
db: image: postgres:14-alpine volumes: - pg_data:/var/lib/postgresql/data/ env_file: - ./.env command: postgres -c password_encryption=md5
Next, I needed the db service to use a custom file
pg_hba.conf. In the project root, I created a copy of the original
pg_hba.conf and modified the last line to use trust as METHOD. Originally the line looked like this:
... #line to modify host all all all md5
After the modification the same line:
#line to modify host all all all trust
Finally, I mount the custom file as a volume to the service and tell PostgreSQL to use it instead of the default
db: image: postgres:14-alpine volumes: - pg_data:/var/lib/postgresql/data/ - ./compose/local/db/:/var/lib/postgresql/custom/ env_file: - ./.env command: postgres -c password_encryption=md5 -c hba_file=/var/lib/postgresql/custom/pg_hba.conf
The command section is in one line, with no line breaks.
I recreated the db container and the PgBouncer pooling service was now able to connect to the database.