Обсуждение: 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
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
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 <heikki.linnakangas@enterprisedb.com> 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 <path to postgres binary>/postgres $PGDATA/core
> bt
and post the output.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
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
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 <tgl@sss.pgh.pa.us> wrote:
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
--
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