/ 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 installing the PostgreSQL database on our TomEE supported CentOS VM image. The instance of PostgreSQL is used to save the data that is generated and created from 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 CentOS 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 ‘otadmin’ user. Password used as an example: ‘admin’.

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

The documentation tells us to install PostgreSQL 11.1.0 64-bits

general_001


As a first step we need to install a dependent repository. That is the Extra Packages for Enterprise Linux repository: sudo yum -y install epel-release

Probably already done, but just to make sure!

As a second step we need to install a PostgreSQL package repository to our VM. This repository contains the installation files for PostgreSQL, but what is the URL we can use? Find that out on this site: postgresql.org

For our 9.4 version we use this URL repo-packages PG 11 and for our CentOS version cat /etc/centos-release we can use this RPM link https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

This command to give: sudo rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm


Now let’s start the installation of PostgreSQL: sudo yum install postgresql11-server -y

After this we need to initialize the database before it can be started: sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

Now see that the default service is disabled: systemctl list-unit-files | grep postgres

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

Now we are able to start PostgreSQL: systemctl start postgresql-11.service

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

1
postgres 12539  0.2  0.1 398736 16872 ?        Ss   12:51   0:00 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/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/11/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/11/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-11.service

And 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 SQL statement: ALTER USER postgres PASSWORD 'admin';

As a result, you will see ‘ALTER ROLE’ and you can quite psql with \q

Now other applications like AppWorks can create databases to 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!


And 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!