3 years ago I have announced my disagree on how Sun was crossing the red line and my decision to move out of MySQL, so now is time to announce that develcuy.com is successfully migrated to PostgreSQL 9.1!

If you are wondering how I have achieved it, let me share that is not an easy job in Drupal 6, you should have good experience on MySQL, PostgreSQL and Drupal database schema. Fortunately, In Drupal 7, migration between database engines seems to be so easy thanks to DBTNG.

I've warned you ;)

So there are my notes for Drupal 6.x:

• create pg user and db
  createuser --pwprompt --encrypted --no-adduser --no-createdb username
  su postgres -c "createdb --encoding=UNICODE --owner=db_name --template=template0 db_name"
• make a clean drupal install in pg
• activate all modules to regenerate functions and tables in pg
  $ pg_dump -U db_name -h localhost --encoding=UTF8 --no-owner -c db_name | gzip -c > db.pgsql-wiped.sql.gz
• truncate all cache and locales tables in mysql
• Use Pentaho Kettle (spoon) to copy mysql data to the wiped pgsql db, make sure to remove all cache&locales tables from migration job
• dump pgsql db
  $ pg_dump -U db_name -h localhost --encoding=UTF8 --no-owner -c db_name | gzip -c > db.pgsql.sql.gz
• import your db to your production server
  $ psql -U db_name -h localhost -d db_name --set client_encoding=UTF8 -f newpgdb.sql
• truncate locales_source, locals_target. Then import locale data (translations) from the old db USING DRUPAL export/import forms
• clear cache twice ;)
• Fix pg sequences: select setval('menu_links_mlid_seq'::regclass,(select max(mlid) from menu_links));
• Fix pg sequences: select setval('blocks_bid_seq'::regclass,(select max(bid) from blocks));
• enjoy!

Just would like to add that Kettle is a great tool that got to use for first time in my entire life, console and a PHP script were my tools in previous migrations, but Kettle allowed me to save time and is very intuitive. So give it a try ;)

Blessings!