Hello Vijaykumar Jain,
it really seems that the reason for the problem is the not executed
shutdown of the service. I also sent a message to EDB. Maybe they will
answer although I'm not a registered customer.
In some earier logfiles I can find the shutdown messages like yours, but
not in all of them.
I'll try reinstalling the package. But I have this problem on two
machines. Maybe I'll try an actual build of the package.
Thanks so far for the service, I'm really greatful that someone helped
me so fast. Maybe I'm back here, when I got an answer from EDB.
Wolfgang
Am 22.07.2021 um 11:04 schrieb Vijaykumar Jain:
On Thu, 22 Jul 2021 at 12:41, WR <wolle321@xxxxxxxxxx
<mailto:wolle321@xxxxxxxxxx>> 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.
--
May the source be with you
