PostgreSQL Setup

Note: These instructions have been validated on IBM i 5.4, 6.1, & 7.1

Software Prerequisites:

  • IBM PASE for i (SS1, Option 33)
  • The XL C/C++ compiler for AIX needs to be installed in PASE.
  • The RPM package manager needs to be installed in PASE
       Note: Use the instructions found here:   OpenSourceBinaries
  • Use the wwwinstall.sh script downloaded as part of the RPM package manager install instructions to download and install the following groups of pre-compiled Open Source binaries for AIX:
    wwwinstall.sh GetBase
    wwwinstall.sh GetWbase (to get zlib)
    wwwinstall.sh GetCompileTools
    wwwinstall.sh DevCompileTools (to get readline-devel)
    wwwinstall.sh DevWbase (to get zlib-devel)

Install the PostgreSQL Software

  • Download the latest PostgreSQL source tar file (version 8.4.8 for this example) from: http://www.postgresql.org/ftp/source/
    Note: FTP the tar file to the /usr/local directory on your IBM i.
  • Start up an interactive PASE session on your IBM i using the QP2TERM program
    ==> CALL QP2TERM
  • Ensure your PATH is setup correctly to utilize the previously downloaded Open Source binaries
    > export PATH=/opt/freeware/bin:/QOpenSys/usr/bin:/usr/ccs/bin:/QOpenSys/usr/bin/X11:/usr/sbin:.:/usr/bin
  • Extract the tar file contents:
    > cd /usr/local
    > tar -xf postgresql-8.4.8.tar
  • Run the PostgreSQL configure script to generate a makefile
    > cd postgresql-8.4.8
    > ./configure CC=xlc CFLAGS=‘-qlanglvl=extc89′ —with-template=aix —prefix=/usr/local/pgsql
    —bindir=/usr/local/pgsql/bin —datadir=/usr/local/pgsql/share —sysconfdir=/usr/local/pgsql/etc
    —libdir=/usr/local/pgsql/lib —includedir=/usr/local/pgsql/include
    —with-includes=/opt/freeware/include —with-libraries=/opt/freeware/lib
  • Run the generated PostgreSQL makefile
    > gmake
    result: All of PostgreSQL successfully made. Ready to install
  • Install PostgreSQL
    > gmake install
    result: PostgreSQL installation complete.
  • Create a PostgreSQL database installation and initialize it
    > mkdir /usr/local/pgsql/data
    > chmod go-rx /usr/local/pgsql/data
    > export PATH=/usr/local/pgsql/bin:$PATH
    > initdb -D /usr/local/pgsql/data —no-locale -E UTF8
  • Modify the /usr/local/pgsql/data/postgresql.conf configuration file to activate the following option
    wal_sync_method = fsync <remove the # at the beginning of this line)
  • Start the PostgreSQL database server in the background
    > postgres -D /usr/local/pgsql/data >/usr/local/pgsql/logfile 2>&1 &

Test the PostgreSQL Installation

  • Create a test PostgreSQL database
    > createdb mydb
  • Start up a PostgreSQL interactive terminal to work with the test database
    > psql mydb
  • Verify the version of the test database
    mydb=# SELECT version();
    results:
    version
    ----------------------------------------------------------------
    PostgreSQL 8.4.8 on powerpc-ibm-os400, compiled by xlc, 32-bit
    (1 row)
  • Create a table in the test database
    mydb=# CREATE TABLE cities ( <press enter>
    mydb(# name varchar(80), <press enter>
    mydb(# location point <press enter>
    mydb(# ); <press enter>
  • Insert a record into the table
    mydb(# INSERT INTO cities VALUES (‘San Francisco’, ‘(−194.0, 53.0)’);
    mydb(# SELECT * FROM cities;
    results:
            name      | location   
       ---------------±---------- 
       San Francisco  | (−194,53)  
       (1 row)    
  • Exit the PostgreSQL database terminal
    mydb(# \q
  • Delete the test PostgreSQL database
    > dropdb mydb
  • Stop the PostgreSQL database server in the background
    > pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile stop

Author(s)

Ryan Watkins - IBM