PostgreSQL for MySQL users

If you have been using MySQL for a while, you have a knowledge investment that may discourage you from exploring other databases. This short guide shows how common tasks in MySQL are accomplished in PostgreSQL. It also highlights some unique features of each database.

One paragraph history of PostgreSQL

PostgreSQL is a popular open source relational database, considered by many to be the most robust and advanced in the open source world. It began as a project at the University of California, Berkeley, in the 1980s and went through several name changes, settling on PostgreSQL in 1996. It has a high level of stability and an active community. PostgreSQL offers many enterprise class features and a future compatible BSD license.

Directory and file layout

Note: these are the default configurations/locations in Fedora. Check the documentation or package manager in your system for details.

MySQL MyISAM databases live in the MySQL home directory (often /var/lib/mysql) with one subdirectory named for each database. MySQL InnoDB databases live in the MySQL home directory and all share table space files named ibdataN.

PostgreSQL databases live in the PostgreSQL data directory (often /var/lib/pgsql/data) and all share the base and global subdirectories. When a new database is created, PostgreSQL creates a subdirectory in /var/lib/pgsql/data/base/, however the name is a number, the Object ID (OID) of the database.

Configuration and log files

The MySQL configuration file is /etc/my.cnf.

PostgreSQL has three configuration files, all in /var/lib/pgsql/data:

• postgresql.conf -- the main config file, contains most options
• pg_hba.conf -- security and authentication, must be changed to allow remote network access
• pg_ident.conf -- unix to postgresql user mapping, not always needed

MySQL logs messages to /var/log/mysqld.log. PostgreSQL logs messages to /var/lib/pgsql/data/pg_log/, but the location can be changed in postgresql.conf.

Administration

When managing MySQL, many people like to use the web based phpMyAdmin package. For PostgreSQL, there is a similar web based package called phpPgAdmin.

The default superuser in MySQL is root. The default superuser in PostgreSQL is postgres.

The command line monitor in MySQL is mysql. The command line monitor in PostgreSQL is psql. Both can be used to manage local or remote databases. SQL commands (CREATE, DROP, SELECT, etc.) work the same in both monitors. The GRANT command is nearly identical, except for remote access. Remote access in PostgreSQL is configured in the pg_hba.conf file. Most system commands in psql begin with a backslash. While the mysql monitor has some backslash commands, I always used the English-like commands so when I started using psql, I struggled to get around at first. Following is map of some common commands.

Normal operation and processes

MySQL listens on TCP port 3306 and usually has these running processes:

MySQL spawns a thread for each connection.

PostgreSQL listens on TCP port 5432 and usually has these running processes:

Postmaster spawns four helper processes: logger, writer, stats buffer, stats collector.

PostgreSQL spawns a process for each connection.

Data types

The most common data types are similar between MySQL and PostgreSQL: INTEGER, FLOAT, DECIMAL, TEXT, DATE, TIMESTAMP.

MySQL uses the AUTO_INCREMENT data type to create unique database generated ID numbers as a key:
CREATE TABLE (id INTEGER AUTO_INCREMENT PRIMARY KEY);

PostgreSQL uses the SERIAL data type to create a sequence for unique database generated ID numbers as a key:
CREATE TABLE (id SERIAL);

MySQL has these additional data types: BLOB, ENUM, SET.
PostgreSQL has these additional data types: Geometric (BOX, POLYGON, etc.), Network (CIDR, MACADDR, etc.), OID, XID. PostgreSQL has large object functions for BLOBs, but only a reference to the BLOB is stored in the database.

Transactions and foreign keys

MySQL InnoDB databases support transactions, while MyISAM databases do not. In PostgreSQL, every SQL command is a transaction (unless disabled for loading a table or more than one command is wrapped in transaction statements). PostgreSQL also uses Multi-Version Concurrency Control (MVCC), considered to be more granular than row level locking.

MySQL (InnoDB) supports foreign keys. PostgreSQL supports foreign keys.

Clustering

MySQL has strong support for clustering and offers the NDB table manager designed specifically for clusters.

PostgreSQL supports clustering through a separate project called Slony.

Stored procedures, functions, triggers, and views

MySQL 5.0+ added stored procedures, functions, triggers, and views. The implementation of these features is relatively new and there may be some issues to be ironed out.

PostgreSQL has mature support for stored procedures, functions, triggers, and views. Stored procedures and functions may be written in several languages including PL/PGSQL, SQL, TCL, PL/Perl, and C.

Backup/Restore

The MySQL backup utility is mysqldump. Restores can be done using the mysql command.

The PostgreSQL backup utility is pg_dump. Restores can be done using pg_restore or psql. PostgreSQL supports hot backups.

Unique MySQL features

MySQL MyISAM databases have a full text indexing feature that can be used when searching text fields. (PostgreSQL has a full text search third party add-on called tsearch2)

MySQL supports cross database queries.

Unique PostgreSQL features

PostgreSQL allows inheritance so a table can inherit its structure from a parent table and add new attributes.

PostgreSQL supports schemas to provide name space and security separation of tables within the same database. Think of schemas as allowing mini-databases to be created within a database.

PostgreSQL enforces data integrity on date fields. For example, it will return an error if you try to store 2006-02-30 in a date field, while MySQL allows it.

PostgreSQL uses the vacuumdb utility program to reclaim space from deleted records and optimize indexes. Vacuum should be scheduled to run via cron at least once a week.

The Oracle factor

No discussion of open source databases is complete without mentioning the long shadow of Oracle. In the last 18 months, Oracle has acquired two open source database companies, Innobase and Sleepy Cat, that provided integral back end support for MySQL. It appears to me that Oracle was feeling threatened by the success of MySQL in the market and chopped the legs out from under it. The future of InnoDB in MySQL is questionable and Berkeley DB support has already been dropped. For these reasons, I believe it is prudent for any MySQL DBA to at least consider PostgreSQL as a safe harbor if Oracle brings down the hammer.

Final thoughts

I've tried to cover the key administrative issues and show the relationships between frequently used MySQL and PostgreSQL features and utilities. Hopefully, it will pave the road for putting your MySQL knowledge to work with PostgreSQL.