Moving from PostgreSQL to MySQL

It's no secret that Drupal 6.x is not very good friends with PostgreSQL. The kernel is friendlier, but individual contrib modules are not always. And now I got tired of observing occasionally spilling warnings and patching — I decided to move to MySQL.

Google stubbornly gave out migration in all sorts of requests for this kind of migration in the opposite direction - MySQL to PostgreSQL, it is understandable why, but not happy. If you throw Drupal out of context, then begin to be paid products and all sorts of not perfect scripts. In the end, I decided to do everything myself handles.

Experimental: drupal-6.8 , mysql-5.0.70-r1 , postgresql-8.0.15 .

Sequence:

1. I don’t know why, but somehow it’s become so custom that I’m not putting the experiments on test installations, but on production right away. Therefore, first of all, disable access to the site. I did it on the frontend server (nginx): I allowed my IP address, but I forbade everything else. And not because I don’t trust the standard Drupal maintenance mode, but because it would probably take a couple of times to get the installation process started from scratch.
2. We go to the admin panel, disable and clean all sorts of caches, indexes and logs that are stored in the database - there will be more benefit to them when transferring them after.
3. Save the list of included modules. I used the plugin for Firefox - ScrapBook.
4. Back up the current database data, without the schema:
   pg_dump -U postgres -D -d drupal > old_data.sql
   Cut from this dump everything except INSERTs.
5. We create for Drupal'a base and user in MySQL.
6. We transfer the current settings.php somewhere and start the Drupal installation process from scratch, specifying the user and database created earlier.
7. After installation, we make the necessary edits in settings.php and enable the necessary modules, referring to the previously saved list. This operation will create the necessary modules in MySQL.
8. We ask phpmyadmin to dump the structure of MySQL tables with their forced DROP - new_schema.sql .
9. Combine dumps:
   cat new_schema.sql old_data.sql > install.sql
   
10. In the resulting search-replace file, we carefully replace the column names of the type “type” with type (with quotes on without quotes).
11. Checking:
    mysql -u drupal -p drupal < install.sql
    In my case, MySQL cursed solely the column names. Repeat the previous and this steps until MySQL doesn’t eat install.sql .
    
12. I didn’t need any additional actions - the site returned to work, but already in MySQL.
13. And do not forget to turn back caches and access to the site from the outside.

On all-pro-everything took about half an hour - google longer.
')
PS Do not judge strictly - this is my first post on habr. Before that, there was a “Chukchi not a writer - a Chukchi reader”.