Tuesday, November 26, 2013

Howto - Upgrade PostgreSQL 8.4 to PostgreSQL 9.3 for GNUmed

Here it is

0.) do a full backup. Save it on some other media then your harddisk ! Do it,
now.

1.) Install PG 9.3 ( I tried with 32bit but should not matter).
- http://get.enterprisedb.com/postgresql/postgresql-9.3.1-1-windows.exe

2.) Run the installer and select (English_UnitedStates) for locale (others
might work as well). Make sure it installs itself on port 5433 (or other but
never ! 5432).

3.) Make sure both PG 8.4 and PG 9.3 are running (e.g. via pgadmin3 from PG
9.3)

4.) open a command shell (dos box) - "run as" administrator (!) in Win7

5.) type : RUNAS /USER:postgres "CMD.EXE"
- this will open another black box (command shell) for user postgres
- for the password use 'postgrespassword' (default)

6.) type: SET PATH=%PATH%;C:\Programme\PostgreSQL\9.3\bin;
- instead of Programme it might be Program Files on your computer

7.) type: cd c:\windows\temp
- changes directory to a writable temporary directory

8.) type: pg_dump -p 5432 -Fc -f gnumedv18.backup gnumed_v18

9.) type: pg_dumpall -p 5432 --globals-only > globals.sql

Important : Protect your PG 8.4 by shutting it down temporarly

10.) type in the first command shell : net stop postgresql-8.4
- check that is says : successfully stopped

11.) psql -p 5433 -f globals.sql
- this will restore roles in the new database (PG 9.3 on port 5433)

12.) pg_restore -p 5433 --dbname postgres --create gnumedv18.backup
- this will restore the database v18 into the PG 9.3 on port 5433

Congratulations. You are done. Now to check some things.

########################################
Here you could run the fingerprint script on both databases to check for an
identical hash

https://gitorious.org/gnumed/gnumed/source/f4c52e7b2b874a65def2ee1b37d8ee3fb3566ceb:gnumed/gnumed/server/gm-fingerprint_db.py

########################################

13.) Open gnumed.conf in c:\programme\gnumed-client\
For the profile GNUmed database on this machine ("TCP/IP": Windows/Linux/Mac)]
change port=5432 to 5433.

14. Run the GNUmed client and check that it is working. If it works (no wrong
schema hash detected) you should see all your patient and data.

15. If you have managed to see you patients and everything is there close
GNUmed client 1.3.x.

16.) in the first command shell type: net stop postgresql-9.3

17.) Go to c:\Ptogramme\PostgresPlus\8.4SS\data and open postgresql.conf. Find
port = 5432 and change it to port = 5433

18.) Go to c:\Programme\Postgresql\9.3\data and open postgresql. Find port =
5433 and change it to 5432. This effectively switches ports for PG 8.4 and 9.3
so PG 9.3 runs on the default port 5432.

19.)  Open gnumed.conf in c:\programme\gnumed-client\
For the profile GNUmed database on this machine ("TCP/IP": Windows/Linux/Mac)]
change port=5433 to 5432.

20.) Restart PG 9.3 with: net start postgresql-9.3.

21.) Open the GNUmed client and connect (to PG 9.3 on port 5432).

22.) Leave PG 8.4 in a shutdown state.

So far we have transferred database v18 from PG 8.4 to 9.3. No data from PG
8.4 is touched/lost.

23.) Now you are free to install gnumed-server v19 and gnumed -client 1.4.
Having installed gnumed-server v19 select 'database upgrade' (not boostrap
database) and it will upgrade your v18 database to a v19 database.

In case you experience problems you can always shut down PG 9.3, switch ports again, install client 1.3.x, start PG 8.4 (net start postgresql-8.4) and work with your old setup.

No comments: