OKEY DOKEY...

Here are some heavily linux-specific instructions for folks who might want to migrate a DB table from MySQL to PostGreSQL. It might be possible to avoid having to login as postgres, but that's how i've done it. Also, I'm wondering if it might be possible to suppress the terminal output when records are inserted to speed things up...maybe just get progress report instead?

STEP 1: Dump the MySQL DB without backquotes

mysqldump --skip-quote-names -u user_name -p database_name table_name > sql_dump_file_name.sql

STEP 2: Alter the table definition part of the dump to be PostGre compatible
NOTE: there might be other steps needed here
change TINYINT(x) to SMALLINT
 remove UNSIGNED wherever you see it
drop any lines saying PRIMARY KEY (field_name)
instead, put the key declaration where the field is defined like so:
fieldname varchar(5) NOT NULL default '' PRIMARY KEY
 remove TYPE=MyISAM from the end of the table create statement

STEP 3: use PSQL to run the script you've got
login to unix command line
execute the now-modified script you just dumped:

psql -f /full/path/to/sql_dump_file_name.sql database_name

STEP 4: Reassign permissions to the newly resulting table to whoever they need to go to.
from linux command line, use the postgres command line client to login to the database (you may need to first login as root and then do 'su - postgres' depending on permissions):

psql database_name

* give ownership to some other user:

ALTER TABLE table_name OWNER TO new_owner;

OR
* just give permissions to the other user:

GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO other_user;

Tada! now it should work for you? Anybody see any mistakes? this seems to work for me.

    Only point I'd make is that you don't have to change the primary key syntax. Either way will work.

      ok now i'm having trouble with my other (HUGE) table...in particular creating an autoincrement field and indexes....

      CREATE TABLE test_zip_assoc (
  id int(12) unsigned NOT NULL auto_increment,
  f_id int(12) unsigned NOT NULL default '0',
  lat_radians decimal(6,5) NOT NULL default '0.00000',
  long_radians decimal(6,5) NOT NULL default '0.00000',
  PRIMARY KEY  (id),
  KEY lat_radians (lat_radians),
  KEY long_radians (long_radians)
) TYPE=MyISAM;

      it seems to complain about int(12):

      postgresql wrote:

      ERROR: syntax error at or near "(" at character 40

      so i change them all to just 'int', removed 'unsigned' and TYPE=MyISAM and get this:

      CREATE TABLE test_zip_assoc (
  id int NOT NULL auto_increment,
  f_id int NOT NULL default '0',
  lat_radians decimal(6,5) NOT NULL default '0.00000',
  long_radians decimal(6,5) NOT NULL default '0.00000',
  PRIMARY KEY  (id),
  KEY lat_radians (lat_radians),
  KEY long_radians (long_radians)
);

      psql complains:

      psql wrote:

      ERROR: syntax error at or near "auto_increment" at character 50

      Is there any way to make the field 'id' an auto-increment value?

        this

        CREATE TABLE test_zip_assoc (
  id int NOT NULL auto-increment == serial,
  f_id int NOT NULL default '0',
  lat_radians decimal(6,5) NOT NULL default '0.00000',
  long_radians decimal(6,5) NOT NULL default '0.00000',
  PRIMARY KEY  (id),
  KEY lat_radians (lat_radians),
  KEY long_radians (long_radians)
);

        still yields the same error:

        ERROR:  syntax error at or near "auto" at character 50

          Some trial and error yields this:

          CREATE TABLE test_zip_assoc (
  id SERIAL PRIMARY KEY,
  f_id int NOT NULL default '0',
  lat_radians decimal(6,5) NOT NULL default '0.00000',
  long_radians decimal(6,5) NOT NULL default '0.00000',
  KEY lat_radians (lat_radians),
  KEY long_radians (long_radians)
);

          which yields this:

          ERROR:  syntax error at or near "(" at character 223

          counting thru the characters is ANNOYING...they should help one out by showing a reasonable error mesg!

            Actually, you're missing half the error message. It should look something like this:

            ERROR:  type "tinyint1" does not exist
LINE 8:   observes_dst tinyint1 default NULL);
                       ^

            Or is this an older version of pgsql?

            As for the key stuff, I assume that's some kind of index? Use create index separately.

            create table...
            create index tablename_field_id on tablename(field);

              tyvm....it occurs to me that i've reached the point where i need to create the Partitioned indexes you described in that other thread. TYVVM sxooter!

              one closing question: does postgresql let you define indexes when u create a table?

                Only unique indexes with the unique keyword:

                create table bubba (id int primary key, info unique);

                will create unique indexes on id and info

                  Wow, I'm guessing that it was all inserts? It would have probably finished in a fraction of the time if you'd enclosed all the insert blocks in begin; end; pairs.

                  But hey, it's done now.

                    Seriously, if you get some free time, try wrapping the insert blocks in begin / end like so:

                    begin;
                    insert into...
                    commit; -- or end; either will do.

                    and see how much faster it is. I'm betting well under an hour to import.

                      Write a Reply...