Proper way to migrate from MySQL to PostgreSQL

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
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:33

Also, it will only export 100,000 articles; if you need more, change the constant on line 146 (or just remove that part of the if statement)

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 » 28 May 2013, 08:42

lotrfan wrote:If you're curious, the error is generated because PHP runs out of memory when loading the (probably quite large) XML file.


That was one of the reasons original import_export only transfers starred articles. Otherwise you need to generate series of XML files or something like that.

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:57

fox wrote:
lotrfan wrote:If you're curious, the error is generated because PHP runs out of memory when loading the (probably quite large) XML file.


That was one of the reasons original import_export only transfers starred articles. Otherwise you need to generate series of XML files or something like that.


I used some hacks to disable output buffering (used by ENABLE_GZIP_OUTPUT), and used readfile(...) (instead of echo file_get_contents(...)) for non-gzipped output. For gzipped output, I stole a snippet from the PHP gzopen page that reads/compresses/outputs the file on the fly in smaller chunks.

Whether importing will work, however, I have not tested. (I suspect not...)

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, 09:07

lotrfan wrote:Whether importing will work, however, I have not tested. (I suspect not...)


well, nope, I tried an import, and it said "opening backend.php" as if it were going to download it.

what I got was {"error":{"code":7}}

if you're going to continue with this, it'd be a lot easier if you'd put it in it's own branch in github, instead of buried... it's a bit of a pain to pick those two files out. of course, I know very little about git, so I'm probably missing something.

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, 20:52

sleeper_service wrote:if you're going to continue with this, it'd be a lot easier if you'd put it in it's own branch in github, instead of buried... it's a bit of a pain to pick those two files out. of course, I know very little about git, so I'm probably missing something.


Good point... https://github.com/lotrfan/ttrss_import_export_all.
I removed the plugin from my TT-RSS fork, so it won't be there anymore.

edit: fixed spelling.

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, 22:44

sleeper_service wrote:well, nope, I tried an import, and it said "opening backend.php" as if it were going to download it.

what I got was {"error":{"code":7}}


Try it now... If you import using the command line, you should get a progress indicator. Run it using:

Code: Select all

./update.php --xml_import_all <file>

You may need to run it as your web server user, e.g., like

Code: Select all

sudo -u http ./update.php --xml_import_all <file>

where "http" is the user your web server runs as.

If you import via the web interface, you might have to increase upload_max_filesize and post_max_size in php.ini. You also won't get any progress, and it will probably take a long while (It INSERTs each article individually, which is really inefficient).

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 » 29 May 2013, 04:41

lotrfan wrote:
sleeper_service wrote:well, nope, I tried an import, and it said "opening backend.php" as if it were going to download it.

what I got was {"error":{"code":7}}


Try it now... If you import using the command line, you should get a progress indicator. Run it using:

Code: Select all

./update.php --xml_import_all <file>

You may need to run it as your web server user, e.g., like

Code: Select all

sudo -u http ./update.php --xml_import_all <file>

where "http" is the user your web server runs as.

If you import via the web interface, you might have to increase upload_max_filesize and post_max_size in php.ini. You also won't get any progress, and it will probably take a long while (It INSERTs each article individually, which is really inefficient).


trying the update.php... method only gives me the update.php help screen...

not to be disrespectful, but, are you testing any of this stuff, or am I the bleeding edge pre-alpha tester here?

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 » 29 May 2013, 05:36

sleeper_service wrote:
lotrfan wrote:
sleeper_service wrote:well, nope, I tried an import, and it said "opening backend.php" as if it were going to download it.

what I got was {"error":{"code":7}}


Try it now... If you import using the command line, you should get a progress indicator. Run it using:

Code: Select all

./update.php --xml_import_all <file>

You may need to run it as your web server user, e.g., like

Code: Select all

sudo -u http ./update.php --xml_import_all <file>

where "http" is the user your web server runs as.

If you import via the web interface, you might have to increase upload_max_filesize and post_max_size in php.ini. You also won't get any progress, and it will probably take a long while (It INSERTs each article individually, which is really inefficient).


trying the update.php... method only gives me the update.php help screen...

not to be disrespectful, but, are you testing any of this stuff, or am I the bleeding edge pre-alpha tester here?


I wasn't really testing it at first, but I have been testing it today...

What's the output of your update.php help screen? Mine shows

Code: Select all

Tiny Tiny RSS data update script.

Options:
  --feeds              - update feeds
  --feedbrowser        - update feedbrowser
  --daemon             - start single-process update daemon
  --task N             - create lockfile using this task id
  --cleanup-tags       - perform tags table maintenance
  --quiet              - don't output messages to stdout
  --log FILE           - log messages to FILE
  --indexes            - recreate missing schema indexes
  --update-schema      - update database schema
  --convert-filters    - convert type1 filters to type2
  --force-update       - force update of all feeds
  --list-plugins       - list all available plugins
  --help               - show this help
Plugin options:
 --xml_import_all FILE - import articles from XML


The last one at the bottom is the important one...

Have you been using this as a user-enabled plugin (in preferences) or as a system-enabled plugin (in config.php)? I'm been testing it as a system plugin, so that may be the issue... I don't think user plugins can add command line options.

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 » 29 May 2013, 05:41

lotrfan wrote:Have you been using this as a user-enabled plugin (in preferences) or as a system-enabled plugin (in config.php)? I'm been testing it as a system plugin, so that may be the issue... I don't think user plugins can add command line options.


I'd only tried it as user enabled... I didn't realize it could be system enabled... I'll give that a try.

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 » 29 May 2013, 05:44

ok, I tried it on the gzipped file, and it said:

[01:42:29/19733] Gunzipping...

and dropped me back to a prompt. so, i gunzipped it manually, and am giving it another try.

so far, it's working.
2750 out of 31k

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 » 29 May 2013, 05:55

it's done, it doesn't *seem* to have destroyed my db... but I'm not sure how to tell easily.

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 » 29 May 2013, 06:02

sleeper_service wrote:ok, I tried it on the gzipped file, and it said:

[01:42:29/19733] Gunzipping...

and dropped me back to a prompt. so, i gunzipped it manually, and am giving it another try.


That's not good... probably more out-of-memory issues, although mine handled the 15MB (gzipped, 70MB uncompressed) file without complaint. What's your `memory_limit` in php.ini? Mine's 128M, which is probably why it succeeded.

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 » 29 May 2013, 06:09

lotrfan wrote:
sleeper_service wrote:ok, I tried it on the gzipped file, and it said:

[01:42:29/19733] Gunzipping...

and dropped me back to a prompt. so, i gunzipped it manually, and am giving it another try.


That's not good... probably more out-of-memory issues, although mine handled the 15MB (gzipped, 70MB uncompressed) file without complaint. What's your `memory_limit` in php.ini? Mine's 128M, which is probably why it succeeded.


default, (at least, I haven't changed it) memory_limit = 128M

box has 16g, 5g free.

it didn't spit out any errors, just dropped me back to a prompt like it was thinking "what else do you want me to do?"

however, it didn't leave a non-compressed file.

my input file was 15m compressed and 68m flat.

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 » 29 May 2013, 07:27

well, I think it blew up my database.

Code: Select all

 ./update.php --feeds
[03:24:52/23762] Lock: update.lock
[03:24:52/23762] Scheduled 57 feeds to update...
[03:24:52/23762] Base feed: http://fcg-bbq.blogspot.com/feeds/posts/default
[03:24:52/23762]  => 2013-05-29 01:27:50.113139, 81 2
PHP Warning:  pg_query(): Query failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block in /var/apache2/2.2/htdocs/ttrss/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:  insert or update on table &quot;ttrss_tags&quot; violates foreign key constraint &quot;ttrss_tags_post_int_id_fkey&quot;
DETAIL:  Key (post_int_id)=(0) is not present in table &quot;ttrss_user_entries&quot;.', 'classes/db/pgsql.php', '38', '', NULL, NOW()) failed: ERROR:  current transaction is aborted, commands ignored until end of transaction block in /var/apache2/2.2/htdocs/ttrss/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 /var/apache2/2.2/htdocs/ttrss/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:  insert or update on table &amp;quot;ttrss_tags&amp;quot; violates foreign key constraint &amp;quot;ttrss_tags_post_int_id_fkey&amp;quot;
DETAIL:  Key (post_int_id)=(0) is not present in table &amp;quot;ttrss_user_entries&amp;quot;.'', ''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 /var/apache2/2.2/htdocs/ttrss/classes/db/pgsql.php on line 43



after a rollback on the database, everything is cooking again.

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 » 29 May 2013, 17:00

If you read it carefully, it's the same error I got with my own script. :( We're back at square 1 [except for the migration of user settings which works really fine].


Return to “Support”

Who is online

Users browsing this forum: No registered users and 9 guests