Deployment

Install PostgreSQL and pgAdmin on Linux CentOS 7

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.

domalab.com install postgreSQL pgAdmin CentOS 7 sudoers

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”

domalab.com install postgreSQL pgAdmin CentOS 7 yum update

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”

domalab.com install postgreSQL pgAdmin CentOS 7 yum rpm repo

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

“yum install postgresql11 postgresql11-server postgresql11-contrib”

domalab.com install postgreSQL pgAdmin CentOS 7 packages

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”

domalab.com install postgreSQL pgAdmin CentOS 7 service enable

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”

domalab.com install postgreSQL pgAdmin CentOS 7 firewall add

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.

domalab.com install postgreSQL pgAdmin CentOS 7 password

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.

domalab.com install postgreSQL pgAdmin CentOS 7 psql

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”

domalab.com install postgreSQL pgAdmin CentOS 7 configuration

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

domalab.com install postgreSQL pgAdmin CentOS 7 md5

the result should be something similar to this.

domalab.com install postgreSQL pgAdmin CentOS 7 md5 authentication

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

“systemctl restart postgresql-11.service”

domalab.com install postgreSQL pgAdmin CentOS 7 restart 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”

domalab.com install postgreSQL pgAdmin CentOS 7 config

The listening addresses and port show localhost and 5432.

domalab.com install postgreSQL pgAdmin CentOS 7 listening port

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

domalab.com install postgreSQL pgAdmin CentOS 7 port config

Again time to restart the PostgreSQL service.

domalab.com install postgreSQL pgAdmin CentOS 7 restart 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.

domalab.com install postgreSQL pgAdmin CentOS 7 epel repo

 

 

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.

domalab.com install postgreSQL pgAdmin CentOS 7 install

Let’s provide the root password to continue.

domalab.com install postgreSQL pgAdmin CentOS 7 root password

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”

domalab.com install postgreSQL pgAdmin CentOS 7 config

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’

domalab.com install postgreSQL pgAdmin CentOS 7 config path

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”

domalab.com install postgreSQL pgAdmin CentOS 7 database initialisation

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/”

domalab.com install postgreSQL pgAdmin CentOS 7 chown chcon

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

 

domalab.com install postgreSQL pgAdmin CentOS 7 pgadmin conf

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”

domalab.com install postgreSQL pgAdmin CentOS 7 apache restart

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”

domalab.com install postgreSQL pgAdmin CentOS 7 apache firewall

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.

domalab.com install postgreSQL pgAdmin CentOS 7 login

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

domalab.com install postgreSQL pgAdmin CentOS 7 GUI

About the author

Michele Domanico

Passionate about Virtualization, Storage, Data Availability and Software Defined Data Center technologies. The aim of Domalab.com is sharing with the Community the knowledge and experience gained with customers, industry leaders and like minded peers. Always open to constructive feedback and new challenges.

Add Comment

Click here to post a comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Browse articles

December 2018
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31  

Articles by Category

Archives

%d bloggers like this: