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.