[Gnumed-devel] Solved: Upgrade Postgresql for GNUmed on Windows

[Sebastian Hilbert]

Hi all,

It was a pain but here it is. The upgrade path from PG 8.4 to 9.3 on Windows 
(XP and 7).

This guide assumes you have PG 8.4 installed on Windows and it is running on 
port 5432 (default). It also assumes you do not already have valuable data in 
an already installed PG 9.3. If so let us know.

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)

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 --globals-only > globals.sql

Important : Protect your PG 8.4 by shutting it down temporarly

10.) type in the first command shell : net stop psotgresql-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 alway shut down PG 9.3, switch ports 
again, install client 1.3.x, start PG 8.4 (net start postgresql-8.4) and works 
with your old setup.

Let us know of any problems:

Side note: Forget about pg_upgrade. It seems to have a bug. Don't use it.

Regards,
Sebastian