PostGreSQL Upgrade from 9.3 to 11
- Install PostGreSQL version 11 to the default directory. This will not replace your current installation.
- Set the path to the bin directory:
- Open command prompt as Administrator and type:SET PATH=%PATH%;C:\Program Files\PostgreSQL\11\bin;
- Still on the command prompt, create a new empty cluster using the initdb command:
- initdb “C:/Program Files/PostGreSQL/11/data”If you get an error that initdb cannot be found or does not exist, close and reopen the command prompt as Administrator.
- Change pg_hba.conf to trust on both versions:
- Create the postgres local account (under Computer Management/Users).
- Add the postgres user to both data folders (9.3 and 11) with full access.
- Login with the postgres user account and launch the command prompt.
- Type cd C:\temp to set a writable location.
- Stop both PostGreSQL 9.3 and PostGreSQL 11 services.
- Run the command pg_upgrade:
- pg_upgrade –d “C:/Program Files/PostgreSQL/9.6/data” –D “C:/Program Files/PostgreSQL/11/data” –b “C:/Program Files/PostgreSQL/9.6/data” –B “C:/Program Files/PostgreSQL/11/data”
- When completed, start only the service for PostGreSQL version 11.
- Uninstall PostGreSQL 9.3 and delete the folder in C:\Program Files\PostGreSQL\9.3.
- Change pg_hba.config auth method back to md5.
- Sign out of the postgress account and login with admin credentials.
- Delete the local postgres account.
- Test applications.
- If you get the error: “PDT FATAL: Could not create lock file “postmaster.pid”: Permission Denied.”, change the program folder permissions to allow the USERS group, and/or the NETWORK SERVICE account.
- If you get the error: “Could not write to log file “pg_upgrade_internal.log”, run the commands from a writable directory like C:\Temp.
- If you get the error: “Connection to database failed: Fe_sendauth: no password supplied.”, modify pg_hba.conf on both versions to enable trust authentication.