Direct JDBC for PostgreSQL and Confluence in Docker

[Christian E_]

I have pulled the docker image for Confluence and want to connect it to my PostgreSQL database.

I have followed this instruction for creating the user and database in PostgreSQL:

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04

and from Atlassian:

https://confluence.atlassian.com/doc/database-setup-for-postgresql-173244522.html

But during the installation process of confluence there is an error:

Configuring database failed
org.postgresql.util.PSQLException: Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

In the setup field I just left the standard input:

DriverClassName: org.postgresql.Driver

DatabaseURL: jdbc:postgresql://localhost:5432/confluence

Username and Password

I have installed apt-get install libpostgresql-jdbc-java and got libpostgresql-jdbc-java (9.2-1002-1)

However, I can not fix this error. Could someone please give me a hint what else I could figure out.

ps -f -u postgres

ps -f -u postgres
UID        PID  PPID  C STIME TTY          TIME CMD
postgres  3718     1  0 14:09 ?        00:00:00 /usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c config_file=/etc/postg
postgres  3720  3718  0 14:09 ?        00:00:00 postgres: checkpointer process   
postgres  3721  3718  0 14:09 ?        00:00:00 postgres: writer process   
postgres  3722  3718  0 14:09 ?        00:00:00 postgres: wal writer process   
postgres  3723  3718  0 14:09 ?        00:00:00 postgres: autovacuum launcher process   
postgres  3724  3718  0 14:09 ?        00:00:00 postgres: stats collector process  

sudo netstat -ltnp | grep postgres

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      3718/postgres   
tcp6       0      0 ::1:5432                :::*                    LISTEN      3718/postgres   

sudo lsof -n -u postgres |grep LISTEN

postgres 3718 postgres    6u  IPv4              23608      0t0     TCP 127.0.0.1:postgresql (LISTEN)
postgres 3718 postgres    7u  IPv6              23609      0t0     TCP [::1]:postgresql (LISTEN)

[OzDeaDMeaT]

Hi mate,

Try the PostgresDB Servers Docker IP address. I just tried it on mine and it connected instantly. Also, you need to create the database, I didnt see anything in your documents about you creating a database. To do that in docker postgres you need to run this command from your docker machine.

docker exec -it postgres psql -U postgres

First create a role

CREATE ROLE confluenceuser WITH LOGIN; 
\password confluenceuser

then enter the password you want to use twice.

To check the user account created ok run this

SELECT rolname FROM pg_roles;

Then you can create your database etc.

CREATE DATABASE confluencedb
OWNER = confluenceuser
TEMPLATE = template0
ENCODING = 'UTF8'

;

Then grant all privs to the db

GRANT ALL PRIVILEGES ON database confluencedb TO confluenceuser;

This took me about 4 hours to work out. I am running a vanilla VMware Photon VM. Hope this helps everyone.

P.S.

Here are the commands I used to create the Containers

//Creation of confluencenet (this is the network that the two containers reside on)

docker network create confluencenet

//POSTGRES

docker run -v postgres-data:/var/lib/postgresql/data \
--name postgres -d \
--network confluencenet \
postgres:9.6

//CONFLUENCE

docker run -v confluence-data:/var/atlassian/application-data/confluence \
--name="Confluence" \
--network confluencenet \
-d -p 8090:8090 -p 8091:8091 \
atlassian/confluence-server:latest

[Aleksandar Dzeletovic]

Thank you so much!

To add to this setup, you can set postgres host value in Confluece DB setup page to postgres instead of 172.18.0.2 since you named it with --name postgres.

It's because Confluence docker container can reach postgres docker container by it's name since they're on the same network.

That way it will continue working if your postgres container ever gets an new IP.

[Nic Brough -Adaptavist-]

Postgres is definitely running, but with those settings, can you just confirm that it is on the same machine as Confluence?

If it is, then the error message you are seeing is good - it says the right drivers are in place (although your apt-get is of zero use - it installs the drivers somewhere else.  You'll have picked up the default ones distributed with Confluence).  It is now simply that it is being refused a connection, so your user/password is wrong, or it's not being allowed in because of the settings in the database.

[Christian E_]

Thank you for your fast reply. Yes, everything is on the same server. Confluence is running in Docker.

However, it is probably easier if I just run the installer of Confluence since I have read that Docker has some limitations in terms of using OpenJDK. Probably this is causing the issue?

https://hub.docker.com/r/atlassian/confluence-server/

[Nic Brough -Adaptavist-]

Confluence does not run properly on OpenJDK - use the java that comes with the Confluence installation.

That doesn't affect the database drivers though.  It still sounds like the credentials are wrong, or the user has no db access.