I moved from MySQL to PostgreSQL a few months ago with a small database but with a significant number of many to many relations as well as one to many. Here is what I did:
Exported the data from MySQL using the export_to_csv_file call from the shell with the model activated which pushes all the data out to one CSV. Copied the application directory under web2py/applications, removed all the files under databases, changed the connection string in the model. Created the database in PostgreSQL, left the DB empty. Ran the application which ran the model and created all the tables and relations but no data. Ran the import_from_csv_file in a shell with the model activated using the file from the export step. Don't forget the db.commit() at the end. The import_from_csv_file does a fantastic job of mapping the old ids to new ids in the foreign key relations. I didn't find any breakage in the data. This allows the import to not use tricks like turning off the auto increment keys such as what happens when using a mysqldump to a file followed by reading it in with mysql. Any database that has been used for a while will usually have holes in the id sequence of records caused by deletion of records. The import_from_csv_file will pack all the ids into a continuous sequence and then fix the foreign key references by maintaining the old to new id mapping. If your application depends on knowing specific ids this is a problem but then I would argue this is an application design issue. I tested the application and only found one DAL statement that worked in MySQL that would not in PostgreSQL. By changing the group by field in the query to the field on the other side of the join it worked in both versions. By copying the application temporarily you are preserving the application/databases directory and since you are not touching the MySQL database exporting to CSV there isn't much danger in losing the data. If the database were very large I am not sure how well this would work because the IDs from original to new database mapping must be kept in memory. For regular backups by all means use the designated utility provided by the database.
