UKOUG Technology Conference

UKOUG Technology Conference
UKOUG Technology Conference 2018 Call for Papers

Tuesday, June 28, 2016

Install PostgreSQL on Solaris 11 (11.3)

Here are some quick instruction on how to install PostgreSQL on Solaris 11.3.
  • PostgreSQL 9.5:

Add a user

At this time  postgres user exists on Solaris, so I am going to use that, but just in case then:

# groupadd -g 90 postgres
# useradd -u 90 -S files -s /usr/bin/pfksh -d / -g postgres postgres
# grep postgres /etc/passwd  /etc/group
/etc/passwd:postgres:x:90:90:PostgreSQL Reserved UID:/:/usr/bin/pfksh


# mkdir /usr/local/postgres
# chown postgres:postgres /usr/local/postgres
# su - postgres
# cd /usr/local

# bunzip2 < /tmp/postgresql-9.5.3-S11.i386-64.tar.bz2 | tar xpf -
# /usr/local/postgres/9.5-pgdg/bin/64/initdb -D /usr/local/postgres/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /usr/local/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /usr/local/postgres/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... not supported on this platform
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/local/postgres/9.5-pgdg/bin/64/pg_ctl -D /usr/local/postgres/data -l logfile start

Success. You can now start the database server using:

/usr/local/postgres/9.5-pgdg/bin/64/postgres -D /usr/local/postgres/data
/usr/local/postgres/9.5-pgdg/bin/64/pg_ctl -D /usr/local/postgres/data -l logfile start

Create SMF

# mkdir -p /usr/local/postgres/svc/method

# svcbundle -s service-name=application/database/postgresql -s start-method="/usr/local/postgres/svc/method/postgresql start" \
-s stop-method="/usr/local/postgres/svc/method/postgresql stop" -o /lib/svc/manifest/site/postgresql.xml
You will need to edit postgresql.xml to add and alter a few settings
# cat /lib/svc/manifest/site/postgresql.xml
<?xml version="1.0" ?>
<!DOCTYPE service_bundle
  SYSTEM '/usr/share/lib/xml/dtd/service_bundle.dtd.1'>
<service_bundle type="manifest" name="application/database/postgresql">
    <service version="1" type="service"
        <dependency restart_on="none" type="service"
            name="multi_user_dependency" grouping="require_all">
            <service_fmri value="svc:/milestone/multi-user"/>
        <exec_method timeout_seconds="300" type="method" name="start"
            exec="/usr/local/postgres/svc/method/postgresql start"/>
        <exec_method timeout_seconds="300" type="method" name="stop"
            exec="/usr/local/postgres/svc/method/postgresql stop"/>
        <exec_method timeout_seconds="300" type="method" name="refresh"
        <property_group type="framework" name="startd">
            <propval type="astring" name="duration" value="transient"/>
        <instance enabled="true" name="default">
                <method_credential user='postgres' group='postgres' />
            <property_group name='postgresql' type='application'>
                <propval name='bin' type='astring'
                           value='/usr/local/postgres/9.5-pgdg/bin/64' />
                <propval name='data' type='astring'
                           value='/usr/local/postgres/data' />
                <propval name='log' type='astring'
                           value='server.log' />
                <propval name='value_authorization' type='astring'
                           value='solaris.smf.value.postgres' />


                <loctext xml:lang="C">
                <loctext xml:lang="C">
# cat /usr/local/postgres/svc/method/postgresql
# Copyright 2008 Sun Microsystems, Inc.  All rights reserved.
# Use is subject to license terms.
#ident  "@(#)postgresql 1.3     08/02/08 SMI"

. /lib/svc/share/

# SMF_FMRI is the name of the target service. This allows multiple instances
# to use the same script.

getproparg() {
        val=`svcprop -p $1 $SMF_FMRI`
        [ -n "$val" ] && echo $val

check_data_dir() {
        if [ ! -d $PGDATA ]; then
                echo "Error: postgresql/data directory $PGDATA does not exist"
                exit $SMF_EXIT_ERR_CONFIG

        if [ ! -w $PGDATA ]; then
                echo "Error: postgresql/data directory $PGDATA is not writable by postgres"
                exit $SMF_EXIT_ERR_CONFIG

        if [ ! -d $PGDATA/base -o ! -d $PGDATA/global -o ! -f $PGDATA/PG_VERSION ]; then
                # If the directory is empty we can create the database files
                # on behalf of the user using initdb
                if [ `ls -a $PGDATA | wc -w` -le 2 ]; then
                        echo "Notice: postgresql/data directory $PGDATA is empty"
                        echo "Calling '$PGBIN/initdb -D $PGDATA' to initialize"

                        $PGBIN/initdb -D $PGDATA
                        if [ $? -ne 0 ]; then
                                echo "Error: initdb failed"
                                exit $SMF_EXIT_ERR
                        echo "Error: postgresql/data directory $PGDATA is not empty, nor is it a valid PostgreSQL data directory"
                        exit $SMF_EXIT_ERR_CONFIG

PGBIN=`getproparg postgresql/bin`
PGDATA=`getproparg postgresql/data`
PGLOG=`getproparg postgresql/log`

if [ -z $SMF_FMRI ]; then
        echo "Error: SMF framework variables are not initialized"
        exit $SMF_EXIT_ERR

if [ -z $PGDATA ]; then
        echo "Error: postgresql/data property not set"
        exit $SMF_EXIT_ERR_CONFIG

if [ -z $PGLOG ]; then
        echo "Error: postgresql/log property not set"
        exit $SMF_EXIT_ERR_CONFIG

case "$1" in
        $PGBIN/pg_ctl -D $PGDATA -l $PGDATA/$PGLOG start

        $PGBIN/pg_ctl -W -D $PGDATA stop

        $PGBIN/pg_ctl -D $PGDATA reload

        echo "Usage: $0 {start|stop|refresh}"
        exit 1


# chmod +x /usr/local/postgres/svc/method/postgresql
# svcs postgresql
svcs: Pattern 'postgresql' doesn't match any instances
STATE          STIME    FMRI

# svcadm restart manifest-import
# svcs postgresql
STATE          STIME    FMRI
online         12:02:25 svc:/application/database/postgresql:default

Any errors check log files:
# svcs -Lv manifest-import
# svcs -Lv postgresql


Hesadrian said...

Thank you Andrew for your kind sharing.

when creating the SMF for Postgre process, is it root shell or postgres user shell?

Andrew Watkins said...

The "Create SMF" part is done at the root shell.
As you can tell the postgres service is run as user postgres with the updates above (yellow highlight).

Mike Fischer said...

if you need UTF8 charset
./initdb -d /usr/local/postgres/data -E UTF8