Migrating large MySQL tables to PostGres

[sneakyimp]

I have a number of very large tables I'd like to move from MySQL to Postres. I am accustomed to using PHPMyAdmin to import and export and I also have some experience with the mysql command line client for linux (i.e., LOAD DATA INFILE and mysqldump).

I'm wondering if a mysqldump from the mysql db will be importable into the postgres db. Do they escape characters the same way? What about table structures? I believe that the autoincrement syntax in MySQL is different than the OID syntax of PostGres. the two tables in Mysql Are as follows:

This one has 10 million records in it:

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 AUTO_INCREMENT=1 ;

this one has about 50k:

CREATE TABLE `demo_zip_codes` (
  `zip` varchar(5) NOT NULL default '',
  `lat_radians` decimal(12,11) default NULL,
  `long_radians` decimal(12,11) default NULL,
  `city` varchar(30) default NULL,
  `st` char(2) default NULL,
  `time_zone_offset` tinyint(4) default NULL,
  `observes_dst` tinyint(1) unsigned default NULL,
  PRIMARY KEY  (`zip`)
) TYPE=MyISAM;

Any more generalized advice about such a migration would be appreciated. My project has about 25 tables most of which contain text fields.

[Weedpacket]

Doesn't phpMyAdmin's export have a SQL compatibility switch? If it doesn't offer PostgreSQL it should at least have ANSI.

PostgreSQL's OIDs and MySQL's autoincrement type are very different beasts. In PostgreSQL, everything has an oid. PostgreSQL does sequences using sequence objects. If you're lucky, the hypothetical compatibility switch will cause statements to create these to be put in the dump.

[sneakyimp]

I see no ANSI option there. My PHPMyAdmin version appears to be 2.6.2.

I'm also guessing that the resulting file will exceed a reasonable PHPMyAdmin size because of my 10 million record table. I'm imaginning that mysqldump will be what I must use.

Is there a POSTGres command to import an sql file from the command line? I searched the PostGre website and found this but it appears u must purchase it:

http://www.sqlmanager.net/products/postgresql/dataimport/download/

[Roger_Ramjet]

Now I have become a great fan of the MySQL Tools and have found the Migration Tool to be very useful. You could use it to create the sql file from your mysql db, including the data. You can also DL a 30 day trial of Navicat for pgsql which will handle the import into pgsql (it may even do the whole migration for you like the mysql tools but you'll have to look)

[sneakyimp]

Thanks for your advice!

I am not trying to migrate TO MySQL so I'm guessing the MySQL Migration tool isn't going to work for me. As best I can tell, it is intended to migrate TO MySQL from various formats. No mention is made of PostGreSQL.

If you are suggesting that I use the MySQL migration tool to create an SQL file, that is exactly what I was hoping to do using 'mysqldump' from the command line. It creates an SQL file.

I was wondering how one might execute an SQL file stored as text using PostGreSQL. Seems to me that's the place to start rather than buying something.

[sneakyimp]

So did a mysqldump:

mysqldump -uroot -p my_database demo_zip_codes > zip.sql

Then I tried running the resulting script using the psql client:

psql -f zip.sql grumbler

An error occurred on every one:

psql:zip.sql:43129: ERROR:  syntax error at or near "`" at character 13

a sample line is :

INSERT INTO `demo_zip_codes` VALUES ('00000','0.31653839348','-1.17142891886',NULL,NULL,NULL,1);

It appears to be choking on the back quotes.

So i pared it down to just this file

CREATE TABLE demo_zip_codes (
  zip varchar(5) NOT NULL default '' PRIMARY KEY,
  lat_radians decimal(12,11) default NULL,
  long_radians decimal(12,11) default NULL,
  city varchar(30) default NULL,
  st char(2) default NULL,
  time_zone_offset tinyint(4) default NULL,
  observes_dst tinyint(1) unsigned default NULL
);

and ran it as user postgres like this:

psql -f zip3.sql my_database

and i got this error:

psql:zip3.sql:9: ERROR:  syntax error at or near "(" at character 254

what's wrong with that create table statement?

[Roger_Ramjet]

I was suggesting that you use the migration tool rather than do a dump - it gives you more options I believe; but I was also using it as an example.

I was then suggesting that you use the FREE 30 day trial of Navicat to import that sql file into pg. According to the blurb navicat will Import from 18 different formats, so I guess mysql is one of them. If not then it will allow you to use ODBC to connect to mysql and copy data across.

[sneakyimp]

Sadly, this will require that I upgrade my database...it's only 7.4.something

Navicat Website wrote:

Please click the button below to start downloading the evaluation version of Navicat PostgreSQL.Navicat PostgreSQL would support PostgreSQL database server version 8.0 or above.

Also, won't all my 10 million records have to be ported through this client on my desktop here? It seems inherently inefficient to transfer 10 million records from mysql to postgres on the same machhine by way of my computer here at work.

I was really hoping to find a way to just export sql from MySQL and pump it right back into PostGres.

Sxooter where are you!

[Roger_Ramjet]

Well now, that is a shame about the ver. (I was just assuming they were on different servers when I suggested that anyway.)

Yes you need Sxooter to tell you how to link to mysql through ODBC and suck the data across. Don't know anything about pg myself except what is generic to all rdbms.

[Sxooter]

A couple of points:

Firstly, pg 7.4 is rather old, and there are a ton of improvements with the 8.x series. 8.1.8 is the current meta-stable version, and 8.2.3 is pretty stable, but there are a few bugs to be fixed in the next week or so in the 8.2.4 release that I'd personally wait for, especially if you're running on Windows.

Next, time_zone_offset tinyint(4) default NULL is a problem. 1: There's no such thing as tinyint (it's a mysqlism, you'd do well to avoid it) and ints don't generally have precision. Go with smallint and no (4) on it.

Take a look at www.pgfoundry.org for useful side projects. I did a search for mysql there and found this:

http://pgfoundry.org/projects/mysql2pgsql/

[csn]

meta-stable? 😛

[Sxooter]

http://en.wikipedia.org/wiki/Meta-

🙂

[sneakyimp]

Ok i will look into how to upgrade. It was so easy to do apt-get or whatever.

In the meantime, that change from tinyint to smallint still didn't fix my create table statement:

[b]SQL error:[/b]

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

[b]In statement:[/b]

CREATE TABLE demo_zip_codes (
  zip varchar(5) NOT NULL default '' PRIMARY KEY,
  lat_radians decimal(12,11) default NULL,
  long_radians decimal(12,11) default NULL,
  city varchar(30) default NULL,
  st char(2) default NULL,
  time_zone_offset smallint default NULL,
  observes_dst tinyint(1) unsigned default NULL
);

[sneakyimp]

duh...another tinyint in there...and 'unsigned' had to be removed...this worked:

CREATE TABLE demo_zip_codes (
zip varchar(5) NOT NULL default '' PRIMARY KEY,
lat_radians decimal(12,11) default NULL,
long_radians decimal(12,11) default NULL,
city varchar(30) default NULL,
st char(2) default NULL,
time_zone_offset smallint default NULL,
observes_dst smallint default NULL
);

[sneakyimp]

HUZZAH!! Sorry for the serial posting, but i have made some progress. This file creates the table and inserts records:

CREATE TABLE demo_zip_codes (
  zip varchar(5) NOT NULL default '' PRIMARY KEY,
  lat_radians decimal(12,11) default NULL,
  long_radians decimal(12,11) default NULL,
  city varchar(30) default NULL,
  st char(2) default NULL,
  time_zone_offset smallint default NULL,
  observes_dst smallint default NULL
);


INSERT INTO demo_zip_codes VALUES ('00000','0.31653839348','-1.17142891886',NULL,NULL,NULL,1);
INSERT INTO demo_zip_codes VALUES ('00210','0.75059440000','-1.23941420000','Portsmouth','NH',-5,1);

HOWEVER it gives a warning:

psql:zip2.sql:11: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "demo_zip_codes_pkey" for table "demo_zip_codes"

And more importantly the table doesn't belong to the user i want it to belong to. I guess the table belongs to user postgres or something? I recall having some luck figuring out how to create tables belonging to certain users on another post....i'll go dig that up now.

Some very good news here at any rate...apparently postgres doesn't like the backquotes (`) so i should just be able to do blanket find/replace. Any got any advice about how I might use pipes or grep to do that on my huge 10Mil record file? But aside from the backquotes complaint, I'll just need to tweak my create table code and it should work.

EDIT: In the process of googling mysqldump and backquotes, this thread showed up in the stop 25 results! haha.

[Sxooter]

Note that if you want to use it, postgresql supports a boolean type... Just fyi.

[Sxooter]

sneakyimp wrote:

HUZZAH!! Sorry for the serial posting, but i have made some progress.

SNIP
HOWEVER it gives a warning:

psql:zip2.sql:11: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "demo_zip_codes_pkey" for table "demo_zip_codes"

That's not a warning, it's a notice. notices are fine. Sometimes they even tell you useful things, so pay attention to them.

And more importantly the table doesn't belong to the user i want it to belong to. I guess the table belongs to user postgres or something? I recall having some luck figuring out how to create tables belonging to certain users on another post....i'll go dig that up now.

Much like the old "He who smelt it dealt it" rule, whoever creates a table owns it. However, if you're a superuser (i.e. postgres) you can change the owner with:

alter table test owner to newowner;

Note that you can leave it belonging to postgresql so the web user can't drop it, and just grant the permissions needed on it:

grant select, insert, update, delete on table to otheruser;

Some very good news here at any rate...apparently postgres doesn't like the backquotes (`) so i should just be able to do blanket find/replace. Any got any advice about how I might use pipes or grep to do that on my huge 10Mil record file? But aside from the backquotes complaint, I'll just need to tweak my create table code and it should work.

EDIT: In the process of googling mysqldump and backquotes, this thread showed up in the stop 25 results! haha.

Yes, backticks are a purely MySQL thing. Really a bad decision back in the day, and they cause havoc cause of the fact that in most unix shells they escape a command. i.e. in bash you can do:

d=date;
echo $d;
Sun Apr 15 18:55:15 CDT 2007

Not good. you can do this:

cat abc.sql|sed "s/`/\"/g"

[sneakyimp]

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.

[Sxooter]

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

[sneakyimp]

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?