Proper way to migrate from MySQL to PostgreSQL

[hrk]

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?

[fox]

I have no words.

[hrk]

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)

[fox]

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]

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]

...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]

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.

[fox]

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

[gbcox]

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]

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]

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.

[sleeper_service]

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]

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.

[sleeper_service]

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]

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.