Identified cause of potential pg_upgrade failure on Windows due to OS update adding partial block list of ports 50xxxx (could not connect to server)

From: Andrew Armstrong <phplasma(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Identified cause of potential pg_upgrade failure on Windows due to OS update adding partial block list of ports 50xxxx (could not connect to server)
Date: 2021-05-04 03:26:53
Message-ID: CADxhDStJq8_1Y+D4J+dz3afv4UPYj6WyaYBAW99JVOE9yzsfSg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm a PostgreSQL user on Windows and encountered trouble running pg_upgrade
with the following error [1], ultimately due to blocked ports in Windows
added via OS updates.
A workaround is to run pg_upgrade with lower range ports for the old/new
servers via the -p/-P parameters.

##################################
> pg_upgrade
Performing Consistency Checks
-----------------------------
Checking cluster versions ok

*failure*
Consult the last few lines of "pg_upgrade_server_start.log" or
"pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: Connection
refused (0x0000274D/10061)
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 50432?
could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 50432?
could not connect to source postmaster started with the command:
"C:/program files/postgresql/12/bin/pg_ctl" -w -l "pg_upgrade_server.log"
-D "C:/program files/postgresql/12/data" -o "-p 50432 -b " start
##################################

The root cause is that pg_upgrade wants to start a pg server instance on
port 50432, which is in the Windows port block list as of (I believe)
KB4074588 (see
https://support.microsoft.com/en-us/topic/february-13-2018-kb4074588-os-build-16299-248-b4e2ca66-dd7a-6fd5-a8f3-dc6683d4922b
and https://superuser.com/a/1504618/74693)

The block list as of writing for me is:

##################################
> netsh interface ipv4 show excludedportrange protocol=tcp
Protocol tcp Port Exclusion Ranges

Start Port End Port
---------- --------
5357 5357
44376 44376
49683 49782
49783 49882
49883 49982
50000 50059 *
50265 50364
50370 50469
50849 50948
51049 51148
51149 51248
51349 51448
51449 51548
51549 51648
51649 51748
56959 57058
57059 57158
62662 62662

* - Administered port exclusions.
##################################

I've seen previous people report trouble with this problem without an
obvious resolution (see
https://www.postgresql-archive.org/Upgrade-9-4-to-12-on-windows-system-td6163643.html
and
https://community.microfocus.com/cyberres/idm/f/idm_discussion/301027/pg_upgrade-fails-on-windows-2012r2-when-updating-to-idm-4-6
).

A silly problem, but perhaps pg_upgrade could look to use different ports
not in the Windows block list to prevent this issue.

Kind regards,
Andrew

Browse pgsql-general by date

  From Date Subject
Next Message Hans Buschmann 2021-05-04 07:40:32 AW: Huge performance penalty with parallel queries in Windows x64 v. Linux x64
Previous Message Thomas Munro 2021-05-04 01:38:59 Re: Huge performance penalty with parallel queries in Windows x64 v. Linux x64