How to do a minor upgrade of PostgreSQL on Windows, e.g. 9.3.0 to 9.3.1?

Question

What is the recommended way to perform a minor upgrade from PostgreSQL e.g. 9.3.0 to 9.3.1 using the Enterprise DB built windows installer? Should I uninstall first or just install over the existing installation?

The current installation was performed with postgresql-9.3.0-1-windows-x64.exe. Now I want to upgrade using postgresql-9.3.1-1-windows-x64.exe.

Answer 1

On Windows, simply stopping the postgresql service, then running postgresql-9.3.1-1-windows-x64.exe on top of the existing 9.3.0 works. No uninstall necessary. Of course, a backup is recommended.

Clear, explicit documentation for the update procedure on Windows is absent. Note that the documentation link provided by @dezso has been moved in the current manual to: https://www.postgresql.org/docs/current/static/upgrading.html

The PostgreSQL Release Notes typically document migration tips in Appendix E. For example,

• Appendix E. Release Notes https://www.postgresql.org/docs/current/static/release.html
• Section E.1. Release 10.1, subsection E.1.1. Migration to Version 10.1 https://www.postgresql.org/docs/current/static/release-10-1.html#idm46428658121200

The best source of Windows installer information was the Enterprise DB forums. Here are some posting I found that addressed the question...

• 2010 April - What's the right way to upgrade to a new version of PostgreSQL https://web.archive.org/web/20121210220213/http://forums.enterprisedb.com/posts/list/2246.page
• 2009 September - Upgrade procedure when using one-click installer? https://web.archive.org/web/20130603094838/http://forums.enterprisedb.com:80/posts/list/1906.page#6791

Note that the upgrade info moves around from release to release. For example,

• Version 10 location: Chapter 18. Server Setup and Operation, section 18.6. Upgrading a PostgreSQL Cluster https://www.postgresql.org/docs/10/static/upgrading.html
• Version 9.3 location: Chapter 17 Server Setup and Operation, section 17.6 Upgrading a PostgreSQL Cluster http://www.postgresql.org/docs/9.3/static/upgrading.html
• Version 9.0 location: Chapter 24 Backup and Restore, section 24.4 Migration Between Releases http://www.postgresql.org/docs/9.0/static/migration.html

Note that as of June 2017, EnterpriseDB replaced their community discussion forums affecting the links in this answer(https://web.archive.org/web/20171021012954/https://www.enterprisedb.com/news/enterprisedb-announces-new-postgres-rocks-online-user-forum). I was able to hunt down some of the original posts on the Wayback Machine. One dead link that I was unable to repair is: "2010 January - Upgrading to 8.4.2 from 8.4.1", http://forums.enterprisedb.com/posts/list/2115.page#7888.

Answer 2

For the record, running the installer for Windows from Enterprise DB (the default, linked from the Postgres site), at least since the current version Postgres 9.4, you don't have to stop the postgresql service. The installer does that for you. You still need to re-establish connections, if any (most clients do this automatically).

Best refer to the current manual (using the current version of Postgres):

http://www.postgresql.org/docs/current/interactive/upgrading.html

Answer 3

I just upgraded Postgres 10.0 to 10.1 and it was a very fast and easy upgrade.

I downloaded the binaries from https://www.enterprisedb.com/download-postgresql-binaries and unzipped them to C:\postgres, then renamed the directory pgsql to pgsql-10.1 so that I can keep older versions until deemed unnecessary.

I copied the dll files msvcp120.dll and msvcr120.dll to C:\postgres\pgsql-10.1\bin because I prefer that simple installation over the "Installer" which probably adds much more bloat than needed.

I then used this simple batch script which I wrote in the past:

set MAJOR_VERSION=10
set MINOR_VERSION=1

set SERVICE_NAME=pgsql-%MAJOR_VERSION%.%MINOR_VERSION%

set PGHOME=C:\postgres\%SERVICE_NAME%
set PGDATA=C:\postgres\pgdata%MAJOR_VERSION%

%PGHOME%\bin\pg_ctl.exe register -N %SERVICE_NAME% -U LocalSystem -S auto --pgdata=%PGDATA%

::: to unregister old service:
::%PGHOME%\bin\pg_ctl.exe unregister -N %SERVICE_NAME%

I ran SELECT version(); in psql to confirm the old version:

postgres=# select version();
-[ RECORD 1 ]-------------------------------------------------------
version | PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit

I then ran the batch script above which installed a service named postgres-10.1.

I stopped the old service and set its Startup Type to Disabled, and started the new service.

Running SELECT version(); again in psql confirmed the upgrade (had to run it twice due to the connection being aborted when I stopped the old server):

postgres=# select version();
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# select version();
-[ RECORD 1 ]-------------------------------------------------------
version | PostgreSQL 10.1, compiled by Visual C++ build 1800, 64-bit

Keep in mind that upgrading a major version requires updating the data directory with pg_upgrade or some other method, but for a minor upgrade this method worked like a charm.