Migration from MySQL to PostgreSQL

Having run tt-rss on MySQL for a while (I was younger and foolisher) I’ve been thinking about migrating to PostgreSQL when I move tt-rss from Ubuntu 14.04 to 16.04.

From the old forum I found GitHub - hrk/tt-rss-mysql2pgsql: A quick & dirty tool to migrate an existing TT-RSS installation from MySQL to PostgreSQL. and http://pgloader.io/ but I would welcome any/all pointers before I “break all the things”.

I should add that tt-rss is running in a VM, and I’ll be taking an offline snapshot before the migration so I can easily revert back when I do break it, but it would be nice not to have to revert it too many times…

I have done so in the past:

  1. Put the coffee machine on.
  2. Take a backup of everything.
  3. Take a OPML backup of your feeds.
  4. Re-install ttrss on postgres.
  5. Import your feeds from the OPML file.
  6. Enjoy your freshly brewed coffee as everything is now fine.

:slight_smile:

As I want to keep everything I have already, an OPML export and import will unfortunately not suffice.

Then you’re going to have to script something yourself. The one you listed up-thread is 2 years old and will likely break many things.

Kierun gave you the right answer, if you value your sanity.

:smiling_face: Pretty much yes.

What do you really need that an OPML will not cover? …

There’s also the import_export plugin which will keep your starred and archived items.

Doing a MySQL dump to PostgreSQL import is a recipe for disaster. Use the plugin and OPML and you should have everything.

An OPML file is a list of feeds, however for sentimental reasons I want to preserve content from several sites that are no longer online, these would be empty if I just used an OPML export and import.

Is there a simple way to set all (rather than the 30 can be selected easily) items in a feed as Archived ?

I don’t understand. Can you not just scroll so all the items you want are loaded on the screen, select them all, then select Archive?

it’s really much easier if you just quit striving for the unattainable… sanity is overrated. :wink:

Original author of GitHub - hrk/tt-rss-mysql2pgsql: A quick & dirty tool to migrate an existing TT-RSS installation from MySQL to PostgreSQL. here
@Reader_Refugee did you know of some bugs which weren’t reported or fixed yet?

The project will still work unless @fox has added some new tables (they’re hardcoded - yeah blame me) in the past two years. I’ll give a look at the schemas.

I did a git pull to get the production instance up to date
I spun up a new VM for postgresql
made sure I had access to MySQL from the new VM to the existing VM
did base setup of web server and git clone of tt-rs (so both production and new are at the same level)
ran through the install wizard and confirmed I could login as admin
edited the config.properties to match the production and new databases
after hitting java heap space errors, finally got it to run, but not completed

java -Xmx4096m -jar tt-rss-mysql2pgsql.jar

at the bottom of the output

}";}') was aborted.  Call getNextException to see the cause.
CAUSE: ERROR: insert or update on table "ttrss_plugin_storage" violates foreign key constraint "ttrss_plugin_storage_owner_uid_fkey"
  Detail: Key (owner_uid)=(2) is not present in table "ttrss_users".
[ttrss_plugin_storage] 13 ms.
[ttrss_plugin_storage] setting sequence to 5
INSERT INTO ttrss_error_log(id, owner_uid, errno, errstr, filename, lineno, context, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
[ttrss_error_log] 12 ms.

Sorry for the heap space - I can’t know in advance how much you’ll need. :slight_smile:

Now, to you rissue… ttrss_users is the first table that gets copied, it looks like the second user wasn’t copied. Would you check what is at the top of the output (being the first table). You can write me an email or open an issue on github if you don’t want to pollute this forum.

e: out of curiosity, did you ty logging in with your user after the tool completed? It did complete, even if it shows some errors during the execution.

The comment about the heap space wasn’t a criticism, it was just to be complete :smile:

Good point about polluting here, I’ve opened an issue on github and attached a full log

Truer words never spoken.