A long time ago on a system far, far away...

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 New Hope

Create the database location (/var has insufficient space; also dislike having the PostgreSQL version number everywhere -- upgrading would break scripts!):

  1. sudo mkdir -p /home/postgres/main
  2. sudo cp -Rp /var/lib/postgresql/8.4/main /home/postgres
  3. sudo chown -R postgres.postgres /home/postgres
  4. sudo chmod -R 700 /home/postgres
  5. sudo usermod -d /home/postgres/ postgres

All good to here. Next, restart the server and configure the database using these installation instructions:

  1. sudo apt-get install postgresql pgadmin3
  2. sudo /etc/init.d/postgresql-8.4 stop
  3. sudo vi /etc/postgresql/8.4/main/postgresql.conf
  4. Change data_directory to /home/postgres/main
  5. sudo /etc/init.d/postgresql-8.4 start
  6. sudo -u postgres psql postgres
  7. \password postgres
  8. sudo -u postgres createdb climate
  9. pgadmin3

Use pgadmin3 to configure the database and create a schema.

The episode continues in a remote shell known as bash, with both databases

mysql postgresql database-migration
Edit
Report