Re: Obsolete or dead serverconnections after reboot - Mailing list pgsql-general

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