migrating

[Stu McGregor]

i'm migrating from one host to another and this means migrating from postgresql to mysql. i'm getting heaps of errors as the postgresql dump file doesn't seem to work with mysql import.

what do i do?

[garvinhicking]

I suggest this:

1. Install a fresh serendipity on your new host.

2. Now only dump the contents of your postgreSQL installation into a SQL file (INSERT INTO ...)

3. Now delete the contents of your fresh serendipity tables (do not drop the tables!) and import the SQL.

Inserts should work fairly well accross systems, and you can use the native s9y DB structure now.

Sadly I don't know postgresql, so I cannot tell if there is a tool like phpMyAdmin which emits ANSI SQL compatible SQL dumps.

Best regards,
Garvin

[stu McGregor]

i'm not at all familiar with database stuff. can you give me the exact syntax and where to type it in with phpadmin?
sorry to be a pain, but i don't want to lose over a year's worth of blogging and comments when i move to my new provider.

[Guest]

I can only help you on the MySQL side of things, so now I expect that your db.sql dumpfile from postgresql only contains INSERTs.

And I assume that you have installed serendipity with the "serendipity_" prefix both for the fresh and the old installation.

Now you enter phpmyadmin, click on the database you installed serendipity into. In the left frame you'll see each table. You need to click every single one of them, and then the right frame refreshes..

On the right frame, click on the "EMPTY" icon on the upper right. NOT the "DROP" icon! Then say yes to the question, and all tables of the fresh s9y installation will be cleared.

Then you click on the database name where you installed s9y into. This will refresh the right frame, and there you'll see a "SQL" menu item. Click on that.

In the next page, you can upload your db.sql dumpfile from postgresql. Select the file from your harddisk via "Browse..." and then execute the query.

It's all pretty easy, actually.

The most problematic issue is to get a right postgresql dump with the data and not the structure. But I'm sure there are tools for this.

Best regards,
Garvin

[stu McGregor]

thanks Garvin, appreciate your help.

some of the tables were in different orders for some reason between the installations, once i fixed that the inserts happened ok.

also, in the old installation some true and false statements wouldn't come through as 'true' and 'false'. a quick search and replace sorted that.

also, deleting the headers and footers with the set language etc was essential: it would always error out on this.

i've lost all my apostrophes and em dashes. but i don't care that much. at least it's all good now.

yay!

stu

[garvinhicking]

Great you got so much figured out and working now

I suppose you might be able to search+replace all the apostrophes and em dashes, but you must say if that's worth the effort

anyways, hope you'll continue to have fun

Best regards,
Garvin