Wiki

Clone wiki

MoleculeDatabaseFramework / Database Migration

Database Migration

Migrating to new version of PostgreSQL and Bingo can be trouble-some. Therefore I recorded the steps taken when migrating from PostgreSQL 9.2 to 9.4 and to Bingo 1.7.10-dev6.

Migrating PostgreSQL on Windows 7 (9.2 to 9.4 with Bingo 1.7.10)

Requirements

  • Installation done with Windows Installer from EnterpriseDB
  • Version Specific Data directory eg. C:\Program Files\PostgreSQL\9.4\data
  • UAC is disabled (I suspect with it enabled this will not work)
  • Service is run using local postgres user (default if installer is used)
  • Old and new version run on different ports (5432 and 5433 for example)

pg_upgrade does not work in my case because I use the Bingo PostgreSQL Cartridge for chemical structure searching. Hence what we need is pg_dumpall

Steps

  • postgres user must have Modify right on C:\Program Files\PostgreSQL directory and all sub-directories

  • edit pg_hba.conf and change method for local ipv4 connection to 'trust' for old and new version:

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  # IPv4 local connections:
  host    all             all             127.0.0.1/32            trust
  • Copy bingo_postgres.dll to C:\Program Files\PostgreSQL\9.4\lib

  • Restart both servers after above change

  • Open command prompt as postgres users and go to C:\Program Files\PostgreSQL\9.4\bin
    RUNAS /USER:postgres "CMD.EXE"
    You will get prompted for the potgres users password
    cd C:\Program Files\PostgreSQL\9.4\bin

  • Run command: pg_dumpall -p 5432 | psql -d postgres -p 5433 where first port number is of old version and second of the new version this will take a while to complete

  • Create Bingo install script: bingo-pg-install.bat -pglibdir

  • This will generate 2 files: bingo_install.sql and bingo_uninstall.sql

  • Modify bingo_install.sql: Remove ; after BEGIN on first line and add END at end of file. Else this will throw an error when running this file in pgAdmin III (no idea why)

  • Run above script in each database that had bingo installed

  • You must re-index all chemical structure columns, for example: create index idx_chemical_structure on chemical_structure using bingo_idx (chemical_structure bingo.molecule)

Updated