Combine server migration and upgrade

evanb · Post by **evanb** » Mon Oct 17, 2005 6:01 am

I'm preparing to move my v.0.7.1 blog to another server, updating to v0.8.5 at the same time. To complicate things further, the old server runs PostgreSQL, the new one MySql. Finally, I have only ftp access to the new server, no shell account.

What is the best way to accomplish this migration?

Thanks.

Post by **garvinhicking** » Mon Oct 17, 2005 9:26 am

Hehe, that's a tough one.

1. Migrate your database from MySQL to postgreSQL. Export a ANSI-compatible MySQL dump which you can then upload to your postgreSQL database.

2. Copy all your old files to the new hosts (Use serendipity 0.7.1 still). On the new host, check that all permissions are like they were (s9y needs to write serendipity_config_local.inc.php, .htaccess, uploads and to the "." core directory!).

3. Edit your serendipity_config_local.inc.php file. Change the mentioned settings there so that they point to your postgreSQL DB credentials.

4. Go to your favourite postgreSQL client and check out the serendipity_config DB table. Adjust all old paths and DB config to the new server.

5. Check if your serendipity installation now runs properly. If yes, proceed. If not, report back here.

6. Upload the Serendipity 0.8 release version to your blog and read http://www.s9y.org/63.html for special notes. Then upgrade and you're done.

Best regards,
Garvin

evanb · Post by **evanb** » Wed Jan 11, 2006 5:46 am

I was beginning to think I'd never get to this but, finally, I did. So, I thought I'd share my experience in case it case it might prove useful to someone.

First, Garvin got my situation a bit backwards. I needed to move FROM PostgreSQL TO MySql. Not a common migration, judging from my Google searching. Also, I had shell (including root) access to the source machine (my home Linux box), but only FTP and phpMyAdmin access to the intended target.

I began with a flash of inspiration. Since I had to convert the data at some point, why not do it where I had the better access? So, I did a backup dump and upgraded my home system to v0.9.1. (Outstanding upgrade support, by the way. The two-point upgrade from 0.7.3 went without a hitch.) Once I was certain the upgraded installation worked, I did another dump and started using the table creation commands in the Postgres dump to build the tables on the target through phpMyAdmin. I got a litte concerned about my ability to translate between Postgres's database structure and MySql's. (E.g., Postgres implements auto-increment fields as a sequence plus a DEFAULT setting for the field that generates values from the sequence; MySql doesn't have a bool type; there were lots of specific constraints in the Postgres dump, and I couldn't figure out how/if they would translate to MySql; etc.) After some time I thought I'd have a go at letting S9Y build the MySql tables for me. So, I installed it on the target and let it do just that.

So far, so good. That set up the framework and non-content tables. Now I was left with migrating my blog entries and related data. By having Postgres dump the data as INSERT statements instead of its default COPY commands, I was able to copy and paste them into phpMyAdmin's query text box, for the most part, verbatim. I had to remove the double quotes Postgres places around field names that match key words, such as "type" and "path". When I forgot, phpMyAdmin was happy to remind me.

Multiple INSERT statements can be pasted and executed at a time, so the process didn't take very long. Since S9y's installation creates a user, I manually edited the author table (I only have two entries) to match the values from my old installation instead of using the dump directly.

My migration was made easier by the fact that I didn't have very many blog entries, but with sufficient care I don't think this would be an insurmountable task even for large databases. In general I was careful that record ID values weren't changed during the migration, but these values are part of the data dump so it was not difficult to do. If your migration gets hopelessly bodged you can just drop all the tables and start over.

Thanks to Garvin for his quick reply (even though it languished for a few months) that give me a handle on how the task might be accomplished, and the sense that it was even feasible.

Post by **garvinhicking** » Wed Jan 11, 2006 2:37 pm

Great you got it working then! And thanks for sharing, I indeed think it will help a lot of people.

Sorry though for misunderstanding the migration path in first instance.

Best regards,
Garvin