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.
