Thread: Urgent!

Urgent!

From
"Nirav Parikh"
Date:
Hi,
 
I got this error message when I tried to do pg_dump on the database.

pg_dump: ERROR:  invalid memory alloc request size 4294967293
pg_dump: SQL command to dump the contents of table "wordlist" failed: PQendcopy() failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc request size 4294967293
pg_dump: The command was: COPY public.wordlist (word_id, word, count_word) TO stdout;
I went to phppgadmin and try to access the worldlist table and it also gives me following error message:
 
ERROR:  could not access status of transaction 6849340
DETAIL:  could not open file "/var/lib/pgsql/data/pg_clog/0006": No such file or directory
 
In statement:
SELECT COUNT(*) AS total FROM (SELECT "oid", * FROM "public"."wordlist") AS sub
 
 CPU was heating up so I turn off the computer and to fix the problem and after that this occour. I don't know if this has anything to with database corruption.
 
This is production server and I don't have latest backup does anyone how to resolve this.
 
Please Help
 
Regards,
 
Nirav Parikh

Re: Urgent!

From
"codeWarrior"
Date:
Lookas as if you've managed to turn off your computer mid-transaction
thereby corrupting the postgreSQL commit logs (pg_clog)...

You should never just turn off a database server... always shut it down
normally... Turning it off was a major mistake. I dont know if you can
recover or not as the system (postgreSQL) now thinks it is in the middle of
a transaction...

As far as recovery goes... You might try a vacuum full and pg_resetxlog
(search the postgreSQL docs for pg_resetxlog)... Other than that you might
need to get Tom Lane's attention on this one...



""Nirav Parikh"" <nirav.p@workskillsprofessionals.com.au> wrote in message
news:221385EC3F18A44DB0925467A1D2395F38FB07@exsrv3.workskillsprofessionals.com.au...
Hi,

I got this error message when I tried to do pg_dump on the database.

pg_dump: ERROR:  invalid memory alloc request size 4294967293
pg_dump: SQL command to dump the contents of table "wordlist" failed:
PQendcopy() failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc request
size 4294967293
pg_dump: The command was: COPY public.wordlist (word_id, word, count_word)
TO stdout;

I went to phppgadmin and try to access the worldlist table and it also gives
me following error message:

ERROR:  could not access status of transaction 6849340
DETAIL:  could not open file "/var/lib/pgsql/data/pg_clog/0006": No such
file or directory

In statement:
SELECT COUNT(*) AS total FROM (SELECT "oid", * FROM "public"."wordlist") AS
sub

 CPU was heating up so I turn off the computer and to fix the problem and
after that this occour. I don't know if this has anything to with database
corruption.

This is production server and I don't have latest backup does anyone how to
resolve this.

Please Help

Regards,

Nirav Parikh



Re: Urgent!

From
"Anjan Dave"
Date:
I've been through a similar situation before (partition was full, and
subsequently, PG wasn't able to access a pg_clog/<one file>.

Tom Lane had referred me to another archive about zeroing out that
particular file, which had worked for me and the database was up.

And yes, don't shutdown the machine like that.

Thanks,
Anjan

-----Original Message-----
From: codeWarrior [mailto:gpatnude@hotmail.com]
Sent: Thursday, October 13, 2005 11:31 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Urgent!

Lookas as if you've managed to turn off your computer mid-transaction
thereby corrupting the postgreSQL commit logs (pg_clog)...

You should never just turn off a database server... always shut it down
normally... Turning it off was a major mistake. I dont know if you can
recover or not as the system (postgreSQL) now thinks it is in the middle
of
a transaction...

As far as recovery goes... You might try a vacuum full and pg_resetxlog
(search the postgreSQL docs for pg_resetxlog)... Other than that you
might
need to get Tom Lane's attention on this one...



""Nirav Parikh"" <nirav.p@workskillsprofessionals.com.au> wrote in
message
news:221385EC3F18A44DB0925467A1D2395F38FB07@exsrv3.workskillsprofessiona
ls.com.au...
Hi,

I got this error message when I tried to do pg_dump on the database.

pg_dump: ERROR:  invalid memory alloc request size 4294967293
pg_dump: SQL command to dump the contents of table "wordlist" failed:
PQendcopy() failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc request

size 4294967293
pg_dump: The command was: COPY public.wordlist (word_id, word,
count_word)
TO stdout;

I went to phppgadmin and try to access the worldlist table and it also
gives
me following error message:

ERROR:  could not access status of transaction 6849340
DETAIL:  could not open file "/var/lib/pgsql/data/pg_clog/0006": No such

file or directory

In statement:
SELECT COUNT(*) AS total FROM (SELECT "oid", * FROM "public"."wordlist")
AS
sub

 CPU was heating up so I turn off the computer and to fix the problem
and
after that this occour. I don't know if this has anything to with
database
corruption.

This is production server and I don't have latest backup does anyone how
to
resolve this.

Please Help

Regards,

Nirav Parikh



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


Re: Urgent!

From
Bruce Momjian
Date:
codeWarrior wrote:
> Lookas as if you've managed to turn off your computer mid-transaction
> thereby corrupting the postgreSQL commit logs (pg_clog)...
>
> You should never just turn off a database server... always shut it down
> normally... Turning it off was a major mistake. I dont know if you can
> recover or not as the system (postgreSQL) now thinks it is in the middle of
> a transaction...

PostgreSQL should never get corrupted by turning off the server.  It
isn't ideal to do that, but it should not get corrupted.

---------------------------------------------------------------------------


>
> As far as recovery goes... You might try a vacuum full and pg_resetxlog
> (search the postgreSQL docs for pg_resetxlog)... Other than that you might
> need to get Tom Lane's attention on this one...
>
>
>
> ""Nirav Parikh"" <nirav.p@workskillsprofessionals.com.au> wrote in message
> news:221385EC3F18A44DB0925467A1D2395F38FB07@exsrv3.workskillsprofessionals.com.au...
> Hi,
>
> I got this error message when I tried to do pg_dump on the database.
>
> pg_dump: ERROR:  invalid memory alloc request size 4294967293
> pg_dump: SQL command to dump the contents of table "wordlist" failed:
> PQendcopy() failed.
> pg_dump: Error message from server: ERROR:  invalid memory alloc request
> size 4294967293
> pg_dump: The command was: COPY public.wordlist (word_id, word, count_word)
> TO stdout;
>
> I went to phppgadmin and try to access the worldlist table and it also gives
> me following error message:
>
> ERROR:  could not access status of transaction 6849340
> DETAIL:  could not open file "/var/lib/pgsql/data/pg_clog/0006": No such
> file or directory
>
> In statement:
> SELECT COUNT(*) AS total FROM (SELECT "oid", * FROM "public"."wordlist") AS
> sub
>
>  CPU was heating up so I turn off the computer and to fix the problem and
> after that this occour. I don't know if this has anything to with database
> corruption.
>
> This is production server and I don't have latest backup does anyone how to
> resolve this.
>
> Please Help
>
> Regards,
>
> Nirav Parikh
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Urgent!

From
Alvaro Herrera
Date:
Nirav Parikh wrote:
> Hi,
>
> I got this error message when I tried to do pg_dump on the database.
>
> pg_dump: ERROR:  invalid memory alloc request size 4294967293
> pg_dump: SQL command to dump the contents of table "wordlist" failed:
> PQendcopy() failed.
> pg_dump: Error message from server: ERROR:  invalid memory alloc request
> size 4294967293
> pg_dump: The command was: COPY public.wordlist (word_id, word,
> count_word) TO stdout;
>
> I went to phppgadmin and try to access the worldlist table and it also
> gives me following error message:
>
> ERROR:  could not access status of transaction 6849340
> DETAIL:  could not open file "/var/lib/pgsql/data/pg_clog/0006": No such
> file or directory

What version is this?  Also, is the system running with fsync=off or on
disks with write cache enabled?

--
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 17.7", W 73º 14' 26.8"
"La fuerza no está en los medios físicos
sino que reside en una voluntad indomable" (Gandhi)

Re: Urgent!

From
Scott Marlowe
Date:
On Thu, 2005-10-13 at 10:56, Bruce Momjian wrote:
> codeWarrior wrote:
> > Lookas as if you've managed to turn off your computer mid-transaction
> > thereby corrupting the postgreSQL commit logs (pg_clog)...
> >
> > You should never just turn off a database server... always shut it down
> > normally... Turning it off was a major mistake. I dont know if you can
> > recover or not as the system (postgreSQL) now thinks it is in the middle of
> > a transaction...
>
> PostgreSQL should never get corrupted by turning off the server.  It
> isn't ideal to do that, but it should not get corrupted.

Unless it is installed on hardware that lies about fsync.

Which almost all ATA drives, parallel and serial, do, sadly.



Re: Urgent!

From
Bruce Momjian
Date:
Scott Marlowe wrote:
> On Thu, 2005-10-13 at 10:56, Bruce Momjian wrote:
> > codeWarrior wrote:
> > > Lookas as if you've managed to turn off your computer mid-transaction
> > > thereby corrupting the postgreSQL commit logs (pg_clog)...
> > >
> > > You should never just turn off a database server... always shut it down
> > > normally... Turning it off was a major mistake. I dont know if you can
> > > recover or not as the system (postgreSQL) now thinks it is in the middle of
> > > a transaction...
> >
> > PostgreSQL should never get corrupted by turning off the server.  It
> > isn't ideal to do that, but it should not get corrupted.
>
> Unless it is installed on hardware that lies about fsync.
>
> Which almost all ATA drives, parallel and serial, do, sadly.

Ah, yes, that one.  We do have a section in the 8.1 beta docs about that
which will help in the future:

    http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Urgent!

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> You should never just turn off a database server... always shut it down
>> normally... Turning it off was a major mistake.

> PostgreSQL should never get corrupted by turning off the server.  It
> isn't ideal to do that, but it should not get corrupted.

We can only make that promise if we are on hardware that doesn't lie to
us about disk write completion ... if this machine is generic PC
hardware it's probably not configured properly.

The symptoms sound to me like corrupt data (probably due to a partial
page disk write).  There isn't going to be any magic fix, but you can
probably recover everything but the specific damaged page(s).  See the
mailing list archives for previous discussions of cleaning up data
corruption.

            regards, tom lane

Re: Urgent!

From
Scott Marlowe
Date:
On Thu, 2005-10-13 at 11:23, Bruce Momjian wrote:
> Scott Marlowe wrote:
> > On Thu, 2005-10-13 at 10:56, Bruce Momjian wrote:
> > > codeWarrior wrote:
> > > > Lookas as if you've managed to turn off your computer mid-transaction
> > > > thereby corrupting the postgreSQL commit logs (pg_clog)...
> > > >
> > > > You should never just turn off a database server... always shut it down
> > > > normally... Turning it off was a major mistake. I dont know if you can
> > > > recover or not as the system (postgreSQL) now thinks it is in the middle of
> > > > a transaction...
> > >
> > > PostgreSQL should never get corrupted by turning off the server.  It
> > > isn't ideal to do that, but it should not get corrupted.
> >
> > Unless it is installed on hardware that lies about fsync.
> >
> > Which almost all ATA drives, parallel and serial, do, sadly.
>
> Ah, yes, that one.  We do have a section in the 8.1 beta docs about that
> which will help in the future:
>
>     http://candle.pha.pa.us/main/writings/pgsql/sgml/reliability.html

I still remember when I found out that basically all IDE drives lied
about this.  So I built my server with battery backed cache and SCSI
drives, and part of it's acceptance testing was having a heavy
transactional load placed on it and have the power switch flipped off.
We did that about 10 times with different loads, and it never failed to
come right back up.

So, 6 months later, when an electrician dropped a piece of clipped off
copper wire into one of our three power conditioners in the hosting
center, thereby causing an overload that blew out all three power
conditioners AND the UPSes they were connected to, and emergency power
was restored, my machine was the only one in a hosting center of about
100 servers that came up perfectly.

Well, almost perfectly.  We had to reboot it because it came up too fast
and none of the nfs servers it normally sees were there yet.  The nfs
servers were all bsd boxes, and they all came up too, except for the
ones with >1 fried drives in their RAID 5s, which was only a couple.

It was nice knowing my research and all on PostgreSQL had paid off.
There were plenty of machines with MySQL and with Oracle which did not
survive the power outage.