/ Installation  

Your way to a 'DONE' PostgreSQL installation

Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

This guide will help you to install the PostgreSQL database on our TomEE supported RHEL VM image. The instance of PostgreSQL saves data; generated and created by the AppWorks platform.

This post is part of the series for ‘AppWorks installation in 10 great steps’.

This is the list of ingredients we are going to use:

  • The already installed ‘Oracle Virtual Box’ software from the previous post
  • Our TomEE supported RHEL VM image from the previous post
  • PuTTY (SSH client). Downloaded from: putty.org

Let get right into it…

Start the Oracle VM VirtualBox tooling and start-up our image. Leave the image as is and make a connection with PuTTY. Login with the created ‘sysadmin’ user. Password used as an example: ‘admin’.

Once connected, we can start off with the installation, but what is the required version? This can be found in the ‘AppWorks Platform Supported Environments’ documentation that can be found on the support site from OpenText.

The documentation tells me we can use install PostgreSQL 13.0.X

general_001

As a first step, we need to install a dependent repository. That is the Extra Packages for Enterprise Linux repository: 

1
2
subscription-manager repos --enable codeready-builder-for-rhel-8-$(arch)-rpms
sudo dnf install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm

Probably already done, but just to make sure!

I follow this tutorial where this is my list of commands:

1
2
3
4
5
sudo yum module list | grep postgresql
#Versio 10 is the default, so we select our version 13
sudo yum install -y @postgresql:13
#This command should return version 13.16 (in my case!)
psql -version

After this we need to initialize the database before it can be started: sudo postgresql-setup --initdb

Get yourself informed about the locations: whereis postgresql-setup and whereis psql

Now check the disabled (by default) service: systemctl list-unit-files | grep postgres

We’ll make it enabled, so it will start PostgreSQL on startup: systemctl enable postgresql.service

Now we are able to start PostgreSQL: systemctl start postgresql

Check if the service is running fine: ps aux | grep pgsql

1
postgres    2880  0.5  0.2 287452 25412 ?        Ss   11:24   0:00 /usr/bin/postmaster -D /var/lib/pgsql/data

To login to PostgreSQL give this command: sudo -u postgres psql

Quick psql-reference

  • Type help for extra info
  • \q to quit
  • \l+ to list databases with tablespaces
  • \du to list accounts
  • \? for more help

When you exit psql, you will sometimes get back in an interactive bash session. This can be exited with the command ‘exit’.

Make sure PostgreSQL listens to all TCP requests and not only the local (= 127.0.0.1) ones. You can check this by: sudo netstat -ltnp | grep 5432

1
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      12512/postgres

We need to edit the postgresql.conf file for this, but where to find it?

sudo find / -name 'postgresql.conf'

Then you can edit it like this: sudo vi /var/lib/pgsql/data/postgresql.conf

Go the ‘Connections and authentication’ section and see the commented #listen_addresses = 'localhost'

Update this line, so it looks like this:

1
2
3
4
5
6
7
8
9
10
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

Also, edit this file: sudo vi /var/lib/pgsql/data/pg_hba.conf

Go the bottom and add this line host all all 0.0.0.0/0 trust so it looks like this:

1
2
3
# IPv4 local connections:
host    all             all             0.0.0.0/0               trust
host    all             all             127.0.0.1/32            ident

Now restart PostgreSQL systemctl restart postgresql

Check the new result on 0.0.0.0

Last step is to give the default created ‘postgres’ user a password as this is required for the installation of AppWorks.

Login to PostgreSQL: sudo -u postgres psql

Give the following command within psql: \password and provide a new password admin (in my case!). You can quite psql with \q.

Now other applications like AppWorks can create databases from the installation wizard!

In the pg_hba.conf you can also use md5 authentication. The password should then also be crypted in the ALTER statement with the ENCRYPTED keyword!

Finally, for a smooth ‘optionally’ PostgreSQL “Xperience” we also install additional modules via: sudo yum -y install postgresql-contrib

Trust me…They will benefit you in the future!

That’s all we need to do to give it a ‘DONE’. This installment of AppWorks tips makes the PostgreSQL database available for serving the data connection so AppWorks can save its data to a nice and steady service.

Nothing else to add here…Let me know your thoughts and if stuff is working the correct way as it is described. Have a good day and see you next time with a new installment of AppWorks tips!