Re: PostgreSQL on AIX platform

On 8/8/2014 3:54 PM, Payal Shah wrote:
> Thank you for your response. If you or someone can provide steps on
> how to bundle PostgreSQL on AIX (using IBM XL C compiler), that would
> be great so I can try it out to see if works for AIX 7 platform. I'm
> new to AIX platform and appreciate any help that you can provide.

these are some notes on how I built pg 9.1 for AIX 6.1. the process
should be virtually identical for newer versions of either PG or AIX.
Note I don't build a lot of the optional features as we don't use them
(plperl, plpython, the contrib modules, etc)

Prerequisites

to build or run

1. IBM Power6 or newer server
2. IBM AIX 6.1
3. IBM XLC 11 or 12 (only needed to build)
4. create a postgres user account and directories
|mkuser postgres|
|mkdir -p /opt/pgsql91 # postgres software directory. |
|mkdir -p /u10/pgsql91 # postgres database directories will go here|
|chown -R postgres /u10/pgsql91 /opt/pgsql91|

5. postgres user environment as follows ...
*/home/postgres/.profile*
|export PATH=/usr/bin:/usr/ucb:$HOME/bin:/opt/pgsql91/bin:/usr/sfw/bin|
|export PGDATA=/u10/pgsql91/data|

*Database Directories*

Postgres data directories, like any database, should always be on RAID1
or RAID10 storage, never RAID5 or RAID6.

This build of postgresql was linked to use the /opt/pgsql91 paths as
specified above, if you put it in a different directory, you will need
to ensure LD_LIBRARY_PATH points to /new/path/to/pgsql91/lib

we use some utils from theIBM AIX Linux Toolkit
<http://www-03.ibm.com/systems/power/software/aix/linux/toolbox/download.html>,
including gtar, gmake, and wget.

Build

To compile, you first need to build zlib 1.2.5 and readline 6.1
libraries, configured for static linking. You will install these in the
build user's $HOME/include and $HOME/lib, they are only needed to
compile PostgreSQL, not to run it, so are not part of the release tarball.

Build prerequisites

This build requires IBM AIX 6.1, and IBM XL C. See the prerequisite .

log on as the build user. this can be a regular user or postgres. Setup
your build environment per the
prerequisites<https://wiki.seagate.com/confluence/display/fisWiki/Installing+PostgreSQL+on+AIX+6.1>

|mkdir $HOME/src|

build static linkable zlib

|cd $HOME/src|
|wget http://zlib.net/zlib-1.2.8.tar.gz|||
|gtar xzf zlib-||1.2||.8||||.tar.gz|
|cd zlib-||1.2||.||8|
|export OBJECT_MODE=||64|
|CC=/usr/vac/bin/xlc CFLAGS=||"-q64 -mcpu=pwr6 -O2"|
|AR=||"/usr/bin/ar"| |./configure --prefix=$HOME --||64| |--||static|
|gmake clean|
|gmake|
|gmake install|

If you have XL C++, then use CC=/usr/vacpp/bin/xlc .....

build static linkable readline

|cd ~/src|
|wget https://ftp.gnu.org/gnu/readline/readline-6.1.tar.gz|||
|gtar xzf readline-||6.1||.tar.gz|
|cd readline-||6.1|
|export OBJECT_MODE=||64|
|CC=/usr/vac/bin/xlc CFLAGS=||"-q64"| |AR=||"/usr/bin/ar"| |./configure
--prefix=$HOME --||64| |--||static|
|gmake clean|
|gmake|
|gmake install|

Build PostgreSQL

replace 9.1.14 with the version you are building.

get postgresql source

|cd ~/src|
|wget http://www.postgresql.org/ftp/source/v9.1.14|||
|gtar xzf postgresql-||9.1||.||14||.tar.gz|
|cd postgresql-||9.1||.||14|

build Postgresql

|cd $HOME/src/postgresql-||9.1||.||4|
|export OBJECT_MODE=||64|
|CC=/usr/vacpp/bin/xlc CFLAGS=||"-q64 -I$HOME/src/include -O2
-qarch=pwr5 -qtune=balanced"| |\|
|||LDFLAGS=||"-L$HOME/src/lib"| |AR=||"/usr/bin/ar"| |\|
|||./configure --prefix=/opt/pgsql91|
|gmake clean|
|gmake|
|cd contrib|
|gmake|
|cd ..|
|gmake check|
|gmake install|

create tarball

|cd /opt|
|gtar cvzf /tmp/postgresql-||9.1||.||4||-AIX-||1.0||.tar.gz pgsql91|

initialize the database...

*important*

this step must be done by the postgres user, who must own the $PGDATA
directory.

|initdb -E UTF8 --locale=C|

Configure

edit $PGDATA/pg_hba.conf

|local all all ident|
|host all all ||127.0||.||0.1||/||32| |md5|
|host all all ||10.5||.||160.0||/||20| |md5 # your local
subnet *OR*|
|host all all ||0.0||.||0.0||/||0| |md5 # full WAN|

edit $PGDATA/postgresql.conf and find/set the following. some are #
commented, some aren't, be sure to delete the # if you edit a value...

|listen_address = ||'0.0.0.0'| |## listen to all IPv4 interfaces only.
AIX IPv6 is somewhat broken.|
|shared_buffers = 256MB # min 128kB - FIS SV suggests
max 2048MB ||for| |a large system|
|work_mem = 16MB # min 64kB|
|maintenance_work_mem = 128MB # min 1MB. use as much as
1024MB ||for| |a large scale server to speed up index creation|
|effective_cache_size = 512MB # ||this| |should be
approximately the value of ||'cached'| |memory during normal system
operation|
|logging_collector = on|
|log_line_prefix = ||'%t %u(at)%h[%d|%p]: '|

The values should be adjusted to suit the application. shared_buffers
usually should be no more than 2GB even on very large systems. adjust
the work_mem down if you expect a large number of concurrent queries as
this can be allocated several times for a complex query.

*AIX Kernel Tuning*

Unlike Solaris and Linux, the AIX 6.1 Kernel does not need any shmmax or
other tuning for Postgres, all defaults are suitable for any usage.

Run

as postgres...

|pg_ctl start|

Admin

as postgres...

|$ psql|
|psql (||9.1||.||4||)|
|Type ||"help"| |for| |help.|
|postgres=# create user myusername with createdb createuser password
||'mypassword'||;|
|CREATE ROLE|
|postgres=# create user myapp with password ||'apppass'||;|
|CREATE ROLE|
|postgres=# create database myapp with owner myapp;|
|CREATE DATABASE|
|postgres=# \q|
|$|

autostart

create /etc/rc.d/init.d/postgres91

*/etc/rc.d/init.d/postgres91*
|#!/usr/bin/ksh|
|# simple postgres sysVinit script|
|#|
|# Make sure that /opt and pgdata is mounted|
|#####################################|
|[ ! -d /opt/pgsql91 ] && exit ||1|
|[ ! -d /u10/pgsql91 ] && exit ||1|
|export PATH=/usr/bin:/opt/pgsql91/bin|
|export PGDATA=/u10/pgsql91/data|
|## export LD_LIBRARY_PATH=/path/to/nonstandard/pgsql91/lib|
|su postgres -c ||"pg_ctl $1"|

as root...

|chmod +x /etc/rc.d/init.d/postgres91|
|cd /etc/rc.d/rc2.d|
|ln -s ../init.d/postgres91 Kpostgres91|
|ln -s ../init.d/postgres91 Spostgres91|

*Caveat*

sysVinit rc.d directories work somewhat differently in AIX than they do
in Solaris or Linux.

To start postgres, as root...

|/etc/rc.d/init.d/postgres91 start|

or stop or restart or reload

--
john r pierce 37N 122W
somewhere on the middle of the left coast