Migrate your site from MySQL to PostgreSQL -- Part 1

Transferring Data From MySQL to Postgres

Porting data from MySQL to Postgres is a little challenging, as Postgres supports a more standard version of SQL than MySQL, and it isn't possible to make a straight SQL dump over to Postgres. However, the SQL syntaxes are quite similar, so in my case, it didn't take too long.

Converting the MySQL Dump

First, ask your Web host to create a database for your account. Databases in Postgres, just like MySQL databases, consist of a collection of tables which contain the actual data. Then make a SQL file dump of your MySQL database using the mysqldump command.

mysqldump -u username -p databasename > sqldump.txt

Download the entire SQL dump file to your computer using FTP. Now that you have a SQL file on your computer, you can turn it into a file that Postgres will import.

First, cut all the MySQL CREATE TABLE queries out of the dump file and paste them into a separate text file. The next step is to redefine the tables using language Postgres can understand.

Postgres's table-creating SQL is similar, but not identical, to MySQL's. Here's an example:

CREATE TABLE practicetable  
{  
someID    SERIAL,  
time      TIMESTAMP DEFAULT now(),  
name      VARCHAR(50),  
address   VARCHAR(50),  
city      VARCHAR(50),  
state     VARCHAR(2),  
country   VARCHAR(3) DEFAULT 'USA',  
postlcode VARCHAR(15),  
age       smallint,  
lattitude real,  
longitude real,  
somebool  boolean,  
message   textitem  
};

In a Postgres table definition, the name of the field should be followed by the field type. A few common field types are given in the previous example, but you can find an extensive list in the Postgres documentation on Data Types. Postgres has a huge selection of data types for different tasks, and can store all manner of data, from Internet addresses and monetary information to geometric object definitions. Here's a quick overview of the most commonly used data types.

The SERIAL type field is the equivalent of a MySQL auto-incrementing unique ID. Postgres databases can contain SQL functions and reports as well as tables and records; the auto-incrementing SERIAL function is automatically added to the database when you define a field of type SERIAL in your table. As the auto-incrementing system resides outside of the actual table, we can custom-build logic for the unique id's value and do other wizardry. When porting from MySQL to Postgres, the default action is sufficient.

The VARCHAR type is exactly what it purports to be, a variable-length text field. The length of the field is defined by the value in parentheses. For example, VARCHAR(5) defines a field that can contain up to 5 characters of text.

SMALLINT, INT, and BIGINT are used to define integer items. SMALLINT fields can store numbers ranging from -32768 to +32767 (this actual size may vary slightly depending on your computer type; the previous is the most common system integer size). INT fields can store larger numbers from -2147483648 to +2147483647. BIGINT field types are for anything bigger and have no size limit.

REAL field types are for real numbers that contain decimal fractions. They can store up to 6 decimal places. DOUBLE PRECISION fields are similar, but they can hold up to 15 decimal places.

A BOOLEAN field is either true or false, one or zero. It is identical to its MySQL counterpart.

TIMESTAMP fields are like their cousins in MySQL. A timestamp is updated to the current date and time each time the record is updated. Postgres time fields can also contain timezone information. For details on the more complex uses of Postgres time data, read the date time page of the PostgreSQL documentation.

Creating The Tables

Once you create a separate table definition SQL file and redefine the tables in Postgres-speak, check to make sure each CREATE TABLE query ends with a semicolon - a Postgres requirement. Then connect to your Web host using a tool like telnet, and create the tables using the following method.

First, open your table definition file with a text editor. Then log in to your host's machine and run the Postgres Interactive Terminal, psql, by typing psql. The default authentication scheme uses your telnet/FTP username for the Postgres account. This allows Postgres to automatically authenticate your identity without forcing you to type in a username and password combination. Your Web host might authenticate differently, in which case you can give the psql program additional arguments like this: psql -d databasename -U username -W. The -d allows you to specify the database, -U specifies the username, and -W asks psql to prompt you for a password.

Once you have psql running, paste each CREATE TABLE query individually into psql and press the enter key. If you make a mistake in your SQL syntax, psql will tell you what went wrong. By entering each table individually, you receive debugging information on a per table basis, which simplifies things greatly.

If, after you enter the table definitions, you realize you missed a field or two, you have two options. You can use the ALTER TABLE command, or just drop the table using DROP TABLE, and then enter it again once you've fixed it. If you use the second method, you will run across a Postgres caveat that leaves certain artefacts from dropped tables that get in the way of creating new ones.

To use the DROP TABLE command, type DROP TABLE practicetable;. This will drop the table, but when you go to redefine the table, you will receive an error. Dropping a table doesn't eliminate the sequence that goes along with fields of type SERIAL that are in the table. These leftover sequences get in the way when you try to recreate the table. To solve this, delete the sequence using DROP SEQUENCE sequencename; before you drop the table. Unfortunately, the sequence name isn't the same as the SERIAL name. When you define a SERIAL type field, Postgres automatically generates a sequence named like this: tablename_colname_seq. In the case of practicetable, the DROP SEQUENCE statement would look like this: DROP SEQUENCE practicetable_someID_seq;. You're then free to drop the table and start again.

After you finish inserting the tables, type \z to double-check a list of the tables. Typing \q will quit psql when you're done. It's finally time to prepare your data for import to Postgres.