- Figure out which modules might not support Postgresql. I put this part first since it might take you a while to track down upgrades, patches, or fixes you apply yourself. Find out all the modules that you are using.
# modules=`echo "SELECT name FROM system WHERE status=1 AND type='module'" | mysql --skip-column-names -u <em>prototype -p prototype`Find all the install files that contain the word db_type:
# cd /path/to/modules
# installs=`for module in $modules; do find . -name $module.install -exec grep db_type {} /dev/null \; ; done | awk -F: '{ print $1 }' |sort | uniq`Find all the install files that do not contain the word pgsql:
# for install in $installs; do grep -q 'pgsql' $install /dev/null || echo "$install does not contain psql schema code."; done
./devel/devel.install does not contain psql schema code.With this list you should see if you can find a patch like this one:
http://drupal.org/files/issues/devel_18.patch
Or perhaps you need to upgrade the version of the module you are using. Otherwise you have to leave it off the list when we update the fresh database we are about to create.
- Create a fresh database on you development server. This way we can get a fresh schema for importing into a second database. This works better than try to import data into an existing fresh installation.
$ createdb -U <em>drupal5 -W drupal5fresh -E UNICODE
CREATE DATABASE
NOTE: You should already have a drupal5 user setup that can add databases - Checkout a fresh installation of your drupal code -or- use your existing development setup
- Update sites/defaults/settings to point to a new database
- Perform a normal installation (e.i. goto http://127.0.0.1/install.php)
- Goto the Admin page and bring up the list of modules. By hand add all the modules based on the list you produce with the code below. At this point leave out any modules that don't support PostgreSQL.
# for module in $modules; do find . -name $module.info -exec grep ^name {} \; ; done | perl -pi -e 's/name = //' | sort
Actions
Aggregator
"Basic event"
Block
Captcha
Content
Devel
Devel Node Access
Drake
Event
. . .NOTE: If you get database errors or warning, then the code isn't supporting PostgreSQL very well. So you need to find a solution to fix it. I had problems with Panels, Textimage and Webform. Make sure you submit patches back to the project, or update tickets that note you success or failure. For example there was a small bug in the panels install I had to change dbtype to db_type. Then I uninstalled it (with errors), then reinstalled it and it worked.
- Now we dump out the schema but now the data.
# pg_dump -h 127.0.0.1 -U drupal5 -W -s drupal5fresh > /var/tmp/drupal5fresh_schema.sql - Create a second database to import the schema with no data.
# createdb -O drupal5 drupal5pgsql -E UNICODE
CREATE DATABASE - Import the schema into the second database
# cat /var/tmp/drupal5fresh_schema.sql | psql -h 127.0.0.1 -U drupal5 -W drupal5pgsql - Dump you existing MySQL database
# mysqldump -t --skip-opt --compact prototype > prototype_data.sql - Convert it. There are two tools, the first one has been worked on recently. But I had success with the second one, during a complete conversion, it failed when I removed the CREATE TABLE syntax. I did have delete the devel_queries, devel_times, cache_menu, cache_views rows. They were just to big or a waste of time.
NOTE: I had to modify the mysql2pgsql.perl code to remove the E'' notation. I am not using PostgreSQL 8.1.
#s/'(.*?)'([,)])/E'$1'$2/g;
# for the E'' see http://www.postgresql.org/docs/8.2/interactive/release-8-1
.html# ./mysql2pgsql.perl --debug prototype_data.sql drupal5pgsql_data.sqlhttp://gborg.postgresql.org/project/mysql2psql/projdisplay.php
http://www.omnistarinc.com/~fonin/projects/my2pg/my2pg.pl - Import the data, with a little luck you will have a full functional conversion.
# cat /var/tmp/drupal5pgsql_data.sql | psql -h 127.0.0.1 -U drupal5 -W drupal5pgsqlNOTE: If you are working with a lot of data, I would run script first. This way you can go back over the output for any errors. I had a couple of CCK tables that didn't get moved over. I am just going to do those by hand.
- Take care of the sequences
# echo "SELECT * FROM sequences" | mysql --skip-column-names prototype | awk '{ print "SELECT pg_catalog.setval(\x27"$1"_seq\x27,"$2",false);" }' | psql -h 127.0.0.1 -U drupal5 -W drupal5pgsqlNOTE: There are any missing sequences there is a problem with the module. It isn't correctly creating the sequence during installation.
NOTE: I suppose you could use a converter on your schema, but I was using MySQL 4.1 and wanted the constraints that came with Postgresql. I was able to just convert it with no problems. I started with MySQL 4.1 and moved to PostgreSQL 7.4