PostgreSQL upgrading from 9.6 to 15.3 has authentication failed (PG::ConnectionBad)

After upgrading a PostgreSQL instance from version 9.6 to the currently latest version 15.3, your application cannot connect to the server, giving an authentication error even if the user/password used is still the same.

This seems to be the result of a change in the method used in the pg_hba.conf file.

Version 9.6 uses md5 but version 15.3 uses scram-sha-256

Changing this in the file pg_hba.conf file from scram-sha-256 to md5 and reloading the configuration file from inside the database with select pg_reload_conf(); , results in the application being able to connect to the database server again.

Update: It is possible to use the recommended scram-sha-256 hash, by doing the following:

  • Uncomment the line with #password_encryption = scram-sha-256 # scram-sha-256 or md5 in file /var/lib/postgresql/data/pgdata/postgresql.conf
  • reload the configuration in the database select pg_reload_conf();
  • recreate the password with the new encryption production=# alter user user_name with password 'password';

Downloading gitlab database backup from s3

You want to download a copy of your gitlab backup (ie when upgrading to version 7.0.0 as described in [here]( ).

Login to the toolbox pod and get the credentials file for s3

cat /etc/gitlab/.s3cfg

Create a new file locally (ie ~/.s3cfg_gitlab) and then download the file locally with

s3cmd get s3://gitlab-backups/database_upgrade_6_gitlab_backup.tar  20230531_gitlab_backup.tar -c ~/.s3cfg_gitlab