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