Obsolete or dead serverconnections after reboot

From: WR <wolle321(at)freenet(dot)de>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Obsolete or dead serverconnections after reboot
Date: 2021-07-21 10:49:06
Message-ID: 9da9e765-9042-4f5e-00a1-4695d1244a47@freenet.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello community,

actually I have a problem wit PG13 on Windows 10 (both x64), Postgres
version is 13.0.2.21090 from EDB installer.

I have to develop a logical replication cluster, where sometimes there
happens a shutdown of one host. Logical replication works nice, thank
you for that.

My issue is, when I do a normal Windows shutdown, when there are
connections open to this database server and later this host comes up
again in statistics I find the old connections from before shutdown.
They don't go away and keep staying there over reboots. Only a restart
of the Windows service throws them away.

The problem is, that they block a place in der connections counter of
the server and after a while I cant login any more, because the maximum
user-connection count is exceeded.

Example:
select pid, datid, application_name, usename , client_addr from
pg_stat_activity;
before reboot (one active psql session, left it open on server shutdown):

pid | datid | application_name | usename | client_addr
-------+--------+------------------+----------+---------------------------------------
13548 | 156501 | psql | postgres | 192.168.2.49

after reboot (and newly started psql session):

pid | datid | application_name | usename | client_addr
-------+--------+------------------+----------+---------------------------------------
13548 | 156501 | psql | postgres | 192.168.2.49
13764 | 156501 | psql | postgres | 192.168.2.49

As we can see at pid column, the old connection is still there, but I
have only on psql session open.

After restarting the postgres service( and psql reconnect):

pid | datid | application_name | usename | client_addr
-------+-------+------------------+----------+---------------------------------------
12132 | 91805 | psql | postgres | 192.168.2.49

Everything is like expected: one psql-session, one row in the statistics

Is there a way to avoid this (without restarting the service after every
reboot). Is this a bug or a normal behavior?

Thank you,
Wolfgang

--
May the source be with you

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vijaykumar Jain 2021-07-21 12:57:15 Re: Obsolete or dead serverconnections after reboot
Previous Message Tom Lane 2021-07-21 09:58:59 Re: Have I found an interval arithmetic bug?