Converting to Postgres from MySQL

What all do I need to think about? I'm writing an app that I want to work with both database types and need to know what exactly must be done because for the most part it's written for mysql.

How do I change the schema? Do I simply add foreign keys? Would that change the way the actual queries look?

And as far as executing queries, do I just use pg_exec() (in PHP) or do I need to start and end transactions every time i want to do a query?

I have been coming across these issues myself lately. FWIW, here are some of my experiences.

Converting from MySQL to PostgreSQL is not a painless procedure unless your database is very simple.

Generally, you can't just do a mysqldump, and use those SQL INSERT statements to create your PostgreSQL database. But, there are a couple of tools that will help somewhat (YMMV): http://freshmeat.net/projects/my2pg/
http://sourcepole.com/sources/software/mysql2pgsql/

These are Perl scripts that will parse a MySQL dumpfile, and create a PostgreSQL-compatible set of SQL statements for table creation and population.

What I found was that I had to take the MySQL dump and delete all reference to table types, such as TYPE=MyISAM, or TYPE=HEAP, because these were not removed.

Here were my biggest problems:

I.

PostgreSQL handles incrementing id keys completely differently from MySQL. These scripts attempt to simply replace the MySQL method with the PostgreSQL one (a column of type SERIAL automatically creates a sequence) This works fine if you are just creating structure. But, if you want to populate your tables from the MySQL database, you will be explicitly inserting your keys, instead of letting the database do it. (otherwise, your relations are lost). PostgreSQL will complain because it will want to insert based on the NEXTVAL of the sequence for that column. So... 1) take out all references to 'autoindex' from your MySQL dump, then run your conversion tool. This will create those columns as simple int(4). Import your data, make sure your columns are unique, then create your sequence, and set your default value for that column to be NESTVAL for that sequence. Obviously, you will have to define your sequence to start at a number greater than your last MySQL id key

II

PostgreSQL date columns are stricter than MySQL's. MySQL will allow you to have a date such as 2000-00-00, which is not really a date. PostgreSQL will not. MySQL will allow you to have 2000-02-31 (31 days in February), while PostgreSQL will not.

III.

What I ended up doing was separating the table structure creation from the data importing. I massaged the MySQL dump, ran it through mysql2pgsql, examined and fixed small errors in the PostgreSQL data structure file, then piped it into psql. I then ran the MySQL data dump through my2pq, and managed to get most of my data imported properly.

Quite a chore...

In answer to your other questions: you do not need to use transactions in PostgreSQL unless you want to. Ditto for key constraints, etc... So you can import your database, get it running, and then work on adding the other parts. Of course you will find that you have to track down and fix certain key violations from MySQL before you can create your foreign keys ;-).

Foreign key constraints do not change the way your SQL statements look, they just don't allow a "bad" query to corrupt the key structure. There are several types of actions that you can specify as a result of a bad query.

Besides foreign keys and transactiosn, a few basic features of PostgreSQL you will be relieved to finally have are Views, subqueries, and SELECT INTO.

Hopefully, the tools to migrate will evolve. I'm going to try to get a Perl guru friend of mine to take a whack at some of it.