Upgrading PostgreSQL under Windows

2016-10-31T16:55:19+01:0010 October, 2013|Developer blog|

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:

  1. Merge pg_hba.conf and postgresql.conf. Put the merged files in a separate folder for now.
  2. Stop all the PostgreSQL services
  3. Start a command prompt as the postgres user:
    RUNAS /USER:postgres "CMD.EXE"
    SET PATH=%PATH%;%NEWVERSION%bin;
  4. 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 …

  5. Notice that the folder you are standing in needs to be writable in order for postgres to write log files

    run:

    pg_upgrade.exe
            --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"
  6. Copy the merged files the new PostgreSQL data folder.
  7. Start the new PostgreSQL service
  8. 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”.

We like cookies. Do you like cookies? Keep on browsing! Ok!