Thread: HELP

HELP

From
"Brendon Matthews"
Date:
Hi,

I have been using PostgreSQL 7.2.2 for a while now on one of our live
machines, however all of a sudden with no warning, we are no longer able to
run any UPDATE statements on one of our databases. SELECT and INSERT
statements have no issues.

The error we are getting is:
"The backend has broken the connection. Possibly the action you have
attempted has caused it to close."
Immediately after this call the database system restarts itself.

We are using:
Red Hat Linux
Apache 1.3.26
PostgreSQL 7.2.2
Java 1.4 + the latest PostgreSQL jdbc driver from jdbc.postgresql.org

Configuration:
We are running postmaster -i listening on localhost only.

We have 3 PostgreSQL databases on the same machine, and the other two have
no problems at all (yet). They are all using the same software mentioned
above.

Things i have tried:
Restarting JVM and Apache (problem still occurs)
Restarting postmaster -i (problem still occurs)
Vaccuming all databases (problem still occurs)
Rebooting entire machine (problem still occurs)
Dropping the database and creating another (worked for a while, but after
processing SQL calls for about a day and a half the problem started up
again)

Is there anything i can do to fix this? Has anyone else seen this before?
We need our database system back up and running ASAP.

Regards,

Brendon Matthews
Analyst Programmer
BBTech Ltd
Ph: (03) 381 1127
Fax: (03) 381 1185

Re: HELP

From
Rod Taylor
Date:
On Thu, 2002-09-12 at 01:13, Brendon Matthews wrote:
> Hi,
>
> I have been using PostgreSQL 7.2.2 for a while now on one of our live
> machines, however all of a sudden with no warning, we are no longer able to
> run any UPDATE statements on one of our databases. SELECT and INSERT
> statements have no issues.

Try VACUUM FULL (different than regular VACUUM).

Try dumping data / shutdown daemon / fry data directory / initdb / start
daemon / restore data.

--
  Rod Taylor

Re: HELP

From
Tom Lane
Date:
"Brendon Matthews" <brendonm@bbtechltd.com> writes:
> I have been using PostgreSQL 7.2.2 for a while now on one of our live
> machines, however all of a sudden with no warning, we are no longer able to
> run any UPDATE statements on one of our databases. SELECT and INSERT
> statements have no issues.
> The error we are getting is:
> "The backend has broken the connection. Possibly the action you have
> attempted has caused it to close."
> Immediately after this call the database system restarts itself.

Sounds like a backend crash.  What do you get in the postmaster log when
this happens?  If there's a core file, can you provide a gdb stack
trace?  (If there's not a core file, restarting the postmaster under
"ulimit -c unlimited" should allow one to be produced.)

            regards, tom lane

Re: HELP

From
junk@ns1.tcu-inc.com
Date:
I would create new tables, copy the data over, then drop the old tables,
and rename the new tables.

I have had similar problems iwht MySQL, but never with PostgreSQL.
Mark


>
> Hi,
>
> I have been using PostgreSQL 7.2.2 for a while now on one of our live
> machines, however all of a sudden with no warning, we are no longer able to
> run any UPDATE statements on one of our databases. SELECT and INSERT
> statements have no issues.
>
> The error we are getting is:
> "The backend has broken the connection. Possibly the action you have
> attempted has caused it to close."
> Immediately after this call the database system restarts itself.
>
> We are using:
> Red Hat Linux
> Apache 1.3.26
> PostgreSQL 7.2.2
> Java 1.4 + the latest PostgreSQL jdbc driver from jdbc.postgresql.org
>
> Configuration:
> We are running postmaster -i listening on localhost only.
>
> We have 3 PostgreSQL databases on the same machine, and the other two have
> no problems at all (yet). They are all using the same software mentioned
> above.
>
> Things i have tried:
> Restarting JVM and Apache (problem still occurs)
> Restarting postmaster -i (problem still occurs)
> Vaccuming all databases (problem still occurs)
> Rebooting entire machine (problem still occurs)
> Dropping the database and creating another (worked for a while, but after
> processing SQL calls for about a day and a half the problem started up
> again)
>
> Is there anything i can do to fix this? Has anyone else seen this before?
> We need our database system back up and running ASAP.
>
> Regards,
>
> Brendon Matthews
> Analyst Programmer
> BBTech Ltd
> Ph: (03) 381 1127
> Fax: (03) 381 1185
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Re: HELP

From
junk@ns1.tcu-inc.com
Date:
Then again, dumping is probably better, and deleting the database, and
then restoring it.

I am just curious if it is a database problem or a table problem.
If you can creat new tables that work fine, but it is a strange issue.

Mark

>
> I would create new tables, copy the data over, then drop the old tables,
> and rename the new tables.
>
> I have had similar problems iwht MySQL, but never with PostgreSQL.
> Mark
>
>
> >
> > Hi,
> >
> > I have been using PostgreSQL 7.2.2 for a while now on one of our live
> > machines, however all of a sudden with no warning, we are no longer able to
> > run any UPDATE statements on one of our databases. SELECT and INSERT
> > statements have no issues.
> >
> > The error we are getting is:
> > "The backend has broken the connection. Possibly the action you have
> > attempted has caused it to close."
> > Immediately after this call the database system restarts itself.
> >
> > We are using:
> > Red Hat Linux
> > Apache 1.3.26
> > PostgreSQL 7.2.2
> > Java 1.4 + the latest PostgreSQL jdbc driver from jdbc.postgresql.org
> >
> > Configuration:
> > We are running postmaster -i listening on localhost only.
> >
> > We have 3 PostgreSQL databases on the same machine, and the other two have
> > no problems at all (yet). They are all using the same software mentioned
> > above.
> >
> > Things i have tried:
> > Restarting JVM and Apache (problem still occurs)
> > Restarting postmaster -i (problem still occurs)
> > Vaccuming all databases (problem still occurs)
> > Rebooting entire machine (problem still occurs)
> > Dropping the database and creating another (worked for a while, but after
> > processing SQL calls for about a day and a half the problem started up
> > again)
> >
> > Is there anything i can do to fix this? Has anyone else seen this before?
> > We need our database system back up and running ASAP.
> >
> > Regards,
> >
> > Brendon Matthews
> > Analyst Programmer
> > BBTech Ltd
> > Ph: (03) 381 1127
> > Fax: (03) 381 1185
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: HELP

From
Rod Taylor
Date:
> Things i have tried:
> Restarting JVM and Apache (problem still occurs)
> Restarting postmaster -i (problem still occurs)
> Vaccuming all databases (problem still occurs)

Was this a regular vacuum?

Might want to try VACUUM FULL;

--
  Rod Taylor