PostgreSQL is one of the most powerful and popular open source databases that built its reputation on architecture, features and the ability pretty much to run on a wide range of operating systems. The same VMware VCSA uses PostgreSQL as the embedded database option for the vCenter appliance. The purpose of this article is to show the simple steps on how to install PostgreSQL on a Linux CentOS 7 virtual machine running on VMware vSphere. Complementary to the PostgreSQL deployment also the pgAdmin install. A powerful tool that provides full management capabilities for all PostgreSQL aspects. From Database Server level to the manipulation of all database objects. Also in this case pgAdmin is available for multiple platforms.

This article shows the process to install PostgreSQL using the command line. Whereas the pgAdmin tool will be installed using the built-in Software Manager in the GNOME Desktop. The configuration for both applications will use the command line or bash shell. A third application that will be installed and configured is Apache or httpd daemon. This one will serve the pgAdmin install and provides a web based GUI to graphically manage the PostgreSQL Server.

The idea to install PostgreSQL on Linux CentOS 7 goes together with option to explore the configurations on how to protect both the server environment and also the PostgreSQL application with its databases. Separate articles will be dedicated on how to provide protection including a file and crash consistent backup. The scenario visits the ability to include the PostgreSQL Server into a Veeam Protection Group. Effectively the protection is operated by the Veeam Agent for Linux working in “Managed Mode”. Typically, this scenario is used for both Physical Servers and Cloud Instances. In this case the homelab offers both scenarios: protecting the Linux CentOS VM within the Hypervisor or simply installing the Veeam Agent. A dedicated article will show both.

 

How to install PostgreSQL on Linux CentOS 7

For this first part to install PostgreSQL Database Server a quick and easy way is to use the excellent Package Manager from the command line. First thing to make things even easier (and run the majority of commands without logging into root account) is to edit the “sudoers” file. Essentially this file controls who and what admin commands can be run from the user context. Its not required but recommended for security reasons and also because future installs might benefit from same configuration. In a nutshell let’s open a Terminal session and issue the “su” command and provide the root password.

At this point let’s run the command

“visudo”

This special command is authorized to make changes to the sudoers file. All we need to do is to add a line for the intended user similar to the one on the screenshot:

“yourUser ALL=(ALL)   ALL”

This is the “vi” editor:

• i=enters INSERT mode
• ESC=exit current mode
• :=specify command
• :w=write changes
• :q=exit vi

Should this not be easy or intuitive to use, let;s not make any change and carry on with the rest of the process to install PostgreSQL with a root account.

Since the install will be done via the network and the Package repositories it’s a good idea to update the Package Manager itself. The operation is very quick. A few seconds.

“sudo yum update -y”

Next step is to add the internet Linux CentOS repositories for the RPMs with the latest version to install PostgreSQL. To install the x86_64 version of 11.x release the command is:

“yum install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm”

Next step is to install PostgreSQL Client, Server and additional packages for dependencies in the Contrib package:

“yum install postgresql11 postgresql11-server postgresql11-contrib”

Now that the necessary packages are installed it is possible to initialize the database, add the PostgreSQL database server to the list of automatic services to start at boot and start the service.

• Initialize database
  • “/usr/pgsql-11/bin/postgresql-11-setup initdb”
• Add PostgreSQL service to boot
  • “systemctl enable postgresql-11
• Start PostgreSQL service
  • “systemctl start postgresql-11”

By default, Linux CentOS 7 runs both a firewall and SELinux configuration. Unless these are disabled the following ports need to be open and accept inbound connections. Network Port 5432 is used by default from PostgreSQL to listen incoming requests. of course this can be changed if required. Port 80 will be used by default from Apache (which will be installed later on). As an extra I’m adding Network Port 5500 to connect to this Linux CentOS 7 Server using a remote desktop.

The command to add the desired ports is:

• “sudo firewall-cmd –permanent –add-port=5432/tcp”
• “sudo firewall-cmd –permanent –add-port=80/tcp”
• “sudo firewall-cmd –permanent –add-port=5500/tcp”

At this point the step to install PostgreSQL server are completed. To test the deployment it is possible to use the internal “psql” command utility to operate the server. By default, a Database user is created with username and password “postgres”. Ideally we can start changing the password for this user. From a Terminal session let’s run:

“sudo su – postgres”

this will output the bash prompt. Let’s enter “psql” to start the utility. This shows the PostgreSQL version installed (11.1) and the help command.

From here let’s run:

“sudo -u postgres psql postgres”

default password is postgres

“\password postgres”

At this point let’s enter a new password and confirm this again.

Within the psql context it is possible to run many commands to create, delete users, databases and a lot more. A command like:

“SELECT version ();”

shows the current version installed.

“SHOW config_file;”

shows the actual configuration

“\l”

shows a list of databases.

Now that the Server Database is installed it’s  a matter to run the configuration for first use. First thing is to change how the passwords are sent to authenticate to the database. This can be changed to use the md5. To change this we can issue the command:

“nano /var/lib/pgsql/11/data/pg_hba.conf”

In the method column all the peer and ident can be changed to md5.

the result should be something similar to this.

To make changes effective it is necessary to restart the PostgreSQL service:

“systemctl restart postgresql-11.service”

Next options to change are the allowed connections and the listening port. By default PostgreSQL accepts only local connections and it is using Network Port 5432. With the following command it is possible to change these parameters:

“nano /var/lib/pgsql/11/data/postgresql.conf”

The listening addresses and port show localhost and 5432.

These can be changed to something similar to this. Also, these lines should be commented out to make this change effective.

Again time to restart the PostgreSQL service.

All the initial configurations from the PostgreSQL server side are competed. Next steps include the installation of httpd and pgAdmin. The latter is located on the epel-release Package repository. Similarly, to what has been done before it’s a matter to the software repo from where to download and install the desired packages:

“sudo yum install epel-release -y”

followed by

“sudo yum install httpd -y”

pgAdmin will be actually installed using the graphical Software Manager.

 

 

Adding pgAdmin to the install of PostgreSQL on Linux CentOS 7

Now everything is ready to install pgAdmin and manage the PostgreSQL Database Server. From the Software Manager let’s look for “pgadmin4” and select to install the first one as per screenshot below.

Let’s provide the root password to continue.

Now pgAdmin requires a bit of configuration together with Apache httpd daemon. From this perspective the first step is to add the location paths where pgAdmin will operate the internal db, logs and storage.

“nano /usr/lib/python2.7/site-packages/pgadmin4-web/config_distro.py”

Using the editor lets add the following lines. Let’s pay attention to the current locations:

• 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’

After making sure the locations are correct next step is to create and initialize the pgAdmin internal database:

“python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py”

As a requirement it is a good idea to make sure the Apache user and Group have the ownership to the pgAdmin locations:

• chown -R apache:apache /var/lib/pgadmin4
• chown -R apache:apache /var/log/pgadmin4

In addition, if the SELinux is running (it is configured by default in Linux CentOS 7) the following is required:

• chcon -R -t httpd_sys_content_rw_t “/var/log/pgadmin4/”
• chcon -R -t httpd_sys_content_rw_t “/var/lib/pgadmin4/”

This step provides the information to create the Apache virtual directory which will host the pgAdmin site.

“nano /etc/httpd/conf.d/pgadmin4.conf”

with the following configuration

ServerName YourFQDNName

  WSGIDaemonProcess pgadmin processes=1 threads=25

  WSGIScriptAlias / /usr/lib/python2.7/site-packages/pgadmin4-web/pgAdmin4.wsgi

  <directory “=”” usr=”” lib=”” python2.7=”” site-packages=”” pgadmin4-web=””>

        WSGIProcessGroup pgadmin

        WSGIApplicationGroup %{GLOBAL}

        Require all granted

 

Apache server provides the capability to test the configuration for any syntax error:

“apachectl configtest”

If working ok it is now time to restart the Apache web server with:

“systemctl restart httpd”

Since SELinux is enabled by default an extra command line isrequired to allow pgAdmin to connect to PostgreSQL:

setsebool -P httpd_can_network_connect 1

 

Finally it is a matter of adding the rules for the firewall and restart the firewall service as root:

“firewall-cmd –permanent –add-service=http”

and

“firewall-cmd –reload”

It’s now time to test the pgAdmin connection to the PostgreSQL Database Server. From any web browser let’s point to the host where the Apache web server is running. Simply the hostname or hostname is sufficient. If everything is working this should output something simlar to this where to enter the default user postgres and the chosen password.

Success! pgAdmin can now manage the install PostgreSQL from any location using a web browser.