This article explains how to install and configure PostgreSQL for a Confluence on a CentOS 7 server.  Read the other Confluence articles on this blog to ensure your web server (if you’re using one) is configured correctly.

yum install postgresql-server postgresql-contrib

Next you’ll need to do the automatic setup:

postgresql-setup initdb

Now set the permissions for the database server:

vi /var/lib/pgsql/data/pg_hba.conf

Replace this:

host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident

With this (not very secure but is a quick start):

host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

Now enable and start the PostgreSQL server:

systemctl enable postgresql
systemctl start postgresql

Now we create the database ready for Confluence. Switch to the appropriate user and log in:

sudo -i -u postgres

Create the new database with the “utf-8” encoding as is required by Confluence. Mine is called “confluencedb”. This command is executed from Bash as the “postgresql” user.

createdb confluencedb --encoding='utf-8' --locale=en_US.utf8 --template=template0

And create a new user called “confluence”. The following will allow you to interactively create a new user. This command is executed from Bash as the “postgresql” user.

createuser --interactive

When asked to specify a username, specify “confluence”.

Grant permissions to your new user to the database. First start the “psql” CLI tool.

#psql

And then execute this command to grant all permissions:

GRANT ALL PRIVILEGES ON DATABASE confluencedb to confluence;

At this point we’re done. You can point Confluence at this new PostgreSQL database and expect it to work.

Within the Confluence installation process, the question of which database to connect to (in Production) should be “PostgreSQL” and then set the “By Connection String” field to the following. Make sure to change the database name form “confluencedb” to whatever you created earlier:

jdbc:postgresql://localhost:5432/confluencedb

Psql command tips

List all users (from within psql):

\deu+

List all databases (from within psql):

\l

Create a new user and password (from bash as postgres):

createuser -P myuser

Delete a user (from bash as postgres):

dropuser myuser