need help for PostgreSQL consistency check mechanism

Chen, Dongdong (GE Healthcare) wrote:

We are now working on database check mechanism in our application. The
situation is when server shutdown abnormally, the postmaster.pid file
still exists when reboot, But the PostgreSQL database itself may not be
damaged. We just do not want to restore from backups when the database
is still good even if server shutdown abnormally. If the PostgreSQL
database records are damaged for sure when server reboots, then the
restoration should be executed automatically.

Just start it up. If there is recovery to execute, it'll be done
automatically.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

What is the mechanism about automatic recovery when startup? I read the
PostgreSQL document and didnt find anything about this. can you specify
that, pls?
Thank you so much!

B.R.

ChenDongdong

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Friday, April 24, 2009 8:22 AM
To: Chen, Dongdong (GE Healthcare)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] need help for PostgreSQL consistency check
mechanism

Chen, Dongdong (GE Healthcare) wrote:

We are now working on database check mechanism in our application. The

situation is when server shutdown abnormally, the postmaster.pid file
still exists when reboot, But the PostgreSQL database itself may not
be damaged. We just do not want to restore from backups when the
database is still good even if server shutdown abnormally. If the
PostgreSQL database records are damaged for sure when server reboots,
then the restoration should be executed automatically.

Just start it up. If there is recovery to execute, it'll be done
automatically.

--
Alvaro Herrera
http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, 2009-04-24 at 09:03 +0800, Chen, Dongdong (GE Healthcare) wrote:

What is the mechanism about automatic recovery when startup? I read the
PostgreSQL document and didnt find anything about this. can you specify
that, pls?

http://www.postgresql.org/docs/8.3/static/wal-intro.html

Sincerely,

Joshua D. Drake

Thank you so much!

B.R.

ChenDongdong

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Friday, April 24, 2009 8:22 AM
To: Chen, Dongdong (GE Healthcare)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] need help for PostgreSQL consistency check
mechanism

Chen, Dongdong (GE Healthcare) wrote:

We are now working on database check mechanism in our application. The

situation is when server shutdown abnormally, the postmaster.pid file
still exists when reboot, But the PostgreSQL database itself may not
be damaged. We just do not want to restore from backups when the
database is still good even if server shutdown abnormally. If the
PostgreSQL database records are damaged for sure when server reboots,
then the restoration should be executed automatically.

Just start it up. If there is recovery to execute, it'll be done
automatically.

--
Alvaro Herrera
http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

I wanna know if there is any way or tool to detect the DB data loss or
damage and inform developer the result before recovery? If there is
data loss, the DB could still open or not ?
Sometimes even if shutdown abnormally, the data still keep in good
condition.

Thanks for your help!

B.R.

ChenDongdong

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Friday, April 24, 2009 9:18 AM
To: Chen, Dongdong (GE Healthcare)
Cc: Alvaro Herrera; pgsql-general@postgresql.org
Subject: Re: [GENERAL] need help for PostgreSQL consistency check
mechanism

On Fri, 2009-04-24 at 09:03 +0800, Chen, Dongdong (GE Healthcare) wrote:

What is the mechanism about automatic recovery when startup? I read
the PostgreSQL document and didnt find anything about this. can you
specify that, pls?

http://www.postgresql.org/docs/8.3/static/wal-intro.html

Sincerely,

Joshua D. Drake

Thank you so much!

B.R.

ChenDongdong

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Friday, April 24, 2009 8:22 AM
To: Chen, Dongdong (GE Healthcare)
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] need help for PostgreSQL consistency check
mechanism

Chen, Dongdong (GE Healthcare) wrote:

We are now working on database check mechanism in our application.
The

situation is when server shutdown abnormally, the postmaster.pid
file still exists when reboot, But the PostgreSQL database itself
may not be damaged. We just do not want to restore from backups when

the database is still good even if server shutdown abnormally. If
the PostgreSQL database records are damaged for sure when server
reboots, then the restoration should be executed automatically.

Just start it up. If there is recovery to execute, it'll be done
automatically.

--
Alvaro Herrera
http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Thu, Apr 23, 2009 at 8:24 PM, Chen, Dongdong (GE Healthcare)
<DongdongChen@ge.com> wrote:

I wanna know if there is any way or tool to detect the DB data loss or
damage and inform developer  the result before recovery? If there is
data loss, the DB could still open or not ?
Sometimes even if shutdown abnormally, the data still keep in good
condition.

Assuming non broken hardware and a drive subsystem that doesn't lie
about fsync, and that the dba hasn't turned off fsync, an abnormal
shutdown should NEVER result in a corrupted database.

When the OS starts up, it wants to detect whether there is data loss in PostgreSQL from last shutdown, is there a method provided?

Thanks for your help!

B.R.

ChenDongdong

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Friday, April 24, 2009 10:29 AM
To: Chen, Dongdong (GE Healthcare)
Cc: jd@commandprompt.com; Alvaro Herrera; pgsql-general@postgresql.org
Subject: Re: [GENERAL] need help for PostgreSQL consistency check mechanism

On Thu, Apr 23, 2009 at 8:24 PM, Chen, Dongdong (GE Healthcare) <DongdongChen@ge.com> wrote:

I wanna know if there is any way or tool to detect the DB data loss or
damage and inform developer  the result before recovery? If there is
data loss, the DB could still open or not ?
Sometimes even if shutdown abnormally, the data still keep in good
condition.

Assuming non broken hardware and a drive subsystem that doesn't lie about fsync, and that the dba hasn't turned off fsync, an abnormal shutdown should NEVER result in a corrupted database.

Chen, Dongdong (GE Healthcare) escribi�:

When the OS starts up, it wants to detect whether there is data loss
in PostgreSQL from last shutdown, is there a method provided?

Why would the OS want to do that?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Fri, 2009-04-24 at 10:04 -0400, Alvaro Herrera wrote:

Chen, Dongdong (GE Healthcare) escribió:

When the OS starts up, it wants to detect whether there is data loss
in PostgreSQL from last shutdown, is there a method provided?

Why would the OS want to do that?

That doesn't make sense at all. Chen, are you talking about an unclean
shutdown where you have to fsck a file system?

Sincerely,

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

2009/4/24 Joshua D. Drake <jd@commandprompt.com>

On Fri, 2009-04-24 at 10:04 -0400, Alvaro Herrera wrote:

Chen, Dongdong (GE Healthcare) escribió:

When the OS starts up, it wants to detect whether there is data loss
in PostgreSQL from last shutdown, is there a method provided?

Why would the OS want to do that?

That doesn't make sense at all.

That is just a bad habit from MySQL world. People just don't understand that
all possible recovery is handled by postgres itself.

OTOH, there could be some consistency check method... If postgres had block-
or row-level checksums, this could do.
The best way I know is to do plain pg_dumpall. But this does not detect all
data corruptions.

--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

Filip Rembiałkowski escribió:

OTOH, there could be some consistency check method... If postgres had block-
or row-level checksums, this could do.

Row level: it would be very expensive to compute, store and keep up to
date. And it doesn't protect you from corruption elsewhere in the
block.

Block level: there was some effort to implement it for 8.4, but it fell
into some deadly traps.

The best way I know is to do plain pg_dumpall. But this does not detect all
data corruptions.

I wrote some plpgsql code a month ago to scan a table and detoast all
toastable attributes, reporting it when an exception was raised. It was
a very effective way to detect corrupted toast entries, which is the
most visible way in which data is corrupted.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Row level: it would be very expensive to compute, store and keep up to date. And it doesn't protect you from corruption elsewhere in the block.

Block level: there was some effort to implement it for 8.4, but it fell into some deadly traps.

Checksum is my primitive thought, is there table level checksum? Our objective is like this: If shutdown normally, OK. If shutdown ABNORMALLY, then reboot the OS, check whether there is database records loss or damage, if NO, OK, start app as usual; if YES, run restore app(currently pg_restore). All this should be done automatically without manual operating and avoid restoring if data records are in good condition.

I wrote some plpgsql code a month ago to scan a table and detoast all toastable attributes, reporting it when an exception was raised. It was a very effective way to detect corrupted toast entries, which is the most visible way in which data is corrupted.

Could you share the code pls, if possible? Just for a referrence.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Thanks!
Chen Dongdong

On Mon, Apr 27, 2009 at 10:13:47AM +0800, Chen, Dongdong (GE

Healthcare) wrote: Checksum is my primitive thought, is there table
level checksum? Our objective is like this: If shutdown normally, OK.
If shutdown ABNORMALLY, then reboot the OS, check whether there is
database records loss or damage, if NO, OK, start app as usual; if
YES, run restore app(currently pg_restore). All this should be done
automatically without manual operating and avoid restoring if data
records are in good condition.

PostgreSQL knows when it has been shutdown abnormally and will recover
to a consistant state (i.e. rollback all uncommitted transactions).
Whether this constitutes "database record loss" depends on your
application. I personally havn't managed to actually corrupt Postgres
enough to lose anything, but it happens.

Just make sure your disk cache is safe, otherwise all bets are off.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/