Install PostgreSQL 9.4 on CentOS 7 Server

Add exclude

exclude=postgresql*

 to CentOS default repository file in [base] and [updates] sections by:

nano /etc/yum.repos.d/CentOS-Base.repo

Install the latest stable version of PostgreSQL 9.4 for CentOS 7 64bit. All available repositories can be found in http://yum.postgresql.org/.

rpm -ivh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-2.noarch.rpm

Install PostgreSQL 9.4 server from YUM repository installed in previous step. YUM will automatically resolve and install dependencies also.

yum install postgresql94-server -y

Need to initialize the PostgreSQL 9.4 database before we can successfully start it:

/usr/pgsql-9.4/bin/postgresql94-setup initdb

Response will be 

Initializing database ... OK.

We can see the PostgreSQL 9.4 service with the following command and also see that it is not enabled to start at boot:

systemctl list-unit-files |grep postgres

The response will be 

postgresql-9.4.service disabled

Enable the PostgreSQL 9.4 service to start at boot:

systemctl enable postgresql-9.4.service

ln -s '/usr/lib/systemd/system/postgresql-9.4.service' '/etc/systemd/system/multi-user.target.wants/postgresql-9.4.service'

..and start the PostgreSQL 9.4 service as follows:

systemctl start postgresql-9.4.service

Check whether the database is running by viewing the process list:

ps auxf |grep postgres

The response will be like

root 3091 0.0 0.1 112640 980 pts/0 S+ 11:41 0:00 _ grep --color=auto postgres
postgres 3042 0.0 1.9 337452 15200 ? S 11:40 0:00 /usr/pgsql-9.4/bin/postgres -D  /var/lib/pgsql/9.4/data
postgres 3043 0.0 0.1 192600 1368 ? Ss 11:40 0:00 _ postgres: logger process
postgres 3045 0.0 0.2 337452 1680 ? Ss 11:40 0:00 _ postgres: checkpointer process
postgres 3046 0.0 0.3 337452 2480 ? Ss 11:40 0:00 _ postgres: writer process
postgres 3047 0.0 0.2 337452 1688 ? Ss 11:40 0:00 _ postgres: wal writer process
postgres 3048 0.0 0.3 337864 2608 ? Ss 11:40 0:00 _ postgres: autovacuum launcher process
postgres 3049 0.0 0.2 192728 1744 ? Ss 11:40 0:00 _ postgres: stats collector process

Connect to the database as follows:

su - postgres

The response will be like: 

Last login: Sun Feb 28 11:41:11 2016 on pts/0
-bash-4.2$

psql

The response will be:

psql (9.4.4)
Type "help" for help.
postgres=#

The default postgres user has its password disabled by default. We can set one using psql like this:

postgres=# \password postgres

Enter new password:
Enter it again:

postgres=# \q
exit

The configuration files for PostgreSQL on CentOS 7 are located in:/var/lib/pgsql/9.4/data/

These files contains helpful comments regarding the configuration options available.

By default, PostgreSQL is operating through a socket on the localhost. In that configuration, the installation is secured against remote threats. If you do not need to access the database from a remote host, you can leave the default configuration. However, we often need to access a database from a remote host.


Configuring access is handled by editing a couple files. First we will tell PostgreSQL to start listening on our network interfaces. This is done by making a change in /var/lib/pgsql/9.4/data/postgresql.conf. Find this section:

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)


and change it by uncommenting the listen_addresses line and changing localhost to *. Like this:

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                            # (change requires restart)

We will leave the port set to its default value of "5432". Make sure and save the change. Now we will edit /var/lib/pgsql/9.4/data/pg_hba.conf and tell PostgreSQL that we want to accept connections from a specific IP address or range. Find this section in the file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
and add a new host line with a specific IP address or range:

host    all             all             <Client IP address>/32        md5

Now we can restart PostgreSQL:

sudo systemctl restart postgresql-9.4.service

and verify that we are now listening on port 5432:

# ss -l -n |grep 5432

u_str  LISTEN     0      128    /var/run/postgresql/.s.PGSQL.5432 7728992                 * 0
u_str  LISTEN     0      128    /tmp/.s.PGSQL.5432 7728994                 * 0
tcp    LISTEN     0      128                    *:5432                  *:*
tcp    LISTEN     0      128                   :::5432                 :::*

We will also add a local firewall rule to allow the incoming connection on port 5432. For example:

firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --permanent --add-port=80/tcp

firewall-cmd --reload

Give ownership of data folder to postgres user by 


chown -R postgres.postgres /var/lib/pgsql/9.4/data/