Confluence won't start when changing PostgreSQL cluster

[Martin Hansen]

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</property>.

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]

Quick disclaimer: We don't test against database clusters and they aren't officially supported: Officially Support Clustered Database for Confluence Basically, if the cluster behaves any differently from the way Confluence expects the single node PostgreSQL to behave, Confluence may not perform as expected.

My expertise is with Confluence but whatever PostgreSQL skills I do have are at your disposal.

Perhaps if you create the role after the cluster has been configured, it will be a cluster-wide role, from the Postgres manual: 21.1. Database Roles

Database roles are global across a database cluster installation (and not per individual database).

[Martin Hansen]

Hello Ann,

Thanks for your reply. Yes, I've had a correspondence with support about it, letting me know that it's unsupported.

So I tried running the command - resulting in:

postgres=# CREATE ROLE "user";
ERROR: role "user" already exists
postgres=#

When entering postgres, I saw the following:

postgres@host:~$ psql
psql (9.6.8, server 9.1.19)
Type "help" for help.

Even though it's supposed to be a cluster-wide role, maybe I should try to run the command for server version 9.6? How would I do that?

(Maybe it's not an issue with the user after all, and that there's some other issue, which is triggering this error for some reason?)

[AnnWorley]

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

[Martin Hansen]

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]

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