Postgres’s pg_upgrade on Windows: the documentation misses a lot

Postgres is the gift of timesuck. It’s a great database, especially for spatial data. However, without insider knowledge, simple tasks eat time.

Latest example: pg_upgrade. It shrinks upgrade complexity–if it works! For Windows users, the documentation has useless and incomplete steps.

Here’s a better procedure. I used it to upgrade from 9.3 to 9.4. You might substitute for any older and newer version? I assume you’re using the default x64 install location of C:\Program Files\PostgreSQL. The steps:

  1. Install 9.4 with the Windows installer. Don’t let it use TCP port 5432 yet; that’s used by your current Postgres install. The installer saw this and recommended 5433. Add needed extensions with the Application Stack Builder. If you use PostGIS, don’t install the sample database. Do nothing else with the new install.
  2. With the Services control panel, shut down both the old and new databases.
  3. Create a new user on your PC named postgres. This is a Windows user, not a database user. Add it to your PC’s Administrators group. (I didn’t have this account. I don’t know why Postgres or pg_upgrade need it. A better design would permit me to specify database accounts for each install with pg_upgrade command line switches. The --username switch didn’t appear to do that, plus it would use the same username across both databases, which may not always be proper.)
  4. With Windows Explorer, give the Windows postgres account Full Control permission on C:\Program Files\PostgreSQL\ and all children.
  5. Edit the pg_hba.conf files for both servers. They are in C:\Program Files\PostgreSQL\[version]\data. If they are stock, comment out ALL lines, then add the below lines at the bottom. Note that this puts your database in an insecure state; you may want to take the host off the network or aggressively firewall it while this configuration is active.  Be sure to save a copy of the current files so you can revert. The new  lines:
    host all all 127.0.0.1/32 trust
    host all all ::1/128 trust
  6. Open a command prompt window in administrator mode. Run this: RUNAS /USER:postgres “CMD.EXE”
  7. In the new command prompt window, run this: pg_upgrade.exe --old-datadir “C:/Program Files/PostgreSQL/9.3/data” --new-datadir “C:/Program Files/PostgreSQL/9.4/data” --old-bindir “C:/Program Files/PostgreSQL/9.3/bin” --new-bindir “C:/Program Files/PostgreSQL/9.4/bin” This will take a while to run if you have a lot of data. Wait until this is done before continuing.
  8. Edit the postgresql.conf file for the new Postgres install and change the listening port to 5432 (from 5433).
  9. Through the Services control panel, start your new Postgres install back up.
  10. Vacuum and reanalyze all databases.
  11. If you created a postgres Windows account above, remove it.

The new Postgres install’s postgres database account (not the Windows account) will now have the same password as in the old Postgres install.

Once you’ve verified that everything works properly, it may be safe to uninstall the old Postgres copy.

8 thoughts on “Postgres’s pg_upgrade on Windows: the documentation misses a lot”

  1. good day please this was the output.

    C:\Windows\system32>RUNAS /USER:postgres “CMD.EXE”
    Enter the password for postgres:
    Attempting to start “CMD.EXE” as user “SLTR-TESTING-PC\postgres” …
    RUNAS ERROR: Unable to run – “CMD.EXE”
    2: The system cannot find the file specified.

    C:\Windows\system32>

  2. Much like Josh, had issues with the cmd.exe not being in the path. I simply logged in as the postgres windows account and ran a command window as an administrator. I went to the new version and ran the pg_upgrade command below as follows.

    C:\Program Files\PostgreSQL\9.4\bin>pg_upgrade.exe -old-datadir "C:/Program File
    s/PostgreSQL/9.3/data" -new-datadir "C:/Program Files/PostgreSQL/9.4/data" -old-
    bindir "C:/Program Files/PostgreSQL/9.3/bin" -new-bindir "C:/Program Files/PostgreSQL/9.4/bin"

    You must identify the directory where the old cluster binaries reside.
    Please use the -b command-line option or the PGBINOLD environment variable.
    Failure, exiting

    As you can see it was unhappy with the command you listed, I will attempt to play with the flags to get this command to execute.

    1. Whoops, I think the problem is where I indicated double dashes, WordPress helpfully collapsed them into an m-dash (a wider dash). Substitute the m-dashes for double dashes and try again.

  3. Cheers. Turned out my problem was much more simple, in having issues with the full trust commands in the pg_hba.conf and some issues around the quotation marks and spaces in explicit path names.

    I eventually improvised slightly, copied the pg_upgrade file (and associated dll’s) to the root PostgreSQL directory and then just set the variables using the set command. ie. set PGDATANEW=9.3/data. See pg_upgrade –help. Not entirely sure why I had such issues with the quotations, but your process described is correctly.

    Everything ran smoothly after that, and my 9.3.5 database transferred smoothly to 9.4.

    Cheers for the blog post, useful and informative.

  4. For those having issue with CMD.EXE not found, prior executing RUNAS :

    SET PATH=%PATH%;C:\Program Files\PostgreSQL\9.4\bin;C:\Windows\System32;
    RUNAS /USER:postgres “CMD.EXE”

  5. And for those having Windows Firewall running : don’t forget to add pgserver to the allowed program list !

Leave a Reply

Your email address will not be published. Required fields are marked *