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.
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,
The best source of Windows installer information was the Enterprise DB forums. Here are some posting I found that addressed the question...
The upgrade info moves around from release to release. For example,
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.
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):
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.
