10

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.

  • As per the documentation: stop the server , install the new binaries, and restart. It is always a good idea to take a backup, and generally, to have a plan B. – dezso Nov 4 '13 at 22:35
  • Thanks for the link @dezso, I had read that section of the 9.3 manual but found it a bit vague, especially with regard to the Windows setup binaries. – buzz3791 Nov 6 '13 at 18:26
  • @buzz3791 The question/title is inaccurate now because 10.0 to 10.1 is a minor upgrade with the new versioning scheme. I think that you should remove the e.g. ... part. – isapir Mar 6 '18 at 17:41
8

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...

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

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.

| improve this answer | |
5

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

| improve this 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.

| improve this answer | |
  • 1
    Good thing I posted this here. I just followed the instructions to upgrade from 10.1 to 10.2 and it worked great. – isapir Feb 20 '18 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.