Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Migration Tool For Converting MySQL to PGSQL?

    I'm being asked during the slow holiday season (next two weeks) to start migrating our old MySQL databases over to a new PostgreSQL database. I was looking around and found that there are some GUI tools like 'Navicat' that will allow me to convert my database from MySQL to PostgreSQL as well as support tons of other features. My question is before I buckle down and focus on one proprietary software suite like Navicat, do you guys have any other recommendations? Navicat is nice for me because it will do the MySQL to PostgreSQL conversion for me as well as manage other basic DBA tasks. I don't want to lose my command line so I will continue to use that always but as far as conversion goes and being able to perform a basic task to view what the SQL statement would be if I didn't have a GUI tool is very nice for me at this beginner level of DBA.

    Any suggestions and or advice?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I would simply re-recreate the table structure in Postgres, then export the data from MySQL into flat files and import it back into Postgres (e.g. using the COPY) command.

    I don't know MySQL very well, but if you can create a complete SQL script in the MySQL dialect, converting that to PostgreSQL should be doable with search & replace for the majority of syntactical difference. And if most probably in about the same time it took you to install and understand Navicat

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    When you say export the data in a flat file, what exactly does that mean? Is that a *.txt file? I can export the data into .db, .dbf, .sql, .txt, .xls, & .rtf files but I don't know if those are examples of "flat files".

  4. #4
    Join Date
    Dec 2011
    Posts
    13
    Navicat and many other tools allow you to easily transfer required tables and data.

    If you decide to convert manually, MySQL allows you exporting database to a script (CREATE TABLE and INSERT statements) but syntax is quite specific:

    Code:
      DROP TABLE IF EXISTS `tab1`;
    
      CREATE TABLE `tab1` (
      `col` int(11) NOT NULL,
      PRIMARY KEY  (`col`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    so you can search/replace manually, or using free online tool: http://www.sqlines.com/online

    Dmitry

  5. #5
    Join Date
    Aug 2011
    Posts
    42
    This is what i did.

    I took backup of MySql database using

    mysqldump --user=root --password=pass --database db

    It generates a SQL file with all data in form of inserts. it also includes table structures and sql commands to create table structures

    I open pgadminIII, run the whole sql string. and thats it.

    But there are differences in such implementation for complex data array and index definitions. but that is a trivial matter considering that all table and data are in place. one just needs to use the beautiful GUI of pgadmin to nail the rest.

  6. #6
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    OK so I've dumped the MySQL database as show below:

    Code:
    [carlos@ghost ~]$ mysqldump -p wiki > wiki.sql
    So now I've got it:

    Code:
    -rw-r--r-- 1 carlos users 122M Dec 14 10:05 wiki.sql
    Now I've got both Navicat Premium & pgAdmin3 both installed. I'm just not sure the exact process for converting this using these front-end tools. I'm just confused by what exactly you're doing in pgAdmin3 when you say "run the whole sql string. and thats it".

    Just to explain to you what I'm doing, I'm taking a very very old MediaWiki database from MySQL and migrating all that data to PostgreSQL running the latest version of the MediaWiki software. The latest version of MediaWiki is installed on the new server which in turn connected to my PostgreSQL database and generated 49 tables. The older MySQL dump from the older version of MediaWiki only has like 42 tables. Now I'm guessing I simply can't just drag/drop the old database into the newly created database since the latest version has tables the old dump doesn't know about.

    How do I proceed from this point on using pgAdmin3 or Navicat? I've got my old MySQL dump file from the old Wiki & I've got my new Wiki database which is empty since the data is all in the MySQL dump file.

  7. #7
    Join Date
    Aug 2011
    Posts
    42
    create a test database and copy the whole Sql lot in the Sql editor box and run it.

    a minimum editing is always required, when you switch server, but that is what the whole job is about. there is no 100% compatible code.

    i also use navicat lite, but i prefer pgadmin3, because of its simple interface.

    (look for Create database , statement and change the name of DB for Sandbox testing)

  8. #8
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    When changing from MySQL to PostgreSQL, in the dump of MySQL, I see the following when I'm editing for example:

    Code:
    -- ----------------------------
    -- Table structure for `dp_archive`
    -- ----------------------------
    DROP TABLE IF EXISTS `dp_archive`;
    CREATE TABLE `dp_archive` (
      `ar_namespace` int(11) NOT NULL default '0',
      `ar_title` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
      `ar_text` mediumblob NOT NULL,
      `ar_comment` tinyblob NOT NULL,
      `ar_user` int(5) unsigned NOT NULL default '0',
      `ar_user_text` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
      `ar_timestamp` varchar(14) character set latin1 collate latin1_bin NOT NULL default '',
      `ar_minor_edit` tinyint(1) NOT NULL default '0',
      `ar_flags` tinyblob NOT NULL,
      `ar_rev_id` int(8) unsigned default NULL,
      `ar_text_id` int(8) unsigned default NULL,
      `ar_deleted` tinyint(3) unsigned NOT NULL default '0',
      `ar_len` int(10) unsigned default NULL,
      `ar_page_id` int(10) unsigned default NULL,
      `ar_parent_id` int(10) unsigned default NULL,
      KEY `name_title_timestamp` (`ar_namespace`,`ar_title`,`ar_timestamp`),
      KEY `usertext_timestamp` (`ar_user_text`,`ar_timestamp`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Above in the SQL dump, I've highlighted the things I think are MySQL only but I have no idea what to change this data type to in my script to be PostgreSQL compatible. This is a dump of my MediaWiki database. Any ideas if I'm editing this script file for PostgreSQL compatibility, what I need to change the highlighted red sections to and or if you see other items in the SQL script that aren't highlighted but will need to be modified?

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    remove all the stupid backticks: `ar_namespace` should be ar_namespace

    mediumblob and tinyblob should be bytea
    tinyint should be integer
    int(x) should be integer

    DEFAULT '0' is invalid for an integer column as '0' is a character literal not a number, so you need DEFAULT 0 in Postgres

    remove "ENGINE=InnoDB DEFAULT CHARSET=latin1;" completely
    remove "character set latin1 collate latin1_bin"

  10. #10
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    Thank you for breaking that down for me Shammat. It's hard to know what's actual universal SQL between MySQL when you hardly know either software well. Appreciate it!

  11. #11
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Red face

    I seem to still be having some issues. I've converted the database based on your instructions in a text editor and here's what I have based off the original above:

    Code:
    -- ----------------------------
    -- Table structure for dp_archive
    -- ----------------------------
    
    CREATE TABLE dp_archive 
    (
      ar_namespace int NOT NULL default 0,
      ar_title varchar(255) NOT NULL default '' ,
      ar_text bytea NOT NULL,
      ar_comment bytea NOT NULL,
      ar_user int unsigned NOT NULL default 0,
      ar_user_text varchar(255) NOT NULL default '',
      ar_timestamp varchar(14) NOT NULL default '',
      ar_minor_edit tinyint NOT NULL default 0,
      ar_flags bytea NOT NULL,
      ar_rev_id int unsigned default NULL,
      ar_text_id int unsigned default NULL,
      ar_deleted tinyint unsigned NOT NULL default 0,
      ar_len int unsigned default NULL,
      ar_page_id int unsigned default NULL,
      ar_parent_id int unsigned default NULL,
      KEY name_title_timestamp (ar_namespace, ar_title, ar_timestamp),
      KEY usertext_timestamp (ar_user_text, ar_timestamp)
    );
    Now when I tested this into PostgreSQL, it didn't like "unsigned":

    Code:
    ERROR:  syntax error at or near "unsigned"
    LINE 7:   ar_user int unsigned NOT NULL default 0,
    Should I remove "unsigned" from the code or does it need to be reformatted / replaced with something else?

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by CarlosinFL View Post
    Should I remove "unsigned" from the code or does it need to be reformatted / replaced with something else?
    PostgreSQL does not have unsigned data types.

    All valid data types are documented in the manual:

    http://www.postgresql.org/docs/curre.../datatype.html

  13. #13
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1
    OK thanks. I wasn't aware it was a 'data type' as it usually follows a datatype in the example above like 'int unsigned' so I assumed perhaps it was an option or something I wasn't familiar with. Now what about the 'KEY' lines at the bottom? I don't understand what they're trying to reference and how to translate this in PostgreSQL. The keys:

    Code:
    KEY name_title_timestamp (ar_namespace, ar_title, ar_timestamp),
    KEY usertext_timestamp (ar_user_text, ar_timestamp)
    Are these constraints? I can see the name of the key but what is the function? They don't appear to be a UNIQUE constraint or a PRIMARY KEY so I'm not sure if I should omit them or translate them to a PostgreSQL commend.

    Thanks!
    Last edited by CacheDrive; 01-10-12 at 12:53.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I was never forced to work with MySQL (other than playing around) so I had a quick look at the MySQL manual (http://dev.mysql.com/doc/refman/5.1/...ate-table.html) and that shows that key is a synonym for INDEX.

    So those two "options" simply define an index on the columns.

    For PostgreSQL you need to move that out of the CREATE TABLE statement and create two separate CREATE INDEX statements:

    Code:
    CREATE INDEX name_title_timestamp ON dp_archive(ar_namespace, ar_title, ar_timestamp);
    CREATE INDEX usertext_timestamp ON dp_archive(ar_user_text, ar_timestamp);

Posting Permissions

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