I decided to install postgresql in a zone on my home server this weekend to play around with PostgreSQL some. So I decided to export my syswatch db from MySQL and import it into PostgreSQL. So I immediatly did a mysqldump -uroot -p syswatch > syswatch.dump. This gave me a file (i actually did each table to its own file but the concept is the same), that I then tried to import in to PostgreSQL without modifying it. (I knew this would not work but wanted to see exactly what wouldn’t work). So I ended up editing one of the smaller tables to create it. Here are some of my notes:
- Even though in mysqlshow a column may show varchar, it is outputed as char. When defining a table in PostgreSQL this will be the full size of what ever you defined it as. For example 255 would be the size of the value plus white space till it filled the 255. So I changed the char(255) to varchar(255), much better
- The datetime value needs changed to timestamp
- int and bigint in mysqldump will have a integer after it for the size, this is not valid in PostgreSQL
- auto_increment is not valid, either user serial or a sequence. I chose to use sequences to replace my auto_increments.
- If you are using a recent version of mysql, make sure to use the –skip-opts option on the mysqldump. If you don’t you will get the extended-insert type insert statements which PostgreSQL does NOT like, and running a 3,000,000 line file through sed to change the ),( to ); insert into blah values ( takes for ever and a day.
- The back ticks around the table names on the inserts need removed
That is all for now, doing a reexport of all the tables with –skip-opts so each insert statement is on it’s own line. Will see if this makes it easier to get it up and running.
The whole purpose for this, other than trying to learn PostgreSQL is to see if PostgreSQL is any faster than MySQL.