Obsolete or dead serverconnections after reboot

[WR]

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

Re: Obsolete or dead serverconnections after reboot

[Vijaykumar Jain]

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

Re: Obsolete or dead serverconnections after reboot

[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.git...@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
>
>
>
> 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.
>
>
>

Re: Obsolete or dead serverconnections after reboot

[WR]

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




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.

Re: Obsolete or dead serverconnections after reboot

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

Re: More records after sort

[Nicolas Seinlet]

Thanks all, I'll try this on Monday when I go back to the office.

Nicolas.
‐‐‐ Original Message ‐‐‐
On Wednesday, July 21st, 2021 at 02:55, Rob Sargent  
wrote:

> > Query:
> > 

> > INSERT INTO invl_aml_mapping_temp(invl_id, aml_id, cond)
> > 

> >    SELECT il.id, ml.id, 48
> > 

> >    FROM account_invoice_line il
> > 

> >    JOIN account_invoice i ON i.id = il.invoice_id
> > 

> >    JOIN account_move m ON m.id = i.move_id
> > 

> >    JOIN account_move_line ml ON ml.move_id = m.id
> > 

> >    JOIN res_company comp ON comp.id = i.company_id
> > 

> >    WHERE il.display_type IS NULL
> > 

> >  AND ml.tax_line_id IS NULL
> > 

> >  AND NOT EXISTS (SELECT invl_id FROM invl_aml_mapping WHERE 
> > invl_id=il.id)
> > 

> >  AND NOT EXISTS (SELECT aml_id FROM invl_aml_mapping WHERE 
> > aml_id=ml.id)
> > 

> >  AND i.id = 
> 

> Out of curiosity, would combining those two NOT EXISTS in one help? Ever? 
> 

> > i.e. WHERE invl_id=il.id or aml_id = ml.id

publickey - nicolas@seinlet.com - 0xCAEB7FAF.asc
Description: application/pgp-keys


signature.asc
Description: OpenPGP digital signature