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 |
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 |
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 |
[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" |
yum -y groupinstall "PostgreSQL Database Server 9.6 PGDG"
and initialize it with
/usr/pgsql-9.6/bin/postgresql96-setup initdb |
/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 |
systemctl start postgresql-9.6.service
systemctl enable postgresql-9.6.service
Switch to the postgres user with
and connect to the server (currently running only on localhost)
Check the installed version with
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)
.... |
...
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) |
#------------------------------------------------------------------------------
# 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) |
#------------------------------------------------------------------------------
# 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 |
host all all YOUR_CLIENT_IP_ADDRESS/32 md5
save the file and restart service with
systemctl restart postgresql-9.6.service |
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 |
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