Migrate from MySQL to PostgreSQL
We are thinking of moving our BASE installation from MySQL to PostgreSQL. It shouldn't be too complicated since BASE already has support for both databases. To make the move easier we'll need some new features. Here is the general outline:
- Create an empty database on PostgreSQL. We should just create tables and columns and skip indexes, foreign keys, etc and no data should be inserted. I think a slight modification to the
initdb.shscript should make this possible.
- Dump the existing MySQL database to a format that can be read by PostgreSQL's COPY command. This shouldn't be too complicated given that we only use vanilla column types. Dates and booleans are probably the most difficult data types. One dump file per table should make the import to PostgreSQL simple.
- Import the dumped data into PostgreSQL. This should be straightforward for the static database. We'll need some metadata about the tables in the dynamic part since they need to be created. If possible, the dump files could include the CREATE TABLE part as well.
- Create indexes and foreign keys. Ensure that sequences are properly reset so that they don't generate id values that have already been used.