Migrating from MySQL to Oracle/Postgres

Hi,

Currently we have a large database with JChem base running on a MySQL (actually MariaDB) database.

We're looking at migrating to Oracle or Postgres, but I can't find any details on if this is possible and how easy it is.

Is there any documentation, or does anyone have any guidelines/experience/advice on going this?

Cheers,

Brendan

Hi Brendan,

Migrating JChem Base structure tables should be typically quite straightforward with JChem Manager.

1. Export the old structure table and its data fields (if any) to a file.

Please note the format of the output file will determine in which format the structures will be represented in the new table.

If possible we suggest MRV format, but if some 3rd party software is reading the cd_structure field, one may be limited to SDF, etc.

Make sure the format can represent all structural features you have (MRV can represent all features).

It can be useful to list and note the key settings of the old table with:

jcman t <table_name>

2. Connect to the new database and create the new table in the other database which will receive the data.

Make sure the table options match the old configuration (table type, fingerprint parameters, standardization, etc.)

If additional data fields are transferred, make sure to add these columns during of after table creation.

3. Import the file into the new table.

Make sure to connect the appropriate input data to the corresponding fields.

Note: the cd_id primary key is normally auto-incremented during import (recommended). If it is important to match the old values, it can take the values from the input file.

Best,

Szilard

 

 

Thanks Szilard. I don't know if we will go ahead with this, but this is very useful to know. I will post back if I have any questions (or experiences to report about the process).