[TOOL] MySQL to PostgreSQL migration

Howtos, instructions and links to related software. Do not ask questions here.
Redha
Bear Rating Trainee
Bear Rating Trainee
Posts: 4
Joined: 15 Jun 2013, 16:47

[TOOL] MySQL to PostgreSQL migration

Postby Redha » 27 Dec 2013, 16:20

This is a tool I wrote to migrate MySQL database to PostgreSQL. To use it
  • Runs on Windows and requires .NET 4
  • If your database is on a remote server, I suggest to dump it and load it locally. I used both WAMP & WAPP then uploaded the result db to my server.
  • Give it an empty PostgreSQL database. Don't load the schema.
tested with TinyTinyRSS v1.10. There was no schema update on v1.11 so it should work too.

Download exe and source code

I've been using TinyTinyRSS for 8 months so far. However with over +450, MySQL database has been growing and demanding more memory according to mysqltuner.pl . The site would become unbearably slow when loading more items or switching feeds. Last week I gave in and decided to give Postgresql a try, (my first time ever working with it). After spending several hours trying to migrate it remotely I gave in and wrote this tool. Ever since the performance has been great, 2-3 times faster than MySQL out of the box. I've been enjoying very responsive reader this week. So giving back to the community. Comes without warranty bla bla bla

Hope other people find it useful as well

koick
Bear Rating Trainee
Bear Rating Trainee
Posts: 2
Joined: 05 Mar 2014, 12:45

Re: [TOOL] MySQL to PostgreSQL migration

Postby koick » 05 Mar 2014, 12:47

I would love to use this tool, but your download link is returning a 404 not found error for me.

Redha
Bear Rating Trainee
Bear Rating Trainee
Posts: 4
Joined: 15 Jun 2013, 16:47

Re: [TOOL] MySQL to PostgreSQL migration

Postby Redha » 06 Mar 2014, 10:52

Fixed, could you try again.

koick
Bear Rating Trainee
Bear Rating Trainee
Posts: 2
Joined: 05 Mar 2014, 12:45

Re: [TOOL] MySQL to PostgreSQL migration

Postby koick » 06 Mar 2014, 10:59

Thanks, link now works, 1000 internet points for you. :D

Questions:
I need to just run TT-RSS Migration v6/bin/Debug/TT-RSS Migration.exe?
What's the difference between TT-RSS Migration.exe and TT-RSS Migration.vshost.exe?

Thanks!

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

Re: [TOOL] MySQL to PostgreSQL migration

Postby fox » 06 Mar 2014, 12:37

You can just attach the file here instead of some bullshit free hosting, op.
Attachments
TT-RSS Migration v6.7z
(3.06 MiB) Downloaded 191 times

Redha
Bear Rating Trainee
Bear Rating Trainee
Posts: 4
Joined: 15 Jun 2013, 16:47

Re: [TOOL] MySQL to PostgreSQL migration

Postby Redha » 09 Mar 2014, 14:40

koick wrote:Questions:
I need to just run TT-RSS Migration v6/bin/Debug/TT-RSS Migration.exe?
What's the difference between TT-RSS Migration.exe and TT-RSS Migration.vshost.exe?

Only "TT-RSS Migration.exe" is required. The vshost.exe one is leftover from build process I should had removed. Glad to hear it helped you.

It didn't cross my mind to attach it here. Thanks fox.

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

Re: [TOOL] MySQL to PostgreSQL migration

Postby XianPalin » 01 Apr 2014, 17:14

I have also been wanting to try out moving to postgresql, so thanks a bunch for making this!

I was running into some problems getting this working, so I grabbed the source and tinkered with it. Just a FYI, right off the bat, there was this error:

Code: Select all

 ";Password=" + options.MysqlPassword + ";Database=" + options.MysqlPassword
where it was sending the password as the database name. (It may have been on the pgsql connection string, I forget now)

I also fixed some other things - it works with v1.12, which is the only version I tested it on. I added a column or two that was missing. I also changed the hardcoded user from "postgres" to what the user passes in - at one point it tries to grant permissions to this user, and I don't have a user "postgres", so it crashed.

One thing to note - make sure you turn off feed updating before you run this. I was getting some foreign key errors, but then realized that it was because the table it was copying had new entries that had just been added.

I threw the modified version on github (my first github upload) here: https://github.com/XianPalin/TTRSSMigration
Let me know if you want me to take it down and I'll remove it.

I also attached an updated zip file with the updated code and an updated exe.

Thanks again!
Attachments
TT-RSS Migration v6.7z
(7.31 MiB) Downloaded 206 times

bastage
Bear Rating Trainee
Bear Rating Trainee
Posts: 16
Joined: 18 Mar 2013, 23:11

Re: [TOOL] MySQL to PostgreSQL migration

Postby bastage » 28 May 2014, 00:51

I'm probably being dense but the link above doesn't extract for me on a Windows 7x64 system using 7zip -- the error is saying it's not an archive. (I'm assuming that's a valid platform since this appears to be a windows utility.)

I don't have any particular issues with my small MySQL setup but would like to move to PostgreSQL since it seems preferred.

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

Re: [TOOL] MySQL to PostgreSQL migration

Postby XianPalin » 28 May 2014, 06:06

You're not dense, it looks like the forum isn't serving up the entire file. It should be 7-8mb, but instead it's only a little over a MB before it acts like it completed downloading. This is why 7zip is throwing an error.

I had deleted the files but I re-downloaded the code and compiled it again. My Dropbox mirror file is way smaller because it only includes the executable debug directory and not all the code as well, so it should be 500-600k. Let me know if you have any issues. Open a command prompt and run TT-RSS Migration from there.

https://www.dropbox.com/s/qxm3td0o736sp ... gration.7z

bastage
Bear Rating Trainee
Bear Rating Trainee
Posts: 16
Joined: 18 Mar 2013, 23:11

Re: [TOOL] MySQL to PostgreSQL migration

Postby bastage » 28 May 2014, 10:19

Thanks ..!

I won't have time to muck around with things for at least a few days but the Dropbox file looks good and the executable runs so I'll report back.

Gooberslot
Bear Rating Trainee
Bear Rating Trainee
Posts: 1
Joined: 13 Jun 2014, 20:04

Re: [TOOL] MySQL to PostgreSQL migration

Postby Gooberslot » 13 Jun 2014, 20:07

I tried this tool but I got an error:

Code: Select all

Unhandled Exception: Npgsql.NpgsqlException: ERROR: 22021: invalid byte sequence
 for encoding "UTF8": 0x00
   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArg
s rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowU
pdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMappi
ng tableMapping)
   at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable,
DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
   at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
   at TT_RSS_Migration.Program.CopyTables(String[] tablesList, MySqlConnection m
ysqlConnection, NpgsqlConnection psqlConnection) in c:\Users\Zarek\Desktop\TT-RS
S Migration v6\Program.cs:line 156
   at TT_RSS_Migration.Program.Main(String[] args) in c:\Users\Zarek\Desktop\TT-
RSS Migration v6\Program.cs:line 43

Gizmoh
Bear Rating Trainee
Bear Rating Trainee
Posts: 5
Joined: 07 Oct 2013, 15:15

Re: [TOOL] MySQL to PostgreSQL migration

Postby Gizmoh » 18 Sep 2014, 15:58

Any chance to get this for the updated database schema?
I only get the message that this version (1.13) is not supported.

Thanks
gizmoh

adventclad
Bear Rating Trainee
Bear Rating Trainee
Posts: 2
Joined: 02 Mar 2015, 16:33

Re: [TOOL] MySQL to PostgreSQL migration

Postby adventclad » 13 Sep 2016, 18:57

<thread_necromancy/> I would like to migrate my MySQL DB to PostgreSQL. Is there now any other tool (that also runs on linux) to do that ?

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

Re: [TOOL] MySQL to PostgreSQL migration

Postby fox » 13 Sep 2016, 19:21

you can export/import your starred articles through the plugin (and feeds & settings via opml)

User avatar
kierun
Bear Rating Trainee
Bear Rating Trainee
Posts: 27
Joined: 16 Feb 2016, 11:43

Re: [TOOL] MySQL to PostgreSQL migration

Postby kierun » 13 Sep 2016, 22:15

fox wrote:you can export/import your starred articles through the plugin (and feeds & settings via opml)


I did just that a few months (year?) ago and it worked just fine.

Do take backups though…


Return to “Knowledge Base”

Who is online

Users browsing this forum: No registered users and 2 guests