Re: Obsolete or dead serverconnections after reboot - Mailing list pgsql-general

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