Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Posts
    265

    convert mysql to postgresql?

    I need convert a huge (more than 4G) mysql db to the latest version of postgresql. I have root right to both db. but I have no idea on how to do it. is there anyone may help me out? I googled the Internet and found a tool named DBConvert. what else way to do it? Thanks.

  2. #2
    Join Date
    Apr 2009
    Location
    Pittsburgh, Pennsylvania
    Posts
    583
    mysqldump databasename -e -t > mydatabaseinserts.sql

    replace database name with the database name...

    that gets all of the insert statements, this would make an sql file with ALL of the inserts for that database.

    Now, make ALL the tables manually on the postgres database.. (i would anyways if you're sql savvy, to ensure you get exactly what you need) and then just use AFTER you created the tables in postgresql.

    pgsql databasename < mydatabaseinserts.sql

    I've personally never done it like this, but i assume it would work, because i use the same inserts for pgsql and mysql for an installation of a script i wrote in php.. and different create table structure (no such thing as autoincrement, its serial in postgresql)

    This will most likely take quite a while... heh. But it will do it, i'd probably let it sit for an hour or 2 perhaps.

    Let me know your results, or if you have any other questions, please post back. ill get an email on my phone when you reply

  3. #3
    Join Date
    Nov 2006
    Location
    Melbourne, Australia
    Posts
    321
    mysqldump databasename -e -t > mydatabaseinserts.sql
    You might want to use
    Code:
    mysqldump databasename -t --compatible=postgresql > mydatabaseinserts.sql
    instead. This will make sure there's no MySQL-specific SQL in the dump file, and I think it also changes some stuff so the file works in Postgres.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •