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.
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.
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.
TEXT(‘Profile for POSTGRESQL database’)
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.
chown postgres /home/postgres
In a shell window, sign on using the POSTGRES profile and run the following commands.
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’))
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
We use the psql command to enter SQL statements into Postgres.
In the figure below, we can see the following SQL statements were performed;
- Created a new database
- Created a new table
- Insert some records into the new table
- Check if the records were inserted
To quit the psql command, use \q which will take you back to Bash
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
Mike Ryan says
Ace! Another reason to explore these Open Source capabilities.