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

e:
====================
The tool is here https://github.com/hrk/tt-rss-mysql2pgsql
Download the contents of the dist directory, edit config.properties w/ the right values, launch the jar. Three (or five) errors you can ignore, and then you're done. More docs at the link.
====================


After hearing of PGSQL's marvelous performance, I'd like to migrate my existing tt-rss installation to PG, and I'd like to keep everything (users, articles, feeds, categories, ...)

I wrote a tool (you can have a laugh at me here if you want) which basically did a SELECT * FROM every table in the order they are created in the ttrss_schema_pgsql.sql file. For every row, it built a INSERT INTO (...).

The procedure completed without errors (except for the already existing admin user and the tt-rss feeds which are created in the schema script itself). I could log in without issues (using the default password for the admin user) and everything seemed to work. However when I started the update daemon I got the following errors in the log:

Code: Select all

[13:35:22/5712] Scheduled 49 feeds to update...
[13:35:22/5712] Base feed: http://feeds.feedburner.com/Androidlab
[13:35:22/5712]  => 2013-05-26 11:10:12, 48 1
PHP Warning:  pg_query(): Query failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block in /home/httpd/roma.sineo.it/htdocs/tt-rss/classes/db/pgsql.php on line 38
PHP Fatal error:  Query INSERT INTO ttrss_error_log
            (errno, errstr, filename, lineno, context, owner_uid, created_at) VALUES
            (2, 'pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "ttrss_entries_pkey"
DETAIL:  Key (id)=(3) already exists.', 'classes/db/pgsql.php', '38', '', NULL, NOW()) failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block in /home/httpd/roma.sineo.it/htdocs/tt-rss/classes/db/pgsql.php on line 43
PHP Warning:  pg_query(): Query failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block in /home/httpd/roma.sineo.it/htdocs/tt-rss/classes/db/pgsql.php on line 38
PHP Fatal error:  Query INSERT INTO ttrss_error_log
            (errno, errstr, filename, lineno, context, owner_uid, created_at) VALUES
            (256, 'Query INSERT INTO ttrss_error_log
            (errno, errstr, filename, lineno, context, owner_uid, created_at) VALUES
            (2, ''pg_query(): Query failed: ERROR:  duplicate key value violates unique constraint "ttrss_entries_pkey"
DETAIL:  Key (id)=(3) already exists.'', ''classes/db/pgsql.php'', ''38'', '''', NULL, NOW()) failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block', 'classes/db/pgsql.php', '43', '', NULL, NOW()) failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block in /home/httpd/roma.sineo.it/htdocs/tt-rss/classes/db/pgsql.php on line 43
[13:35:24/5709] removing lockfile (5709)...
[13:35:24/5516] [reap_children] child 5709 reaped.
[13:35:24/5516] [SIGCHLD] jobs left: 0


I am not very familiar with PGSQL, I wonder if it's related to its internal sequencies or if it is something stupid on my part. Is there another way to migrate an installation to PGSQL without starting from scratch?
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

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


Yuppers...I'd prefer to have the old data also, but for me definitely wasn't worth the potential headaches.

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

fox wrote: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.


This^^

Follow the advice of the wise old fox, while my migration was relatively successful using the ruby gem, I did have to spend several hours fixing the constraints foreign keys. Import your OPML and start afresh.

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 import/export plugin... 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 wrote: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.


it blows up my plugin list with an error on line 234 of init.php.

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

sleeper_service wrote:it blows up my plugin list with an error on line 234 of init.php.


It should work (or at least load) now... It was using a new shorthand in PHP 5.4 to create arrays. Thanks for reporting.

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 wrote:
sleeper_service wrote:it blows up my plugin list with an error on line 234 of init.php.


It should work (or at least load) now... It was using a new shorthand in PHP 5.4 to create arrays. Thanks for reporting.


I tried to do an export, and when it got to the point of saying "you can get the file here" when I clicked it, I got:

File not found Firefox can't find the file at .../backend.php?op=pluginhandler&plugin=import_export_all&subop=exportget.

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 11 guests