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

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 <path to postgres binary>/postgres $PGDATA/core
> bt

and post the output.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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.




--
With Regards,
Bhushan

Bhushan Verma <verma.bhushan@gmail.com> 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

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.





--
With Regards,
Bhushan

On Tue, Sep 1, 2009 at 8:47 AM, Bhushan Verma<verma.bhushan@gmail.com> wrote:
> 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

Same here, please upgrade to 8.3.7 and see if you can reproduce the problem.

If so, please provide more information: a self contained test case
would be nice if you can build one or a backtrace as Heikki suggested
it.

If you can't upgrade for a good reason, please provide the self
contained test case so that we can check ourselves if it's still
reproducible with 8.3.7.

Thanks.

--
Guillaume

On Tue, 2009-09-01 at 12:17 +0530, Bhushan Verma wrote:
>
> But my fedora 9 machine have as follows postgres version.
> rpm -qa|grep postgres
> postgresql-server-8.3.1-1.fc9.i386

Please note that you will need to follow this procedure before updating
PostgreSQL on Fedora 9:

https://fedoraproject.org/wiki/Enabling_new_signing_key#Fedora_9

Otherwise it will stay on 8.3.1 .

--
Devrim GÜNDÜZ, RHCE
Command Prompt - http://www.CommandPrompt.com
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

Thanks for this information.
By the way my same queries is workig fine on as follows version:

Machine: Fedora core 4[FC4]
PostGresVersion:
$ 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


with regards,
Bhushan




--
With Regards,
Bhushan