PostgreSQL on IBM i

In this article I will show you how we can install and use the very popular PostgreSQL database on our IBM i.

PostgreSQL is an open source relational database management system, a DBMS developed by a worldwide team of developers.  It has been around since 1986 and now available on the IBM i.

Install

To install this database, we use either ACS, Open-Source Package Management, or yum.

As we can see in the figure below, I chose the yum option, in a shell window, to install.

yum install postgresql12-server postgresql12-contrib

Look out for the completion message.

And that’s how easy it is to install this popular open source database.

Setup

Now we have the product installed, we need to create a user profile to perform the setup.

For ease, I created a user called POSTGRES, with QSECOFR authority to accomplish this and additionally specified a home directory of /HOME/POSTGRES.

CRTUSRPRF USRPRF(POSTGRES)
USRCLS(*SECOFR)
TEXT(‘Profile for POSTGRESQL database’)
HOMEDIR(‘/home/postgres’)

Next we have to create the home directory and ensure the directories owner is correct. 

Use the following commands, in a shell window, to achieve this.

mkdir /home/postgres
chown postgres /home/postgres

In a shell window, sign on using the POSTGRES profile and run the following commands.

export PGDATA=/home/postgres

export PGDATA=/home/postgres
export PATH=$PATH:/QOpenSys/pkgs/bin/

this will setup our path for all open source packages and let Postgres know where its databases, and environment, are stored.

Warning – Do not place any files in the POSTGRES home directory, it will cause you grief!

Next we have to initialise the configuration, using this command.

initdb -E UTF-8 -D /home/postgres -W -A scram-sha-256

During the configuration, you will have to provide a POSTGRES profile.  This is internal for POSTGRES, and nothing to do with the POSTGRES user profile created earlier in this article.

The figure below shows the database initialisation.

Starting the Server

Once the setup has completed, it instructs us to start the server.  Use the command below to achieve this.

pg_ctl -D /home/postgres -l logfile start

All completed!  Our Postgres database server is now up and running.

The Postgres server runs on port 5432.  This can be checked by viewing the logfile in the /home/postgres directory on our IFS.

Alternatively, we can use the NETSTAT *CNN command from a 5250 session to check port 5432 is accepting connections or check the jobs are running in QUSRWRK subsystem.

Submitting to Batch

If you wish to submit the start server job instead of running it, you can use the submit job command if you wish.

SBMJOB CMD(QSH CMD(‘/QOpenSys/pkgs/bin/pg_ctl -D /home/postgres -l logfile start’))
JOB(STR_POSTGR)

Ensure you run this command using the POSTGRES user profile.

Stopping the Server

For completeness, we can use the command below to stop the server.

pg_ctl -D /home/postgres -l logfile stop

Using Postgress

We use the psql command to enter SQL statements into Postgres.

In the figure below, we can see the following SQL statements were performed;

1. Created a new database
2. Created a new table
3. Insert some records into the new table
4. Check if the records were inserted

To quit the psql command, use \q which will take you back to Bash

Conclusion

Another great addition to the open source on IBM i catalogue, that opens the possibilities of what can be achieved on the IBM i server.

All the examples I have written for this article can be found on my open-source repository on GitHub, which can be found at https://github.com/AndyYouens/f_Learning

If you have any questions, either on this article, or anything else on the IBM i, use the comments below, or send me a message on twitter @AndyYouens

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience. 

IBM Champion 2021