Re: Obsolete or dead serverconnections after reboot

Hello Vijaykumar Jain,

at first: select pg_stat_reset(); doesn't help, the pg_stat_activity is
the same after it.

I added some interesting rows after two reboots (which have been
complete power cycles)

artea=# select pid ,application_name , client_addr ,client_port
,backend_start ,query_start,state from pg_stat_activity;
pid | application_name | client_addr |
client_port | backend_start | query_start | state
-------+-----------------------------+---------------------------------------+-------------+-------------------------------+-------------------------------+--------
11116 | | | | 2021-07-21
12:38:06.76295+02 | |
9320 | | | | 2021-07-21
12:38:06.77185+02 | |
11292 | psql |
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 | 59545 | 2021-07-22
07:52:20.110569+02 | 2021-07-22 07:52:24.727718+02 | idle
9624 | arteasubartlt15wolleartlt34 |
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 | 59574 | 2021-07-22
07:56:16.235684+02 | 2021-07-22 07:56:16.278479+02 | active
11396 | psql | 192.168.2.49 | 59550
| 2021-07-22 07:54:03.736197+02 | 2021-07-22 07:54:06.488585+02 | idle
10448 | psql |
2003:fc:1f03:f200:71ae:2cc5:2e77:9003 | 59589 | 2021-07-22
07:58:14.715886+02 | 2021-07-22 07:59:01.652215+02 | active
15788 | | | | 2021-07-21
12:38:06.736352+02 | |
11216 | | | | 2021-07-21
12:38:06.722957+02 | |
14092 | | | | 2021-07-21
12:38:06.739031+02 | |
(9 Zeilen)

(Sorry for bad formatting)

So you can see we have two idle connections, which are those from before
the reboots (one had a IPv4 name resolution and two did it by IPv6, psql
commandline was the same). The backend_start is the same before and
after reboot, so they are the same instances of connections.

I scanned the logfiles and I did not find a shutdown of PostgresServer
on reboot time.

But when I restart the Windows-service postgres manually, then I get
those messages:

On stop:
2021-07-22 08:27:33.451 CEST [11216] LOG: fahre herunter
(I'm shutting down)
2021-07-22 08:27:33.512 CEST [14000] LOG: Datenbanksystem ist
heruntergefahren
(DB is now shutdown)
On start:
2021-07-22 08:27:39.565 CEST [11500] LOG: PostgreSQL 13.2, compiled by
Visual C++ build 1914, 64-bit startet
2021-07-22 08:27:39.744 CEST [17304] LOG: Datenbanksystem wurde am
2021-07-22 08:27:33 CEST heruntergefahren
(DB was shutdown on 2021-07-22 08:27:33)
2021-07-22 08:27:39.787 CEST [11500] LOG: Datenbanksystem ist bereit,
um Verbindungen anzunehmen
(DB is ready to accept connections)

(sorry, its in german, tried to translate)

So maybe, the DB-service seems not to stopped propperly on shutdown of
the host?

Greets, Wolfgang

Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
> select pg_stat_reset();

--
May the source be with you