How to Install PostgreSQL and pgAdmin4 on CentOS 7
PostgreSQL or Postgres is a powerful high-performance object-relational database management system (ORDBMS) released under a flexible BSD-style license. PostgreSQL is well suited for large databases and has many advanced features.
pgAdmin4 is an open-source PostgreSQL management tool designed for multiple PostgreSQL database versions. pgAdmin4 has been created with all of the features that can be found on the PostgreSQL server. Written in python and jquery and can be installed on Windows, Mac, and Linux. It provides multiple deployment models, can be installed as a desktop application or a server application running behind the webserver such as Apache2.
In this tutorial, we will show you how to install the latest PostgreSQL database and the pgAdmin4 on CentOS 7 Server. We will install the PostgreSQL On CentOS 7 Server and then configure the password for default 'postgres' user. After that, we will install and configure the pgAdmin4 PostgreSQL management tool.
What we will do:
- Install Dependencies
- Install PostgreSQL 11 on CentOS 7
- Configure PostgreSQL User and Authentication
- Install pgAdmin4 on CentOS 7
- Configure pgAdmin4
- Setup SELinux for PostgreSQL and pgAdmin4
- Setup Firewalld
- Testing
Step 1 - Install Dependencies
Firstly, we're going to upgrade all packages to the latest version and install some packages that are required for this tutorial.
Now upgrade all packages to the latest version and add the EPEL repository to the CentOS 7 system.
Run the yum command below.
sudo yum update
sudo yum -y install epel-release
After that, install some packages that are required for this tutorial, including the SELinux policy management, firewalld, vim editor, and the net-tools.
sudo yum -y install policycoreutils-python firewalld vim net-tools
Now all packages needed has been installed to the CentOS 7 system.
Step 2 - Install PostgreSQL
In this step, we're going to install the PostgreSQL from the official repository. to do that, we need to add the PostgreSQL repository for CentOS 7 to our system.
Add the PostgreSQL repository for CentOS 7 system using the following command.
sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Now check all available on the system.
sudo yum repolist
And you will get the PostgreSQL repository has been added to the CentOS 7 system.
Next, we will install the latest PostgreSQL version '11' and initialize the PostgreSQL database setup.
Install the PostgreSQL 11 using the yum command below.
sudo yum -y install postgresql11 postgresql11-server postgresql11-contrib
Once all installation is complete, initialize the PostgreSQL database using the following command utility that provides by the PostgreSQL itself.
/usr/pgsql-11/bin/postgresql-11-setup initdb
And now you're able to start the PostgreSQL service. Start the PostgreSQL service and add it to the system boot.
systemctl start postgresql-11
systemctl enable postgresql-11
And the PostgreSQL 11 service is up and running on CentOS 7 system.
Step 3 - Setup PostgreSQL User and Authentication
After installing the PostgreSQL 11 on the CentOS 7 system, we're going to configure the password for the 'postgres' user and change the default 'ident' authentication method to 'md5'.
To set up the password for the 'postgres' user, you will need to login to the 'postgres' user and access the PostgreSQL command-line interface 'psql'.
su - postgres
psql
Change the password for the 'postgres' user using the following query.
\password postgres
TYPE YOUR PASSWORD
Once it's complete, type 'exit' to logout from the 'psql' interface and the 'postgres' user.
Next, we're going to change the authentication method for all internal access to 'md5'.
Go to the '/var/lib/pgsql/11/data/' directory and edit the configuration file 'pg_hba.conf' using vim editor.
cd /var/lib/pgsql/11/data/
vim pg_hba.conf
Change the authentication line for host '127.0.0.1/32', from the 'ident' to 'md5'.
host all all 127.0.0.1/32 md5
Save and close.
Now restart the PostgreSQL service.
systemctl restart postgresql-11
And you will be able to connect to the database using the password that you just set up.
Test the configuration using the psql command as below.
psql -h 127.0.0.1 -U postgres
Password:
Type the password that you just created and make sure you can log in to the PostgreSQL command-line interface with that password.
And the PostgreSQL 11 installation and configuration on CentOS 7 system has been completed.
Step 4 - Install pgAdmin4
In this step, we're going to install the 'pgAdmin4' to the CentOS 7 system.
Install the pgAdmin4 package using yum command below.
sudo yum -y install pgadmin4
By installing the pgAdmin4 package, it will automatically install packages dependencies for itself, including the httpd web server and the 'pgAdmin-web'.
Once all installation is complete, start the httpd service and add it to startup boot time.
systemctl start httpd
systemctl enable httpd
Now the pgAdmin4 is running with the default configuration under the httpd webserver.
Step 5 - Configure pgAdmin4
In this step, we're going to configure the pgAdmin4 on the CentOS 7 system. We're going to define the data/storage and log directory for the pgAdmin4 and then set up the user admin for the pgAdmin4.
Before going any further, go to the '/etc/httpd/conf.d' directory and rename the sample of pgAdmin4 configuration to 'pgadmin4.conf'.
cd /etc/httpd/conf.d/
mv pgadmin4.conf.sample pgadmin4.conf
Now create a new data and log directory for the pgAdmin4.
mkdir -p /var/log/pgadmin4/
mkdir -p /var/lib/pgadmin4/{sessions,storage}
The data directory '/var/lib/pgadmin4/' will be used to store all pgAdmin4 configuration, and all pgAdmin4 logs will be available at the '/var/log/pgadmin4' directory.
After that, we need to define the data and log directory to the pgAdmin4 configuration. Edit the pgAdmin4 config file 'config.py' using vim editor as below.
vim /usr/lib/python2.7/site-packages/pgadmin4-web/config.py
Paste the following configuration to the bottom line.
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'
Save and close.
Next, create a new admin user for the pgAdmin4 using the pgAdmin setup script as below.
python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py
Type your email address and password, and you will get the result as below.
As a result, the pgAdmin admin user has been created.
Now we need to restart the httpd service.
systemctl restart httpd
And change the owner of the pgAdmin4 data and log directory to user 'apache'.
chown -R apache:apache /var/lib/pgadmin4/
chown -R apache:apache /var/log/pgadmin4/
And the configuration of pgAdmin4 has been completed.
Step 6 - Configure SELinux for pgAdmin4
If you're installing the pgAdmin4 on the CentOS 7 server with the SELinux 'enforcing' mode, then you must do this section.
Check the status of SELinux using the command below.
sestatus
Below is the result of the SELinux with 'Enforcing' mode.
Firstly, we must allow the httpd service to connect to the network. Allow the pgAdmin4 under the httpd web server to access the PostgreSQL server on the local IP address '127.0.0.1' with port '5432'.
Run the setsebool command below.
setsebool -P httpd_can_network_connect 1
After that, we need to add context and label of all pgAdmin4 files on the data and log directory to the 'httpd_sys_rw_content_t'.
sudo semanage fcontext -a -t httpd_sys_rw_content_t "/var/lib/pgadmin4(/.*)?"
sudo semanage fcontext -a -t httpd_sys_rw_content_t "/var/log/pgadmin4(/.*)?"
Then restore the SELinux context of files on both data and log directory.
sudo restorecon -R /var/lib/pgadmin4/
sudo restorecon -R /var/log/pgadmin4/
And the SELinux configuration for the pgAdmin4 has been completed.
Step 7 - Setup Firewalld
In this step, we will add HTTP and HTTPS services to the firewalld configuration.
Use the command below to add the HTTP and HTTPS services to the firewalld rules.
firewall-cmd --add-service=http --permanent
firewall-cmd --add-service=https --permanent
Now reload the firewall rules.
firewall-cmd --reload
And the firewalld configuration has been completed, and the pgAdmin4 will be accessible through the httpd webserver.
Step 8 - Testing
Open your web browser and type the server IP address and the URL path '/pgadmin4' on the address bar.
http://10.5.5.15/pgadmin4
And you will get the pgAdmin4 login page.
Log in with your email address and the password that initialized during the installation, then click the 'Login' button.
Once logged in to the pgAdmin4, you will get the dashboard as below.
After that, we must add our PostgreSQL server to the pgAdmin4 management tool.
On the pgAdmin4 dashboard, click the 'Add New Server' button.
On the 'General' tab, type the new server's name. Type details about your PostgreSQL server on the 'Connection' tab, including Host/IP Address, Username, and Password.
Now click the 'Save' button.
Once the PostgreSQL server is added, you will get the details about your PostgreSQL server on the left menu.
As a result, the installation and configuration of PostgreSQL and pgAdmin4 on CentOS 7 Server has been completed successfully.
