[BUGS] psql: FATAL: the database system is in recovery mode
Hi All, I am doing some database related queries and this is working fine at fedora core 4. But the same database queries giving the FATAL error on fedora 9. If I restarts the database on fedora core 9 then this is perfectlry working without giving any error. My postgres version is On Fedora core-9[FC9] Machine: $ rpm -qa|grep postgres postgresql-server-8.3.1-1.fc9.i386 postgresql-devel-8.3.1-1.fc9.i386 postgresql-python-8.3.1-1.fc9.i386 postgresql-8.3.1-1.fc9.i386 postgresql-libs-8.3.1-1.fc9.i386 On Fedora core-4[FC49] Machine: $ rpm -qa|grep postgres postgresql-server-8.0.3-1 postgresql-libs-8.0.3-1 postgresql-odbc-08.00.0100-1 postgresql-jdbc-8.0.3-1 postgresql-docs-8.0.3-1 postgresql-tcl-8.0.3-1 postgresql-test-8.0.3-1 postgresql-devel-8.0.3-1 postgresql-8.0.3-1 gnucash-backend-postgres-1.8.11-3 freeradius-postgresql-1.0.2-2 postgresql-python-8.0.3-1 postgresql-contrib-8.0.3-1 postgresql-pl-8.0.3-1 Problem is: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode psql: FATAL: the database system is in recovery mode server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost psql: FATAL: the database system is in recovery mode -- Is this database bug or there is any versioning incopatability. Can anyone please help me in this regards. -- With Regards, Bhushan
Re: [BUGS] psql: FATAL: the database system is in recovery mode
Thanks for your response. As follows is log details ERROR: table "transactionrecord_5917" does not exist STATEMENT: drop table TransactionRecord_5917; ERROR: table "pagerecord_5917" does not exist STATEMENT: drop table PageRecord_5917; ERROR: table "urlrecord_5917" does not exist STATEMENT: drop table URLRecord_5917; LOG: server process (PID 29225) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing FATAL: the database system is in recovery mode LOG: database system was interrupted; last known up at 2009-06-23 12:49:49 IST FATAL: the database system is in recovery mode FATAL: the database system is in recovery mode LOG: database system was not properly shut down; automatic recovery in progress FATAL: the database system is in recovery mode LOG: redo starts at 0/185239F8 FATAL: the database system is in recovery mode FATAL: the database system is in recovery mode FATAL: the database system is in recovery mode LOG: record with zero length at 0/185A67FC LOG: redo done at 0/185A67D0 LOG: last completed transaction was at log time 2009-06-23 14:49:10.37602+05:30 FATAL: the database system is in recovery mode LOG: autovacuum launcher started LOG: database system is ready to accept connections With Regards, Bhushan On 6/24/09, Craig Ringer wrote: > > Bhushan Verma wrote: > > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > connection to server was lost > > > Look in the PostgreSQL server logs for more information. It's probably > in /var/log/postgresql or something like that - it depends on exactly > how the distro packaged PostgreSQL. > > -- > > Craig Ringer > -- With Regards, Bhushan
Re: [BUGS] psql: FATAL: the database system is in recovery mode
Hi, Thanks for your response. I am not able to find out the core file. but in log file its giving the message like: LOG: server process (PID 29225) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing FATAL: the database system is in recovery mode LOG: database system was interrupted; last known up at 2009-06-23 12:49:49 IST FATAL: the database system is in recovery mode FATAL: the database system is in recovery mode I have searched for the core file for this pid. Is there any spcefic path for the postgres executable? I have already checked for ulimit -c unlimited etc. On my system core file for some other application are generating properly. With Regards, Bhushan On 6/24/09, Heikki Linnakangas wrote: > > Bhushan Verma wrote: > > I am doing some database related queries and this is working fine at > fedora > > core 4. > > But the same database queries giving the FATAL error on fedora 9. > > > > If I restarts the database on fedora core 9 then this is perfectlry > working > > without giving any error. > > > > My postgres version is > > On Fedora core-9[FC9] Machine: > > $ rpm -qa|grep postgres > > postgresql-server-8.3.1-1.fc9.i386 > > postgresql-devel-8.3.1-1.fc9.i386 > > postgresql-python-8.3.1-1.fc9.i386 > > postgresql-8.3.1-1.fc9.i386 > > postgresql-libs-8.3.1-1.fc9.i386 > > > It probably won't solve this problem, but you need to upgrade. The > latest 8.3 release is 8.3.7. See > http://www.postgresql.org/support/versioning > > > > On Fedora core-4[FC49] Machine: > > $ rpm -qa|grep postgres > > postgresql-server-8.0.3-1 > > > ... > > Same here. Latest 8.0 minor release is 8.0.21 > > > > Problem is: > > > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > > > connection to server was lost > > > > psql: FATAL: the database system is in recovery mode > > > ... > > > > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > connection to server was lost > > psql: FATAL: the database system is in recovery mode > > > > -- > > > > Is this database bug or there is any versioning incopatability. > > > You'll have to give more details or no-one will be able to help you. > Please share the query that caused the crash, and CREATE statements of > all the tables involved in the query. Is there any triggers or anything > else involved? > > Can you get a core dump and post stack trace from it? Something along > the lines of: > 1. ulimit -c unlimited > 2. pg_ctl start > 3. > 4. gdb /usr/bin/postgres /core > 5. bt > > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > -- With Regards, Bhushan
Re: [BUGS] psql: FATAL: the database system is in recovery mode
>The core file is generated in the data directory. I believe the default >location on Fedora is /var/lib/pgsql/dat Yes I also think the same. But there is no core file in /var/lib/pgsql/dat on my system. I am trying to find out why this is not generating core while log message "LOG: server process (PID 14255) was terminated by signal 11: Segmentation fault" is wriiten in the log file. With Regards, Bhushan On 6/24/09, Heikki Linnakangas wrote: > > Bhushan Verma wrote: > > I have searched for the core file for this pid. > > Is there any spcefic path for the postgres executable? > > > > I have already checked for ulimit -c unlimited etc. > > On my system core file for some other application are generating > properly. > > > The core file is generated in the data directory. I believe the default > location on Fedora is /var/lib/pgsql/data > > > -- > > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > -- With Regards, Bhushan
Re: [BUGS] psql: FATAL: the database system is in recovery mode
Hi Thanks one again for your response. >>You still haven't posted the offending query, I am running on shell script that contains various select statment, one of the example is as follows; -- SELECT PageIndex, (SUM(ConnectDoneTime - StartTime) * 100) / SUM(EndTime - StartTime) AS "ConnectTimePct", (SUM(WriteCompleTime - ConnectDoneTime) * 100) / SUM(EndTime - StartTime) AS "RequestSentTimePct", (SUM(FirstByteRcdTime - WriteCompleTime) * 100) / SUM(EndTime - StartTime) AS "FirstByteTimePct", (SUM(EndTime - FirstByteRcdTime) * 100) / SUM(EndTime - StartTime) AS "DownloadTimePct" FROM UrlRecord_$1 WHERE EndTime <> 0 AND ConnectDoneTime <> 0 AND WriteCompleTime <> 0 AND FirstByteRcdTime <> 0 GROUP By PageIndex ORDER By PageIndex; --- I want to tell you that its happening after sometime ie at random. time is not fixed. >>Is it a particular one, or does it crash at random? its crash at random. On 6/24/09, Heikki Linnakangas wrote: > > Bhushan Verma wrote: > >>> postmaster -D /var/lib/pgsql/data > > I am using the same command as you said. > > > I'm afraid I'm out of ideas on how to get the core dump then. > > You could also try attaching gdb to the backend process before it > crashes, and get the backtrace from there. Something along the lines of: > > > postmaster -D /var/lib/pgsql/data > > psql postgres ... > ps ax | grep postgres # check the PID of the backend process psql is > connected to. > gdb > gdb> attach > gdb> cont > > gdb> bt > > > You still haven't posted the offending query, BTW. Is it a particular > one, or does it crash at random? > > > -- > > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > -- With Regards, Bhushan
[BUGS] psql: FATAL: the database system is in recovery mode
Hi All, I am trying to run as follows queries -- SELECT PageIndex, (SUM(ConnectDoneTime - StartTime) * 100) / SUM(EndTime - StartTime) AS "ConnectTimePct", (SUM(WriteCompleTime - ConnectDoneTime) * 100) / SUM(EndTime - StartTime) AS "RequestSentTimePct", (SUM(FirstByteRcdTime - WriteCompleTime) * 100) / SUM(EndTime - StartTime) AS "FirstByteTimePct", (SUM(EndTime - FirstByteRcdTime) * 100) / SUM(EndTime - StartTime) AS "DownloadTimePct" FROM UrlRecord_$1 WHERE EndTime <> 0 AND ConnectDoneTime <> 0 AND WriteCompleTime <> 0 AND FirstByteRcdTime <> 0 GROUP By PageIndex ORDER By PageIndex; --- after running this query, I am getting as follows error --- server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost psql: FATAL: the database system is in recovery mode --- when I analyze the log file this is showing as follows: LOG: database system is ready to accept connections LOG: server process (PID 11736) was terminated by signal 11: Segmentation fault LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2009-08-31 19:23:34 IST LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 1/64AF7AE0 LOG: redo is not required LOG: database system is ready to accept connections LOG: autovacuum launcher started Is there any limitations or some database configuration problem. Can anyone please help me in this regards. Regards -- With Regards, Bhushan
Re: [BUGS] psql: FATAL: the database system is in recovery mode
Hi, Sorry for the insufficient information. As follows is the infomrations: - What version of PostgreSQL you are using? #rpm -qa|grep postgres postgresql-server-8.0.3-1 postgresql-libs-8.0.3-1 postgresql-odbc-08.00.0100-1 postgresql-jdbc-8.0.3-1 postgresql-docs-8.0.3-1 postgresql-tcl-8.0.3-1 postgresql-test-8.0.3-1 postgresql-devel-8.0.3-1 postgresql-8.0.3-1 gnucash-backend-postgres-1.8.11-3 freeradius-postgresql-1.0.2-2 postgresql-python-8.0.3-1 postgresql-contrib-8.0.3-1 postgresql-pl-8.0.3-1 - What does "UrlRecord_$1" table look like? #select * from UrlRecord_9492 ; testindex | urlindex | sessionindex | sessioninstance | transactionindex | pageindex | childindex | txinstance | pageinstance | fetchedfrom | httsreqreused | conreused | urltype | starttime | dnsstarttime | dnsendtime | connectstarttime | connectdonetime | sslhandshakedone | writecompletime | firstbytercdtime | requestcompletedtime | renderingtime | endtime | httpresponsecode | httppayloadbytessent | appbytessent | ethbytessent | httppayloadbytesrcd | appbytesrcd | ethbytesrcd | completionmode | status | connectionnumber | connectiontype | retries ---+--+--+-+--+---+++--+-+---+---+-+---+--++--+-+--+-+--+--+---+-+--+--+--+--+-+-+-+++--++- 9492 |0 |0 | 0 | | 0 | 0 | 0 |0 | 0 | f | f | 1 | 2719 |0 | 0 | 2719 |2720 | 2720 |2720 | 2733 | 2733 | 0 |2733 | 200 |0 | 453 |0 |2975 |3065 | 0 | 0 | 0 | 0 | 1 | 0 - What is your operating system OS is Fedora core-9[FC9]. I have already done ulimit -c unlimited, but not able to generate core file in $PGDATA ie /var/lib/pgsql/data. On 8/31/09, Heikki Linnakangas wrote: > > Bhushan Verma wrote: > > Hi All, > > > > I am trying to run as follows queries > > -- > > SELECT PageIndex, > >(SUM(ConnectDoneTime - StartTime) * 100) / SUM(EndTime - > StartTime) > > AS "ConnectTimePct", > >(SUM(WriteCompleTime - ConnectDoneTime) * 100) / SUM(EndTime - > > StartTime) AS "RequestSentTimePct", > >(SUM(FirstByteRcdTime - WriteCompleTime) * 100) / SUM(EndTime - > > StartTime) AS "FirstByteTimePct", > >(SUM(EndTime - FirstByteRcdTime) * 100) / SUM(EndTime - StartTime) > AS > > "DownloadTimePct" > > FROM UrlRecord_$1 > > WHERE EndTime <> 0 > > AND ConnectDoneTime <> 0 > > AND WriteCompleTime <> 0 > > AND FirstByteRcdTime <> 0 > > GROUP By PageIndex > > ORDER By PageIndex; > > > > --- > > after running this query, I am getting as follows error > > --- > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > > > connection to server was lost > > > ... > > > Is there any limitations or some database configuration problem. > > Can anyone please help me in this regards. > > > A segmentation fault is certainly not normal, but it's hard to tell if > it's caused by a bug, misconfigured or buggy 3rd party modules, or > faulty hardware without some more information. Like: > > - What version of PostgreSQL you are using? > - What does "UrlRecord_$1" table look like? > - What is your operating system? > > > A backtrace would help a lot to pinpoint the problem. Please run "ulimit > -c unlimited" or similar to enable core dumps, and then do: > > gdb /postgres $PGDATA/core > > bt > > and post the output. > > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > -- With Regards, Bhushan
Re: [BUGS] psql: FATAL: the database system is in recovery mode
Hi, Sorry that was for my fedora 4 machine. But my fedora 9 machine have as follows postgres version. rpm -qa|grep postgres postgresql-server-8.3.1-1.fc9.i386 postgresql-devel-8.3.1-1.fc9.i386 postgresql-python-8.3.1-1.fc9.i386 postgresql-8.3.1-1.fc9.i386 postgresql-libs-8.3.1-1.fc9.i386 I am getting the problem on Fedora 9 not on fedora 4. On 9/1/09, Tom Lane wrote: > > Bhushan Verma writes: > > - What version of PostgreSQL you are using? > > #rpm -qa|grep postgres > > postgresql-server-8.0.3-1 > > > 8.0.3 is four years obsolete. If you can still reproduce the crash > after updating to some recent 8.0.x version, it'd be worth looking > closer. > > regards, tom lane > -- With Regards, Bhushan
[BUGS] Databse installation problem
I have tried to install postgres version 8.3.7 as follows rm -rf /var/lib/pgsql mkdir /var/lib/pgsql ./configure --prefix /var/lib/pgsql gmake su gmake install adduser postgres mkdir /var/lib/pgsql/data chown postgres /var/lib/pgsql/data su - postgres /usr/local/pgsql/bin/initdb -D /var/lib/pgsql/data /usr/local/pgsql/bin/postgres -D /var/lib/pgsql/data >pgstartup.log 2>&1 & /usr/local/pgsql/bin/createdb test /usr/local/pgsql/bin/psql test then i log on database to see the version test=> select version(); version --- PostgreSQL 8.3.1 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.0 20080314 (Red Hat 4.3.0-3) (1 row) but its showing me 8.3.1 that was my previous version. Could anyone tell me what is the problem. -- With Regards, Bhushan
