How to migrate from MySQL to Postgres?

[Emme]

Hi!

I am migrating all apps on my server from MySQL to PostgreSQL now, but I don't know how to migrate my ttrss database (Mediawiki went smooth via the XML import/export).

What would be the best way to convert the ttrss database to PostgreSQL? Is there a backup/restore function I missed?

Thanks

Martin

[fox]

I'm afraid, there is no such function. You can probably cook something based on (obsolete) xml import/export tools in utils/, but that would require some coding.

It wouldn't hurt to create a ticket on the tracker to add backup/restore function.

[Emme]

Ok, I just filed a wishlist bugreport: http://tt-rss.org/trac/ticket/203

I would try to implement this myself, but I wonder if the database schemas are different between mysql and postgres?

[fox]

Not in any meaningful way. E.g. the types might be named differently (timestamp vs datetime), but the data is essentially the same.

I eagerly await your patches.

BTW, I did some experimenting on the subject too, today, but it seems that my idea doesn't work:

http://madoka.spb.ru/~fox/testbox/tt-rss/xml-tools/

Note: Do NOT run the import tool on your DB, it wipes data. Do not leave the import tool in the publicly accessible place, because it doesn't have any access control.

[Emme]

Ok, i downloaded them and take a look at them when I have some free time (of which I don't have much currenlty). But it's definitely on my todo list for the next weeks.

Ciao

Martin

[Emme]

Hi!

I hacked together an export+import script based on the scripts you pointed me to: http://www.emmes-world.de/packages/ttrss-dump.tar.gz

1. Make a reliable backup of your database by other means (pg_dumpall, mysqldump).
2. Edit each script and configure the database connection, the path to your tt-rss installation and the name of the dump file.
3. Then run it from the commandline (not from the web server/browser).

I just tested an export from MySQL and an import to PostgreSQL, no other directions. Feel free to use/improve/hack on it.

Ciao

Martin

[fox]

Cool. I'll try to take a look later today.

[fox]

Looks good, although sequence export doesn't work for me in PGSQL:

Code: Select all

ttrss_db=> SELECT currval('ttrss_enclosures_id_seq');
ERROR:  currval of sequence "ttrss_enclosures_id_seq" is not yet defined in this session


It probably should be something like this (maybe it's version specific? I'm on 8.1):

Code: Select all

SELECT last_value FROM ttrss_enclosures_id_seq


Just in case, I mirrored the archive on tt-rss site here.

[Emme]

This is probably happening when the database is new, and the sequence was not used yet. This patch sets currval to 1 if the query fails:

Code: Select all

--- ttrss-export-dump.php   (Revision 60)
+++ ttrss-export-dump.php   (Arbeitskopie)
@@ -119,15 +119,17 @@
     $result = db_query($link, "SELECT MAX(".$pkey_name.")+1 as currval FROM ttrss_".$table_name.";");
   }
   
-  if ($line = db_fetch_assoc($result)) {
+  if ($result && $line = db_fetch_assoc($result)) {
     $currval =  $line["currval"];
+  }
+  else {
+    /* no sequence/no entries yet, set to 1 */
+    $currval = 1;
   }
+  if ($currval < 1 || $currval == "") {
+    $currval = 1;
+  }

-  /* if there was nothing yet, default to 1 */
-  if ($currval < 1 || $currval == "")
-    {
-      $currval = 1;
-    }
   
   fwrite($filehandle,"<sequence_".$sequence_name.">\n") || exit(1);
   fwrite($filehandle,"  <object>\n    <![CDATA[" . base64_encode($currval) . "]]>\n  </object>\n");