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 for upgrades on Windows. It assumes you only have the old version installed and you’re using the default x64 install location of C:\Program Files\PostgreSQL. The steps:
- Install the newer Postgres with the Windows installer. Don’t let it use TCP port 5432 yet; that’s used by your current Postgres instance. The installer should see 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 instance.
- With the Services control panel, shut down both the old and new databases.
- Create a new account on your PC named postgres. This is a Windows user, not a user inside the Postgres database. This account does not need to have the same password as the Postgres account in your databases. 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.)
- With Windows Explorer, give the Windows postgres account Full Control permission on both 1. C:\Program Files\PostgreSQL\ and 2. both instances’ data directories, which are at C:\Program Files\PostgreSQL\version\data. Yes, you must give this permission to both data directories. For some reason, the data directories do not inherit permissions. (Make sure the permissions are standard, in the sense that they are inherited by children. This should happen if you use the simple permissions dialog.)
- (This change puts your database in an insecure state. I strongly recommend you revert these changes before you do anything that causes the database services to turn on.) Create new pg_hba.conf files for both instances. These files are in C:\Program Files\PostgreSQL\version\data. It takes two steps:
- Back up the current files by renaming them to pg_hba.conf.bak. You’ll revert them when done.
- Create new pg_hba.conf files in each instance’s data directory. The new files only have these two lines:
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
- Open a command prompt window in administrator mode, then:
RUNAS /USER:postgres "CMD.EXE"
- Change to the bin directory of the newer install of Postgres. It will be C:\Program Files\PostgreSQL\version\bin.
- Run this:
pg_upgrade.exe --old-datadir "C:/Program Files/PostgreSQL/oldVersion/data" --new-datadir "C:/Program Files/PostgreSQL/newVersion/data" --old-bindir "C:/Program Files/PostgreSQL/oldVersion/bin" --new-bindir "C:/Program Files/PostgreSQL/newVersion/bin"(change oldVersion and newVersion to reflect your actual directories for the old and new versions of Postgres.) This will take a while to run if you have a lot of data. Wait until this is done before continuing.
- In the postgresql.conf file for the new Postgres instance, change the listening port to 5432 (from 5433).
- Revert the security-reducing changes to the pg_hba.conf files for both servers:
- Delete the current pg_hba.conf files.
- Rename the pg_hba.conf.bak files back to pg_hba.conf.
- Through the Services control panel:
- Start your new Postgres instance.
- Reconfigure both Postgres services as needed. It is possible that the service for your old Postgres instance has Startup type set to Automatic. It probably should be set to Manual or Disabled. This makes it harder for both the old and new instances to run at the same time.
- Vacuum and reanalyze all databases.
- If you created a postgres Windows account, remove it.
The new Postgres instance’s postgres database account (database account, not the Windows account you already deleted) will have the same password as the old Postgres instance.
Once you’ve verified that everything works properly, you might consider uninstalling the old Postgres copy.
QUESTION: Does pg_upgrade.exe cause the stopped Postgres instances to start? If not, then some of the above steps may be unnecessary. In short, the old instance would be shut down as step 1, the new instance is installed also using port 5432, and the edits to pg_hba.conf are unnecessary. Let me know if you want to try this!