This is guide, howto install PostgreSQL 9.4 (currently 9.4.5) database server on Fedora 23/22/21/20/19, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 7.2/6.7/5.11. This guide uses PostgreSQL own YUM repos, which are always up-to-date and stable releases are available instantly.

Note: If you are upgrading PostgresSQL (from earlier version), then make sure that you backup (dump and copy) your database and configs.

1. Install PostgreSQL 9.4.5 Database Server on Fedora 23/22/21/20/19, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 7.2/6.7/5.11

1.1 Change root user


su -
## OR ##
sudo -i

1.2 Exclude Fedora, CentOS, Red Hat (RHEL) and Scientific Linux (SL) own PostgreSQL Packages

This is important step to get PostgreSQL repository working properly. Exclude PostgreSQL packages from the repository of the distro.

Fedora

Add exclude to /etc/yum.repos.d/fedora.repo file [fedora] section:


[fedora]
...
exclude=postgresql*

Add exclude to /etc/yum.repos.d/fedora-updates.repo file [updates] section:


[updates]
...
exclude=postgresql*

CentOS

Add exclude to /etc/yum.repos.d/CentOS-Base.repo file [base] and [updates] sections:


[base]
...
exclude=postgresql*

[updates]
...
exclude=postgresql*

Red Hat (RHEL)

Add exclude to /etc/yum/pluginconf.d/rhnplugin.conf file [main] section:


[main]
...
exclude=postgresql*

Scientific Linux (SL)

Add exclude to /etc/yum.repos.d/sl.repo file [sl] and [sl-security] sections:


[sl]
...
exclude=postgresql*

[sl-security]
...
exclude=postgresql*

1.3 Install PostgreSQL 9.4 Repository

Fedora


## Fedora 23 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/fedora/fedora-23-x86_64/pgdg-fedora94-9.4-4.noarch.rpm

## Fedora 22 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/fedora/fedora-22-x86_64/pgdg-fedora94-9.4-4.noarch.rpm

## Fedora 21 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/fedora/fedora-21-x86_64/pgdg-fedora94-9.4-3.noarch.rpm

## Fedora 21 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/fedora/fedora-21-x86_64/pgdg-fedora94-9.4-3.noarch.rpm

## Fedora 20 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/fedora/fedora-20-i386/pgdg-fedora94-9.4-2.noarch.rpm

## Fedora 20 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/fedora/fedora-20-x86_64/pgdg-fedora94-9.4-2.noarch.rpm

## Fedora 19 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/fedora/fedora-19-x86_64/pgdg-fedora94-9.4-1.noarch.rpm

## Fedora 19 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/fedora/fedora-19-x86_64/pgdg-fedora94-9.4-1.noarch.rpm

CentOS 7.2/6.7/5.11


## CentOS 7 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-2.noarch.rpm

## CentOS 6 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-i386/pgdg-centos94-9.4-2.noarch.rpm

## CentOS 6 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-2.noarch.rpm

## CentOS 5 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-5-i386/pgdg-centos94-9.4-2.noarch.rpm

## CentOS 5 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/pgdg-centos94-9.4-2.noarch.rpm

Red Hat (RHEL) 7.2/6.7/5.11


## Red Hat (RHEL) 7 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-i386/pgdg-redhat94-9.4-2.noarch.rpm

## Red Hat (RHEL) 7 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-redhat94-9.4-2.noarch.rpm

## Red Hat (RHEL) 6 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-i386/pgdg-redhat94-9.4-2.noarch.rpm

## Red Hat (RHEL) 6 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-2.noarch.rpm

## Red Hat (RHEL) 5 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-5-i386/pgdg-redhat94-9.4-2.noarch.rpm

## Red Hat (RHEL) 5 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/pgdg-redhat94-9.4-2.noarch.rpm

Scientific Linux (SL) 7.2/6.7/5.11



## Scientific Linux (SL) 7 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-i386/pgdg-sl94-9.4-2.noarch.rpm

## Scientific Linux (SL) 7 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-sl94-9.4-2.noarch.rpm

## Scientific Linux (SL) 6 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-i386/pgdg-sl94-9.4-2.noarch.rpm

## Scientific Linux (SL) 6 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-sl94-9.4-2.noarch.rpm

## Scientific Linux (SL) 5 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-5-i386/pgdg-sl94-9.4-2.noarch.rpm

## Scientific Linux (SL) 5 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/pgdg-sl94-9.4-2.noarch.rpm

1.4 Install PostgreSQL 9.4 with DNF/YUM


## Fedora 23/22 ##
dnf install postgresql94 postgresql94-server postgresql94-contrib

## Fedora 21/19 and Centos/RHEL/SL 7.2/6.7/5.11 ##
yum install postgresql94 postgresql94-server postgresql94-contrib

2. Configure PostgreSQL 9.4

2.1 Initialize Cluster with initdb Command

Here is multiple alternatives howto do this (like service postgresql-9.x initdb, /etc/init.d/postgresql-9.x initdb) and postgresql-setup initdb, so that’s why I use here universal PostgreSQL initdb method, which should work with Fedora 23/22/21/20/19, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 7.2/6.7/5.11:


su - postgres -c /usr/pgsql-9.4/bin/initdb

2.2 Set PostgreSQL Server to Listen Addresses and Set Port

Open /var/lib/pgsql/9.4/data/postgresql.conf file, and add/uncomment/modify following:


listen_addresses = '*'
port = 5432

If you want just localhost setup, then use following:


listen_addresses = 'localhost'
port = 5432

Or if you want use specific ip, then use following:


listen_addresses = '192.1.2.33'
port = 5432

2.3 Set PostgreSQL Permissions

Modify PostgreSQL /var/lib/pgsql/9.4/data/pg_hba.conf (host-based authentication) file:


# Local networks
host	all	all	        xx.xx.xx.xx/xx	md5
# Example
host	all	all     	10.20.4.0/24	md5
# Example 2
host	test	testuser	127.0.0.1/32	md5

You can find more examples and full guide from PostgreSQL pg_hba.conf manual.

2.4 Start PostgreSQL Server and Autostart PostgreSQL on Boot

Fedora 23/22/21/20/19 and CentOS/Red Hat (RHEL)/Scientific Linux (SL) 7.2


## Start PostgreSQL 9.4 ##
systemctl start postgresql-9.4.service

## Start PostgreSQL 9.4 on every boot ##
systemctl enable postgresql-9.4.service

CentOS/Red Hat (RHEL)/Scientific Linux (SL) 6.7/5.11


## Start PostgreSQL 9.4 ##
service postgresql-9.4 start
## OR ##
/etc/init.d/postgresql-9.4 start

## Start PostgreSQL 9.4 on every boot ##
chkconfig --levels 235 postgresql-9.4 on

2.5 Create Test Database and Create New User

Change to postgres user


su postgres

Create test database (as postgres user)


createdb test

Login test database (as postgres user)


psql test

Create New “testuser” Role with Superuser and Password


CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

Test Connection from localhost (as Normal Linux User)


psql -h localhost -U testuser test

3. Enable Remote Connections to PostgreSQL Server –> Open PostgreSQL Port (5432) on Iptables Firewall

3.1. CentOS/Red Hat (RHEL) 6.7/5.11

3.1.1 Edit /etc/sysconfig/iptables file:

nano -w /etc/sysconfig/iptables

3.1.2 Add following INPUT rule:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

3.1.3 Restart Iptables Firewall:


service iptables restart
## OR ##
/etc/init.d/iptables restart

3.2. Fedora 23/22/21/20/19 and CentOS/Red Hat (RHEL) 7.2

3.2.1 List Your Active Firewalld Zones


firewall-cmd --get-active-zones

Example output:

public
  interfaces: wlp1s0

3.2.2 Add New Rule to Firewalld

You might have active zone like public, FedoraWorkstation, FedoraServer.


firewall-cmd --permanent --zone=public --add-service=postgresql

## OR ##

firewall-cmd --permanent --zone=public --add-port=5432/tcp

3.2.3 Restart firewalld.service


systemctl restart firewalld.service

4. Test remote connection


psql -h dbserver_name_or_ip_address -U testuser -W test

Note: You have to allow remote connections on pg_hba.conf, check step 2.3 and PostgreSQL pg_hba.conf manual.