Previous Index Next

ISCS version 0.1.1

Converting from MySQL to PostgreSQL

There are some differences between MySQL and PostgreSQL in the way in which some critical ISCS data is represented. Thus, one cannot simply dump an existing MySQL database and import it into PostgreSQL. There are three scripts in the DBD directory of the source tarball whose names begin with my2pg which can be used to migrate an existing MySQL DBD to PostgreSQL. Be sure to run all ISCS MySQL database upgrades if you are upgrading your ISCS version BEFORE converting the database to PostgreSQL.

The my2pg script is used to convert the database in one step. This requires both the MySQL and PostgreSQL databases to be running and accessible. The PostgreSQL DBD should already be configured including setting up the spm role, the valid users and the plpgsql language. The script must be run from a directory where one has read/write access in order to create some temporary files. It will also need read access to the spmskeleton.pgsql file which is found in the DBD directory of the tarball, is ultimately copied to the /usr/local/SPM/Database/ directory but can be located anywhere including the current working directory for purposes of this script. Simply run the script (./my2pg) from a console screen and follow the instructions. ISCS prepends spm_ to each database name by default. If you have changed this prefix, you will need to specify your prefix as the first command line argument (./my2pg myprefix_). It is very rare that one would change the prefix so, if you do not understand this issue, you probably do not need to worry about it.

The my2pgexport and my2pgimport scripts are also run from a console screen but use a two step process. This is for situations where both DBDs are not simultaneously available. Both scripts must be run from a directory where you have read/write access and take the same optional prefix argument.

The my2pgexport script requires access to the MySQL database and will produce a PostgreSQL usable import file named <database name>.pgsql in the current directory.

The my2pgimport script must have access to this file, the spmskeleton.pgsql file and the PostgreSQL database. It will create the database, create the schema with the spmskeleton.pgsql file, populate the database with the import file, analyze it and set proper permissions.

If you are migrating to a different host, you will also need to copy the DBD file structure. This procedure assumes one has followed the normal DBD installation instructions including copying the PostgreSQL appropriate scripts, e.g., Pgdbcopy, Pgdbnew and Pgdbdelete, to the executable path.

Previous Index Next