Proper way to migrate from MySQL to PostgreSQL

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
hrk
Bear Rating Disaster
Bear Rating Disaster
Posts: 75
Joined: 24 Apr 2013, 12:39

Proper way to migrate from MySQL to PostgreSQL

Postby hrk » 26 May 2013, 19:43

Last edited by hrk on 02 Apr 2014, 16:22, edited 1 time in total.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Proper way to migrate from MySQL to PostgreSQL

Postby fox » 26 May 2013, 20:01

I have no words.

hrk
Bear Rating Disaster
Bear Rating Disaster
Posts: 75
Joined: 24 Apr 2013, 12:39

Re: Proper way to migrate from MySQL to PostgreSQL

Postby hrk » 26 May 2013, 22:21

Knowing you, it means I did a stupid thing, or asked a stupid question. Or both, mind you.

So... is there a simpler way to move data from MySQL to PostgreSQL?

In another thread, somebody used a ruby gem, but from what I understood, it converted the existing mysql schema to the postgresql one. I wanted to use the original/official pgsql you wrote. Were I dumb in doing that? (I'm prepared)

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Proper way to migrate from MySQL to PostgreSQL

Postby fox » 26 May 2013, 22:28

You can move some of the data using xml import/export plugin, anything else is not supported and is bound to seriously screw up your database.

Edit: in general I would suggest importing your OPML (which includes tt-rss settings and categories) into a clean database and starting from that.

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Proper way to migrate from MySQL to PostgreSQL

Postby gbcox » 26 May 2013, 22:33

I would recommend you start here:

http://wiki.postgresql.org/wiki/Convert ... eSQL#MySQL

I've recently converted but haven't had the interest to migrate my mysql database, there was only a few months data in it anyway.

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Proper way to migrate from MySQL to PostgreSQL

Postby gbcox » 26 May 2013, 22:38


hrk
Bear Rating Disaster
Bear Rating Disaster
Posts: 75
Joined: 24 Apr 2013, 12:39

Re: Proper way to migrate from MySQL to PostgreSQL

Postby hrk » 26 May 2013, 22:44

I'd rather preserve everything, but I understand that it is not supported and thus easily cause for the "screwing up".

I tried your approach, but it didn't work well enough. Some feeds "lost" their category, and categories order wasn't preserved. Is this to be considered a bug or is it intentional?

Preferences were preserved at the point of customized CSS (as it seems), that's a plus, thank you.

@gbcox: I'll give a try to one of those tools, but they appear to do the same thing my tool does.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Proper way to migrate from MySQL to PostgreSQL

Postby fox » 26 May 2013, 22:56

Category order will not be preserved, I guess you'd have to waste a few minutes fixing it. Categories shouldn't be "lost".

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Proper way to migrate from MySQL to PostgreSQL

Postby gbcox » 26 May 2013, 23:27

There is some work going on regarding importing the feed cache from Google Reader...
viewtopic.php?f=8&t=1962&p=11230

It's not quite working yet, but that may be another alternative for you.

cqrt
Bear Rating Disaster
Bear Rating Disaster
Posts: 70
Joined: 05 Apr 2013, 00:37

Re: Proper way to migrate from MySQL to PostgreSQL

Postby cqrt » 26 May 2013, 23:56


lotrfan
Bear Rating Disaster
Bear Rating Disaster
Posts: 73
Joined: 18 Mar 2013, 04:42

Re: Proper way to migrate from MySQL to PostgreSQL

Postby lotrfan » 27 May 2013, 10:59

You can try using my ... It's basically fox's, but it saves all articles (not just starred/archived), as well as some more metadata (unread state, some more times). It should also preserve HTML in articles.

Before using it (especially the importer), make a backup.

Usage is pretty much the same as the import_export plugin, except the name is import_export_all.

Also note that I wrote (and tested) this a few TT-RSS versions (and schema versions) ago, so it's possible it won't work any more. If that's the case, let me know, and I'll see if I can fix it.

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Proper way to migrate from MySQL to PostgreSQL

Postby sleeper_service » 27 May 2013, 12:07


lotrfan
Bear Rating Disaster
Bear Rating Disaster
Posts: 73
Joined: 18 Mar 2013, 04:42

Re: Proper way to migrate from MySQL to PostgreSQL

Postby lotrfan » 27 May 2013, 20:19


User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Proper way to migrate from MySQL to PostgreSQL

Postby sleeper_service » 28 May 2013, 07:14


lotrfan
Bear Rating Disaster
Bear Rating Disaster
Posts: 73
Joined: 18 Mar 2013, 04:42

Re: Proper way to migrate from MySQL to PostgreSQL

Postby lotrfan » 28 May 2013, 08:20

Ok... try it now.

If it doesn't work, try it again after disabling gzip output in config.php (the setting is ENABLE_GZIP_OUTPUT).

If it still doesn't work, post the output of /backend.php?op=pluginhandler&plugin=import_export_all&subop=debug

If you're curious, the error is generated because PHP runs out of memory when loading the (probably quite large) XML file.


Return to “Support”

Who is online

Users browsing this forum: No registered users and 13 guests