Support #609
Updated by Daniel Curtis almost 9 years ago
This is a guide for migrating a MySQL database to a PostgreSQL database on Debian 8. This process was done using a VM and access granted to h2. Dump The MySQL Database * Dump the VM for exampledb database as XML: <pre> mysqldump --xml -u root -p exampledb > exampledb.mysql </pre> * Securely transfer the MySQL database and to the PostgreSQL database, this may not be ideal for all use cases. new database server: <pre> scp exampledb.mysql db2.example.com:/tmp/ </pre> h2. Prepare Setup The Conversion On the Environment new PostgreSQL server, db2.example.com, prepare the environment to convert the MySQL database to a PostgreSQL database. * Make sure the system is up to date: <pre> apt-get update && apt-get upgrade -y </pre> * Install a few dependencies: git: <pre> apt-get install ruby bundler gawk libreadline6-dev libyaml-dev libsqlite3-dev sqlite3 autoconf libgdbm-dev libncurses5-dev automake libtool bison pkg-config libffi-dev curl git libpq-dev libmysqlclient-dev </pre> * Add a user for RVM: <pre> useradd -m -g users -s /bin/bash migrator </pre> #* and add a password for the migrator user: <pre> passwd migrator </pre> h3. Install RVM * Switch to the migrator user: <pre> su - migrator </pre> * Add PHP cli package and the RVM GPG key: postgres module: <pre> gpg --keyserver hkp://keys.gnupg.net --recv-keys 409B6B1796C275462A1703113804BB82D39DC0E3 </pre> * Install RVM and Ruby 1.9 <pre> curl -sSL https://get.rvm.io | bash -s stable source /home/migrator/.rvm/scripts/rvm echo '[[ -s "/usr/local/rvm/scripts/rvm" ]] && source "/usr/local/rvm/scripts/rvm"' >> ~/.bashrc rvm autolibs read-fail rvm apt-get install 1.9 php5-cli php5-pgsql </pre> h2. Install mysql2postgres * Install bundler: Download mysql2postgresql: <pre> gem install rubygems-bundler bundler git clone https://github.com/mihailShumilov/mysql2postgresql.git </pre> * Install the pg gem: cd mysql2postgresql <pre> gem install pg </pre> * Install Convert the mysql2psql gem: database: <pre> gem install mysql2psql php converter.php -i /tmp/exampledb.mysql -o exampledb.pgsql </pre> * Install the mysqltopostgres gem: <pre> gem install mysqltopostgres </pre> * Generate a new database.yml file: <pre> mysqltopostgres </pre> h2. Resources * https://github.com/maxlapshin/mysql2postgres https://github.com/mihailShumilov/mysql2postgresql