Obsolete or dead serverconnections after reboot

Is there a way to avoid this (without restarting the service after every
reboot). Is this a bug or a normal behavior?

I have less knowledge of windows.
https://www.enterprisedb.com/blog/postgresql-shutdown

Do you see shutdown/termination messages in the db logs or windows event
logs when the machine is rebooted?

You get the same pid and query, does it also has the same age( time since
it started),
I mean is the stats table updated with new data for stale connections or
they remain static.

Do you see the same issue when the machine is power cycled.

Maybe windows might be preserving the memory state on disk and reading it
back on reboot (like sleep) unless there are instructions to shutdown the
db server on reboot. Idk.

What are the state of the connections in pg_stat_activity abd process
explorer before and after reboot. The sockets exists and active, or are
residual in pg stats only.

select pg_stat_reset();

Can you run above function, to check if stats are reset and things are fine
without a pg restart?
I not able to understand how new connection stats are added, along with old
stale states, if I assume stats collector process is having issues , or the
stats folder is corrupt etc.
That would also mean, all table stats would be off or not updated too?
Is that so? Or analyse works fine on tables without a restart?

On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain <
vijaykumarjain.github@gmail.com> wrote:

Hello Vijaykumar Jain,

thank you for fast answer, today I'm not able to access the hardware,
I'll be back tomorrow and will do the required tests.

Wolfgang

Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:

Hello,

Would you be able to verify the process trees for those PIDs on Windows?
You may be able to see who holds the connections?

Regards,
Ninad Shah

On Wed, 21 Jul 2021 at 19:15, WR <wolle321@freenet.de> wrote:

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

Hello Ninad Shah,

I think, nobody holds the connections, because the state is idle. But
I'm not shure what means: the connection is idle.

One interesting fact was: when I dont stop the psql commandline on the
client (and dont start another query, wht ends up in a connection reset,
while the server is down), psql uses the same connection after reboot
and the state goes from idle back to active.

So there are two questions: what will happen to idle connctions after a
while, if the client doesnt exist anymore.
And is this desired behavior, the the postgres-Server-Service does not a
shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)

Thanks you, Greeting from germany,
Wolfgang

Am 21.07.2021 um 16:10 schrieb Ninad Shah:

Hello,

Would you be able to verify the process trees for those PIDs on Windows?
You may be able to see who holds the connections?

Regards,
Ninad Shah

On Wed, 21 Jul 2021 at 19:15, WR <wolle321@freenet.de
<mailto:wolle321@freenet.de>> wrote:

Hello Vijaykumar Jain,

thank you for fast answer, today I'm not able to access the hardware,
I'll be back tomorrow and will do the required tests.

Wolfgang

Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:

select pg_stat_reset();

Can you run above function, to check if stats are reset and

things are

fine without a pg restart?
I not able to understand how new connection stats are added,

along with

old stale states, if I assume stats collector process is having

issues ,

or the stats folder is corrupt etc.
That would also mean, all table stats would be off or not updated

too?

Is that so? Or analyse works fine on tables without a restart?

On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
<vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>

<mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>>> wrote:

         Is there a way to avoid this (without restarting the service
         after every
         reboot). Is this a bug or a normal behavior?

     I have less knowledge of windows.
https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;

     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;&gt;

     Do you see shutdown/termination messages in the db logs or

windows

     event logs when the machine is rebooted?

     You get the same pid and query, does it also has the same

age( time

     since it started),
     I mean is the stats table updated with new data for stale
     connections or they remain static.

     Do you see the same issue when the machine is power cycled.

     Maybe windows might be preserving the memory state on disk and
     reading it back on reboot (like sleep) unless there are

instructions

     to shutdown the db server on reboot. Idk.

     What are the state of the connections in pg_stat_activity abd
     process explorer before and after reboot. The sockets exists and
     active,  or are residual in pg stats only.

--
May the source be with you

I also looked for the shutdown mode:

since there is no -m parameter in the commandline for starting the
windows-postgres-service, the shutdown mode seems to be the default:
fast

Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:

select pg_stat_reset();

Can you run above function, to check if stats are reset and things are
fine without a pg restart?
I not able to understand how new connection stats are added, along with
old stale states, if I assume stats collector process is having issues ,
or the stats folder is corrupt etc.
That would also mean, all table stats would be off or not updated too?
Is that so? Or analyse works fine on tables without a restart?

On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
<vijaykumarjain.github@gmail.com
<mailto:vijaykumarjain.github@gmail.com>> wrote:

Is there a way to avoid this (without restarting the service
after every
reboot). Is this a bug or a normal behavior?

I have less knowledge of windows.
https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;

Do you see shutdown/termination messages in the db logs or windows
event logs when the machine is rebooted?

You get the same pid and query, does it also has the same age( time
since it started),
I mean is the stats table updated with new data for stale
connections or they remain static.

Do you see the same issue when the machine is power cycled.

Maybe windows might be preserving the memory state on disk and
reading it back on reboot (like sleep) unless there are instructions
to shutdown the db server on reboot. Idk.

What are the state of the connections in pg_stat_activity abd
process explorer before and after reboot. The sockets exists and
active,  or are residual in pg stats only.

--
May the source be with you

On Thu, 22 Jul 2021 at 12:41, WR <wolle321@freenet.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.

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@freenet.de
<mailto:wolle321@freenet.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.

--
May the source be with you

Factually, Windows itself has a number of issues. Hence, it is always
suggested to use Linux with it.

It can be explored further by querying the table from which
pg_stat_activity gathers data.

Regards,
Ninad Shah

On Thu, 22 Jul 2021 at 12:51, WR <wolle321@freenet.de> wrote:

Hello,

unfortunately, it's my companys choice of OS.
In private life I'm riding the penguin.

One last thing: I have 2 Laptops, where the shutdown doesn't work, and
one VirtualBox machine where it works like a charm. Maybe it has
something to do with the power management.

As a workaround I set the value
idle_in_transaction_session_timeout
to 5min. So the server kills this idle connections from before the power
cycle (and all others too) after 5 minutes.

Thank you, guys

(PS.: still no answer from EDB)

Am 22.07.2021 um 16:51 schrieb Ninad Shah:

Factually, Windows itself has a number of issues. Hence, it is always
suggested to use Linux with it.

It can be explored further by querying the table from which
pg_stat_activity gathers data.

Regards,
Ninad Shah

On Thu, 22 Jul 2021 at 12:51, WR <wolle321@freenet.de
<mailto:wolle321@freenet.de>> wrote:

Hello Ninad Shah,

I think, nobody holds the connections, because the state is idle. But
I'm not shure what means: the connection is idle.

One interesting fact was: when I dont stop the psql commandline on the
client (and dont start another query, wht ends up in a connection
reset,
while the server is down), psql uses the same connection after reboot
and the state goes from idle back to active.

So there are two questions: what will happen to idle connctions after a
while, if the client doesnt exist anymore.
And is this desired behavior, the the postgres-Server-Service does
not a
shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)

Thanks you, Greeting from germany,
Wolfgang

Am 21.07.2021 um 16:10 schrieb Ninad Shah:

Hello,

Would you be able to verify the process trees for those PIDs on

Windows?

You may be able to see who holds the connections?

Regards,
Ninad Shah

On Wed, 21 Jul 2021 at 19:15, WR <wolle321@freenet.de

<mailto:wolle321@freenet.de>

<mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>>> wrote:

     Hello Vijaykumar Jain,

     thank you for fast answer, today I'm not able to access the

hardware,

     I'll be back tomorrow and will do the required tests.

     Wolfgang

     Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
      > select pg_stat_reset();
      >
      > Can you run above function, to check if stats are reset and
     things are
      > fine without a pg restart?
      > I not able to understand how new connection stats are added,
     along with
      > old stale states, if I assume stats collector process is

having

     issues ,
      > or the stats folder is corrupt etc.
      > That would also mean, all table stats would be off or not

updated

     too?
      > Is that so? Or analyse works fine on tables without a restart?
      >
      > On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
      > <vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>

     <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>>

      > <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>

     <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>>>> wrote:

      >
      >
      >
      >         Is there a way to avoid this (without restarting

the service

      >         after every
      >         reboot). Is this a bug or a normal behavior?
      >
      >
      >     I have less knowledge of windows.
      > https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;

     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;&gt;

      >     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;

     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;&gt;&gt;

      >
      >
      >
      >     Do you see shutdown/termination messages in the db logs or
     windows
      >     event logs when the machine is rebooted?
      >
      >     You get the same pid and query, does it also has the same
     age( time
      >     since it started),
      >     I mean is the stats table updated with new data for stale
      >     connections or they remain static.
      >
      >     Do you see the same issue when the machine is power

cycled.

      >
      >     Maybe windows might be preserving the memory state on

disk and

      >     reading it back on reboot (like sleep) unless there are
     instructions
      >     to shutdown the db server on reboot. Idk.
      >
      >     What are the state of the connections in

pg_stat_activity abd

      >     process explorer before and after reboot. The sockets

exists and

      >     active,  or are residual in pg stats only.
      >
      >

--
May the source be with you

--
May the source be with you

Just to make it clear, "idle in transaction" and "idle" are different
terms. "Idle in transaction" refers to connections that are waiting for
either commit or rollback.

Additionally, this could be a bug as well. However, it's difficult to
conclude at this stage. You may report this to postgresql-bugs group.

Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 17:25, WR <wolle321@freenet.de> wrote:

Hello Ninad Shah,

Thank you for clarification.
The state of the mentioned connections was "Idle" in pg_stat_activity.

Now on my Laptops the shutdown of the postgres-service works too.

I had to switch off the "Schnellstart" (in english something like "fast
start" or "fast boot"), which can be found under Settings > System >
Energy Options > Behavior when pushing the Power-Button / Closing the
Computer

There in the lower part of the dialog "Settings for Shutdown"

The Option is then disabled first, but can be enabled with a link in the
upper Part of the dialog.

(Dont ask me why it is so complicated, and what this Option has to do
with the Power Button / closing the Display, it affects every kind of
shutdown)

Thanks you for your help.
Wolfgang

Am 23.07.2021 um 16:31 schrieb Ninad Shah:

Just to make it clear, "idle in transaction" and "idle" are different
terms. "Idle in transaction" refers to connections that are waiting for
either commit or rollback.

Additionally, this could be a bug as well. However, it's difficult to
conclude at this stage. You may report this to postgresql-bugs group.

Regards,
Ninad Shah

On Fri, 23 Jul 2021 at 17:25, WR <wolle321@freenet.de
<mailto:wolle321@freenet.de>> wrote:

Hello,

unfortunately, it's my companys choice of OS.
In private life I'm riding the penguin.

One last thing: I have 2 Laptops, where the shutdown doesn't work, and
one VirtualBox machine where it works like a charm. Maybe it has
something to do with the power management.

As a workaround I set the value
idle_in_transaction_session_timeout
to 5min. So the server kills this idle connections from before the
power
cycle (and all others too) after 5 minutes.

Thank you, guys

(PS.: still no answer from EDB)

Am 22.07.2021 um 16:51 schrieb Ninad Shah:

Factually, Windows itself has a number of issues. Hence, it is

always

suggested to use Linux with it.

It can be explored further by querying the table from which
pg_stat_activity gathers data.

Regards,
Ninad Shah

On Thu, 22 Jul 2021 at 12:51, WR <wolle321@freenet.de

<mailto:wolle321@freenet.de>

<mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>>> wrote:

     Hello Ninad Shah,

     I think, nobody holds the connections, because the state is

idle. But

     I'm not shure what means: the connection is idle.

     One interesting fact was: when I dont stop the psql

commandline on the

     client (and dont start another query, wht ends up in a connection
     reset,
     while the server is down), psql uses the same connection

after reboot

     and the state goes from idle back to active.

     So there are two questions: what will happen to idle

connctions after a

     while, if the client doesnt exist anymore.
     And is this desired behavior, the the postgres-Server-Service

does

     not a
     shutdown on reboot on windows. (seem my mail to Vijaykumar Jain)

     Thanks you, Greeting from germany,
     Wolfgang

     Am 21.07.2021 um 16:10 schrieb Ninad Shah:
      > Hello,
      >
      > Would you be able to verify the process trees for those

PIDs on

     Windows?
      > You may be able to see who holds the connections?
      >
      >
      > Regards,
      > Ninad Shah
      >
      > On Wed, 21 Jul 2021 at 19:15, WR <wolle321@freenet.de

<mailto:wolle321@freenet.de>

     <mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>>
      > <mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>

<mailto:wolle321@freenet.de <mailto:wolle321@freenet.de>>>> wrote:

      >
      >     Hello Vijaykumar Jain,
      >
      >     thank you for fast answer, today I'm not able to

access the

     hardware,
      >     I'll be back tomorrow and will do the required tests.
      >
      >     Wolfgang
      >
      >     Am 21.07.2021 um 15:14 schrieb Vijaykumar Jain:
      >      > select pg_stat_reset();
      >      >
      >      > Can you run above function, to check if stats are

reset and

      >     things are
      >      > fine without a pg restart?
      >      > I not able to understand how new connection stats

are added,

      >     along with
      >      > old stale states, if I assume stats collector

process is

     having
      >     issues ,
      >      > or the stats folder is corrupt etc.
      >      > That would also mean, all table stats would be off

or not

     updated
      >     too?
      >      > Is that so? Or analyse works fine on tables without

a restart?

      >      >
      >      > On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain
      >      > <vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>

     <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>>

      >     <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>

     <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>>>

      >      > <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>

     <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>>

      >     <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>

     <mailto:vijaykumarjain.github@gmail.com

<mailto:vijaykumarjain.github@gmail.com>>>>> wrote:

      >      >
      >      >
      >      >
      >      >         Is there a way to avoid this (without

restarting

     the service
      >      >         after every
      >      >         reboot). Is this a bug or a normal behavior?
      >      >
      >      >
      >      >     I have less knowledge of windows.
      >      >

https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;

     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;&gt;

      >     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;

     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;&gt;&gt;

      >      >

 <https://www.enterprisedb.com/blog/postgresql-shutdown
<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;

     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;&gt;

      >     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;

     <https://www.enterprisedb.com/blog/postgresql-shutdown

<https://www.enterprisedb.com/blog/postgresql-shutdown&gt;&gt;&gt;&gt;

      >      >
      >      >
      >      >
      >      >     Do you see shutdown/termination messages in the

db logs or

      >     windows
      >      >     event logs when the machine is rebooted?
      >      >
      >      >     You get the same pid and query, does it also

has the same

      >     age( time
      >      >     since it started),
      >      >     I mean is the stats table updated with new data

for stale

      >      >     connections or they remain static.
      >      >
      >      >     Do you see the same issue when the machine is power
     cycled.
      >      >
      >      >     Maybe windows might be preserving the memory

state on

     disk and
      >      >     reading it back on reboot (like sleep) unless

there are

      >     instructions
      >      >     to shutdown the db server on reboot. Idk.
      >      >
      >      >     What are the state of the connections in
     pg_stat_activity abd
      >      >     process explorer before and after reboot. The

sockets

     exists and
      >      >     active,  or are residual in pg stats only.
      >      >
      >      >
      >
      >
      >

     --
     May the source be with you

--
May the source be with you

--
May the source be with you

On 2021-07-26 09:31:03 +0200, WR wrote:

Now on my Laptops the shutdown of the postgres-service works too.

I had to switch off the "Schnellstart" (in english something like "fast
start" or "fast boot"), which can be found under Settings > System > Energy
Options > Behavior when pushing the Power-Button / Closing the Computer

...

(Dont ask me why it is so complicated, and what this Option has to do with
the Power Button / closing the Display, it affects every kind of shutdown)

Windows "fast startup" is a lie. If this is activated, the system isn't
shut down, it just hibernates. So the reason you are seeing connections
from "before the shutdown" is that there never really was a shutdown.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"