I've been a casual user of MySQL. Now that the Solaris 10 8/07 OS has been officially
released, with updated support for PostgreSQL, I decided to give it a try and see what it took for a MySQL user to
Get Started with PostgreSQL.

Here are a few of the PostgreSQL features and enhancements in this release of Solaris 10:

Notice: Before we go any further, note that there are two
different instances of the
postgresql service. The Solaris 10 (8/07) OS provides support for the newer version of PostgreSQL 8.2 and for the previous PostgreSQL version 8.1. This document will only cover the "8.2" version of PostgreSQL. If you are working on new database projects, I suggest you use the 8.2 version of PostgreSQL. Please check out the
Postgres web site for details.

The Solaris 10 8/07 OS also has a preconfigured
postgres user and it is using Solaris RBAC features so that this user/role can administrate the SMF
postgresql:version_82 service.

PostgreSQL needs to be
set up before we can use it. There are a few simple things we need to do:

Set up your environment (mostly to make commands easier),
PATH,
MANPATH and
PGDATA.

Initialize the database.

Start the database service.

Setting Up Your Environment (Version 8.2)

Change
PATH

As mentioned above, Solaris supports both PostgreSQL version 8.1 and version 8.2. If the default
PATH variable is not changed, Postgres commands (in
/usr/bin) will be used. Modify your PATH and put
/usr/postgres/8.2/binbefore any other pathnames. You can set your
PATH in a user's login script, a global login script (e.g.
/etc/profile), or set it on the command line:

As mentioned above, Solaris supports both PostgreSQL version 8.1 and version 8.2. If the default
MANPATH variable is not changed, Postgres man pages (in
/usr/man) will be used. Modify your
MANPATH and put
/usr/postgres/8.2/manbefore any other pathnames. You can set your
MANPATH in a user's login script, a global login script (e.g.
/etc/profile), or set it on the command line:

The PostgreSQL database can leverage an environment variable called
PGDATA. This variable is used by many of the PostgreSQL commands. If
PGDATA is used, it needs to be set to the fully qualified pathname to where the PostgreSQL data directory is located. The default location for PostgreSQL data is
/var/postgres/8.2/data. You can set
PGDATA to this, or another location, and then you will not have to use the
-D option for those PostgreSQL commands that need to know the pathname to the data directory.

Initializing the Database (Version 8.2)

Before the PostgreSQL database can be started it needs a "first-time" initialization. Use the
initdb command to initialize the database. See the
initdb man page for more details. Run the
initdb command as the
postgres user. In this example, I'm using the fully qualified path to the
initdb command and to the PostgreSQL data directory. If you have your
PATH and
PGDATA environment variables set up, you should only need to execute
$ initdb:

Notice: The output of the
initdb command tells you how to start and stop the database.
You can ignore these commands. We will be using the Solaris Service Management Facility to manage the PostgreSQL database.

Starting the Database Service (Version 8.2)

The PostgreSQL database is ready to be started. Use the
svcs and
svcadm commands (as user
postgres) to start, stop and monitor the service:

Solaris Service Management Facility will automatically start the PostgreSQL database when the system is booted.

Using the Database (Version 8.2)

The main PostgreSQL command for interactive, command-line, operations is
psql. See the man page for
psql details. Here is the output of the
--help option (I'm using the fully qualified path to the
psql command; if your
PATH is set up properly, you can use just the command name):

To enter the interactive terminal mode, use the
psql command without any options:

$
/usr/postgres/8.2/bin/psql
Welcome to psql 8.2.4, 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
postgres=#

The
postgres=# prompt means that PostgreSQL is ready and waiting for instructions. There's a bunch of PostgreSQL tutorials on the Internet so I won't go into any specifics on how to use PostgreSQL. The
PostgreSQL documentation web site has a complete set of how-to and reference docs. Here are a few commands to test some basic operations.

Documentation

Many manual pages provide lots of information. I'd suggest starting with the
postgres_82 man page, which covers PostgreSQL specific to the Solaris implementation. Here's a list of some man pages that I use:

Using Admin Tool

For those who want a Graphical User Interface (GUI),
pgadmin3 is very nice. Go to the
pgAdmin site and download
pgadmin for Solaris. There's pre-built packages for both SPARC and x86/x64 platforms. The installation is really easy. Download the tar.gz file and save it in a temp location. The binaries will be installed in
/opt/bin

Converting SQL Scripts From MySQL to PostgreSQL

I have a few projects that were using MySQL. I needed to have the same functionality in PostgreSQL. Unfortunately these scripts did not work without some minor modifications. Here is what I changed, relative to the
CREATE TABLE commands. The commands for creating TABLESPACE, DATABASE and SCHEMA were not a problem.

Converting Scripts

MySQL

PostgreSQL

Notes

TIMESTAMP

DATETIME

TIMESTAMP(0) DEFAULT LOCALTIMESTAMP

TIMESTAMP(3)

The PostgreSQL use of timestamp seems to have more features, and you need to be more specific as to how you want the timestamp information.

AUTO_INCREMENT

SERIAL

The
AUTO_INCREMENT appears to be a MySQL feature. From my research, it appears that the
SERIAL (serializer) feature is more of a standard SQL.

BIGINT

NUMERIC(19,0)

Per the PostgreSQL docs I could have used
BIGINT. I decided to change to the
NUMERIC(precision,scale) option.

MEDIUMTEXT

TEXT

Per the PostgreSQL docs,
TEXT has "variable unlimited length".

Summary

The conversion from MySQL to PostgreSQL was easy. I didn't have to:

Install MySQL 5.x.

Create the
mysql user/group.

Set up RBAC or create a SMF service or init scripts.

Get a JDBC driver.

PostgreSQL Features and Enhancements in Solaris 10 8/07 OS

Here's an overview of the new PostgreSQL features and enhancements in this release of Solaris 10:

Open source, integrated, and included
for free with the Solaris 10 OS
. PostgreSQL can be easily extended and customized and, with no license fees, provides great value.

Performance-optimized and enhanced to take advantage of leading Solaris technologies, with support for native DTrace Probes, Predictive Self-Healing and Solaris Containers.

Reliable and highly available with capabilities such as Multi-Version Currency Control (MVCC), Point-in-Time Recovery and full ACID compliance. These are further enhanced through its support for Solaris Predictive Self-Healing and Solaris Containers, designed to eliminate the risks and costs associated with downtime and lost data.

Built to be secure by default with support for Multiple Authentication Methods, ANSI Roles and Permissions and Cryptographic Capabilities.

High performing and scalable under heavy loads -- both in the amount of data it can manage and in the number of concurrent users it can accommodate. PostgreSQL for Solaris core code natively supports DTrace Probes to enhance visibility and speed resolution of performance bottlenecks.