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 trusthost 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!
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>
This is a configuration problem with your own computer.
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 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"
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.
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.
Should be fixed now.
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.
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”
And for those having Windows Firewall running : don’t forget to add pgserver to the allowed program list !
I recommend restoring the edited versions of pg_hba.conf before starting the new cluster.
Thank you. I added that step shortly after you left this comment.
Well done, thanks alot!
I just upgraded Postgres 10 to 12. Incredibly, the official documentation is still deficient! I’ve tweaked these steps and made them more generic.
Crazy that this is still so poorly documented. Thanks for this. One note, I for some reason had to repeat the permission settings on each of the folders (9.6 and 11.7), rather than the parent folder, and then had to do it AGAIN for 11.7/data because it did not inherit the permissions. I think this must be more of a Windows problem, but it is nevertheless really weird.
Thank you. That is what I meant to say, and I’ve clarified the wording to be clearer.
Thanks so much for this!! Our linux upgrades were seamless but the windows upgrade was leaving me very frustrated. This was an incredibly clear explanation to get it done.
I have been doing research on this issue and came across a few threads that mention this process:
-Stop any running postgres instance;
-Install the new version and start it; Check if you can connect to the new version as well;
-Change old version’s postgresql.conf -> port from 5432 to 5433;
-Start the old version postgres instance;
-Open a terminal and cd to the new version bin folder;
-Run pg_dumpall -p 5433 -U | psql -p 5432 -U
-Stop old postgres running instance;
Wow, still works. Thanks.
Upgraded from 10 to 12 on Win7.
Some hints for future users:
– The user account that you want to run the program as (postgres) must be password protected.
– The Secondary Logon service needs to be set to Manual and not be disabled. By default, it is set to Manual unless changed.
-You can run also cmd.exe and then inside type: RUNAS /USER:postgres CMD
Just took on the very intimidating task of upgrading our deeply outdated PostgreSQL 9.1 database to 10.3. This tutorial worked to the word. I watched with my mouth agape as my 3.5 TB database upgraded in minutes using the –link flag without hitting a single hitch. Thank you thank you thank you for this post.
Thanks Aren.
And about the PostGIS, we need to upgrade in old version before pg_upgrade?
And after upgrade, we need to do anything concerning with statistics or other things?
Thanks a lot.
It has been a while, but I think that I did the Postgres upgrade first, then the PostGIS upgrade. I recall PostGIS’s instructions being clearer, and I think guided me through upgrading the data to be compatible with a newer release.
Thanks
After a good bit of wailing and gnashing of teeth while trying everything I could think of (following the docs from Postgres), I stumbled upon this.
I can’t say Thank You too much. I very much appreciate the post!
Just used this to upgrade from PostgreSQL 12 to 13. I agree, the Postgres documentation is lacking. There is no way I would have gotten through the upgrade without this tutorial, so thank you!
I followed this documents to upgrade from Postgres 9.6 to 13 and it help me lot to resolve some major issue.\
Thank you!
2021, and this worked perfectly.
I migrated from 9.6 to 14 without any issue.
This will duplicate the database, coping the files over, but converting the format of them to the newver version. I will try the “link” option
Also there is a “cleaner” way (probably a little slower) exporting and importing directly from old to new server by this command run from BIN of the new install.
pg_dumpall -p 5432 -U postgres | psql -p 5433 -U postgres
(same scenario with old postgres in 5432 and new in 5433. No need to create windows users or give folder access. But I DID modify the pg_hba.conf to “trust” local in both instances and restarting service.
Everything in here was sensible and to-the-point. Thanks for a very helpful guide that got me over the hump. One possible addition, after changing the listening port from 5433 to 5432 you may need to reconfigure that Database Server in PGAdmin4, if you’re running PGAdmin4. Right-click on the new server in the PGAdmin4 browser, select Properties, and then the Connection tab to expose and modify the port number.
Hi, very good article.
Migrated from 13 to 14 and now from 14 to 15. At least, on the last version -U parameter to set the username worked for me.
One important thing to add: Ensure to use the same encoding and collation for the new installation else you won’t be able to execute the upgrade.
is it possible to upgrade from one major to another major version at one step, like from pg12 to pg16 or must i upgrade it step by step on each major version like pg12 to pg13 ot pg14 to pg15 to pg16?