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
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
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
helpfor extra info
\l+to list databases with tablespaces
\duto 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
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:
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:
# IPv4 local connections:
Now restart PostgreSQL
systemctl restart postgresql-11.service
And check the new result on
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
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!