21.5. Migrating from MySQL to PostgreSQL

It is possible to migrate a BASE installation on a MySQL database to a PostgreSQL database. In a real-world scenario a migration is probably coupled with a hardware upgrade, i.e. the MySQL installation is on one (the old) server and the PostgreSQL installation is on another (the new) server. While this is not any problem per se, it requires a few extra steps to ensure that everything has been moved to the new server. There are three main steps involved:

Export

The first step is to export all data in the existing database. Use the following procedure:

  1. Upgrade to the latest BASE release. This is recommended since it probably has fewer bugs.

  2. Make sure that no other processes are writing to the database when the export is running. Shut down Tomcat and all job agents. It may also be a good idea to ensure that no backup scripts or other external programs are reading from the database at the same time. If the database is large, this may affect performance due to increased disk I/O.

  3. Create a temporary working directory in a suitable location. This directory will be used for storing the exported data. Disk I/O performance may be better if this directory is on a different disk than what the database is using. Ensure that the location has enough free space to hold all data from the BASE database. The dump typically uses less than 10% of the disk space used by the MySQL tables.

  4. Make sure that you have configured migration-specific settings in the base.config file. In most cases the default settings should be good, but if you are experiencing performance problems it may be necessary to change some settings. See the section called “Migration section” for more information.

  5. Start the export by changing to the <base-dir>/bin/ directory and execute:

    ./migrate.sh export /path/to/migration/dir
    

    where /path/to/migration/dir is replaced with the path to the working directory created above. Depending on the size of the BASE installation the export may take a long time.

[Note] Note

Make sure that the migration working directory is empty to perform a full export. Existing files in the directory causes the corresponding tables to be skipped. This can be useful when debugging and after a server crash, since the export will resume where it stopped. Just make sure that the database hasn't been modified in the meantime.

[Warning] Warning

When exporting, make sure that no other process is updating the database since that may create an inconsistent snapshot. The export process does not lock the database or take any other means to protect it against concurrent modifications.

Moving data

This step is about moving the data from the old BASE server to the new BASE server. If the migration is taking place on a single server, this step can probably be skipped.

  1. Download and unpack the BASE software on the new server. Make sure that you are using the same version as on the old server. It is also important that the database is identically configured. Pay special attention to the extended-properties.xml and raw-data-types.xml files and any files in the <base-dir>/WEB-INF/classes/extended-properties and <base-dir>/WEB-INF/classes/raw-data-types directories. The import program protects against some mistakes by comparing the column names from the export with the column names in the new database, but it will, for example, not check that data types match.

    [Tip] Tip
    The easiest way to do this is to simply copy the BASE installation from the old server to the new server. Then, go through the configuration files and make sure that paths are correct.
  2. Move user files from the old server to the new server. Make sure that the userfiles setting in base.config is correct.

  3. Move plug-ins from the old server to the new server. Make sure that the plugins.dir setting in base.config is correct.

  4. Check other settings in base.config and other configuration files for settings that may be affected by the move.

Import

When everything has been moved and is properly configured it is time to start with the import.

  1. Create a new empty database following the instructions in BASE (database engine). Make the corresponding changes in base.config so that the BASE installation points to the new database. Also, make sure that you have configured migration-specific settings in the base.config file. In most cases the default settings should be good, but if you are experiencing performance problems it may be necessary to change some settings. See the section called “Migration section” for more information.

  2. Read the http://www.postgresql.org/docs/9.1/interactive/populate.html document from the PostgreSQL documentation and consider implementing some of the tips. The migration script makes sure that no indexes or foreign key constraints are active during the data import, but the tips about memory, checkpoint intervals, WAL archiving, etc. (section 14.4.5 and on) can be useful. It may also be a good idea to turn off the auto-vacuum daemon during the import.

  3. Start the import by changing to the <base-dir>/bin/ directory and execute:

    ./migrate.sh import /path/to/migration/dir
    

    where /path/to/migration/dir is replaced with the path to the directory where the exported data is stored. Depending on the size of the BASE installation this may take a long time.

    [Note] Installations with separate web and database servers

    Both export and import may be executed against remote MySQL/PostgreSQL servers without limitation. The migration working directory need only to be accessible by the BASE migration program.

  4. Restart Tomcat and verify that the migration was successful. Eg. check that you can log in to BASE, that you can access files, that plug-ins are working, etc. Then, shut everything down again.

  5. Setup backup procedures for the new server. Verify that the backup is working before starting up Tomcat again. Restart any job agents (make sure that the are configured to use the new server). When everything is up and running again, the /path/to/migration/dir directory and all files can be deleted.