Обсуждение: need help for PostgreSQL consistency check mechanism
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.
So, is there any way or tool to automatically check the consistency status of PostgreSQL database when server starts up (like the DBCC in SQL server)?
Thanks for the suggestion.
B.R.
Kevin Chen
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 methodprovided? 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>
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.
On Fri, 2009-04-24 at 10:04 -0400, Alvaro Herrera wrote:That doesn't make sense at all.
> 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 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 corruptionelsewhere 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. Ifshutdown ABNORMALLY, then reboot the OS, check whether there is database records loss or damage, if NO, OK, start app asusual; if YES, run restore app(currently pg_restore). All this should be done automatically without manual operating andavoid 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 exceptionwas raised. It was a very effective way to detect corrupted toast entries, which is the most visible way in whichdata 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/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines.