Re: Obsolete or dead serverconnections after reboot

On Thu, 22 Jul 2021 at 12:41, WR <wolle321(at)freenet(dot)de> wrote:

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

one thing, i forgot to mention. After a pg_stat_reset(), I would run, *vacuum
analyze* on the dbs, so that stats are rebuilt.
else queries may have some bad plans due to lack of estimates.
my assumption was, stats were corrupt, but it should have been there in the
logs
or the stats collector was broken.
autovacuum would do it, but it would take its own time.

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

you can use \x (extended mode on) on psql. it will dump the results in a
mode that can be pasted as text fine.

>
> 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 just installed EDB 13.3 on windows. It is managed as a windows service
(set as automatic), when I rebooted.

shutdown
the machine, it shutdown and terminated connections fine, and restarted
back fine.
2021-07-22 14:27:19.171 IST [4636] LOG: disconnection: session time:
0:03:10.662 user=postgres database=postgres host=::1 port=53494
2021-07-22 14:27:21.805 IST [16120] ERROR: canceling statement due to user
request
2021-07-22 14:27:21.810 IST [8080] LOG: background worker "logical
replication launcher" (PID 16120) exited with exit code 1
2021-07-22 14:27:21.811 IST [8080] LOG: received fast shutdown request
2021-07-22 14:27:21.813 IST [8080] LOG: aborting any active transactions
2021-07-22 14:27:21.821 IST [11884] LOG: shutting down
2021-07-22 14:27:21.841 IST [8080] LOG: database system is shut down

startup
2021-07-22 14:28:01.373 IST [7268] LOG: starting PostgreSQL 13.3, compiled
by Visual C++ build 1914, 64-bit
2021-07-22 14:28:01.376 IST [7268] LOG: listening on IPv6 address "::",
port 5432
2021-07-22 14:28:01.378 IST [7268] LOG: listening on IPv4 address
"0.0.0.0", port 5432
2021-07-22 14:28:01.505 IST [8228] LOG: database system was shut down at
2021-07-22 14:27:21 IST
2021-07-22 14:28:01.550 IST [7268] LOG: database system is ready to accept
connections

i do not see new connections open, until explicitly do so
then
2021-07-22 14:30:20.733 IST [15240] LOG: connection received: host=::1
port=64579
2021-07-22 14:30:20.745 IST [15240] LOG: connection authorized:
user=postgres database=postgres application_name=psql

the connections i created early on before reboot were terminated, and did
not show up in pg_stat_activity after reboot.
The event logs (i am not pasting screenshots) also show normal shutdown and
restart.
I do not see new psql based connections automatically created unless i open
psql manually.
can you try setting
log_connections = on
log_disconnections = on
in the postgresql.conf file (this would require a restart)
then open a few psql connections, and do a reboot,
does it log connection states in logs?

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:
>
>
I would have gone ahead and said if this is a test machine, then take
backup using pg_dumpall and uninstall and reinstall EDB.
I have no clue why reboot is not triggering the shutdown of the service.
and check if the problem still persists. But I guess that would not be
really helpful other than good old windows solution to all problems.