#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0

    Convert mysql db to postgres


    Hi all,

    I currently have a centos 5.x server running 5.1.37 MySQL Community Server. I need to migrate the server over to a centos 6.x environment, using postgres 9.x. I have direct access to both servers, postgres is installed and usable.
    How can I import the db without getting errors such as those below:

    invalid command \'s
    invalid command \';
    invalid command \'

    Many thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    I assume the SQL script you are running is a MySQL database dump.

    You need to convert the MySQL syntax in that script to comply with PostgreSQL syntax.

    Things you need to check:

    • Remove the dreaded backticks around any name
    • Remove the engine=.... options
    • Make sure you only have valid data (Postgres does not accept 0000-00-00 or 2013-02-31 as a date)
    • convert the "KEY (x,y)" parts inside a table definition into a proper "CREATE INDEX ..." statement
    • make sure the dump is taken in the proper encoding so that the data inside the dump can successfully be inserted into Postgres
    • remove the "DEFAULT CHARSET=..." options
    • remove the LOCK TABLES and UNLOCK TABLES statements


    There are probably many more things to change.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Thanks shammat

    Is there no automated way of doing this? I assumed this would have been done thousands of times and there would be scripts around to do this?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by ark99
    Is there no automated way of doing this? I assumed this would have been done thousands of times and there would be scripts around to do this?
    Did you go through the Postgres Wiki page related to this question?

    http://wiki.postgresql.org/wiki/Conv..._to_PostgreSQL
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0
    Yes, I've read the first link in the mysql documentation section (backend database switcheroo).
    That is a very manual process. Are there any perl or python scripts to do the conversion?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by ark99
    Are there any perl or python scripts to do the conversion?
    Did you go through those listed on Postgres Wiki page related to this question?

    http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo