Based on Postgres 4.2, PostgreSQL was developed at Berkeley Computer Science Development, University of California. It was created to be a powerful open source relational database system able to run across multiple platforms such as Linux, Windows, Solaris, Mac, etc. The code is available for anyone to modify and distribute under PostgreSQL license.
PostgreSQL is not the only contender in the database industry but what makes this a superior choice is that due to high-level stability and lower management overhead, the cost of PostgreSQL ownership is much lower in the long run. PostgreSQL is also the first database platform to implement Multi-Version Concurrency Control or MVCC.
In this tutorial, we are going to learn how to install and configure PostgreSQL 11.x 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: This email address is being protected from spambots. You need JavaScript enabled to view it.
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.
