Migrating a Database to PostgreSQL

From Zanecorpwiki

First, get a data dump in standard SQL syntax (using standard CREATE TABLE and INSERT statements):

• for postgres source DBs, use something like 'pg_dump -DOi db.sql'; 'D' indicates to use column INSERTs, 'O' drops the ownership stuff, and 'i' ignores server mismatch (i.e., you can use pg_dump 8.1 when contacting an 8.2 server; useful for remote dumps)

If this is a simply copy migration, create the new database. If not, create a temporary database:

pgcreate dbname

Load your data into the new database:

psql dbname  db.sql

If you're just copying the data, you're data. If you need to change the data, you're done. If you need to alter the data, then create the db which will be the final destination.

Now it's time to write the scripts to transform the data from the old format into the new. There are many choices for this. If you're already set up with PHP and DB access, then PHP + PearDB is probably one of the best choices for knocking quick scripts.