Re: Issue in PG start
The above is the contents of the log file from 'data' folder of PG. Message in Windows Application Event2021-05-08 10:26:19.425 IST [5588] LOG: listening on IPv4 address "0.0.0.0", port 5433 On Saturday, 8 May, 2021, 11:02:06 am IST, David G. Johnston wrote: On Friday, May 7, 2021, sivapostg...@yahoo.com wrote: Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following messageFATAL: the database system is starting up I uninstalled PG and installed [v 11.11] PG with the same data directory. Fortunately it worked even when we switched off /on the computer for lunch. NO issues yesterday. But today morning [08th May], again we have the same issue and same message. The log file is with the following message only 2021-05-08 10:26:20.783 IST [7360] LOG: database system was interrupted; last known up at 2021-05-07 18:46:00 IST How / where to debug the issue and correct it ? To see why the server is not completing its startup cycle you need to look at the server’s log file. All you’ve shown is a client failing to establish a connection every ten seconds. David J.
Re: pg_upgrade and wraparound
On Mon, May 03, 2021 at 11:10:44AM -0400, Jan Wieck wrote: > Not yet, but I will enter it so that we can get it into 15 for sure. I may be missing something but this is not listed: https://commitfest.postgresql.org/33/ Could you add it to the CF app please? There are so many patches and discussions that this would easily get lost if you don't register it. And from what I can see having a discussion on this matter looks adapted to me. -- Michael signature.asc Description: PGP signature
Re: idle_in_transaction_session_timeout
ok, But what is the workaround of this parameter in postgres 9.5, ,I need to increase the time of "idle in transaction" transactions. Regards, Atul On 5/7/21, luis.robe...@siscobra.com.br wrote: > > - Mensagem original - >> De: "Atul Kumar" >> Para: "pgsql-general" >> Enviadas: Sexta-feira, 7 de maio de 2021 3:34:44 >> Assunto: idle_in_transaction_session_timeout > >> Hi, > >> I have postgres 9.5 version running on my machine. > >> When I am trying to find out the parameter >> idle_in_transaction_session_timeout it is showing me below error: > >> postgres=# show idle_in_transaction_session_timeout; >> ERROR: unrecognized configuration parameter >> "idle_in_transaction_session_timeout" > >> I also checked postgresql.conf but even in this file there is no such >> parameter. > >> Please help me to find this parameter. > >> Regards, >> Atul > > idle_in_transaction_session_timeout first appears in v9.6[1] > > [1] https://www.postgresql.org/docs/9.6/runtime-config-client.html > > Luis R. Weck >
Re: idle_in_transaction_session_timeout
Why do you want to increase that timeout ? I hope you are aware long idle in transactions connections would delay vacuuming and result in much larger bloats and slow down the db over period of time. You need to upgrade and push using the reasons that these are not supported. asking for workarounds around an unsupported version will only increase headaches in the future. On Sat, May 8, 2021, 3:58 PM Atul Kumar wrote: > ok, But what is the workaround of this parameter in postgres 9.5, ,I > need to increase the time of "idle in transaction" transactions. > > > > > > Regards, > Atul > > > > > > > > On 5/7/21, luis.robe...@siscobra.com.br > wrote: > > > > - Mensagem original - > >> De: "Atul Kumar" > >> Para: "pgsql-general" > >> Enviadas: Sexta-feira, 7 de maio de 2021 3:34:44 > >> Assunto: idle_in_transaction_session_timeout > > > >> Hi, > > > >> I have postgres 9.5 version running on my machine. > > > >> When I am trying to find out the parameter > >> idle_in_transaction_session_timeout it is showing me below error: > > > >> postgres=# show idle_in_transaction_session_timeout; > >> ERROR: unrecognized configuration parameter > >> "idle_in_transaction_session_timeout" > > > >> I also checked postgresql.conf but even in this file there is no such > >> parameter. > > > >> Please help me to find this parameter. > > > >> Regards, > >> Atul > > > > idle_in_transaction_session_timeout first appears in v9.6[1] > > > > [1] https://www.postgresql.org/docs/9.6/runtime-config-client.html > > > > Luis R. Weck > > > > >
Re: Issue in PG start
Or server’s log file is available elsewhere ? On Saturday, 8 May, 2021, 01:24:50 pm IST, sivapostg...@yahoo.com wrote: The above is the contents of the log file from 'data' folder of PG. Message in Windows Application Event2021-05-08 10:26:19.425 IST [5588] LOG: listening on IPv4 address "0.0.0.0", port 5433 On Saturday, 8 May, 2021, 11:02:06 am IST, David G. Johnston wrote: On Friday, May 7, 2021, sivapostg...@yahoo.com wrote: Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following messageFATAL: the database system is starting up I uninstalled PG and installed [v 11.11] PG with the same data directory. Fortunately it worked even when we switched off /on the computer for lunch. NO issues yesterday. But today morning [08th May], again we have the same issue and same message. The log file is with the following message only 2021-05-08 10:26:20.783 IST [7360] LOG: database system was interrupted; last known up at 2021-05-07 18:46:00 IST How / where to debug the issue and correct it ? To see why the server is not completing its startup cycle you need to look at the server’s log file. All you’ve shown is a client failing to establish a connection every ten seconds. David J.
Re: idle_in_transaction_session_timeout
On Sat, May 8, 2021 at 7:55 AM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Why do you want to increase that timeout ? > I hope you are aware long idle in transactions connections would delay > vacuuming and result in much larger bloats and slow down the db over period > of time. > You need to upgrade and push using the reasons that these are not > supported. > asking for workarounds around an unsupported version will only increase > headaches in the future. > +1 > > > On Sat, May 8, 2021, 3:58 PM Atul Kumar wrote: > >> ok, But what is the workaround of this parameter in postgres 9.5, ,I >> need to increase the time of "idle in transaction" transactions. >> >> >> >> >> >> Regards, >> Atul >> >> >> >> >> >> >> >> On 5/7/21, luis.robe...@siscobra.com.br >> wrote: >> > >> > - Mensagem original - >> >> De: "Atul Kumar" >> >> Para: "pgsql-general" >> >> Enviadas: Sexta-feira, 7 de maio de 2021 3:34:44 >> >> Assunto: idle_in_transaction_session_timeout >> > >> >> Hi, >> > >> >> I have postgres 9.5 version running on my machine. >> > >> >> When I am trying to find out the parameter >> >> idle_in_transaction_session_timeout it is showing me below error: >> > >> >> postgres=# show idle_in_transaction_session_timeout; >> >> ERROR: unrecognized configuration parameter >> >> "idle_in_transaction_session_timeout" >> > >> >> I also checked postgresql.conf but even in this file there is no such >> >> parameter. >> > >> >> Please help me to find this parameter. >> > >> >> Regards, >> >> Atul >> > >> > idle_in_transaction_session_timeout first appears in v9.6[1] >> > >> > [1] https://www.postgresql.org/docs/9.6/runtime-config-client.html >> > >> > Luis R. Weck >> > >> >> >> -- Regards, Avinash Vallarapu +1-902-221-5976
Re: Issue in PG start
On 5/7/21 10:14 PM, sivapostg...@yahoo.com wrote: Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following message FATAL: the database system is starting up I uninstalled PG and installed [v 11.11] PG with the same data directory. Fortunately it worked even when we switched off /on the computer for lunch. NO issues yesterday. First why switch the computer off for lunch? Second did you stop the Postgres server before shutting down the machine? But today morning [08th May], again we have the same issue and same message. The log file is with the following message only In postgresql.conf what is log_min_messages set to? See here: https://www.postgresql.org/docs/11/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN Table 19.2. Message Severity Levels for more information on what the levels represent. Do you have replication set up with this server? 2021-05-08 10:26:20.783 IST [7360] LOG: database system was interrupted; last known up at 2021-05-07 18:46:00 IST 2021-05-08 10:28:36.159 IST [7048] FATAL: the database system is starting up Happiness Always BKR Sivaprakash -- Adrian Klaver adrian.kla...@aklaver.com
Re: Issue in PG start
On 5/8/21 10:53 AM, Adrian Klaver wrote: On 5/7/21 10:14 PM, sivapostg...@yahoo.com wrote: Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following message FATAL: the database system is starting up I uninstalled PG and installed [v 11.11] PG with the same data directory. Fortunately it worked even when we switched off /on the computer for lunch. NO issues yesterday. First why switch the computer off for lunch? If it's a desktop and he's a developer, then that's reasonable. Second did you stop the Postgres server before shutting down the machine? Is Windows Postgresql a service? If so then Windows "should" shut it down cleanly when you cleanly power off the machine. -- Angular momentum makes the world go 'round.
Re: Issue in PG start
On 5/8/21 9:23 AM, Ron wrote: On 5/8/21 10:53 AM, Adrian Klaver wrote: On 5/7/21 10:14 PM, sivapostg...@yahoo.com wrote: Hello, PG 11.8 in Windows 10 and currently PG 11.11 Yesterday [07th May] morning when we switched on the computer and subsequently PGAdmin, we got the message following message FATAL: the database system is starting up I uninstalled PG and installed [v 11.11] PG with the same data directory. Fortunately it worked even when we switched off /on the computer for lunch. NO issues yesterday. First why switch the computer off for lunch? If it's a desktop and he's a developer, then that's reasonable. Second did you stop the Postgres server before shutting down the machine? Is Windows Postgresql a service? If so then Windows "should" shut it down cleanly when you cleanly power off the machine. The log would indicate otherwise: 2021-05-08 10:26:20.783 IST [7360] LOG: database system was interrupted; last known up at 2021-05-07 18:46:00 IST So something is not working as it should. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Issue in PG start
Adrian Klaver writes: > On 5/8/21 9:23 AM, Ron wrote: >> Is Windows Postgresql a service? If so then Windows "should" shut it >> down cleanly when you cleanly power off the machine. > The log would indicate otherwise: > >>> 2021-05-08 10:26:20.783 IST [7360] LOG: database system was >>> interrupted; last known up at 2021-05-07 18:46:00 IST > > So something is not working as it should. Yeah, that's bad news. In theory, as long as you didn't set fsync = off, Postgres should be able to recover from an unclean shutdown. In practice, that requires all the levels of the hardware and software stack to pay strict attention to write ordering requirements. It is, um, pretty common for inexpensive Windows gear to not be very careful about that. I'd say this particular machine has clearly failed the plug-pull test [1]. If the OP is in the habit of just hitting the power switch at lunchtime, I'd say he's got to stop doing that. Telling Windows to shut down will add a few seconds, but greatly reduce the risk of problems (and not only for Postgres). If he *did* tell Windows to shut down, then there's something else that needs adjustment. regards, tom lane [1] https://wiki.postgresql.org/wiki/Reliable_Writes
Re: idle_in_transaction_session_timeout
On 2021-05-08 15:58:27 +0530, Atul Kumar wrote: > ok, But what is the workaround of this parameter in postgres 9.5, ,I > need to increase the time of "idle in transaction" transactions. What makes you think that there is an "idle in transaction" timeout in 9.5? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Metric to calculate WAL size left to transfer to Standby
Hello Laurenz, The above metric works fine for my primary server. However, We have a cascading setup in our production system. This particular query doesn't work for my intermediate server which is standby to the primary server but also a master to one more standby server. We get the following error: prod=# SELECT round(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / (1024 :: NUMERIC ^ 2), 2 ) AS replay_lag FROM pg_stat_replication where application_name like 'rtv%'; ERROR: recovery is in progress HINT: WAL control functions cannot be executed during recovery. I wanted to also track the transfer/replay lag between the intermediate server and the final standby. I narrowed down the problem with pg_current_wal_lsn() procedure. Any idea on how to mitigate this? or any alternative to the pg_current_wal_lsn() procedure that I can use to get transfer lag? Thanks, Viral Shah On Fri, Apr 16, 2021 at 7:52 PM Viral Shah wrote: > Hello Laurenz, > > Thank you so much for sending the query. It was exactly what I needed. I > just made 1 modification to beautify the transfer and replay lag and I can > see the size in bytes. > > SELECT application_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), > flush_lsn)) AS transfer_lag, >pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS > replay_lag > FROM pg_stat_replication; > > I am now using zabbix to constantly monitor them and notify myself if it > breaches a certain threshold. > > > Thanks again! > > > Best, > > Viral Shah > > Nodal Exchange LLC > > > > On Thu, Apr 15, 2021 at 8:10 AM Laurenz Albe > wrote: > >> On Wed, 2021-04-14 at 17:50 -0400, Viral Shah wrote: >> > We have a PostgreSQL 10.12 cluster of servers in two different data >> centers. >> > Off lately, in the case of a large WAL generation, we are seeing >> replication >> > delay between the master and the standby server. These delays have off >> lately >> > been there for an unusually long time. I was wondering if we have any >> metric >> > that can calculate the amount (size) of WAL transfer left between >> master and >> > standby? >> > >> > PS: We have ensured we have upgraded our firewalls for better speed >> transfer. >> > >> > Any help on how to figure out the slowness in the WAL transfer would be >> much appreciated. >> >> SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS transfer_lag, >>pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag >> FROM pg_stat_replication; >> >> If both are delayed, it might be that the network cannot cope. >> >> If only the second number is delayed, you have replication conflicts >> with queries on the standby. >> >> Yours, >> Laurenz Albe >> -- >> Cybertec | https://www.cybertec-postgresql.com >> >>
Re: Issue in PG start
1. First why switch the computer off for lunch? It's a development machine and I'm a developer. We used to shut down every computer for lunch. We shut down the windows and not just hit the power switch. We advice and follow every users and client to shut down the windows properly and we follow it. 2. In postgresql.conf what is log_min_messages set to? Warning 3. Do you have replication set up with this server? No 4. Is Windows Postgresql a service? If so then Windows "should" shut it down cleanly when you cleanly power off the machine. Yes, Postgresql is a service and windows is shut down properly. We do shut down computers during lunch and when closing office, regularly and properly for the past 6 months [ since PG was installed ] without any issue. This is the first time that we face this issue. Need to find a solution that will be helpful if that happens in a production machine. Happiness Always BKR Sivaprakash On Saturday, 8 May, 2021, 10:24:03 pm IST, Tom Lane wrote: Adrian Klaver writes: > On 5/8/21 9:23 AM, Ron wrote: >> Is Windows Postgresql a service? If so then Windows "should" shut it >> down cleanly when you cleanly power off the machine. > The log would indicate otherwise: > >>> 2021-05-08 10:26:20.783 IST [7360] LOG: database system was >>> interrupted; last known up at 2021-05-07 18:46:00 IST > > So something is not working as it should. Yeah, that's bad news. In theory, as long as you didn't set fsync = off, Postgres should be able to recover from an unclean shutdown. In practice, that requires all the levels of the hardware and software stack to pay strict attention to write ordering requirements. It is, um, pretty common for inexpensive Windows gear to not be very careful about that. I'd say this particular machine has clearly failed the plug-pull test [1]. If the OP is in the habit of just hitting the power switch at lunchtime, I'd say he's got to stop doing that. Telling Windows to shut down will add a few seconds, but greatly reduce the risk of problems (and not only for Postgres). If he *did* tell Windows to shut down, then there's something else that needs adjustment. regards, tom lane [1] https://wiki.postgresql.org/wiki/Reliable_Writes
Re: Issue in PG start
Yesterday's log file. Till we shut down the windows the message was:2021-05-08 19:03:32.151 IST [10376] FATAL: the database system is starting up2021-05-08 19:03:42.210 IST [7568] FATAL: the database system is starting up Today morning log file, I tried to start the service [ after setting the startup type to Manual ] twice***2021-05-09 07:43:02.122 IST [16776] LOG: test message did not get through on socket for statistics collector2021-05-09 07:43:02.122 IST [16776] LOG: trying another address for the statistics collector2021-05-09 07:43:02.624 IST [16776] LOG: test message did not get through on socket for statistics collector2021-05-09 07:43:02.624 IST [16776] LOG: disabling statistics collector for lack of working socket2021-05-09 07:43:02.624 IST [16776] WARNING: autovacuum not started because of misconfiguration2021-05-09 07:43:02.624 IST [16776] HINT: Enable the "track_counts" option.2021-05-09 07:43:02.685 IST [17144] LOG: database system was interrupted; last known up at 2021-05-07 18:46:00 IST2021-05-09 07:43:04.489 IST [17196] FATAL: the database system is starting up2021-05-09 07:43:14.538 IST [17332] FATAL: the database system is starting up2021-05-09 07:43:24.577 IST [2716] FATAL: the database system is starting up2021-05-09 07:43:34.638 IST [12840] FATAL: the database system is starting up2021-05-09 07:43:44.782 IST [6472] FATAL: the database system is starting up2021-05-09 07:43:54.852 IST [5288] FATAL: the database system is starting up2021-05-09 07:44:04.885 IST [17292] FATAL: the database system is starting up2021-05-09 07:44:14.949 IST [17304] FATAL: the database system is starting up2021-05-09 07:44:25.021 IST [15816] FATAL: the database system is starting up2021-05-09 07:44:35.216 IST [15892] FATAL: the database system is starting up2021-05-09 07:44:45.251 IST [7704] FATAL: the database system is starting up2021-05-09 07:44:55.432 IST [15644] FATAL: the database system is starting up2021-05-09 07:45:05.549 IST [3188] FATAL: the database system is starting up2021-05-09 07:45:14.796 IST [17144] LOG: database system was not properly shut down; automatic recovery in progress2021-05-09 07:45:14.864 IST [17144] LOG: redo starts at 2/88985C802021-05-09 07:45:14.866 IST [17144] LOG: invalid record length at 2/889872B8: wanted 24, got 02021-05-09 07:45:14.866 IST [17144] LOG: redo done at 2/889872802021-05-09 07:45:14.866 IST [17144] LOG: last completed transaction was at log time 2021-05-07 18:48:44.227574+05:302021-05-09 07:45:15.046 IST [16776] LOG: database system is ready to accept connections2021-05-09 07:50:17.295 IST [17204] ERROR: canceling statement due to user request2021-05-09 07:50:17.300 IST [16776] LOG: received fast shutdown request2021-05-09 07:50:17.322 IST [4408] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. 2021-05-09 07:50:17.322 IST [11700] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. 2021-05-09 07:50:17.322 IST [16744] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. 2021-05-09 07:50:17.322 IST [16960] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. 2021-05-09 07:50:17.322 IST [14856] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host. 2021-05-09 07:50:17.412 IST [16776] LOG: aborting any active transactions2021-05-09 07:50:17.419 IST [16776] LOG: background worker "logical replication launcher" (PID 17204) exited with exit code 12021-05-09 07:50:17.548 IST [11032] LOG: shutting down2021-05-09 07:50:17.607 IST [16776] LOG: database system is shut down** Log file after a re-start ***2021-05-09 07:52:40.341 IST [13220] LOG: test message did not get through on socket for statistics collector2021-05-09 07:52:40.342 IST [13220] LOG: trying another address for the statistics collector2021-05-09 07:52:40.844 IST [13220] LOG: test message did not get through on socket for statistics collector2021-05-09 07:52:40.844 IST [13220] LOG: disabling statistics collector for lack of working socket2021-05-09 07:52:40.845 IST [13220] WARNING: autovacuum not started because of misconfiguration2021-05-09 07:52:40.845 IST [13220] HINT: Enable the "track_counts" option.2021-05-09 07:52:40.904 IST [13860] LOG: database system was shut down at 2021-05-09 07:50:17 IST2021-05-09 07:52:41.122 IST [13220] LOG: database system is ready to accept connections2021-05-09 07:55:23.628 IST [11188] LOG: using stale statistics instead of current ones because stats collector is not responding* Not sure what happened, it's working now. Any idea of what happened and what s
Re: Issue in PG start
On 5/8/21 9:19 PM, sivapostg...@yahoo.com wrote: 1. First why switch the computer off for lunch? It's a development machine and I'm a developer. We used to shut down every computer for lunch. We shut down the windows and not just hit the power switch. We advice and follow every users and client to shut down the windows properly and we follow it. 2. In postgresql.conf what is log_min_messages set to? Warning 3. Do you have replication set up with this server? No 4. Is Windows Postgresql a service? If so then Windows "should" shut it down cleanly when you cleanly power off the machine. Yes, Postgresql is a service and windows is shut down properly. We do shut down computers during lunch and when closing office, regularly and properly for the past 6 months [ since PG was installed ] without any issue. This is the first time that we face this issue. Need to find a solution that will be helpful if that happens in a production machine. I suggest that you manually stop postgresql before shutting down your PC. If that solves the problem, then you've discovered that there's a problem in how the service stops Postgresql. pg_ctl stop -D X:\path\to\data -mfast -- Angular momentum makes the world go 'round.
