PostgreSQL on CentOS 7 – Howto

In this post I’ll try to show you how to install PostgreSQL 9.6 on CentOS 7.3 which is the current versions of PostgreSQL and CentOS.

The default PostgreSQL version on┬áCentOS 7.3 is PostgreSQL 9.2 which is still maintained, but in case you’re more for a “cutting edge” technology, try to follow the next steps

First, remove the already installed version (in case you installed the default version)

yum remove postgresql-server postgresql-contrib

Install official PostgreSQL Yum repo with

yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

which will create a new repo file /etc/yum.repos.d/pgdg-96-centos.repo with the next content

[pgdg96]
name=PostgreSQL 9.6 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
 
[pgdg96-source]
name=PostgreSQL 9.6 $releasever - $basearch - Source
failovermethod=priority
baseurl=https://download.postgresql.org/pub/repos/yum/srpms/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
 
[pgdg96-updates-testing]
name=PostgreSQL 9.6 $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/testing/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96
 
[pgdg96-source-updates-testing]
name=PostgreSQL 9.6 $releasever - $basearch - Source
failovermethod=priority
baseurl=https://download.postgresql.org/pub/repos/yum/srpms/testing/9.6/redhat/rhel-$releasever-$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-96

Now install PostgreSQL with

yum -y groupinstall "PostgreSQL Database Server 9.6 PGDG"

and initialize it with

/usr/pgsql-9.6/bin/postgresql96-setup initdb

Start and enable service with

systemctl start postgresql-9.6.service
systemctl enable postgresql-9.6.service

Switch to the postgres user with

su postgres -

and connect to the server (currently running only on localhost)

psql

Check the installed version with

SELECT version();

You should get something like

...
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)
....

The default PostgreSQL installation listens only on localhost so if you want to connect from the remote host you’ll need to change a few things.

Open /var/lib/pgsql/9.6/data/postgresql.conf and find the line #listen_addresses = ‘localhost’… and replace it with the listen_addresses = ‘*’

Before

#------------------------------------------------------------------------------
# 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)
max_connections = 100                   # (change requires restart)

after

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

Now open /var/lib/pgsql/9.6/data/pg_hba.conf and add at the end

host    all             all             YOUR_CLIENT_IP_ADDRESS/32            md5

save the file and restart service with

systemctl restart postgresql-9.6.service

The last step before you test it is to change the password for postgres user with

su postgres -
 
bash-4.2$ psql
psql (9.6.1)
Type "help" for help.
 
postgres=# \password
Enter new password:
Enter it again:
postgres=# \q
exit

Now add new server in pgAdmin and test it

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.