Migrate your site from MySQL to PostgreSQL -- Part 1

Cleaning Up The Dump

Because MySQL keeps mostly to the SQL language standard, importing actual data from a SQL dump file isn't too hard. However, there is a caveat that requires us to edit the SQL dump before we hand it to Postgres.

The main data-entry difference between MySQL and Postgres involves quotes. In Postgres, string values (values that contain text) must be surrounded by single quotes. MySQL allows both, but the mysqldump program fortunately uses single quotes, which are Postgres-friendly. However, MySQL and Postgres differ on what to do with quotes that appear within a string. Where MySQL represents quotes with a "", Postgres requires a \". Use your text editor's search/replace features to replace all of the "" with \". Interestingly, Postgres uses '' to represent single quotes; MySQL uses that method as well, so you won't have to change the single quote escaping.

Importing To Postgres

After you fix the quotes in the SQL dump file, upload the file to your Web host's machine log in as you did before to create the tables. Navigate to the directory where the SQL dump file resides. Start psql, but with a few different command-line options: psql -f sqldump.txt, and replace sqldump.txt with the new name you give the file. This command takes the entire SQL file and imports it right into the proper Postgres tables. As before, you may have to add extra options so psql can authenticate you properly. If you receive any errors, psql will tell you where they occurred. Search for that part of the file, try to figure out and fix the problem, and feed the problematic query manually into the psql command-line tool. In my case, everything imported without a hitch and I was ready to finish the job. It wasn't until a little later that I noticed another problem.

After I started to use the new Postgres-driven site, I ran across another incompatibility issue between MySQL and Postgres. Postgres sequences, used by SERIAL type auto-incrementing fields, start with 1 and auto-increment by one each time a record with a SERIAL type field is inserted. However, when I imported the MySQL dump, the SQL in that dump defined the values of my main integer key. In my case, I had unique IDs counting the whole way to 60, but the sequence was still at one. Each INSERT statement I made failed because the sequence was giving a result that was not a unique ID. Frustrated, I ran 60 INSERT statements to bring the sequence up to the proper point, but I later learned a quicker fix from a friend who regularly uses Postgres. Here's what he told me to do:

Connect to your host using a terminal program like telnet. Then start the psql program as you did when you defined the tables. First, identify the highest ID value that exists in the table. This can be done using SELECT fieldname FROM tablename WHERE fieldname=MAX(fieldname);. Then delete the sequence in question from the database using DROP SEQUENCE table_colname_seq;, where table is the table name and colname is the name of the SERIAL field. Then recreate the sequence using CREATE SEQUENCE table_colname_seq START 61;, and replace 61 with the number of the highest ID value in the database plus one.

Installing a GUI Tool

Once I successfully put my data into Postgres, I needed a way for my non-Unix-savvy partner to successfully work with the data in the database. My choice for that with MySQL had been phpMyAdmin, a great tool for viewing and editing databases online. Fortunately for me, phpMyAdmin has been ported to Postgres in the form of phpPgAdmin.

The installation of phpPgAdmin is simple. First, download the latest stable distribution from the phpPgAdmin Website and put it somewhere on your Web host account where it can be viewed on the Web. Then log on to your host's machine using a tool like telnet. Navigate to the directory where the phpPgAdmin.tar.gz file resides and type tar -xzvf phpPgAdmin.tar.gz to unpack the program (you may have to substitute a different filename; this is just used as an example). Next, move to the new subdirectory created by the unpacking process and read the README file.

Finally, open config.inc.php in a text editor on your Web host's machine (vi, pico, and emacs are popular text editors; check with your host to see what they have available). Inside the file I found instructions for the configuration of phpPgAdmin. Fill out a few questions within the file and load up the page in a browser. PhpPgAdmin will prompt you for your username and login, and you can then begin to manage your database through its interface.