By canen on

Hello all,

I was wondering if anyone tackled this task before, coverting from postgres to mysql. Searching on google only seem to find results that go the other way. I was hoping for some help here before I attempt this myself.

Thanks in advance for any help.

⋅ Categories: Drupal 4.5.x or older

Comments

canen’s picture

canen commented

I did it, just in case anyone has to ever do this.

Using Drupal version 4.5.x. There were some issues mainly with table definitions not being the same, for example, the url_alias table definition had dst and src switched in the table creation scripts from both database. This caused a problem after the move with drupal not being able to find the old pages, only found this out after comparing the table definitions from the two files. The node table also had the created field in a different position which caused a problem as well. Basically all the problems could have been avoided by checking the table creations scripts and making sure they matched.

So here are the steps:

1. Backup the postgres database
2. dump data from database, remove all postgres specific statements, such as sequences and database user access information. You may also want to ignore the search_index and cache tables here, they can always be rebuilt.
3. check database.mysql and make sure order of fields in tables match those in database.pgsql
4. remove insert statements from database.mysql
5. create database using database.mysql
6. import data from pg_dump created file
7. If all goes well, insert sequence values from postgres to the sequence table in mysql. \ds gets you the list of sequences in psql. In phpPgAdmin there is also a link to get all the sequences.

That should be it.

sevo’s picture

sevo commented

Dumping data-only with insert statements and column names (pg_dump -Da) will deal with different column orders. The only thing that needs to be manually rewritten in the resulting dump would be the sequence initializations.

Sevo