Support #609
Updated by Daniel Curtis almost 9 years ago
This is a guide for migrating a MySQL database to a PostgreSQL database using on Debian 8, Ubuntu 14.04, or FreeBSD 9. 8. This process was done using a VM and access granted to the VM for the MySQL database and the PostgreSQL database, this may not be ideal for all use cases. h2. Prepare the Environment h3. Debian/Ubuntu * Make sure the system is up to date: <pre> apt-get update && apt-get upgrade -y </pre> * Install a few dependencies: <pre> apt-get install virtualenv python-dev 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> * Setup the python virtual environment: Add a user for RVM: <pre> virtualenv --no-site-packages ~/envs/py-mysql2pgsql useradd -m -g users -s /bin/bash migrator source ~/envs/py-mysql2pgsql/bin/activate </pre> * Install py-mysql2pgsql #* and add a password for the migrator user: <pre> pip install py-mysql2pgsql passwd migrator </pre> h3. FreeBSD Install RVM * Make sure Switch to the system is up to date: migrator user: <pre> portsnap fetch extract && portmaster -a su - migrator </pre> * Install py-mysql2pgsql: Add the RVM GPG key: <pre> portmaster databases/py-mysql2pgsql gpg --keyserver hkp://keys.gnupg.net --recv-keys 409B6B1796C275462A1703113804BB82D39DC0E3 </pre> h2. Migrate The Database * Generate a new config file: Install RVM and Ruby 1.9 <pre> py-mysql2pgsql curl -sSL https://get.rvm.io | bash -s stable </pre> source /home/migrator/.rvm/scripts/rvm #* _Example output_: echo '[[ -s "/usr/local/rvm/scripts/rvm" ]] && source "/usr/local/rvm/scripts/rvm"' >> ~/.bashrc <pre> rvm autolibs read-fail No configuration file found. rvm install 1.9 A new file has been initialized at: mysql2pgsql.yml </pre> h2. Install mysql2postgres * Install bundler: Please review the configuration and retry... <pre> gem install rubygems-bundler bundler </pre> * Edit Install the mysql2pgsql config file: pg gem: <pre> nano mysql2pgsql.yml gem install pg </pre> * Install the mysql2psql gem: <pre> #* And modify the config file to connect to both the mysql and postgresql databases: gem install mysql2psql mysql: hostname: mysql.example.com port: 3306 username: exampleuser password: SuperSecretPassword database: exampledb compress: false </pre> destination: postgres: hostname: pgsql.example.com port: 5432 username: exampleuser password: SuperSecretPassword database: exampledb # if supress_data is true, only * Install the schema definition will be exported/migrated, and not the data mysqltopostgres gem: supress_data: false # if supress_ddl is true, only the data will be exported/imported, and not the schema <pre> supress_ddl: false # if force_truncate is true, forces a table truncate before table loading gem install mysqltopostgres force_truncate: false </pre> * Now run the migration: Generate a new database.yml file: <pre> py-mysql2pgsql -v mysqltopostgres </pre> h2. Resources * https://github.com/philipsoutham/py-mysql2pgsql https://github.com/maxlapshin/mysql2postgres