Notes on converting from MySQL 5.0.x to PostgreSQL 8.1.4

On Converting from MySQL 5.0.x to PostgreSQL 8.1.x
--------------------------------------------------

I am in the process of converting a couple of major sites from MySQL 5.0.22
to PostgreSQL 8.1.4, and I thought I would share some of my observations on
this process and the two database systems in general. I feel I am in a good
position to do this, as I am fairly familiar in-depth with MySQL's modern
features, am relatively technology-agnostic, and having done this since
the MySQL project has matured substantially over the last couple of years, I
feel I can give insight into the features of both systems from a current
perspective.

I will attempt to do this from an objective standpoint, as I still see
benefits and drawbacks to both systems (no software will ever meet the needs
of every situation). If you are looking for a document that mocks one system
or another, this is not it; there are countless results for "X sucks" on
$your_favorite_search_engine.

My reasons for making this switch are primarily due to having a bit of spare
time, wanting to expand my horizons and familiarize myself with another
well-respected open source project, some of the nice in-built procedural
language features of PostgreSQL, and basically wanting to form my own
opinion of the features of both systems. That being said, I have really
been impressed so far with the features I am discovering, and becoming
happier each day.

It is by no means comprehensive; I have just highlighted some of the
more notable and obvious differences as I discovered them. Most of the
information in here is pretty basic for those of you very familiar with
both systems. I also only highlight the differences in the Unix/POSIX
versions of these programs. (I don't run Windows as a server, and I don't
care to. My technology agnosticism stops at inadequate systems.)

All comments are current as of the time of writing (Spring/Summer 2006).
Error corrections are very welcome. Flames can go to /dev/null. Don't care.
Oh, and since I wrote this in vim, it is best read with a monospace font. :^)

Major differences I have noted:
-------------------------------

MySQL 5.0.x:
* Multiple storage engines with different features.
* Supports multi-insert syntax (INSERT INTO foo VALUES (1,2), (3,4) ...)
* A few more access controls on features built-in to the GRANT tables. Many
of these are still present, but implemented in other ways in PostgreSQL.
* Single AUTO_INCREMENT column allowed per table.
* Easy, built-in and extensive replication support.
* Single datastore location per server.
* ALL Stored Procedures are kept in the mysql system database.

PostgreSQL 8.1.x:
* Embedded procedures in multiple native languages (stored procedures and
functions can be written in native Perl, Python, Tcl, SQL, PL/PgSQL)
* Extensive and versatile procedural language functionality.
* User-definable data types and operators.
* Multiple sequence generators allowed per table.
* Replication support still rudimentary.
* Stored procedures are kept (somewhat more logically, imho) in the
corresponding databases.
* Multiple datastore locations possible using tablespaces concept.
(For the record, MySQL will have tablespaces when 5.1.x is stabilized.)
* Most system variables, "built-in" types and features configurable as they
are just kept in a system catalog.
* Allows deletions and subselects to specify the same table (e.g. DELETE
FROM foo WHERE id IN (SELECT id FROM foo WHERE...) ). MySQL does not
allow this as of 5.0.22.
* Copious documentation on the database internals, for extending the
database itself.

Pointers, tips, quick facts and gotchas for other people converting:
--------------------------------------------------------------------

* Don't bother using an old version, just go for 8.1.4 (or whatever is new
at the time of your conversion. This should be common sense.)
* Since Pg uses a full transactional storage engine, the speed is roughly
comparable to InnoDB, rather than the stock MyISAM format.
* PostgreSQL's TCP port is 5432 by default.
* The main server process on PostgreSQL is 'postmaster'.
* 'postmaster' can be controlled via the 'pg_ctl' command.
* The administrative user is called 'postgres' by default.
* Like MySQL, Pg uses the system user as default, if no username is
specified when connecting.
* The command-line client is called 'psql'.
* PostgreSQL by default comes configured to disallow network connections.
To enable these, you must follow these steps:
1. Edit $DATADIR/pg_hba.conf and add access permissions.
2. Edit $DATADIR/postgresql.conf and uncomment the listen_addresses
line, setting it to something reasonable.
3. Restart postmaster.
* PostgreSQL relies extensively upon quick aliases for common features within
the CLI shell. MySQL offers many similar features, but they aren't used
as much from what I have observed.
* MySQL combines the concepts of 'database' and 'schema' into one. PostgreSQL
differentiates the two. While the hierarchy in MySQL is
database.table.field, PostgreSQL is roughly: database.schema.table.field.
A schema is a 'logically grouped set of tables but still kept within a
particular database.' This could allow separate applications to be built
that still rely upon the same database, but can be kept somewhat logically
separated. The default schema in each database is called 'public', and is
the one referred to if no others are specified. This can be modified with
'SET search_path TO ...'.
* Pg uses a 'template1' pseudo-database that can be tailored to provide
default objects for new database creation, if you should desire. It
obviously also offers a 'template0' database that is read-only and
offers a barebones database, more equivalent to the empty db created with
mysql's CREATE DATABASE statement.
* Pg's ROLEs can specify a single user or a group, and be nested to contain
multiple users.
* Pg's default character set (in 8.1.4) is UTF8.
* Pg uses the 'serial' column type instead of AUTO_INCREMENT. This allows
more than one independent sequence to be specified per table (though the
utility of this may be of dubious value). These are closer to Oracle's
concept of sequence generators, and they can be manipulated with the
currval(), nextval(), setval(), and lastval() functions.
* Pg requires its tables and databases be 'vacuumed' regularly to remove
completed transaction snapshots and optimize the tables on disk. It is
necessary because the way that PostgreSQL implements true MVCC is by
writing all temporary transactions to disk and setting a visibility
flag for the record. Vacuuming can be performed automatically, and in
a deferred manner by using vacuum_cost settings to limit it to low-load
periods or based upon numerous other criteria. See the manual for more
information.
* Kept internally in Pg, there is a concept called the OID, which is a
continuously incremented number used to assign unique IDs to system
objects. This allows the database to store and refer uniquely to user
operators, new databases, basically anything that the system needs to
refer to in the 'data directory', regardless of user-defined names.
* Most administrative procedures will refuse to run as root, and require you
to su to the 'postgres' system user to perform the action.
* PgAdminIII gives you a great overview of the hierarchy of system objects
throughout the server. Even though you may administrate your server
primarily via the CLI, as I do, it is still valuable during the learning
process to use this tool to browse around the various objects, to learn
the system setup and hierarchy visually.
* While MySQL supports transactions with the InnoDB databases, many MySQL
users generally do not use them extensively enough. With Pg, due to the
behaviour of the server in attempting to ensure data integrity in a
variety of situations (client disconnection, network trouble, server
crashes, etc.), it is highly advisable to become familiar and utilize
transactions a lot more, to ensure your DATA is left in a consistent state
before and after every change you wish to make.
* There is a conversion utility called 'mysql2pgsql' that will convert
dump files from the mysqldump format, to a format that psql can
understand. It is available at:
- http://gborg.postgresql.org/project/mysql2psql/projdisplay.php
* To turn on query time output, similar to the mysql CLI, use the '\timing'
command from psql. (Note that the time is displayed in milliseconds,
whereas in the mysql client it is displayed in seconds.)

Common equivalents:
-------------------

MySQL PostgreSQL
----- -----------
mysql database system tables
mysql psql
mysqld postmaster
mysqladmin initdb/dropdb/createuser/dropuser/
createlang/droplang/vacuumdb
mysqldump pg_dump/pg_dumpall/pg_restore
-nothing- ecpg
SHOW DATABASES; \l
SHOW GRANTS; \du
SHOW TABLES; \dt
DESC tblname; \d foo
USE dbname; \c dbname
ALTER TABLE foo AUTO_INCREMENT = n; SELECT setval('seq_name',n);
SHOW PROCESSLIST; SELECT * FROM pg_stat_activity;
OPTIMIZE TABLE ... VACUUM ...

Final thoughts:
---------------
Overall, I have been happy and very impressed with the features offered by
PostgreSQL 8.1.4, and believe that I will be using it for the majority of my
future projects. There are still some niches where I see utility for the
pluggable storage engines and raw speed of MySQL. I will be keeping my eye
on that project closely, also, as I want to test out the new Scheduled
Events feature from the 5.1 series once it is stabilized.

I have also been using Apress' "Beginning Databases with PostgreSQL, 2nd
Edition" (2005) as my learning material. I briefly examined Sams'
"PostgreSQL - The Comprehensive Guide, 2nd Ed." (2005) and Pearson's
"PostgreSQL: Introduction and Concepts" (2001), but found the Apress book
the best of the 3 that I had access to. YMMV. More info in the book
reviews linked below.

Further information:
--------------------
PostgreSQL manual: http://www.postgresql.org/docs/manuals/
PgAdmin3: http://www.pgadmin.org/
PostgreSQL book reviews:
http://techdocs.postgresql.org/techdocs/bookreviews.php
Community Support Channel: irc.freenode.net in #postgresql
Varlena Consulting's General Bits archives:
http://www.varlena.com/GeneralBits/

---------

Thank you, and I hope that these notes prove helpful to others!

Jason McManus