Обсуждение: Urgent!
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
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
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
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
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
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
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)
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.
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
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
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.