Created
December 28, 2016 20:03
-
-
Save brycied00d/339d8460e7b1955bd9e15542bbbee21b to your computer and use it in GitHub Desktop.
TT-RSS Database Migrator, Migration from MySQL to Postgres
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env ruby | |
| =begin | |
| Migrate TT-RSS data from MySQL to Postgres | |
| Author: Bryce Chidester <[email protected]> | |
| Provided as-is, no warranty. Make backups! | |
| This script may work in the reverse direction... but probably not. And seeing as | |
| TT-RSS doesn't officially support any other database backend at this time, there's | |
| really no point in migrating to or from any other database. | |
| Really this script was just the quickest and easiest way for me to "convert" my | |
| TT-RSS instance from the legacy MySQL instance to Postgres, without losing all | |
| the histories, entries, starred entries, tags etc. I found an old Java migrator, | |
| which wasn't an option for many reasons, and I messed around with pgloader for | |
| awhile but it required too much configuration and coaxing. In the end, it was | |
| much simpler to write my own migrator/converter. This script is largely based | |
| off sequel's --copy-database mode. | |
| Basic Usage: | |
| 1. Create the target database, and a user. If you don't know how to do this, | |
| I refer you to Postgres' documentation. | |
| 2. Load the bare TT-RSS Postgres schema. | |
| psql <connect string> -f <path to schema/ttrss_schema_pgsql.sql> | |
| 3. Make sure update-daemon2.php is stopped | |
| 4. Run this script. | |
| ttrss-migrator.rb <MySQL connect string> <Postgres connect string> | |
| Ex: 'mysql2://localhost/tt-rss?user=<user>&password=<password>&encoding=utf8' | |
| Note: I used the mysql2 connector and explicitly specified encoding=utf8 to | |
| avoid some encoding issues I encountered early on. If newlines appear as | |
| "\012" then you probably messed up this part. | |
| Ex: 'postgres://localhost/ttrss?user=<user>&password=<password>&encoding=utf8' | |
| Note: Again, I explicitly specified encoding=utf8 to ensure everything is clear. | |
| 5. Update your TT-RSS config.php with the Postgres connection details. | |
| License: | |
| Copyright (c) 2016, Bryce Chidester <[email protected]> | |
| All rights reserved. | |
| Redistribution and use in source and binary forms, with or without | |
| modification, are permitted provided that the following conditions are met: | |
| 1. Redistributions of source code must retain the above copyright notice, this | |
| list of conditions and the following disclaimer. | |
| 2. Redistributions in binary form must reproduce the above copyright notice, | |
| this list of conditions and the following disclaimer in the documentation | |
| and/or other materials provided with the distribution. | |
| THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND | |
| ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED | |
| WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE | |
| DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR | |
| ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES | |
| (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; | |
| LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND | |
| ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT | |
| (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS | |
| SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. | |
| The views and conclusions contained in the software and documentation are those | |
| of the authors and should not be interpreted as representing official policies, | |
| either expressed or implied, of the FreeBSD Project. | |
| =end | |
| require 'logger' | |
| require 'sequel' | |
| def usage | |
| "Usage:\n" + | |
| "#{$0} <MySQL connect string> <Postgres connect string>\n" + | |
| "See file header for more information." | |
| end | |
| abort usage if ARGV.count < 2 | |
| start_time = Time.now | |
| Srcloggers = [] | |
| Srcloggers << Logger.new($stderr) | |
| Srcloggers.each { |l| l.progname="SourceDB" } | |
| Tgtloggers = [] | |
| Tgtloggers << Logger.new($stderr) | |
| Tgtloggers.each { |l| l.progname="TargetDB" } | |
| SourceDB = Sequel.connect(ARGV[0]) | |
| SourceDB.loggers = Srcloggers | |
| SourceDB.test_connection | |
| TargetDB = Sequel.connect(ARGV[1]) | |
| TargetDB.loggers = Tgtloggers | |
| TargetDB.test_connection | |
| =begin | |
| 1. Drop foreign_keys from TargetDB | |
| 2. Make any custom table modifications to avoid constraints | |
| 3. Copy data | |
| 4. Make any custom table modifications to reset constraints | |
| 5. Re-add foreign_keys to TargetDB | |
| =end | |
| Sequel.extension :migration | |
| TargetDB.extension :schema_dumper | |
| #index_migration = eval(TargetDB.dump_indexes_migration(:same_db=>true, :index_names=>true)) | |
| fk_migration = eval(TargetDB.dump_foreign_key_migration(:same_db=>true)) | |
| SourceDB.transaction do | |
| TargetDB.transaction do | |
| puts "Begin removing foreign key constraints" | |
| fk_migration.apply(TargetDB, :down) | |
| puts "Finished removing foreign key constraints" | |
| # Temporarily allow null in section_name from ttrss_prefs_sections | |
| TargetDB.alter_table(:ttrss_prefs_sections) do | |
| set_column_allow_null :section_name | |
| end | |
| SourceDB.tables.each do |table| | |
| puts "Truncating TargetDB table: #{table}" | |
| TargetDB[table].truncate | |
| puts "Begin copying records for table: #{table}" | |
| time = Time.now | |
| to_ds = TargetDB.from(table) | |
| j = 0 | |
| SourceDB.from(table).each do |record| | |
| if Time.now - time > 5 | |
| puts "Status: #{j} records copied" | |
| time = Time.now | |
| end | |
| to_ds.insert(record) | |
| j += 1 | |
| end | |
| puts "Finished copying #{j} records for table: #{table}" | |
| end | |
| puts "Finished copying data" | |
| # Apply schema updates to ttrss_prefs_sections and reset section_name allowing nulls | |
| TargetDB[:ttrss_prefs_sections].where(:id=>1).update(:section_name => 'General') | |
| TargetDB[:ttrss_prefs_sections].where(:id=>2).update(:section_name => 'Interface') | |
| TargetDB[:ttrss_prefs_sections].where(:id=>3).update(:section_name => 'Advanced') | |
| TargetDB[:ttrss_prefs_sections].where(:id=>4).update(:section_name => 'Digest') | |
| TargetDB.alter_table(:ttrss_prefs_sections) do | |
| set_column_not_null :section_name | |
| end | |
| puts "Begin adding foreign key constraints" | |
| fk_migration.apply(TargetDB, :up) | |
| puts "Finished adding foreign key constraints" | |
| TargetDB.tables.each{|t| TargetDB.reset_primary_key_sequence(t)} | |
| puts "Primary key sequences reset successfully" | |
| puts "Database copy finished in #{Time.now - start_time} seconds" | |
| end | |
| end | |
| puts "Finished copying data" | |
| exit | |
| =begin | |
| For reference: | |
| if copy_databases | |
| Sequel.extension :migration | |
| DB.extension :schema_dumper | |
| db2 = ARGV.shift | |
| error_proc["Error: Must specify database connection string or path to yaml file as second argument for database you want to copy to"] if db2.nil? || db2.empty? | |
| extra_proc.call | |
| start_time = Time.now | |
| TO_DB = connect_proc[db2] | |
| same_db = DB.database_type==TO_DB.database_type | |
| index_opts = {:same_db=>same_db} | |
| index_opts[:index_names] = :namespace if !DB.global_index_namespace? && TO_DB.global_index_namespace? | |
| puts "Databases connections successful" | |
| schema_migration = eval(DB.dump_schema_migration(:indexes=>false, :same_db=>same_db)) | |
| index_migration = eval(DB.dump_indexes_migration(index_opts)) | |
| fk_migration = eval(DB.dump_foreign_key_migration(:same_db=>same_db)) | |
| puts "Migrations dumped successfully" | |
| schema_migration.apply(TO_DB, :up) | |
| puts "Tables created" | |
| puts "Begin copying data" | |
| DB.transaction do | |
| TO_DB.transaction do | |
| DB.tables.each do |table| | |
| puts "Begin copying records for table: #{table}" | |
| time = Time.now | |
| to_ds = TO_DB.from(table) | |
| j = 0 | |
| DB.from(table).each do |record| | |
| if Time.now - time > 5 | |
| puts "Status: #{j} records copied" | |
| time = Time.now | |
| end | |
| to_ds.insert(record) | |
| j += 1 | |
| end | |
| puts "Finished copying #{j} records for table: #{table}" | |
| end | |
| end | |
| end | |
| puts "Finished copying data" | |
| puts "Begin creating indexes" | |
| index_migration.apply(TO_DB, :up) | |
| puts "Finished creating indexes" | |
| puts "Begin adding foreign key constraints" | |
| fk_migration.apply(TO_DB, :up) | |
| puts "Finished adding foreign key constraints" | |
| if TO_DB.database_type == :postgres | |
| TO_DB.tables.each{|t| TO_DB.reset_primary_key_sequence(t)} | |
| puts "Primary key sequences reset successfully" | |
| end | |
| puts "Database copy finished in #{Time.now - start_time} seconds" | |
| exit | |
| end | |
| =end |
Thank you! This seems to be the only reasonable alternative to spamming my server with Java ... :-)
I failed to compile pgloader on my system - some dependencies could just not be found, and I did not want to waste time with understanding the config file of the now-unsupported pgloader 2.x for a one-time conversion. Well, this one it is then. (Meaning, I wholeheartedly agree with the author's motivation.)
For those who need to know the dependencies:
gem install sequel
gem install mysql2
That worked for me. :-)
For the previous gems to install correctly you need some dev headers installed. You also need the Ruby PostgreSQL package. Example for Debian systems:
sudo apt-get install ruby-dev libmysql++-dev
sudo gem install sequel mysql2 pg
And note that the script does not create tables itself: I needed to initialize my PostgreSQL database first.
This script worked beautifully on TT-RSS v19.2, thank you!
I could not get the dependencies installed using the prior comments. Fortunately these gems are provided in Ubuntu's repo:
apt-get install ruby-mysql2 ruby-pg
No need to install dev packages nor run gem install after.
+1 Thanks! Another success data point here for my v19.2 (088fcf8) migration.
My database is old and has some garbage that needed collecting first. These were my fixes:
(Delete deprecated plugin tables missed by the migration scripts)
DROP TABLE IF EXISTS ttrss_plugin_af_sort_bayes_references ;
DROP TABLE IF EXISTS ttrss_plugin_af_sort_bayes_wordfreqs ;
DROP TABLE IF EXISTS ttrss_plugin_af_sort_bayes_categories ;
(Patch ttrss-migrator.rb to drop columns that don't exist in destination schema)
130,133c130
< mapped = record.slice(*TargetDB[table].columns)
< if !mapped.empty?
< to_ds.insert(mapped)
< end
---
> to_ds.insert(record)
I know next to nothing about Ruby, so sorry in advance if my edits suck.
+1
My initial installation is super old, currently v19.2 (e40c8da)
I used mstroud's patch, although I didn't check if I needed it or not
First issue was I had a 'theme_id' column on ttrss_users, which the values were null anyway, so I dropped it.
alter table ttrss_users drop column theme_id;In postgres, I had to drop a bunch of contraints due to violations
alter table ttrss_enclosures drop constraint ttrss_enclosures_post_id_fkey;
alter table ttrss_feeds drop constraint ttrss_feeds_cat_id_fkey;
alter table ttrss_user_entries drop constraint ttrss_user_entries_feed_id_fkey;
alter table ttrss_user_entries drop constraint ttrss_user_entries_orig_feed_id_fkey;
alter table ttrss_user_entries drop constraint ttrss_user_entries_ref_id_fkey;
alter table ttrss_tags drop constraint ttrss_tags_post_int_id_fkey;Then at the end I put them back, removing any stale records that were violating the contraints
alter table ttrss_enclosures add constraint ttrss_enclosures_post_id_fkey foreign key (post_id) references ttrss_entries(id) ON DELETE cascade;
alter table ttrss_feeds add constraint ttrss_feeds_cat_id_fkey foreign key (cat_id) references ttrss_feed_categories(id) ON DELETE set null;
alter table ttrss_user_entries add constraint ttrss_user_entries_feed_id_fkey foreign key (feed_id) references ttrss_feeds(id) ON DELETE cascade;
alter table ttrss_user_entries add constraint ttrss_user_entries_orig_feed_id_fkey foreign key (orig_feed_id) references ttrss_archived_feeds(id) ON DELETE set null;
alter table ttrss_user_entries add constraint ttrss_user_entries_ref_id_fkey foreign key (ref_id) references ttrss_entries(id) ON DELETE cascade;
alter table ttrss_tags add constraint ttrss_tags_post_int_id_fkey foreign key (post_int_id) references ttrss_user_entries(int_id) ON DELETE cascade;I had alot of stale data, in mysql ttrss_tags was 4+ million rows, 125k after removing the stale rows
My ttrss runs with docker-compose, this is the Dockerfile I used to make an image to run on the docker-compose network
FROM debian:stable
RUN apt update && apt install -qyy ruby ruby-dev ruby-mysql2 ruby-pg
RUN gem install sequel
ADD ttrss-migrator.rb /
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Just in case anyone happens upon this because they're looking to migrate from mysql to postgres like I did today. This worked GREAT (once I got ruby set up along with all of the dependencies needed for the script to work). It took me a while getting all of the dependencies installed, but once I got that done, the migration was nice and quick. After reconfiguring the config.php file to point to the new DB everything worked just like nothing had changed. I honestly thought it was still using the old DB until I shut it down and tried again. Thanks for this script!