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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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 docker-compose.yml respectively:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 |
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’ve 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:
|
1 2 3 |
ERROR: srv/username@181.55.0.2:5432 cannot do SCRAM authentication: wrong password type LOG: srv/username@181.55.0.1:53604 closing because: server login failed: wrong password type (age=0s) WARNING: srv/username@181.55.0.1: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 password_encryption to md5. This can be done by adding the command line to the service settings in the file docker-compose.yaml:
|
1 2 3 4 5 6 7 8 |
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:
|
1 2 3 |
... #line to modify host all all all md5 |
After the modification the same line:
|
1 2 |
#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 pg_hba.conf file:
|
1 2 3 4 5 6 7 8 |
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.
