BigAdmin Feature Article: Switching Databases From MySQL to PostgreSQL 8.2

BigAdmin System Administration Portal

Feature Article

Print-friendly Version

Submit Content | Check BigAdmin Bucks

Switching Databases From MySQL to PostgreSQL 8.2

Scott Fehrman, October 2007

Contents:

Getting Started
Setting Up Your Environment
Initializing the Database
Starting the Database Service
Using the Database Service
Stopping the Database
Documentation
Using Admin Tool
Converting SQL Scripts From MySQL to PostgreSQL
Summary
PostgreSQL Features and Enhancements in Solaris 10 8/07 OS
More Information

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:

Performance improvements
Service Management Facility (SMF) integration
Dynamic Tracing (DTrace) probes
Predefined postgres user with RBAC support
Bundled JDBC driver

See below for a list of the new PostgreSQL features and enhancements in this release of Solaris 10. The official Sun PostgreSQL web page has more information.

Getting Started

By default the PostgreSQL service is disabled:

# svcs postgresql
STATE          STIME    FMRI
disabled       Sep_06   svc:/application/database/postgresql:version_81
disabled       Sep_06   svc:/application/database/postgresql:version_82
#

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.

# grep postgres /etc/user_attr
postgres::::type=role;profiles=Postgres Administration,All
# su - postgres
$ id
uid=90(postgres) gid=90(postgres)
$ profiles
Postgres Administration
All
Basic Solaris User
$ profiles -l

    Postgres Administration:
        /usr/postgres/8.2/bin/initdb            uid=postgres
        /usr/postgres/8.2/bin/ipcclean          uid=postgres
        /usr/postgres/8.2/bin/pg_controldata    uid=postgres
        /usr/postgres/8.2/bin/pg_ctl            uid=postgres
        /usr/postgres/8.2/bin/pg_resetxlog      uid=postgres
        /usr/postgres/8.2/bin/postgres          uid=postgres
        /usr/postgres/8.2/bin/postmaster        uid=postgres
....
$

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/bin before 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:
```
# echo $PATH
/usr/sbin:/usr/bin
# PATH=/usr/postgres/8.2/bin:
# export PATH
# echo $PATH
/usr/postgres/8.2/bin:/usr/sbin:/usr/bin
```
Change MANPATH

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/man before 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:
```
# echo $MANPATH
/usr/man:/usr/sfw/man
# MANPATH=/usr/postgres/8.2/man:
# export MANPATH
# echo $MANPATH
/usr/postgres/8.2/man:/usr/man:/usr/sfw/man
#
```
Set PGDATA

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.
```
$ PGDATA=/var/postgres/8.2/data
$ export PGDATA
$ echo $PGDATA
/var/postgres/8.2/data
$
```

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:

# su - postgres
$  /usr/postgres/8.2/bin/initdb -D /var/postgres/8.2/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.

fixing permissions on existing directory /var/postgres/8.2/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in /var/postgres/8.2/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

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

Success. You can now start the database server using:

    /usr/postgres/8.2/bin/postgres -D /var/postgres/8.2/data
or
    /usr/postgres/8.2/bin/pg_ctl -D /var/postgres/8.2/data -l logfile start

$

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:

# su - postgres

$ svcs postgresql
STATE          STIME    FMRI
disabled       Sep_06   svc:/application/database/postgresql:version_81
disabled       14:06:11 svc:/application/database/postgresql:version_82

$ svcadm -v enable -s postgresql:version_82
svc:/application/database/postgresql:version_82 enabled.

$ svcs -l postgresql:version_82
fmri         svc:/application/database/postgresql:version_82
name         PostgreSQL RDBMS
enabled      true
state        online
next_state   none
state_time   Fri Sep 14 14:23:49 2007
logfile      /var/svc/log/application-database-postgresql:version_82.log
restarter    svc:/system/svc/restarter:default
contract_id  379 
dependency   require_all/none svc:/milestone/network:default (online)
dependency   require_all/none svc:/system/filesystem/local:default (online)

$

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):

$ /usr/postgres/8.2/bin/psql --help
This is psql 8.2.4, the PostgreSQL interactive terminal.

Usage:
  psql [OPTIONS]... [DBNAME [USERNAME]]

General options:
  -d DBNAME       specify database name to connect to (default: "postgres")
  -c COMMAND      run only single command (SQL or internal) and exit
  -f FILENAME     execute commands from file, then exit
  -1 ("one")      execute command file as a single transaction
  -l              list available databases, then exit
  -v NAME=VALUE   set psql variable NAME to VALUE
  -X              do not read startup file (~/.psqlrc)
  --help          show this help, then exit
  --version       output version information, then exit

Input and output options:
  -a              echo all input from script
  -e              echo commands sent to server
  -E              display queries that internal commands generate
  -q              run quietly (no messages, only query output)
  -o FILENAME     send query results to file (or |pipe)
  -n              disable enhanced command line editing (readline)
  -s              single-step mode (confirm each query)
  -S              single-line mode (end of line terminates SQL command)
  -L FILENAME     send session log to file

Output format options:
  -A              unaligned table output mode (-P format=unaligned)
  -H              HTML table output mode (-P format=html)
  -t              print rows only (-P tuples_only)
  -T TEXT         set HTML table tag attributes (width, border) (-P tableattr=)
  -x              turn on expanded table output (-P expanded)
  -P VAR[=ARG]    set printing option VAR to ARG (see \pset command)
  -F STRING       set field separator (default: "|") (-P fieldsep=)
  -R STRING       set record separator (default: newline) (-P recordsep=)

Connection options:
  -h HOSTNAME     database server host or socket directory (default: "local socket")
  -p PORT         database server port (default: "5432")
  -U NAME         database user name (default: "postgres")
  -W              prompt for password (should happen automatically)

For more information, type "\?" (for internal commands) or "\help"
(for SQL commands) from within psql, or consult the psql section in
the PostgreSQL documentation.

Report bugs to .
$

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.

Create a table:

postgres=# CREATE TABLE location 
( 
CODE        VARCHAR(8) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR(32) NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "location_pkey" for table "location"
CREATE TABLE

Describe the table:

postgres=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | location | table | postgres
(1 row)

Populate the table:

postgres=# INSERT INTO location ( CODE, DESCRIPTION ) VALUES
('loc30','Austin, Texas'),
('loc31','New York, New York'),
('loc32','Chicago, Illinois'),
('loc33','Dallas, Texas'),
('loc34','San Jose, California'),
('loc35','Atlanta, Georgia'),
('loc99','Denver, Colorado');
INSERT 0 7

Select the table:

postgres=# select * from location;
 code  |     description
-------+----------------------
 loc30 | Austin, Texas
 loc31 | New York, New York
 loc32 | Chicago, Illinois
 loc33 | Dallas, Texas
 loc34 | San Jose, California
 loc35 | Atlanta, Georgia
 loc99 | Denver, Colorado
(7 rows)

Drop the table:

postgres=# drop table location;
DROP TABLE

Stopping the Database (Version 8.2)

If you want to stop the PostgreSQL database, use the Solaris Service Management Facility svcadm command:

# su - postgres
$ svcadm disable -s postgresql:version_82
$ svcs postgresql:version_82
STATE          STIME    FMRI
disabled       15:11:38 svc:/application/database/postgresql:version_82

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:

List of Man Pages

Man Page	Description
`postgres_82`	PostgreSQL RDBMS version 8.2 for Solaris
`initdb`	Create a new PostgreSQL database cluster
`psql`	PostgreSQL interactive terminal
`svcs`	Report service status
`svcadm`	Manipulate service instances

The PostgreSQL web site has a nice set of online documentation:

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

# gunzip pgadmin3-1.6.3_x86.tar.gz
# tar xf pgadmin3-1.6.3_x86.tar
# pkgadd -d . pgadmin3
# /opt/bin/pgadmin3

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.
Supported by Sun. Sun offers comprehensive, worldwide service offerings for PostgreSQL for Solaris, including 24x7 enterprise-class support.
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.
Predefined postgres user with RBAC support
Bundled JDBC driver