Confluence won't start when changing PostgreSQL cluster

Question

Hello Community! I'm having an issue where I've upgraded an Atlassian Confluence instance to 6.7.2, and PostgreSQL to 9.6 In order to use the new PostgreSQL version, I need to change the port that is used in the confluence.cfg.xml file. Currently it's defined as: name="hibernate.connection.url">jdbc:postgresql://localhost:5432/confluence. The new cluster version 9.6 runs on port 5433. So what I did was shutdown Confluence, changed the port from 5432 to 5433, and then start Confluence again. But it never came back up. So I checked the log file for 9.6 under /var/log/.. and it was spammed with the following: Connection matched pg_hba.conf line 92: "host all all 127.0.0.1/32 md5" 2018-04-17 12:03:18 CEST FATAL: password authentication failed for user "username" 2018-04-17 12:03:18 CEST DETAIL: Role "username" does not exist. Apparently I don't quite understand how PostgreSQL databases and clusters work. My understanding, from an 'upgrade PostgreSQL'-article, was that I should install the new version, and upgrade the old to the latest version using sudo pg_upgradecluster. How can the user not exist for the new cluster? I'm beginning to think that the 9.6 version is empty - but then again, my knowledge of how PostgreSQL works and its clusters, is very limited - at best. Ver Cluster Port Status Owner Data directory Log file 9.1 main 5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log 9.3 main 5435 down postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log 9.6 main 5433 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log 9.6 main_pristine 5434 online postgres /var/lib/postgresql/9.6/main_pristine /var/log/postgresql/postgresql-9.6-main_pristine.log Please let me know if you need more information if anything is unclear. Thanks!

AnnWorley · Answer

Thank you very much for letting us know how you resolved this.

Martin Hansen · Answer

Hi Ann,

I decided to try running the cluster upgrade again, first removing the 9.6 main cluster that was created last time.

Something must have gone wrong the last time, because the results and behavior this time was different, and all old cluster versions went in offline mode (down) when the operation completed.
Now, only 9.6 main is online, and Confluence started up without a hitch.

..Guess I should have gone through with this again in the beginning, and I wouldn't have wasted so many hours. At least I've done the upgrade now, so I know what to expect, and not, the next time :)

Thanks for your effort.

AnnWorley · Answer

Hi Martin,

My theory is we need to create a user and not a role, using a command like:

CREATE USER myaccount WITH ENCRYPTED PASSWORD 'mypassword';

This article I found mentions:

The CREATE ROLE creates user account without the LOGIN privilege.

Please test whether you can log into the database at the command line using the same parameters (username, port, password, etc) you are using for Confluence to eliminate the account as the issue.

I also wanted to share this article in case you haven't seen it. It covers some of the common "gotchas" when working with Confluence and PostgreSQL: Database Setup for PostgreSQL

Thanks,

Ann