PostGreSQL Upgrade from 9.3 to 11

1. Install PostGreSQL version 11 to the default directory.  This will not replace your current installation.
2. Set the path to the bin directory:
   1. Open command prompt as Administrator and type:SET PATH=%PATH%;C:\Program Files\PostgreSQL\11\bin;
3. Still on the command prompt, create a new empty cluster using the initdb command:
   1. 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.
4. Change pg_hba.conf to trust on both versions:
   1. Edit the file C:\Program Files\PostgreSQL\[9.3 and 11]\data\pg_hba.conf
   2. Change the entry:host all all 0.0.0.0/0 md5 to host all all 0.0.0.0/0 trust
5. Create the postgres local account (under Computer Management/Users).
6. Add the postgres user to both data folders (9.3 and 11) with full access.
7. Login with the postgres user account and launch the command prompt.
8. Type cd C:\temp to set a writable location.
9. Stop both PostGreSQL 9.3 and PostGreSQL 11 services.
10. Run the command pg_upgrade:
    1. 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”
11. When completed, start only the service for PostGreSQL version 11.
12. Uninstall PostGreSQL 9.3 and delete the folder in C:\Program Files\PostGreSQL\9.3.
13. Change pg_hba.config auth method back to md5.
14. Sign out of the postgress account and login with admin credentials.
15. Delete the local postgres account.
16. Test applications.

Troubleshooting:

1. 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.
2. 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.
3. 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.

References:

https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/installation-getting-started/upgrade-guide/11/EDB_Postgres_Advanced_Server_Upgrade_Guide.1.12.html