Proper way to migrate from MySQL to PostgreSQL

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
bmar
Bear Rating Trainee
Bear Rating Trainee
Posts: 14
Joined: 28 Mar 2013, 09:49
Location: NN, Russia

Re: Proper way to migrate from MySQL to PostgreSQL

Postby bmar » 12 Sep 2013, 08:21

hrk wrote:Ok, I made some small changes and pushed everything to github: https://github.com/hrk/tt-rss-mysql2pgsql
Download the contents of the dist directory (if you're not familiar with github, just download everything as a zip, unzip and open the dist directory).
Edit config.properties to your liking and then launch the tt-rss-mysql2pgsql.jar file.


I've tried your tool, but got a bunch of errors like:

Code: Select all

ERROR: ERROR: insert or update on table "ttrss_user_prefs" violates foreign key constraint "ttrss_user_prefs_owner_uid_fkey"
  Detail: Key (owner_uid)=(3) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_user_prefs" violates foreign key constraint "ttrss_user_prefs_owner_uid_fkey"
  Detail: Key (owner_uid)=(2) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_user_prefs" violates foreign key constraint "ttrss_user_prefs_owner_uid_fkey"
  Detail: Key (owner_uid)=(5) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_user_prefs" violates foreign key constraint "ttrss_user_prefs_owner_uid_fkey"
  Detail: Key (owner_uid)=(3) is not present in table "ttrss_users".
[ttrss_user_prefs] 44 ms.
INSERT INTO ttrss_feedbrowser_cache(feed_url, site_url, title, subscribers) VALUES (?, ?, ?, ?)
[ttrss_feedbrowser_cache] 71 ms.
INSERT INTO ttrss_labels2(id, owner_uid, caption, fg_color, bg_color) VALUES (?, ?, ?, ?, ?)
[ttrss_labels2] 0 ms.
INSERT INTO ttrss_user_labels2(label_id, article_id) VALUES (?, ?)
[ttrss_user_labels2] 0 ms.
INSERT INTO ttrss_access_keys(id, access_key, feed_id, is_cat, owner_uid) VALUES (?, ?, ?, ?, ?)
ERROR: ERROR: insert or update on table "ttrss_access_keys" violates foreign key constraint "ttrss_access_keys_owner_uid_fkey"
  Detail: Key (owner_uid)=(2) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_access_keys" violates foreign key constraint "ttrss_access_keys_owner_uid_fkey"
  Detail: Key (owner_uid)=(2) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_access_keys" violates foreign key constraint "ttrss_access_keys_owner_uid_fkey"
  Detail: Key (owner_uid)=(2) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_access_keys" violates foreign key constraint "ttrss_access_keys_owner_uid_fkey"
  Detail: Key (owner_uid)=(5) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_access_keys" violates foreign key constraint "ttrss_access_keys_owner_uid_fkey"
  Detail: Key (owner_uid)=(5) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_access_keys" violates foreign key constraint "ttrss_access_keys_owner_uid_fkey"
  Detail: Key (owner_uid)=(3) is not present in table "ttrss_users".
[ttrss_access_keys] 4 ms.
[ttrss_access_keys] setting sequence to 7
INSERT INTO ttrss_linked_instances(id, last_connected, last_status_in, last_status_out, access_key, access_url) VALUES (?, ?, ?, ?, ?, ?)
[ttrss_linked_instances] 1 ms.
INSERT INTO ttrss_linked_feeds(feed_url, site_url, title, created, updated, instance_id, subscribers) VALUES (?, ?, ?, ?, ?, ?, ?)
[ttrss_linked_feeds] 0 ms.
INSERT INTO ttrss_plugin_storage(id, name, owner_uid, content) VALUES (?, ?, ?, ?)
[ttrss_plugin_storage] 0 ms.
INSERT INTO ttrss_error_log(id, owner_uid, errno, errstr, filename, lineno, context, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
[ttrss_error_log] 0 ms.

bmar
Bear Rating Trainee
Bear Rating Trainee
Posts: 14
Joined: 28 Mar 2013, 09:49
Location: NN, Russia

Re: Proper way to migrate from MySQL to PostgreSQL

Postby bmar » 12 Sep 2013, 09:16

that's when initializing db from web-interface, sorry.
when I initialize it from command-line:

Code: Select all

~/tt-rss-mysql2pgsql-master/dist# java -jar tt-rss-mysql2pgsql.jar
INSERT INTO ttrss_users(id, login, pwd_hash, last_login, access_level, theme_id, email, full_name, email_digest, last_digest_sent, salt, created, twitter_oauth, otp_enabled) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ERROR: ERROR: relation "ttrss_users" does not exist
  Position: 13
ERROR: ERROR: relation "ttrss_users" does not exist
  Position: 13
ERROR: ERROR: relation "ttrss_users" does not exist
  Position: 13
ERROR: ERROR: relation "ttrss_users" does not exist
  Position: 13
[ttrss_users] 15 ms.
[ttrss_users] setting sequence to 5
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: relation "ttrss_users_id_seq" does not exist
   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
   at it.sineo.ttrssMySQL2PGSQL.Sync.main(Sync.java:220)

bmar
Bear Rating Trainee
Bear Rating Trainee
Posts: 14
Joined: 28 Mar 2013, 09:49
Location: NN, Russia

Re: Proper way to migrate from MySQL to PostgreSQL

Postby bmar » 12 Sep 2013, 15:29

I found that the problem is in column "theme_id", scheme has changed and the migration script is not.

Code: Select all

2013-09-12 15:20:26 MSK LOG:  execute <unnamed>: SET extra_float_digits = 3
2013-09-12 15:20:26 MSK ERROR:  column "theme_id" of relation "ttrss_users" does not exist at character 72
2013-09-12 15:20:26 MSK STATEMENT:  INSERT INTO ttrss_users(id, login, pwd_hash, last_login, access_level, theme_id, email, full_name, email_digest, last_digest_sent, salt, created, twitter_oauth, otp_enabled) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
2013-09-12 15:20:26 MSK ERROR:  column "theme_id" of relation "ttrss_users" does not exist at character 72
2013-09-12 15:20:26 MSK STATEMENT:  INSERT INTO ttrss_users(id, login, pwd_hash, last_login, access_level, theme_id, email, full_name, email_digest, last_digest_sent, salt, created, twitter_oauth, otp_enabled) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
2013-09-12 15:20:26 MSK ERROR:  column "theme_id" of relation "ttrss_users" does not exist at character 72
2013-09-12 15:20:26 MSK STATEMENT:  INSERT INTO ttrss_users(id, login, pwd_hash, last_login, access_level, theme_id, email, full_name, email_digest, last_digest_sent, salt, created, twitter_oauth, otp_enabled) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
2013-09-12 15:20:26 MSK ERROR:  column "theme_id" of relation "ttrss_users" does not exist at character 72
2013-09-12 15:20:26 MSK STATEMENT:  INSERT INTO ttrss_users(id, login, pwd_hash, last_login, access_level, theme_id, email, full_name, email_digest, last_digest_sent, salt, created, twitter_oauth, otp_enabled) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
2013-09-12 15:20:26 MSK LOG:  execute <unnamed>: SELECT setval($1, $2)

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 » 12 Sep 2013, 21:22

I am not at home these days and I am writing from a smartphone.

Migration tool has no knowledge of table columns, just tables. Provided fox hasn't added a new table, you're fine.

It has to be run on an empty (but created!) pgsql schema. When you got "user table does not exist", you run it on a not existing schema.

It has to run on a schema with the same version you're migrating, you can't expect the tool to deal with new columns, new tables and such. :)

vilain
Bear Rating Trainee
Bear Rating Trainee
Posts: 40
Joined: 29 Jun 2013, 08:57

Re: Proper way to migrate from MySQL to PostgreSQL

Postby vilain » 13 Sep 2013, 01:49

The migration tool assumes an empty database.


That explains why I had duplicates of some records, including two Admin users. Another table also had duplicates--ttrss_user_prefs. This came from installing postgreSQL and running the install script to initialize the database. It creates the tables and populates some of them. Then you migrate from the MySQL tables to PostgreSQL, you get duplicates.

bmar
Bear Rating Trainee
Bear Rating Trainee
Posts: 14
Joined: 28 Mar 2013, 09:49
Location: NN, Russia

Re: Proper way to migrate from MySQL to PostgreSQL

Postby bmar » 13 Sep 2013, 09:57

hrk wrote:I am not at home these days and I am writing from a smartphone.
Migration tool has no knowledge of table columns, just tables. Provided fox hasn't added a new table, you're fine.
It has to be run on an empty (but created!) pgsql schema. When you got "user table does not exist", you run it on a not existing schema.
It has to run on a schema with the same version you're migrating, you can't expect the tool to deal with new columns, new tables and such. :)


:D I've solved a problem with a friend help - four eyes are always better. Some updates were made sometime, but one of .sql files wasn't used, the one about "ALTER TABLE ttrss_users DROP COLUMN theme_id"...

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 » 13 Sep 2013, 17:22

vilain wrote:
The migration tool assumes an empty database.

That explains why I had duplicates of some records, including two Admin users. Another table also had duplicates--ttrss_user_prefs. This came from installing postgreSQL and running the install script to initialize the database. It creates the tables and populates some of them. Then you migrate from the MySQL tables to PostgreSQL, you get duplicates.


Glad you solved it, but you shouldn't have had any duplicates. The INSERT would have used the same value for the id table and failed. You should get 3 failed inserts when you use the tool to migrate: one in ttrss_users and two in ttrss_feeds (maybe two more in ttrss_user_feeds now that I think about it) which are the admin account and TT-RSS release and discussion rss.

johnnychicago
Bear Rating Trainee
Bear Rating Trainee
Posts: 36
Joined: 25 Jul 2011, 23:26

Re: Proper way to migrate from MySQL to PostgreSQL

Postby johnnychicago » 26 Mar 2014, 15:42

@hrk --

Just wanted to inquire if you think your tool is still fit - I am pondering migrating an instance with a number of users (total around 3500 feeds) over. Obviously I will run a dry run before. But if there's a good reason from your end not to even bother, I'd be glad to know. :)

Thanks

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

Re: Proper way to migrate from MySQL to PostgreSQL

Postby XianPalin » 02 Apr 2014, 05:57

I'm not sure if this has been mentioned or not (I didn't read through the four pages), but there is a windows based tool here that does migration as well, and with some tweaking that I did last night I was able to migrate from mysql to postgresql on a local server on my network with no issues.

viewtopic.php?f=16&t=2795

It is a little work getting mysql and postgresql to accept non-localhost connections, if you haven't done that already.

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 » 02 Apr 2014, 16:11

Thank you for not reading before posting.

@johnnychicago: sorry I didn't see your message. I guess the tool is still fit, it will break only when fox adds new tables to the schema. I'll go check out if he did in 1.11 and 1.12.

e: @johnnychicago: yep, still valid.

johnnychicago
Bear Rating Trainee
Bear Rating Trainee
Posts: 36
Joined: 25 Jul 2011, 23:26

Re: Proper way to migrate from MySQL to PostgreSQL

Postby johnnychicago » 02 Apr 2014, 22:45

@XianPalin,

Thanks for mentioning, but I do not have access to a Windows machine.

@hrk,

Thanks for looking up. I gave it a quick try just now, sadly it does not want to play.

Ran a few tests, always after recreating the postgresql database. I've tried with the original pgsql schema from ttrss/schema directory as well as with the modified one you provided. Running java without -mx256m didn't even initialise the VM. With it, it started reading data, but did not seem to get anywhere. The last few lines before it died were these:

Code: Select all

  Detail: Key (owner_uid)=(8) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_cat_counters_cache" violates foreign key constraint "ttrss_cat_counters_cache_owner_uid_fkey"
  Detail: Key (owner_uid)=(4) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_cat_counters_cache" violates foreign key constraint "ttrss_cat_counters_cache_owner_uid_fkey"
  Detail: Key (owner_uid)=(8) is not present in table "ttrss_users".
ERROR: ERROR: insert or update on table "ttrss_cat_counters_cache" violates foreign key constraint "ttrss_cat_counters_cache_owner_uid_fkey"
  Detail: Key (owner_uid)=(6) is not present in table "ttrss_users".
[ttrss_cat_counters_cache] 27 ms.
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3619)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4110)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
        at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2006)
        at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3504)
        at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:490)
        at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3198)
        at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2366)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2789)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1612)
        at it.sineo.ttrssMySQL2PGSQL.Sync.main(Sync.java:110)


I've tried the batch loading as well as individual lines, same result.

Could it be that the order in which the tables are loaded is off in my setup? And why would that be?

As for the memory issues, I could run it on another box that has sufficient oomph to probably not run into memory issues. Just wondering if it would change anything on the above issue.

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 » 03 Apr 2014, 02:09

johnnychicago wrote:Ran a few tests, always after recreating the postgresql database. I've tried with the original pgsql schema from ttrss/schema directory as well as with the modified one you provided.


I don't remember providing a schema, could you hook me up? The tool has to run on a previously-initialized PGSQL database using the latest schema from TT-RSS.

johnnychicago wrote:Running java without -mx256m didn't even initialise the VM.


Just out of curiosity, which Java version are you running and where?

johnnychicago wrote:I've tried the batch loading as well as individual lines, same result.


The out of memory happens when reading from the ttrss_entries table. How many items do you have in your MySQL database? Please, run the script in one of my first posts where I wrote a script which gives the approximative size of the whole database.

johnnychicago wrote:Could it be that the order in which the tables are loaded is off in my setup?


No, it can't.

johnnychicago wrote:As for the memory issues, I could run it on another box that has sufficient oomph to probably not run into memory issues. Just wondering if it would change anything on the above issue.


It could fix the out of memory issue, however the previous issues (which custom schema? why the missing users?) have to be sorted first.

johnnychicago
Bear Rating Trainee
Bear Rating Trainee
Posts: 36
Joined: 25 Jul 2011, 23:26

Re: Proper way to migrate from MySQL to PostgreSQL

Postby johnnychicago » 03 Apr 2014, 10:36

hrk wrote:I don't remember providing a schema, could you hook me up? The tool has to run on a previously-initialized PGSQL database using the latest schema from TT-RSS.


My apologies, I caught that from another thread, not from you. :) It did not have an impact on the mode of failure, though.

Just out of curiosity, which Java version are you running and where?


~/tt-rss-mysql2pgsql-master/dist$ java -version
java version "1.6.0_27"
OpenJDK Runtime Environment (IcedTea6 1.12.6) (6b27-1.12.6-1~deb7u1)

On Debian Wheezy in a 32 bit environment with 1.5GB RAM.

The database I am trying this on is around 660MB, of which ttrss_entries is around 580MB with 109000 records.

johnnychicago wrote:Could it be that the order in which the tables are loaded is off in my setup?

No, it can't.


OK. Thought it'd have started loading e.g. the entries table before the users table, failing with a foreign key restriction or similar.

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 » 03 Apr 2014, 12:57

Please, try it with -Xmx1024m and send me a PM with the full log. I'm astonished that MySQL's JDBC driver tries to load the whole table in memory. Alas, MySQL has always been a shit in terms of JDBC compliance and functionality.

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 » 03 Apr 2014, 23:54

Ok, problem found in https://github.com/gothfox/Tiny-Tiny-RS ... _mysql.sql

Code: Select all

create table ttrss_users (id integer primary key not null auto_increment,
login varchar(120) not null unique,
pwd_hash varchar(250) not null,
last_login datetime default null,
access_level integer not null default 0,
theme_id integer default null,
email varchar(250) not null default '',
full_name varchar(250) not null default '',
email_digest bool not null default false,
last_digest_sent datetime default null,
salt varchar(250) not null default '',
created datetime default null,
twitter_oauth longtext default null,
otp_enabled boolean not null default false,
resetpass_token varchar(250) default null,
index (theme_id)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;


There's still the theme_id column creation, although it disappeared w/ schema revision 83 (https://github.com/gothfox/Tiny-Tiny-RS ... sql/83.sql)


Return to “Support”

Who is online

Users browsing this forum: No registered users and 9 guests