How to do a minor upgrade of PostgreSQL on Windows, e.g. 9.3.0 to 9.3.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.
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
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.