Upgrading from PostgreSQL 13 to 14: Issues with PgBouncer, Authentication Methods

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 docker-compose.yml respectively:

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: 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:

  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 pg_hba.conf file:

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.

Oleg.Ishenko