The first thing to note about upgrading PostgreSQL is that you will need to have both versions (the old and the new version) installed while upgrading. This is mostly a shortened version of http://www.postgresql.org/docs/9.2/static/pgupgrade.html
After installing the new version on the computer:
- Merge pg_hba.conf and postgresql.conf. Put the merged files in a separate folder for now.
- Stop all the PostgreSQL services
- Start a command prompt as the postgres user:
RUNAS /USER:postgres "CMD.EXE"
- Make sure to set postgres as trusted temporary in all pg_hba.conf files:
host all postgres 127.0.0.1/32 trust
host all postgres ::1/128 trust
these lines must be above the lines: host all all …
- Notice that the folder you are standing in needs to be writable in order for postgres to write log files
--old-datadir "C:/Program Files/PostgreSQL/%OLDV%/data"
--new-datadir "C:/Program Files/PostgreSQL/%NEWV%/data"
--old-bindir "C:/Program Files/PostgreSQL/%OLDV%/bin"
--new-bindir "C:/Program Files/PostgreSQL/%NEWV%/bin"
- Copy the merged files the new PostgreSQL data folder.
- Start the new PostgreSQL service
- Now you can run scripts left in the folder by pg_upgrade.
If you are upgrading from a version older than 9.2 you may want to remove the postgres service account since PostgreSQL now runs under the Windows account “Network Service”.