LSB DB and PostgreSQL

From ISP_RAS
Jump to: navigation, search

Moving data from MySQL to PostgreSQL

Assume you have checked out the specdb from LSB Bazaar and have the LSB DB set up and managed by MySQL.

In order to set up the PostgreSQL-managed copy, you should first dump the database in a slightly different format.

Let's go to the directory where the specdb folder is located and create specdb copy there:

$ cp -r specdb specdb.postgres && cd specdb.postgres

In the specdb.postgres, folder, edit the makefile: and add --compatible=postgresql to DUMPOPTS at the top of the file.

Now in the specdb.postgres folder:

$ make dumpall

(not forget to provide mysql user with write access to the specdb.postgres folder)

Now take the scripts from specdb-aux-scripts/postgres folder in Bazaar and put all scripts from ther to the specdb.postgres folder (some of these files are new, the others should replace replace the original files).

Launch the migration script - it will bring sql scripts to the form that can be used by PostgreSQL (note that --compatible=postgresql option used above performs only a small part of this work):

$ ./postgres_migration.sh

Set LSB* variable to proper values:

$ export LSBDBHOST=postgres-host LSBUSER=postgres-user LSBDB=postgres-db

NOTE: Currently makefile supposes that no password is required for PostgreSQL user.

Now you are ready to restore the LSB DB with PostgreSQL - either the whole database:

make restoreall

or the specification part only:

make restore

Limitations

  • Currently names of all tables and fields are brought to low case
  • No support for password for PostgreSQL user
  • MySQL stored procedures are not converted, so some functionality in Navigator will be missing
  • 'make restore' and 'make restoreall' produce a huge amount of warnings about wrong escape sequences. Not sure if it really break something, but it make the restoration process rather slow.
Personal tools