Migrate from MySQL to PostgreSQL on Linux (Kubuntu)
- by Dave Jarvis
Storyline
Trying to migrate a database from MySQL to PostgreSQL. All the documentation I have read covers, in great detail, how to migrate the structure. I have found very little documentation on migrating the data. The schema has 13 tables (which have been migrated successfully) and 9 GB of data.
MySQL version: 5.1.x
PostgreSQL version: 8.4.x
I want to use the R programming language to analyze the data using SQL select statements; PostgreSQL has PL/R, but MySQL has nothing (as far as I can tell).
A long time ago in a galaxy far, far away...
Create the database location (/var has insufficient space; also dislike having the PostgreSQL version number everywhere -- upgrading would break scripts!):
sudo mkdir -p /home/postgres/main
sudo cp -Rp /var/lib/postgresql/8.4/main /home/postgres
sudo chown -R postgres.postgres /home/postgres
sudo chmod -R 700 /home/postgres
sudo usermod -d /home/postgres/ postgres
All good to here. Next, restart the server and configure the database using these installation instructions:
sudo apt-get install postgresql pgadmin3
sudo /etc/init.d/postgresql-8.4 stop
sudo vi /etc/postgresql/8.4/main/postgresql.conf
Change data_directory to /home/postgres/main
sudo /etc/init.d/postgresql-8.4 start
sudo -u postgres psql postgres
\password postgres
sudo -u postgres createdb climate
pgadmin3
Use pgadmin3 to configure the database and create a schema.
A New Hope
The episode began in a remote shell known as bash, with both databases running, and the installation of a command with a most unusual logo: SQL Fairy.
perl Makefile.PL
sudo make install
sudo apt-get install perl-doc (strangely, it is not called perldoc)
perldoc SQL::Translator::Manual
Extract a PostgreSQL-friendly DDL and all the MySQL data:
sqlt -f DBI --dsn dbi:mysql:climate --db-user user --db-password password -t PostgreSQL > climate-pg-ddl.sql
mysqldump --skip-add-locks --complete-insert --no-create-db --no-create-info --quick --result-file="climate-my.sql" --databases climate --skip-comments -u root -p
The Database Strikes Back
Recreate the structure in PostgreSQL as follows:
pgadmin3 (switch to it)
Click the Execute arbitrary SQL queries icon
Open climate-pg-ddl.sql
Search for TABLE " replace with TABLE climate." (insert the schema name climate)
Search for on " replace with on climate." (insert the schema name climate)
Press F5 to execute
This results in:
Query returned successfully with no result in 122 ms.
Replies of the Jedi
At this point I am stumped.
Where do I go from here (what are the steps) to convert climate-my.sql to climate-pg.sql so that they can be executed against PostgreSQL?
How to I make sure the indexes are copied over correctly (to maintain referential integrity; I don't have constraints at the moment to ease the transition)?
How do I ensure that adding new rows in PostgreSQL will start enumerating from the index of the last row inserted (and not conflict with an existing primary key from the sequence)?
Resources
A fair bit of information was needed to get this far:
https://help.ubuntu.com/community/PostgreSQL
http://articles.sitepoint.com/article/site-mysql-postgresql-1
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL
http://pgfoundry.org/frs/shownotes.php?release_id=810
http://sqlfairy.sourceforge.net/
Thank you!
