Обсуждение: Obsolete or dead serverconnections after reboot

Hello community,

actually I have a problem wit PG13 on Windows 10 (both x64), Postgres 
version is 13.0.2.21090 from EDB installer.

I have to develop a logical replication cluster, where sometimes there 
happens a shutdown of one host. Logical replication works nice, thank 
you for that.

My issue is, when I do a normal Windows shutdown, when there are 
connections open to this database server and later this host comes up 
again in statistics I find the old connections from before shutdown. 
They don't go away and keep staying there over reboots. Only a restart 
of the Windows service throws them away.

The problem is, that they block a place in der connections counter of 
the server and after a while I cant login any more, because the maximum 
user-connection count is exceeded.

Example:
select pid, datid, application_name, usename , client_addr from 
pg_stat_activity;
before reboot (one active psql session, left it open on server shutdown):

   pid  | datid  | application_name | usename  |              client_addr
-------+--------+------------------+----------+---------------------------------------
  13548 | 156501 | psql             | postgres | 192.168.2.49

after reboot (and newly started psql session):

   pid  | datid  | application_name | usename  |              client_addr
-------+--------+------------------+----------+---------------------------------------
  13548 | 156501 | psql             | postgres | 192.168.2.49
  13764 | 156501 | psql             | postgres | 192.168.2.49

As we can see at pid column, the old connection is still there, but I 
have only on psql session open.

After restarting the postgres service( and psql reconnect):

   pid  | datid | application_name | usename  |              client_addr
-------+-------+------------------+----------+---------------------------------------
  12132 | 91805 | psql             | postgres | 192.168.2.49

Everything is like expected: one psql-session, one row in the statistics

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

Thank you,
Wolfgang

-- 
May the source be with you

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

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>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <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.
>      >
>      >
> 
> 
> 


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

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

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>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>>
>      >      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <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.
>      >      >
>      >      >
>      >
>      >
>      >
> 
> 
>     -- 
>     May the source be with you
> 


-- 
May the source be with you

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>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>>
>      >      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>>>
>      >      >      >   
>       <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>>
>      >      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <https://www.enterprisedb.com/blog/postgresql-shutdown>
>      >     <https://www.enterprisedb.com/blog/postgresql-shutdown
>     <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.
>      >      >      >
>      >      >      >
>      >      >
>      >      >
>      >      >
>      >
>      >
>      >     --
>      >     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!"