Moving from PostgreSQL to MySQL

It's no secret that Drupal 6.x is not very good friends with PostgreSQL. Rather, the kernel is friends, but individual contrib modules are far from always. And now I'm tired of observing periodically falling warning'i and applying patches - I decided to move to MySQL.

Google stubbornly issued migration in the opposite direction to all kinds of queries about this kind of move - MySQL to PostgreSQL, it’s understandable why, but it’s not good. If you throw Drupal out of context, then paid products and all sorts of non-ideal scripts begin to be located. In the end, I decided to do everything myself with pens.

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

  1. I don’t know why, but it so happened that I didn’t put experiments on test installations, but immediately on production. Therefore, the first thing we turn off access to the site. I did this on the frontend server (nginx): I allowed my IP address, and forbade everyone else. And not because I do not trust the standard Drupal maintenance mode, but because it would probably be necessary to drive the installation process from scratch a couple of times.
  2. We go to the admin panel, turn off and clean all sorts of caches, indexes and logs that are stored in the database - the crap from them during the transfer will be more than useful after.
  3. We save the list of included modules. I used the plugin for Firefox - ScrapBook.
  4. We backup the data of the current database, and without a scheme:
    We cut everything from this dump except for INSERTs.
  5. We create a database and user for Drupal in MySQL.
  6. We transfer the current settings.php somewhere and start the process of installing Drupal from scratch, indicating the user and database created earlier.
  7. After installation, make the necessary changes to settings.php and enable the necessary modules, checking the previously saved list. This operation will create the tables required by the modules in MySQL.
  8. We ask phpmyadmin to dump the structure of the MySQL tables with their forced DROP - new_schema.sql .
  9. Merge 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 and without quotes).
  11. Check:
    In my case, MySQL swore solely on column names. We repeat the previous and this steps until MySQL as a whole eats up install.sql .
  12. I did not 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.

It took about half an hour for all-about-all - Google longer.

P.S. Не судите строго — это мой первый пост на habr. Before that, there was "a Chukchi not a writer - a Chukchi reader."