Revision as of 16:00, 24 September 2018

Installing | Database Server Installation

Operating System

I chose CentOS 7 x64 with 1 CPU, 1GB RAM, 20GB HDD, which PostgreSQL should run fine on.

Package Installations

reference here: https://www.postgresql.org/download/linux/redhat/
I installed the following initial packages, going with Postgres 9.6 b/c that was the highest supported version for Confluence.

~$ sudo yum update && sudo yum upgrade -y
~$ sudo yum install -y vim https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm 
~$ sudo yum install -y postgresql96 postgresql96-server

Initialize DB and enable autostart

~$ sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
~$ sudo systemctl enable postgresql-9.6
~$ sudo systemctl start postgresql-9.6

Verify installation

~$ sudo -u postgres /usr/pgsql-9.6/bin/psql -c "SELECT version();"

User/DB Configuration

very basic, lets create a user and db

~$ sudo -u postgres /usr/pgsql-9.6/bin/psql -c "create database confluence_db;"
~$ sudo -u postgres /usr/pgsql-9.6/bin/psql -c "create user confluence_user with encrypted password 'Password1';"
~$ sudo -u postgres /usr/pgsql-9.6/bin/psql -c " grant all privileges on database confluence_db to confluence_user;"

next let's adjust postgresql so that you can connect to it and authenticate with a password (by default it authenticates based on the current logged in linux user without a password)

~$ sudo vim /var/lib/pgsql/9.6/data/postgresql.conf
#uncomment and/or change:
listen_addresses = '*'
port = 5432
authentication_timeout = 1min

~$ sudo vim /var/lib/pgsql/9.6/data/pg_hba.conf
#change the following from ident to password authentication:
...
# IPv6 local connections:
host    all             all             ::1/128                 password
...

restart to implement changes and test connection

~$ sudo systemctl restart postgresql-9.6
~$ psql -h localhost -d confluence_db -U confluence_user -W
Password for user confluence_user:

Verify it is listening properly

~$ ss -nlt | grep 5432
LISTEN     0      128    127.0.0.1:5432                     *:*
LISTEN     0      128        ::1:5432                    :::*

Firewall Configuration

by default firewalld may have to be adjusted. Here's a quick example on how to get the correct zone and then set a rule up for the postgresql service.

~$ sudo firewall-cmd --get-active-zones
~$ sudo firewall-cmd --get-default-zone
~$ sudo firewall-cmd --zone=public --add-service=postgresql
~$ sudo firewall-cmd --zone=public --add-service=postgresql --permanent

In case you were wondering about the default services and what ports they opened, check out the configuration here:

~$ ls /usr/lib/firewalld/services/ 

Finally list your zone info

~$ sudo firewall-cmd --info-zone=public