Upgrade of PostgreSQL database together with JChem PostgreSQL Cartridge

Each version of JChem PostgreSQL Cartridge (JPC) is only compatible with one version of PostgreSQL database requiring parallel upgrade of the database and of JPC in some cases.

The steps of this upgrade procedure are described in this documentation.

See the compatibility matrix here:

JPC version

PosgreSQL version

1.4 - 2.0

9.4

2.1 - 2.10

9.5

3.0 - 4.4

10

5.0 -

12

Assumptions

PostgreSQL <old_pg> is supported by JChem PostgreSQL Cartridge (JPC) version <old_jpc>

PostgreSQL <new_pg> is supported by JPC version <new_jpc>.

Consequently, upgrading to PostgreSQL <new_pg> requires upgrading JPC to JPC <new_jpc> as well, and also - in same cases - upgrading JPC to <new_jpc> requires upgrading PostgreSQL database to <new_pg>.

Upgrade without keeping existing molecule type data

  1. Remove JPC <old_jpc> (jchem-psql). E.g.:

    sudo service postgresql stop
    sudo service jchem-psql stop
    sudo apt-get remove jchem-psql
  2. Remove PostgreSQL <old_pg>. E.g. following https://wiki.debian.org/PostgreSql.

  3. Install PostgreSQL <new_pg>. E.g. following https://wiki.debian.org/PostgreSql.

  4. Install new cartridge JPC <new_jpc>. Follow steps described here.

Upgrade with keeping existing molecule type data

  1. Dump the <old_pg> databases. Follow the Postgres pg_dump or pg_dumpall documentation.

  2. Remove JPC <old_jpc> (jchem-psql). E.g.:

    sudo service postgresql stop
    sudo service jchem-psql stop
    sudo apt-get remove jchem-psql
  3. Remove PostgreSQL <old_pg>. E.g. following https://wiki.debian.org/PostgreSql.

  4. Install PostgreSQL <new_pg>. E.g. following https://wiki.debian.org/PostgreSql.

  5. Install new cartridge JPC <new_jpc>. Follow steps described here.

  6. Make sure that the new postgres and cartridge service are both started.

    sudo service jchem-psql start
    sudo service postgresql start
  7. Restore databases in PostgreSQL <new_pg>. Follow the Postgres pg_dump, pg_dumpall or pg_restore documentation how to restore the dump file(s).
    https://www.postgresql.org/docs/current/app-pgdump.html
    https://www.postgresql.org/docs/current/app-pg-dumpall.html