PowerWire.eu

Independent IBM i, AIX and Linux news and tech tips for Europe and beyond

  • Home
  • News
  • Technical Articles
    • IBM i
    • AIX
    • Linux
    • VIOS
  • Subscribe
  • About Us
  • Contact Us
  • Advertise with PowerWire.eu

PostgreSQL on IBM i

December 5, 2021 by Andy Youens 1 Comment

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

Related Posts

  • Data-Queues and DB2 ServicesData-Queues and DB2 Services
  • SQLite and a Node APISQLite and a Node API
  • Windows Terminal & IBM iWindows Terminal & IBM i
  • Tinker’s Tech Tips – Maximum Number of Object owned by a User ProfileTinker’s Tech Tips – Maximum Number of Object owned by a User Profile
  • Rant for better security on IBM iRant for better security on IBM i
  • Probably the best Interactive Debugger on the IBM iProbably the best Interactive Debugger on the IBM i

Filed Under: IBM i, Technical Articles Tagged With: Andy Youens, IBM i, IBM Power Systems

Comments

  1. Mike Ryan says

    December 8, 2021 at 11:05 am

    Ace! Another reason to explore these Open Source capabilities.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Free monthly newsletter signup

News

Rant for better security on IBM i

This rant is intended as a wakeup call to companies and ITSecurity experts.I have 25 years of experience in … [Read More...]

Probably the best Interactive Debugger on the IBM i

Get Started Open up ‘IBM i Client Access’, make sure you have entered the correct system, then choose … [Read More...]

How to find the right IBM i printer configuration

You might think that we live in a world of standards and that any printer would be able to work out of the box … [Read More...]

More articles from this section

Quick Links

  • Advertise
  • Subscribe

Follow Us…

  • Email
  • LinkedIn
  • Twitter

Search This Website

Copyright © 2022 · Cue Communications · All Rights Reserved

Log in

This site uses cookies More info