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

    Join Date
    Feb 2001
    Posts
    49
    Rep Power
    14

    mysql -> postgresql


    Does anyone know of an easy way to transfer data and structure from a mysql database to a potgresql database?

    Both database are on two seperate server which I have somewhat limited access to.
    Auction off your web site real estate.

    http://www.webmasterbids.com
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    And see this post.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    49
    Rep Power
    14
    I downloaded the mysql2pgsql + but I don't know anything about perl, so I don't know how to use it. Do I upload it to the server and connect it to the database? or do I run it from my machine?
    Auction off your web site real estate.

    http://www.webmasterbids.com
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    mysql2pgsql is simply a command-line Perl script that processes a text dump file. It doesn't connect to the database itself. If you don't have command-line access to a Unix or Linux server (such as telnet, SSH), then you will have to install Perl on your Mac or PC in order to complete this. All it does is read in the MySQL dump file, and process it to turn it into a (hopefully) Postgres-compatible set of SQL commands to create a database. (read the usage instructions in the beginning of the script for the exact command-line syntax)
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13
    Keep in mind there are alot of MySQL specific functions that may not be convertable with the mysql2pgsql script.

    the mysql_data_seek (think that was how it's spelled) function is one such function that won't convert.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    49
    Rep Power
    14
    Thanks I am just learning that now. I managed to get the database structure set up using pgadmin. But getting the data in there was a nightmare. However, I did get it in, and that was when I found out about the differences in syntax.
    Auction off your web site real estate.

    http://www.webmasterbids.com
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2002
    Posts
    409
    Rep Power
    13
    I've found that trying to import a large amount of data through pgadmin didn't work out too well, because the ODBC receive buffer overloads and chokes at a certain point. For small loads it's a nice tool, but for big loads I find the Postgres COPY command has worked out the best. Though sorry that this information probably doesn't help you after the fact.

IMN logo majestic logo threadwatch logo seochat tools logo