Solaris 10: Using Postgres (Part 1)

(Postgres Logo)

Abstract:
Solaris had long been the operating system for performing managed services in the telecommunication arena. During a time when Oracle priced Solaris out of the market by charging a higher fee for similarly performing hardware than other competitors, Sun Microsystems started bundling Postgres and later purchased MySQL for bundling. Postgres is a simple, easy to enable, royalty free database available for Solaris. This article will discuss setting up the Solaris 10 bundled Postgres database.

(Sun Microsystems Logo)

History:
From the first pages of the PostgreSQL documentation:

The object-relational database management system now known as PostgreSQL is derived from the POSTGRES package written at the University of California at Berkeley. With over a decade of development behind it, PostgreSQL is now the most advanced open-source database available anywhere. The POSTGRES project, led by Professor Michael Stonebraker, was sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. The implementation of POSTGRES began in 1986.

Postgres has existed a long time, from the same roots as Berkeley UNIX, the original base operating system for Sun Microsystem's Solaris.

(Sun Solaris Logo)

Versions:
Under Solaris 10, Sun Microsystems bundled Postgres. Basic directory structures are as follows:

V240/root$ ls -la /*r/postgres
/usr/postgres:
total 12
drwxr-xr-x  6 root bin  512 Jan  2  2010 .
drwxr-xr-x 44 root sys 1024 Mar  6  2010 ..
drwxr-xr-x 10 root bin  512 Jan  2  2010 8.2
drwxr-xr-x  9 root bin  512 Jan  2  2010 8.3
drwxr-xr-x  2 root bin  512 Jan  2  2010 jdbc
drwxr-xr-x  6 root bin  512 Jan  2  2010 upgrade

/var/postgres:
total 8
drwxr-xr-x  4 postgres postgres  512 Jan  2 2010 .
drwxr-xr-x 51 root     sys      1024 Nov 10 2010 ..
drwxr-xr-x  4 postgres postgres  512 Jan  2 2010 8.2
drwxr-xr-x  5 postgres postgres  512 Jan  2 2010 8.3 

Under Solaris 10, Postgres 8.2 and 8.3 are shipped. With 8.3, both 32 and 64 bit versions.

(It should be noted that with Postgres 8.3, community support is projected to end in 2012.)

File System Locations:
Before using Postgres, it may be advisable to mount additional disks in a ZFS pool and mount them. This is not strictly the "correct" way to set up a set of database directories, but for a small system where root disks are mirrored and a second set of mirrored disks are used for applications, it will be adequate.

V240/root$ zfs create zpool1/pg_8_3_backups
V240/root$ zfs create zpool1/pg_8_3_data  
V240/root$ zfs create zpool1/pg_8_3_data_64

V240/root$ zfs set mountpoint=/var/postgres/8.3/backups zpool1/pg_8_3_backups
V240/root$ zfs set mountpoint=/var/postgres/8.3/data zpool1/pg_8_3_data     
V240/root$ zfs set mountpoint=/var/postgres/8.3/data_64 zpool1/pg_8_3_data_64
 
V240/root$ zfs list
NAME                   USED AVAIL REFER MOUNTPOINT
zpool1                1.92G 65.0G 1.92G /u001
zpool1/pg_8_3_backups   21K 65.0G   21K /var/postgres/8.3/backups
zpool1/pg_8_3_data      21K 65.0G   21K /var/postgres/8.3/data
zpool1/pg_8_3_data_64   21K 65.0G   21K /var/postgres/8.3/data_64

V240/root$ cd /var/postgres/8.3
V240/root$ chown -R postgres:postgres *

The final 2 steps are critical, if ZFS file systems will be mounted and used, the default ownership is "root" and the starting process will fail if those ZFS directories are not owned by the dba "postgres".

When starting 8.3 version of Postgres, the data should now be stored on zpool1 application pool.

Services:
Postgres is a first-class citizen under Solaris 10. There are no start/sop scripts needed to be written - they are pre-bundled as a variety of services within Solaris Service Management Facility (SMF).

V240/root$ svcs "*postgres*"  
STATE    STIME    FMRI
disabled 12:49:12 svc:/application/database/postgresql:version_82
disabled 12:49:12 svc:/application/database/postgresql:version_82_64bit
disabled 12:49:12 svc:/application/database/postgresql_83:default_32bit
disabled 12:49:12 svc:/application/database/postgresql:version_81
disabled 12:49:13 svc:/application/database/postgresql_83:default_64bit

 The database version of choice can be enabled through SMF.

Review Database Owner:
Solaris comes with role based access to Postgres pre-installed. They should look similar to the following:

V240/root$ grep postgres /etc/passwd /etc/user_attr /etc/security/exec_attr
 
/etc/passwd:postgres:x:90:90:PostgreSQL Reserved UID:/:/usr/bin/pfksh
 
/etc/user_attr:postgres::::type=role;profiles=Postgres Administration,All
 
/etc/security/exec_attr:Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/initdb:uid=postgres
/etc/security/exec_attr:Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/ipcclean:uid=postgres
/etc/security/exec_attr:Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/pg_controldata:uid=postgres
/etc/security/exec_attr:Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/pg_ctl:uid=postgres
/etc/security/exec_attr:Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/pg_resetxlog:uid=postgres
/etc/security/exec_attr:Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/postgres:uid=postgres
/etc/security/exec_attr:Postgres Administration:solaris:cmd:::/usr/postgres/8.2/bin/postmaster:uid=postgres

With the permissions and dba account set up correctly, it should be ready to start.

Starting Postgres:
The Postgres database can be started from the  dba user.

V240/user$ su - root
Password:
V240/root$ su - postgres
V240/postgres$ svcadm enable svc:/application/database/postgresql_83:default_64bit

V240/postgres$ svcs "*postgres_83:default_64bit"
STATE    STIME   FMRI
offline* 0:43:27 svc:/application/database/postgresql_83:default_64bitsvcs: Pattern
 

V240/postgres$ svcs "*postgresql_83:default_64bit"
STATE   STIME   FMRI
online  0:43:37 svc:/application/database/postgresql_83:default_64bit

It may take a couple of minutes to start up for the first time, since many files from a sample database will need to be copied into the new directory structure, and onto the ZFS file systems.

Setting Up Sample Role/User, Database, and Client Access:
By default, all authenticated users are allowed to leverage the Postgres database under Solaris, but only on the same host. The default version of Postgres may be older than the version you wish to use.

V240/ivadmin$ type createdb psql
createdb is /usr/bin/createdb
psql is /usr/bin/psql

V240/ivadmin$ psql --version
psql (PostgreSQL) 8.1.18
contains support for command-line editing

V240/ivadmin$ createdb --version
createdb (PostgreSQL) 8.1.18

Since Solaris supports multiple versions of Postgres, it is important to set paths before using commands. A command should be used in the top of any script which runs Postgres or any command prompt where the user is intending on performing a lot of Postgres work.

V240/user$ PATH=/usr/postgres/8.3/bin:$PATH
V240/user$ export PATH

A privileged "role" (or "user") can set up a database and client access from another "user" or "role". The "createuser" binary is a wrapper around the "CREATE ROLE" command in Postgres.
In the case below, a new non-superuser (-S) will be created, which can create databases ("-d"), be restricted from creating new "roles" or "users" (-R), and log into the database ("-l"). Also, the binary command will echo the postgres command used ("-e"), for clarity sake.

V240/user$ su - root
Password:
V240/root$ su - postgres
V240/postgres$ PATH=/usr/postgres/8.3/bin:$PATH 
V240/postgres$ export PATH
V240/postgres$ createuser -S -d -R -l -e user
CREATE ROLE user NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;

(The addition of the proper path was used, in case it is not set up globally on the platform.)

The creation of the database can now be done by the Solaris user "user", which is also Postgres "role". By default, the name of the database is the same name as the "user".

V240/user$ PATH=/usr/postgres/8.3/bin:$PATH
V240/user$ export PATH

V240/ivadmin$ createdb -e
CREATE DATABASE user;

After the database is created, the

V240/ivadmin$ psql
Welcome to psql 8.3.8, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
user=>

The process of creating objects in the database can now take place.

Client Access Error:
If the user has never created a database, the first attempt access attempt will present an error such as:

V240/user$ psql
psql: FATAL:  database "user" does not exist

This indicates that a database must be created for that user.

Creating Database Error:
The "createdb" executable is a binary wrapper around the "create database" Postgres command. Databases are created by "cloning" a standard database template. If a database is created before the role is created, an error such as the following is presented:

V240/user$ createdb
createdb: could not connect to database postgres: FATAL:  role "user" does not exist

Before a database can be created, a user must be able to do this.

Creating Role Error:
A "role" is sometimes referred to as a "user". The Solaris user name is often tied directly as the "role". If the user is not privileged, the following error is presented:

V240/ivadmin$ createuser
Enter name of role to add: user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
createuser: could not connect to database postgres: FATAL:  role "user" does not exist

A privileged user must create new "roles". Under Solaris, this is the "postgres" user.

Resources:
Other Postgres resources are noted below:
[html] PostgreSQL 8.3.21 Documentation
[html] Dynamic Tracing of PostgreSQL via DTrace (in 8.3)
[pdf] Availability of PostgreSQL in the Data Center
[html] 2010-05 Setting up PostgreSQL under Solaris 10
[pdf] 2008-?? - Best Practices with PostgreSQL on Solaris
[html] 2005-11 - Tuning PostgreSQL under Solaris x64
[html] 2005-05 - Tuning Write Performance of PostgreSQL on Solaris
[html] 2005-04 - Tuning Solaris for PostgreSQL Read and Write Performance (8.0.2)