Upgrade from PostgreSQL 9.4 to 9.5

PostgreSQL 9.4 is supported by JChem PostgreSQL Cartridge (JPC) version <= 2.0 while PostgreSQL 9.5 is supported by JPC version >= 2.1. Consequently, upgrading to PostgreSQL 9.5 requires upgrading JPC, as well. The steps of this upgrade procedure are described in this documentation.

Upgrade without keeping existing molecule type data

  1. Remove cartridge (jchem-psql)

    sudo service postgresql stop
    sudo service jchem-psql stop
    sudo apt-get remove jchem-psql
  2. Remove PostgreSQL 9.4 instance.

    sudo apt-get remove postgresql-9.4 postgresql-contrib-9.4
  3. Install PostgreSQL 9.5 instance.

    sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
    wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
    sudo apt-get update
    sudo apt-get install postgresql-9.5 postgresql-contrib-9.5
  4. Install new cartridge. Follow steps described here. The installed jchem-psql package must be of version >= 2.1.

Upgrade with keeping existing molecule type data

Dump 9.4 databases

  1. Login as Linux user 'postgres'

  2. Dump roles

    /usr/lib/postgresql/9.4/bin/pg_dumpall -p [postgres port] -g > roles.sql
    [postgres port] is the port of postgres service of version 9.4
  3. Dump the database

    /usr/lib/postgresql/9.4/bin/pg_dump --port [postgres port] --username postgres --format=custom --file=[dump file name] [database name]
    [postgres port] is the port of postgres service of version 9.4
  4. Repeat previous step (with different file names and database names) for all databases which should be migrated.

Upgrade PostgreSQL and JPC cartridge

  1. Remove cartridge (jchem-psql)

    sudo service postgresql stop
    sudo service jchem-psql stop
    sudo apt-get remove jchem-psql
  2. Remove PostgreSQL 9.4 instance.

    sudo apt-get remove postgresql-9.4 postgresql-contrib-9.4
  3. Install PostgreSQL 9.5 instance.

    sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
    wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
    sudo apt-get update
    sudo apt-get install postgresql-9.5 postgresql-contrib-9.5
  4. Install new cartridge. The installed jchem-psql package must be of version >= 2.1.
    Download the install package from https://chemaxon.com/products/jchem-engines/download

    sudo dpkg -i jchem-psql_[version].deb
    sudo service jchem-psql init
    Answer yes with 'y'.
    sudo service jchem-psql start
    sudo service postgresql start

Restore databases in PostgreSQL 9.5

  1. Login as Linux user 'postgres'

  2. Restore roles

    /usr/lib/postgresql/9.5/bin/psql -p [postgres port] -U postgres -f roles.sql
    [postgres port] is the port of postgres service of version 9.5
  3. Restore the database

    /usr/lib/postgresql/9.5/bin/pg_restore --port [postgres port] --username postgres --create --exit-on-error --verbose --dbname=template1 [dump file name]
    [postgres port] is the port of postgres service of version 9.5
  4. Repeat previous step (with different file names and database names) for all databases which should be migrated.