Install and setup PostgreSQL on CentOS

For one of my projects I need an instance with CentOS 7.5 and PostgreSQL 9.6 but I didn’t find any working instruction on how to install and setup PostgreSQL on CentOS. So I decided to wtite this article.

Requirements & Constraints

• A machine with CentOS 7.5. I use digitalocean.com for it.
• In this article, I need exactly PostgreSQL 9.6.
• All following commands run under root user.

Setup

Optional – Install a firewall and configure it

You should think about sequirity as early as possible. For install firewalld run:

yum install firewalld

Start it and enable by systemclt:

systemctl enable firewalld
systemctl start firewalld
systemctl status firewalld

Also you can get a current state of the firewalld by following command:

firewall-cmd --state

In default case you can’t connect to CentOS PostgreSQL default port (5432) without adding rules. We will add it at the end of the article.

Install PostgreSQL

We need wget for getting RPM packet of PostgreSQL 9.6:

yum install wget

Download RPM packet from PostgreSQL:

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

Install it:

yum install pgdg-centos96-9.6-3.noarch.rpm

So we now ready to install PostgreSQL 9.6 and the contrib package

yum install postgresql96-server postgresql96-contrib

Init PostgreSQL

After installing we need to invoke a initdb:

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

Start PostgreSQL:

systemctl start postgresql-9.6

Optional – start PostgreSQL on boot

It’s simple:

systemctl enable postgresql-9.6

Change password for Linux’s postgres user

We should think about security so we have to change a password for postgres UNIX user:

passwd postgres

Change password for database’s postgres user

For change password of the database’s postgres user we should login as it and invoke ALTER ROLE (don’t forget change “newpassword” to the desired password):

su - postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

Create a new database

su - postgres
createdb awesomeapp

At this moment we already can use own new database locally but we have no access to it outside of the current machine. For expose 5432 port of the PostgreSQL to the internet, we should configure a hb_pba.conf file.

Optional – Install a uuid-ossp extension

The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms instead of an incremental ID for each record. This module can be installed for each database separately. For gettings things are done:

# Switch to posgres user
su - postgres
# Connect to awesomeapp database
psql awesomeapp
# Create uuid-ossp extension
CREATE EXTENSION "uuid-ossp";
# Exit from psql
\q

Configuring a pg_pba.conf

For allow password-based authentication you should edit hb_pba.conf:

vi /var/lib/pgsql/9.6/data/pg_hba.conf

and locate similar block:

Allow remote connections in postgresql.conf

Open postgresql.conf by the following command:

vi /var/lib/pgsql/9.6/data/postgresql.conf

and find the “Connections and authentication” block:

We need to uncomment and change listen_addresses and port to:

listen_addresses = '*'
port = 5432

After this own PostgreSQL should look like this:

Restart PostgreSQL

Execute:

systemctl restart postgresql-9.6

After restart, your database will be available to accept connections from the internet.

Optional – Add rules for the firewall

Firstly get a list of active services in the public domain

firewall-cmd --zone=public --list-services
# In my case output is:
# ssh dhcpv6-client

We can add a PostgreSQL service by the following command:

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

After restart the firewalld the postgresql service will be available on default port 5432:

systemctl restart firewalld
firewall-cmd --zone=public --list-services
# In my case output is:
# ssh dhcpv6-client postgresql

And now if I try to connect to 5432 port then my request doesn’t reject by firewalld.

If you exposed PostgreSQL database on another port (for example on 5000 port) you may add a port to public zone by the following command (instead of adding a service):

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

How to check the connection to the database

You can use pgAdmin. No magic.

Conclusion

At this point, you have working PostgreSQL on CentOS with the firewall if you no a lazy developer=))

About secure

For production environments please revise all settings of a PostgreSQL, a CentOS, and a firewall.

Useful links

• How to set up a firewall on CentOS 7
• PostgreSQL Installation for Red Hat
• Install PostgreSQL
• Configure PostgreSQL to allow remote connection
• How To Install And Use PostgreSQL on CentOS 7
• pgAdmin Homepage

Have similar projects or tasks which need to be done?

I can install PostgreSQL on CentOS for you project or company too. You can contact me with such requests because I offer service in this area. Please use the following contact form.