Recovery Data Cant Be!!!

On Fri, 07 Nov 2003 18:08:32 +0000
"Edwin Quijada" <listas_quijada@hotmail.com> wrote:

LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097

Hmm.. Does this machine bychance run with fsync=false or an IDE drive?

You may be able to salvage things if you turn zero_damaged_pages on in
postgresql.conf..

but in general it seems your db is corrupt.

You might want to make a copy of $PGDATA before you try the
zero_damaged_pages thingy.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

Where set zero_damaged_pages ??

*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo
comun"
*-------------------------------------------------------*

From: Jeff <threshar@torgo.978.org>
To: "Edwin Quijada" <listas_quijada@hotmail.com>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Recovery Data Cant Be!!!
Date: Fri, 7 Nov 2003 13:25:18 -0500

On Fri, 07 Nov 2003 18:08:32 +0000
"Edwin Quijada" <listas_quijada@hotmail.com> wrote:

LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097

Hmm.. Does this machine bychance run with fsync=false or an IDE drive?

You may be able to salvage things if you turn zero_damaged_pages on in
postgresql.conf..

but in general it seems your db is corrupt.

You might want to make a copy of $PGDATA before you try the
zero_damaged_pages thingy.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/

_________________________________________________________________
Las mejores tiendas, los precios mas bajos, entregas en todo el mundo,
YupiMSN Compras: www.yupimsn.com/compras

You're likely looking for pgfsck:

http://svana.org/kleptog/pgsql/pgfsck.html

Strong medicine, please backup your $PGDATA directory before playing
there, as you might wind up breaking things.

What's the exact error message in your logs when the database won't start
up?

The problem you're seeing is common when running on IDE drives with write
cache enabled, or with fsync=false on IDE with write cache disabled or
SCSI. On a solid storage subsystem, postgresql is close to
indestrubtable, i.e. you can crank up 500 simo transactions, pull the
plug, and the database will come right back up. That's how we test our
servers to make sure that the WAL and such is working properly.

On Fri, 7 Nov 2003, Edwin Quijada wrote:

Edwin Quijada wrote:

Where set zero_damaged_pages ??

You can't set it from postgresql.conf. It has to be set in your psql
session:

SET zero_damaged_pages = true;

-- 
  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:

Edwin Quijada wrote:

Where set zero_damaged_pages ??

You can't set it from postgresql.conf.

Sure you can, and in fact that's the only place that will work for
Edwin's problem. He hasn't *got* a psql session, because postmaster
startup is failing.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Edwin Quijada wrote:

Where set zero_damaged_pages ??

You can't set it from postgresql.conf.

Sure you can, and in fact that's the only place that will work for
Edwin's problem. He hasn't *got* a psql session, because postmaster
startup is failing.

Oh, I never realized it would work with damage that severe.

-- 
  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:

Oh, I never realized it would work with damage that severe.

I'm not sure it will, but it's his best shot.

Actually, I think there is a TODO item here. In most cases where
a page is being touched during WAL replay, we have a complete image of
the page stashed in WAL and are just going to overwrite what we find
in the data file. I think that the zero_damaged_pages code may be
barfing in situations where it has no need to. Someone needs to look
into that and see if we do have a problem; if so, what's the best fix?

regards, tom lane

This is the way to do that?

./pgfsck -D /usr/local/pgsql/data -da union_seguros >
/var/lib/pgsql/bak_union

What is the correct setting to never do that?
What is a solid storage subsystem?
fsync=false ?
write cache enabled?

Can you explain me this?
Postgres
I use IDE disk

*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo
comun"
*-------------------------------------------------------*

From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Edwin Quijada <listas_quijada@hotmail.com>
CC: <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Recovery Data Cant Be!!!
Date: Fri, 7 Nov 2003 13:29:57 -0700 (MST)

You're likely looking for pgfsck:

http://svana.org/kleptog/pgsql/pgfsck.html

Strong medicine, please backup your $PGDATA directory before playing
there, as you might wind up breaking things.

What's the exact error message in your logs when the database won't start
up?

The problem you're seeing is common when running on IDE drives with write
cache enabled, or with fsync=false on IDE with write cache disabled or
SCSI. On a solid storage subsystem, postgresql is close to
indestrubtable, i.e. you can crank up 500 simo transactions, pull the
plug, and the database will come right back up. That's how we test our
servers to make sure that the WAL and such is working properly.

On Fri, 7 Nov 2003, Edwin Quijada wrote:

Hi!!
There is an app to recover data for postgres after inapropiate server
shutdown or power electrical down?
I have a great problem. My system go down inapropiate and now my server
doesnt up.
What 's up with my data?
I lost my data?
I dont believe that a robust database like postgres is so weak for power
down and understand that can go down electrical power but fail 2 ups.

If somebody has any idea to help me this is the log file

The server must be started under an unprivileged user id to prevent
a possible system security compromise. See the documentation for
more information on how to properly start the server.

LOG: database system was interrupted at 2003-11-07 10:54:32 CET
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1327) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted being in recovery at 2003-11-07
23:33:19 CET
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1354) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted being in recovery at 2003-11-07
23:35:34 CET
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1090) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted being in recovery at 2003-11-07
23:40:47 CET
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1196) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted being in recovery at 2003-11-07
23:46:13 CET
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1531) was terminated by signal 6
LOG: aborting startup due to startup process failure

*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de

lo

comun"
*-------------------------------------------------------*

_________________________________________________________________

From Beethoven to the Rolling Stones, your favorite music is always

playing

on MSN Radio Plus. No ads, no talk. Trial month FREE!
http://join.msn.com/?page=offers/premiumradio

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

_________________________________________________________________
Is your computer infected with a virus? Find out with a FREE computer virus
scan from McAfee. Take the FreeScan now!
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

OKi
how can I do that from postgresql.conf?

*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo
comun"
*-------------------------------------------------------*

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: Edwin Quijada <listas_quijada@hotmail.com>, threshar@torgo.978.org,
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Recovery Data Cant Be!!! Date: Fri, 07 Nov 2003
16:29:18 -0500

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Edwin Quijada wrote:

Where set zero_damaged_pages ??

You can't set it from postgresql.conf.

Sure you can, and in fact that's the only place that will work for
Edwin's problem. He hasn't *got* a psql session, because postmaster
startup is failing.

regards, tom lane

_________________________________________________________________
Send a QuickGreet with MSN Messenger
http://www.msnmessenger-download.com/tracking/cdp_games

Sure you can, and in fact that's the only place that will work for

Edwin's problem. He hasn't *got* a psql session, because postmaster
startup is failing

Please, How can I do that

*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo
comun"
*-------------------------------------------------------*

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: Edwin Quijada <listas_quijada@hotmail.com>, threshar@torgo.978.org,
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Recovery Data Cant Be!!! Date: Fri, 07 Nov 2003
16:29:18 -0500

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Edwin Quijada wrote:

Where set zero_damaged_pages ??

You can't set it from postgresql.conf.

Sure you can, and in fact that's the only place that will work for
Edwin's problem. He hasn't *got* a psql session, because postmaster
startup is failing.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

_________________________________________________________________
Compare high-speed Internet plans, starting at $26.95.
https://broadband.msn.com (Prices may vary by service area.)

How can I do from postgresql.conf?

From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Edwin Quijada <listas_quijada@hotmail.com>,threshar@torgo.978.org,
pgsql-general@postgresql.org
Subject: Re: [GENERAL] Recovery Data Cant Be!!!
Date: Fri, 7 Nov 2003 16:31:43 -0500 (EST)

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Edwin Quijada wrote:

Where set zero_damaged_pages ??

You can't set it from postgresql.conf.

Sure you can, and in fact that's the only place that will work for
Edwin's problem. He hasn't *got* a psql session, because postmaster
startup is failing.

Oh, I never realized it would work with damage that severe.

--
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

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

_________________________________________________________________
Las mejores tiendas, los precios mas bajos, entregas en todo el mundo,
YupiMSN Compras: www.yupimsn.com/compras

On Fri, Nov 07, 2003 at 11:50:36PM +0000, Edwin Quijada wrote:

How can I do from postgresql.conf?

You don't need to state your questions over and over. Once is enough.

Add a line
zero_damaged_pages = true
to postgresql.conf

and follow the procedure I outlined in some previous email.
Fortunately, as pointed out by Tom, you don't need to patch anything, so
forget that part.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Las cosas son buenas o malas segun las hace nuestra opini�n" (Lisias)

can you tell me what is the rigth way to setting the params to become
Postgre in SD (SuperDatabse, indestructible) storage subsystems, wal
parameters, etc.

*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo
comun"
*-------------------------------------------------------*

From: "scott.marlowe" <scott.marlowe@ihs.com>
To: Edwin Quijada <listas_quijada@hotmail.com>
CC: <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Recovery Data Cant Be!!!
Date: Fri, 7 Nov 2003 13:29:57 -0700 (MST)

You're likely looking for pgfsck:

http://svana.org/kleptog/pgsql/pgfsck.html

Strong medicine, please backup your $PGDATA directory before playing
there, as you might wind up breaking things.

What's the exact error message in your logs when the database won't start
up?

The problem you're seeing is common when running on IDE drives with write
cache enabled, or with fsync=false on IDE with write cache disabled or
SCSI. On a solid storage subsystem, postgresql is close to
indestrubtable, i.e. you can crank up 500 simo transactions, pull the
plug, and the database will come right back up. That's how we test our
servers to make sure that the WAL and such is working properly.

On Fri, 7 Nov 2003, Edwin Quijada wrote:

Hi!!
There is an app to recover data for postgres after inapropiate server
shutdown or power electrical down?
I have a great problem. My system go down inapropiate and now my server
doesnt up.
What 's up with my data?
I lost my data?
I dont believe that a robust database like postgres is so weak for power
down and understand that can go down electrical power but fail 2 ups.

If somebody has any idea to help me this is the log file

The server must be started under an unprivileged user id to prevent
a possible system security compromise. See the documentation for
more information on how to properly start the server.

LOG: database system was interrupted at 2003-11-07 10:54:32 CET
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1327) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted being in recovery at 2003-11-07
23:33:19 CET
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1354) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted being in recovery at 2003-11-07
23:35:34 CET
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1090) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted being in recovery at 2003-11-07
23:40:47 CET
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1196) was terminated by signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted being in recovery at 2003-11-07
23:46:13 CET
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
LOG: checkpoint record is at 1/34600318
LOG: redo record is at 1/34600318; undo record is at 0/0; shutdown

FALSE

LOG: next transaction id: 6326695; next oid: 5776573
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 1/34600358
PANIC: Invalid page header in block 8805 of 17097
LOG: startup process (pid 1531) was terminated by signal 6
LOG: aborting startup due to startup process failure

*-------------------------------------------------------*
*-Edwin Quijada
*-Developer DataBase
*-JQ Microsistemas
*-809-747-2787
* " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de

lo

comun"
*-------------------------------------------------------*

_________________________________________________________________

From Beethoven to the Rolling Stones, your favorite music is always

playing

on MSN Radio Plus. No ads, no talk. Trial month FREE!
http://join.msn.com/?page=offers/premiumradio

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

_________________________________________________________________
MSN Shopping upgraded for the holidays! Snappier product search...
http://shopping.msn.com