I'm not a MySQL fun, so some troubles with MySQL database (MyISAM data corruption and too slow InnoDB) force migration of three existing Drupal 6 sites from MySQL to PostgreSQL. Since Drupal 6.2 some PgSQL-related bugs (sample) in core modules has been fixed, so I have a try.
Drupal 6.5 and PostgreSQL 8.3 compatibility
Drupal Core Modules
I've installed clean Drupal 6.5 on top of PostgreSQL 8.3.3 (with UTF8 database encoding) and played with it a lot. There is no heavy troubles, only minor ones:
- My database instance has non-UTF charset set as default client_encoding (in postgresql.conf). It is not possible to change this setting due to large amount of legacy code. It is much simpler to explicitly set connection encoding after connect to database.
Patch: drupal.pgsql.diff.gz (see also my bug report).
- Blob data (in cache_* tables) is not escaped correctly. PostgreSQL requires 'E' prefix before backslash-escaped values. So, database log is filled with warnings (2 lines on each cached element).
Patch: drupal65-pgsql8x-patch2.diff.gz and and bug report (with same patch attached).
- MySQL truncates too long string values (longer than specified in DDL), that violates SQL standards (but more 'user friendly'). PgSQL refuses such records and raises an error. This problem occurs in many places, such as too long module name passed to watchdog() function, too long translated string in Locale module and so on. There is no easy way to fix it, you need to examine database logs and fix Drupal code step by step. I've fixed just one module name ('comment_subscribe' becomes 'comment_subscrib').
Contributed Drupal Modules
I use only ten 3rd-party modules. Most of them works under PgSQL without problems:
- Admin Block
- GeSHi Filter и GeSHi node
- Image, Image assist, Image Attach, ImageMagick Advanced Options
- Advanced Help
- Site Menu
- Taxonomy Menu
- CAPTCHA pack, Random CAPTCHA
Comment Subscribe Module changes
The Comment Subscribe is working with PgSQL only after heavy rewrite. This module uses many non-standard MySQL features such as if/ifnul (instead of case/coalesce), concat (instead of || operator) and multi-table update clause.
Patch: comment_subscribe-pgsql.diff and bug report.
MySQL to PgSQL Drupal Migration
There is simple: you need to convert database data structures, copy data, than change data source in drupal configuration. Sounds easy.
In real life it is not so easy:
- Backup and Migrate module does not compatible with PgSQL. Amount of required changes is not small.
- There is several mysql2pgsql conversion scripts does not do the job (I've tried several ones).
So, I opt to alternate way: create new empty site with same data structures, than move data by special migration script.
In Drupal any module may have own data tables in database. So real migration procedure is:
- Backup your existing site! Both code and database !!!
- Install Drupal from source, add patches (see above), add modules and module patches (if any). If you already have running Drupal site, you need only PgSQL-compatibility patches (see above).
Also, your existing site should have separate directory under sites/, not just sites/default.
- Create any non-used DNS name (i.e. www-new.mysite.com), create new PostgreSQL database, than install Drupal for this hostname on fresh database. You can supply any site data on installation phase, this data will be overwritten on data copy phase.
- Enable all modules, enabled on migrating site. Module enabling will create all tables/sequences in your PostgreSQL database.
- Run Magic Migration Script (see below). Your new site now have all data copied (with exception of cache_* tables and locale).
- If you use Locale module, export your Locale data on source site and import on new site.
- Change data source ($db_url in settings.php) in your old site settings.php to new database.
- That's all, folks!
Note: You should have shell-access to your server.
Magic drupal-mysql2pgsql.pl Script
Download it here: drupal-mysql2pgsql.pl.gz (do not forget to gunzip it).
The script requires Perl 5.x (I use 5.8, but Perl 5.6 should work) with DBI, DBD:Pg and DBD::mysql libraries.
Usage: drupal-mysql2pgsql.pl source-db dest-db, source-db - source MySQL database,
dest-db - destination PostgreSQL.
You either should passwordless access to both databases or should change the script to add your username/password. This is one-time use script, so I have not added many command-line parsing code.
How it works:
- Both databases are queried for list of tables. If any table exists in source DB, but not in destination one, the script will warn you.
- All data in destination tables is erased.
- Most data from source tables is copied into destination. Locale data and cache not copied.
- All sequencer initialized to max(column);
All destination data changes is done within transaction.
The script is provided AS IS. It works for me, you can change it as you wish.