server closed the connection unexpectedly

[ruben]

Hi:

I'm getting this error when accessing a table with certain WHERE condition:

"server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed."

I've read through the posts but found no answer to the problem.

When I "Vaccum analize" the table I get the same error.

I have droped and re-created the indexes.

Version is
"PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)"

Any help on how to recover the table would be greatly appreciated.
Thanks, Ruben.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[mike g]

Re: server closed the connection unexpectedly

Can you use pg_dump to backup the database and possibly then upgrade the
db? 7.2 is rather old. I recall reading similar postings in the
mailing this that recommend you upgrade to 7.2.4 or .6(?) if you must
stay on 7.2.


On Mon, 2004-07-05 at 17:48, ruben wrote:[color=blue]
> Hi:
>
> I'm getting this error when accessing a table with certain WHERE condition:
>
> "server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed."
>
> I've read through the posts but found no answer to the problem.
>
> When I "Vaccum analize" the table I get the same error.
>
> I have droped and re-created the indexes.
>
> Version is
> "PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
> 20020903 (Red Hat Linux 8.0 3.2-7)"
>
> Any help on how to recover the table would be greatly appreciated.
> Thanks, Ruben.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org[/color]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[ruben]

Re: server closed the connection unexpectedly

Hi Mike:

Thanks for your answer, I'm unable to dump the table:

-bash-2.05b$ pg_dump -Fc -t afected_table database_name -f
../afected_table.d ump
pg_dump: connection not open
pg_dump: lost synchronization with server, resetting connection
pg_dump: SQL command to dump the contents of table "afected_ta ble"
failed: PQendcopy() failed.
pg_dump: Error message from server: FATAL 1: The database system is
starting up


Ruben.


mike g wrote:[color=blue]
> Can you use pg_dump to backup the database and possibly then upgrade the
> db? 7.2 is rather old. I recall reading similar postings in the
> mailing this that recommend you upgrade to 7.2.4 or .6(?) if you must
> stay on 7.2.
>
>
> On Mon, 2004-07-05 at 17:48, ruben wrote:
>[color=green]
>>Hi:
>>
>>I'm getting this error when accessing a table with certain WHERE condition:
>>
>>"server closed the connection unexpectedly
>>This probably means the server terminated abnormally
>>before or while processing the request.
>>The connection to the server was lost. Attempting reset: Failed."
>>
>>I've read through the posts but found no answer to the problem.
>>
>>When I "Vaccum analize" the table I get the same error.
>>
>>I have droped and re-created the indexes.
>>
>>Version is
>>"PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
>>20020903 (Red Hat Linux 8.0 3.2-7)"
>>
>>Any help on how to recover the table would be greatly appreciated.
>>Thanks, Ruben.
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>> http://archives.postgresql.org[/color]
>[/color]



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[mike g]

Re: server closed the connection unexpectedly

Ok,

Other suggestions:
1) Have you done a Vaccum full on the table? That should reduce the
table size and resources required.

2) Use psql to dump the table instead of pg_dump. In psql do a COPY
affected table TO /file name. That will output the table results in a
tab delimited text file which could then be reimported later using COPY
affected table from /that file name.

COPY BINARY affected table to /File name could be considered as well.

3) create a few smaller tables with the same data definitions. Insert
specific sections of the table into each smaller table via Insert into
one_smaller_tab le
Select * from affected_table where date between X and Y

HTH



On Tue, 2004-07-06 at 10:43, ruben wrote:[color=blue]
> Hi Mike:
>
> Thanks for your answer, I'm unable to dump the table:
>
> -bash-2.05b$ pg_dump -Fc -t afected_table database_name -f
> ./afected_table.d ump
> pg_dump: connection not open
> pg_dump: lost synchronization with server, resetting connection
> pg_dump: SQL command to dump the contents of table "afected_ta ble"
> failed: PQendcopy() failed.
> pg_dump: Error message from server: FATAL 1: The database system is
> starting up
>
>
> Ruben.
>
>
> mike g wrote:[color=green]
> > Can you use pg_dump to backup the database and possibly then upgrade the
> > db? 7.2 is rather old. I recall reading similar postings in the
> > mailing this that recommend you upgrade to 7.2.4 or .6(?) if you must
> > stay on 7.2.
> >
> >
> > On Mon, 2004-07-05 at 17:48, ruben wrote:
> >[color=darkred]
> >>Hi:
> >>
> >>I'm getting this error when accessing a table with certain WHERE condition:
> >>
> >>"server closed the connection unexpectedly
> >>This probably means the server terminated abnormally
> >>before or while processing the request.
> >>The connection to the server was lost. Attempting reset: Failed."
> >>
> >>I've read through the posts but found no answer to the problem.
> >>
> >>When I "Vaccum analize" the table I get the same error.
> >>
> >>I have droped and re-created the indexes.
> >>
> >>Version is
> >>"PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
> >>20020903 (Red Hat Linux 8.0 3.2-7)"
> >>
> >>Any help on how to recover the table would be greatly appreciated.
> >>Thanks, Ruben.
> >>
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 6: Have you searched our list archives?
> >>
> >> http://archives.postgresql.org[/color]
> >[/color]
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster[/color]

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[ruben]

Re: server closed the connection unexpectedly

Hi again Mike:

mike g wrote:[color=blue]
> Ok,
>
> Other suggestions:
> 1) Have you done a Vaccum full on the table? That should reduce the
> table size and resources required.[/color]

First thing I tried was VACCUUM on the table, but got the same errror:

This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
[color=blue]
> 2) Use psql to dump the table instead of pg_dump. In psql do a COPY
> affected table TO /file name. That will output the table results in a
> tab delimited text file which could then be reimported later using COPY
> affected table from /that file name.
>
> COPY BINARY affected table to /File name could be considered as well.[/color]

I could not try this since I already solved the problem saving partial
contents of the table, recreating and inserting. Finally, I only lost
300 tuples :-(
[color=blue]
> 3) create a few smaller tables with the same data definitions. Insert
> specific sections of the table into each smaller table via Insert into
> one_smaller_tab le
> Select * from affected_table where date between X and Y[/color]

What worries me most, besides recovering this table, is why could this
happened and how to avoid it. I guess it's due to a hardware problem and
the only solution is a frequent backup.

Thanks for your help, Mike
Ruben.



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)