Proper way to migrate from MySQL to PostgreSQL

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
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 » 29 May 2013, 20:43

hrk wrote:If you read it carefully, it's the same error I got with my own script. :( We're back at square 1 [except for the migration of user settings which works really fine].


Can you dump and attach your schema for me to take a look at, via phppgadmin or CLI below.

Code: Select all

pg_dump -s -Fp --file=schema.sql -n public your_database_name

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 » 29 May 2013, 22:12

sleeper_service wrote:well, I think it blew up my database.


I haven't had any issues with it...

Code: Select all

$ sudo -u http ./update.php --feeds
[17:52:59] Lock: update.lock
[17:52:59] Scheduled 85 feeds to update...
[17:52:59] Base feed: http://allanmcrae.com/feed/
[17:52:59]  => , 18 2
[17:52:59] Base feed: http://bit-player.org/feed
[17:52:59]  => , 20 2
[17:53:04] Base feed: http://blog.chromium.org/feeds/posts/default
[17:53:04]  => , 79 2
[17:53:06] Base feed: http://blog.xkcd.com/feed/
[17:53:06]  => , 69 2
[...snip...]
[17:55:32] Sending digests, batch of max 15 users, headline limit = 1000
[17:55:32] All done.
[17:55:32] /srv/http/cache/simplepie: removed 0 files.
[17:55:32] /srv/http/cache/images: removed 0 files.
[17:55:32] /srv/http/cache/export: removed 0 files.
[17:55:32] /srv/http/cache/upload: removed 0 files.
[17:55:32] Removed 0 old lock files.
[17:55:32] Removing old error log entries...
[17:55:32] Feedbrowser updated, 85 feeds processed.
[17:55:32] Purged 335 orphaned posts.
[17:55:32] Cleaned 0 cached tags.

although I feel like there should be timestamps on the " => " lines...

I'm using a new postgres install, version 9.2.4 on a fresh database. TT-RSS is running commit df2655e01566f3c59337a020b1d70054ff585d75 (from Sunday). PHP is version 5.4.15.

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 » 29 May 2013, 22:16

hrk wrote:If you read it carefully, it's the same error I got with my own script. :( We're back at square 1 [except for the migration of user settings which works really fine].

I thought I'd give that other guy's export/import thing a try, since it was running 'inside' (sort of) ttrss, instead of more directly whacking the database, but... *shrug* all I can say is, I'm glad I did a zfs snapshot before I tried it. *grins*

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 » 29 May 2013, 22:18

lotrfan wrote:
sleeper_service wrote:well, I think it blew up my database.


I haven't had any issues with it...


I already had several days worth of data in the database, I wanted to migrate in old data from the old one, in addition.

but, I 'spose it's not worth the effort at this point.

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 » 29 May 2013, 23:13

sleeper_service wrote:*shrug* all I can say is, I'm glad I did a zfs snapshot before I tried it. *grins*

Always a good idea...

sleeper_service wrote:I already had several days worth of data in the database, I wanted to migrate in old data from the old one, in addition.

but, I 'spose it's not worth the effort at this point.


In theory, it should work with old data... but apparently it doesn't. Have you tried the (original) import_export plugin? If that one doesn't work, then we can blame fox :D

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 » 29 May 2013, 23:22

lotrfan wrote:
sleeper_service wrote:*shrug* all I can say is, I'm glad I did a zfs snapshot before I tried it. *grins*

Always a good idea...

sleeper_service wrote:I already had several days worth of data in the database, I wanted to migrate in old data from the old one, in addition.

but, I 'spose it's not worth the effort at this point.


In theory, it should work with old data... but apparently it doesn't. Have you tried the (original) import_export plugin? If that one doesn't work, then we can blame fox :D

blame fox at your own risk, I'm not that brave... and, no, I haven't. ;)

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 » 30 May 2013, 00:24

It's been a while since I checked whether import_export works, but I think it should. If it doesn't indeed I screwed something up.

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 » 08 Jun 2013, 16:59

Okay, today I decided to give a try again to my migration tool, to understand what was wrong. As it happened, it was a (conceptual) mistake on my part: since I am "copying" data from the tables, I was using the very same keys in the INSERT statement, but since the datatype was declared as serial, the sequence was never proprerly advanced (as it happens automatically when you don't use the column int the INSERT statement).
After migrating a table, I added "SELECT setval(tableName + "_" + columnName + "_seq", lastReadValue)" and now everything is working fine. :)

EDIT: and now I am left wondering how comes that MySQL reports a database size of 86MB whereas PGSQL has a 40MB tablespace usage... PG FTW? :D

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 » 09 Jun 2013, 03:08

hrk wrote:Okay, today I decided to give a try again to my migration tool, to understand what was wrong. As it happened, it was a (conceptual) mistake on my part: since I am "copying" data from the tables, I was using the very same keys in the INSERT statement, but since the datatype was declared as serial, the sequence was never proprerly advanced (as it happens automatically when you don't use the column int the INSERT statement).
After migrating a table, I added "SELECT setval(tableName + "_" + columnName + "_seq", lastReadValue)" and now everything is working fine. :)

EDIT: and now I am left wondering how comes that MySQL reports a database size of 86MB whereas PGSQL has a 40MB tablespace usage... PG FTW? :D

does that include empty space from deleted stuff on the mysql side?

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 » 10 Jun 2013, 01:19

PGSQL's size is a very rough "du -hs" on the tablespace directory. MySQL's size was calculated with:

Code: Select all

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;

XianPalin
Bear Rating Trainee
Bear Rating Trainee
Posts: 21
Joined: 16 May 2013, 23:10

Re: Proper way to migrate from MySQL to PostgreSQL

Postby XianPalin » 10 Jun 2013, 01:41

Is there really much of a speed difference between the two for ttrss?

Saliency
Bear Rating Trainee
Bear Rating Trainee
Posts: 49
Joined: 27 Mar 2013, 20:05

Re: Proper way to migrate from MySQL to PostgreSQL

Postby Saliency » 10 Jun 2013, 17:53

I have successfully done a full migration.
My shit still works, It still works after upgrading tt-rss from .8 to .9

I was going to write it up but was to lazy to refine the process.
Also I am sure that others data will break in different ways.

Having done this my advice.
----------------------------------------

Ask yourself two question
1) Why do I care about mySQL vs pgSQL?
2) How much do I care about all my data?

If you want all your data and you want pgSQL for some reason you have some work ahead of you.
Ask yourself again do I really give a fuck?

If you are fine losing some data use the tool. It is pretty good.
If you want all your data, do you really need to move to pgSQL as long as fox is supporting mySQL? (pg might perform marginally better but fuck...)
----------------------
1) I converted because I am hacking out some code for my personal use and connecting directly to the DB. I also have a overblown hate of mySQL...
2) I had over 60k of shared articles. I forget all the things the tool missed but it did miss a lot of stuff I cared about.
2b) My notes say.
** opml resets label id to start.
** Export does not export archived feeds.
** Export article format looks like shit for some reason.

----------------------
First I suggest you try to find a professional tool to do the translation.
I was unable to find a tool that did this. I tried navecat and bombed on boolean fields. (I know wtf)
Perhaps you can find a better tool.
----------------------
I have attached my conversion notes. They miss lots of shit I am sure and have not been updated. For instance more then a few times I just edited the output sql instead of updating the generation script.
Attachments
MtP.txt
SQL/Crappy Instructions.
(9.42 KiB) Downloaded 109 times

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 » 14 Jun 2013, 21:30

Well, my tool is more automated. :) Just give it the connection URLs, username and password and run it. It will return some errors when trying to copy the admin user and ttrss-forum feeds, which are created in the schema script, but they can be easily ignored. Everything else is copied.

You need to compile it with javac and then run it w/ MySQL and PostgreSQL's JDBC drivers in the classpath. I'll see if I feel like making it a bit more user friendly but... honestly it's just a "run once, forget about mysql forever" kind of thing... does it need polish?!
Attachments
Sync.txt
(4.98 KiB) Downloaded 117 times

XianPalin
Bear Rating Trainee
Bear Rating Trainee
Posts: 21
Joined: 16 May 2013, 23:10

Re: Proper way to migrate from MySQL to PostgreSQL

Postby XianPalin » 15 Jun 2013, 00:39

hrk, could you be a little more specific on how to run this? I've downloaded the mysql and postgresql jar files and tried to include them in the classpath (I also just have your script and those files in the same directory) and can't get it to work. I wanted to fiddle with it and see how PostgreSQL performed.

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 » 17 Jun 2013, 11:17

Ok, I made some small changes and pushed everything to github: https://github.com/hrk/tt-rss-mysql2pgsql

Download the contents of the dist directory (if you're not familiar with github, just download everything as a zip, unzip and open the dist directory).

Edit config.properties to your liking and then launch the tt-rss-mysql2pgsql.jar file.


Return to “Support”

Who is online

Users browsing this forum: No registered users and 9 guests