How To Install PostgreSQL 11 on CentOS 7
Step 1: Update CentOS
It is always best to start with an updated operating system. If you have not done so, use
the following command to update CentOS and reboot:
$ yum update -y
$ reboot
Step 2: Configure Yum Repo
Add the PostgreSQL repository in CentOS 7.
$ rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-
latest.noarch.rpm
Visit PostgreSQL Repositories for additional package information on various operating
systems.
Step 3: Install PostgreSQL Server and Client
Use the following command to install PostgreSQL:
$ yum install postgresql11-server postgresql11 -y
Step 4: Initialize Database
After installation is complete, we need to initialize the PostgreSQL database, which will
create a data directory to store database and few configuration files:
$ /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK
Depending on the storage system used or allocated resources, the initialization may
take some time. The default directory where PostgreSQL will store data
is /var/lib/pgsql/11/data.
Step 5: Enable and Start PostgreSQL Service
Enable the service so the PostgreSQL starts automatically during server reboot:
$ systemctl enable postgresql-11.service
$ systemctl start postgresql-11.service
Step 6: Configure Firewall
If the firewall is configured on CentOS, we need to open ports so remote users can
connect to PostgreSQL:
$ firewall-cmd --add-service=postgresql --permanent
$ firewall-cmd --reload
Step 7: Enable Remote Access
To allow PostgreSQL to accept remote connections, first, we need to change the listen
to address to * in the configuration file:
$ nano /var/lib/pgsql/11/data/postgresql.conf
listen_address = '*'
Also, we need to let PostgreSQL know to accept remote connections:
$ nano /var/lib/pgsql/11/data/pg_hba.conf
# Accept from anywhere
host all all 0.0.0.0/0 md5
Restart service after making configuration changes:
$ systemctl restart postgresql-11.service
Step 8: Set PostgreSQL Admin Password
Set admin user and password for PostgreSQL:
$ su - postgres
postgresql=$ psql -c "alter user postgres with password 'password'"
ALTER ROLE
postgresql=$
Step 9: Create A Demo User and Database
Login to PostgreSQL console to create a user and database for testing:
postgresql=$ createuser demo_usr
postgresql=$ createdb demo_db -O demo_usr
postgresql=$ grant all privileges on database demo_db to demo_usr
Login as a demo user to test:
$ psql -U demo_usr -h localhost -d demo_db
To modify the password for a database with a particular user:
$ psql -c "ALTER USER postgres WITH PASSWORD 'yourPassword'" -d template1
Step 10: pgAdmin Tool
pgAdmin is a management and development tool for PostgreSQL server. It provides a
graphical user interface to administer and a development platform all through a user-
friendly interface.
Step 10.1: Installing pgAdmin
Install pgAdmin through repository already added for PostgreSQL:
$ yum install pgadmin4 -y
Package pgadmin4-web and web server httpd are also automatically installed as
needed dependencies.
Step 10.2: Enable pgAdmin Apache Configuration
A sample configuration file for pgAdmin is installed during installation. We need to
rename or copy it as following:
$ cp /etc/httpd/conf.d/pgadmin4.conf.sample /etc/httpd/conf.d/pgadmin4.conf
Step 10.3: Configure VirtualHost
Edit the Apache configuration file to add a VirtualHost as appears below:
<VirtualHost *:80>
ServerName pgadmin.example.com
LoadModule wsgi_module modules/mod_wsgi.so
WSGIDaemonProcess pgadmin processes=1 threads=25
WSGIScriptAlias /pgadmin4 /usr/lib/python2.7/site-packages/pgadmin4-
web/pgAdmin4.wsgi
<Directory /usr/lib/python2.7/site-packages/pgadmin4-web>
WSGIProcessGroup pgadmin
WSGIApplicationGroup %{GLOBAL}
<IfModule mod_authz_core.c>
# Apache 2.4
Require all granted
</IfModule>
<IfModule !mod_authz_core.c>
# Apache 2.2
Order Deny,Allow
Deny from All
Allow from 127.0.0.1
Allow from ::1
</IfModule>
</Directory>
</VirtualHost>
Step 10.4: Configure Data Directories
Create data directories to be used by pgAdmin and mention the directories in the
configuration file:
$ mkdir -p /var/lib/pgadmin4/ /var/log/pgadmin4/
$ nano /usr/lib/python2.7/site-packages/pgadmin4-web/config_distro.py
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'
SQLITE_PATH = '/var/lib/pgadmin4/pgadmin4.db'
SESSION_DB_PATH = '/var/lib/pgadmin4/sessions'
STORAGE_DIR = '/var/lib/pgadmin4/storage'
Step 10.5: Create pgAdmin Configuration Database
Run the python script to create a database to store pgAdmin configurations:
$ python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py
When prompted, fill in the Email address and Password. This email address and
password will be used as login credential to access the pgAdmin web interface:
Email address: admin@domain.com
Password: <password>
Retype password: <password>
pgAdmin 4 - Application Initialisation
======================================
Step 10.6: Directories Permission
Set permissions for pgAdmin directories so Apache user can access:
$ chown -R apache:apache /var/lib/pgadmin4 /var/log/pgadmin4
Step 10.7: Configure SELinux
Ignore this step if SELinux is disabled. But if it is enabled in Enforcing mode, create the
following policies to allow the web server to access pgAdmin interface:
$ semanage fcontext -a -t httpd_sys_rw_content_t "/var/lib/pgadmin4(/.*)?"
$ semanage fcontext -a -t httpd_sys_rw_content_t "/var/log/pgadmin4(/.*)?"
$ restorecon -R /var/lib/pgadmin4/
$ restorecon -R /var/log/pgadmin4/
Enable and start httpd service:
$ systemctl enable httpd
$ systemctl start httpd
Step 10.8: Configure Firewall
Open web service ports so that pgAdmin interface can be accessed:
$ firewall-cmd --permanent --add-service=http
$ firewall-cmd --reload
The pgAdmin web interface can now be accessed from
https://pgadmin.domain.com/pgadmin4. Use the credentials created in Step 10.5 to
access the pgAdmin web interface.
If not successful, then try thus method:
Service httpd stop
Service postgresql stop
Setsebool -P httpd_can_network_connect 1
Service httpd start
Service postgresql start
