Обсуждение: Re: [BUGS] Integrity check
On Tue, Jun 23, 2009 at 03:38:35PM +0800, Prasad, Venkat wrote: > Hello, > > Please can you assist on following questions. This is an issue for pgsql-general, where I'm redirecting this. > > * do you any tool to check postgreSQL database integrity check? No more than Oracle does. We get it right in the first place. The existence of "integrity checking" tools means the DBMS is done with high incompetence. > * how do we confirm that dump file is proper data? See above. > * do you any doc to check the integrity of psql db? See above. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Jun 23, 2009 at 1:38 PM, David Fetter<david@fetter.org> wrote: > On Tue, Jun 23, 2009 at 03:38:35PM +0800, Prasad, Venkat wrote: >> * do you any tool to check postgreSQL database integrity check? > > No more than Oracle does. We get it right in the first place. The > existence of "integrity checking" tools means the DBMS is done with > high incompetence. > >> * how do we confirm that dump file is proper data? > > See above. Whilst I don't disagree with the sentiment, that sort of answer is unlikely to impress auditors in my experience. Annoyingly they tend to want you to be able to prove to them that your backups are good. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Tue, Jun 23, 2009 at 02:00:51PM +0100, Dave Page wrote: > On Tue, Jun 23, 2009 at 1:38 PM, David Fetter<david@fetter.org> wrote: > > On Tue, Jun 23, 2009 at 03:38:35PM +0800, Prasad, Venkat wrote: > > >> * do you any tool to check postgreSQL database integrity check? > > > > No more than Oracle does. We get it right in the first place. The > > existence of "integrity checking" tools means the DBMS is done with > > high incompetence. > > > >> * how do we confirm that dump file is proper data? > > > > See above. > > Whilst I don't disagree with the sentiment, that sort of answer is > unlikely to impress auditors in my experience. Annoyingly they tend > to want you to be able to prove to them that your backups are good. There is no general way to do that, apart from creating a test suite specific to your scenario and hoping it doesn't have more bugs that the thing it's testing. Sometimes it's good not to agree with the premises underlying a question, and I believe this is one of those times. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Jun 23, 2009 at 2:11 PM, David Fetter<david@fetter.org> wrote: > There is no general way to do that, apart from creating a test suite > specific to your scenario and hoping it doesn't have more bugs that > the thing it's testing. You don't have to tell me that :-) > Sometimes it's good not to agree with the premises underlying a > question, and I believe this is one of those times. Auditors can be a funny breed. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Tue, Jun 23, 2009 at 02:17:02PM +0100, Dave Page wrote: > On Tue, Jun 23, 2009 at 2:11 PM, David Fetter<david@fetter.org> wrote: > > > There is no general way to do that, apart from creating a test suite > > specific to your scenario and hoping it doesn't have more bugs that > > the thing it's testing. > > You don't have to tell me that :-) I didn't imagine I did. :) > > Sometimes it's good not to agree with the premises underlying a > > question, and I believe this is one of those times. > > Auditors can be a funny breed. They can, at that, but in this case, they're simply doing the normal human thing of trying to figure out whether there's a way they can push off their work to someone or something else. In this case, they can't. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Jun 23, 2009 at 2:26 PM, David Fetter<david@fetter.org> wrote: >> Auditors can be a funny breed. > > They can, at that, but in this case, they're simply doing the normal > human thing of trying to figure out whether there's a way they can > push off their work to someone or something else. In this case, they > can't. Depends what they've been hired to audit exactly - there's a difference in the way they will attack the IT aspects of a financial audit versus an IT management audit for example. In my experience, the former is worse because they are often done by finance people who will simply box-tick 'verified backups' before reporting to the board or regulator, unlike an IT auditor who will look at the systems as a whole and judge the processes based on their technical merit. Anyway - this is pre-beer conference talk... -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Tue, Jun 23, 2009 at 02:30:50PM +0100, Dave Page wrote: > On Tue, Jun 23, 2009 at 2:26 PM, David Fetter<david@fetter.org> wrote: > > >> Auditors can be a funny breed. > > > > They can, at that, but in this case, they're simply doing the > > normal human thing of trying to figure out whether there's a way > > they can push off their work to someone or something else. In > > this case, they can't. > > Depends what they've been hired to audit exactly - there's a > difference in the way they will attack the IT aspects of a financial > audit versus an IT management audit for example. In my experience, > the former is worse because they are often done by finance people > who will simply box-tick 'verified backups' before reporting to the > board or regulator, unlike an IT auditor who will look at the > systems as a whole and judge the processes based on their technical > merit. Right :) > Anyway - this is pre-beer conference talk... Excellent idea :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Jun 23, 2009 at 2:00 PM, Dave Page<dpage@pgadmin.org> wrote: > On Tue, Jun 23, 2009 at 1:38 PM, David Fetter<david@fetter.org> wrote: >> On Tue, Jun 23, 2009 at 03:38:35PM +0800, Prasad, Venkat wrote: > >>> * do you any tool to check postgreSQL database integrity check? >> >> No more than Oracle does. We get it right in the first place. The >> existence of "integrity checking" tools means the DBMS is done with >> high incompetence. Uhm well, data corruption still happens even if the DBMS is entirely bug-free (which Postgres hasn't always been in the past anyways...) Hardware sometimes fails, filesystems have bugs, and there are gotchas with hot backups which can result in corrupt databases. Witness the recent attempts to implement CRC checksums which failed for technical reasons. There is demand for this type of assurance but it's just a lot harder to implement usefully than it sounds. Consider also the problem that there's a limited amount of corruption it could actually test for. A lot of corruption could happen within the data itself, rather than in the metadata. Or metadata could be corrupted but still look reasonable. Even if we had checksums it's easy to get into a situation where the checksum is calculated based on the corrupt data. But the real reason we don't have such a tool, imho, is that it would be boring unsexy work, especially when you factor in the maintenance effort. And the various code paths for corrupt data would rarely get used -- there's not much glory in writing a tool that all anyone sees it print is "everything's ok". >>> * how do we confirm that dump file is proper data? >> >> See above. > > Whilst I don't disagree with the sentiment, that sort of answer is > unlikely to impress auditors in my experience. Annoyingly they tend to > want you to be able to prove to them that your backups are good. If I were an auditor the only proof i would believe that a backup was good was to see it actually get restored successfully (and the restored database pass integrity checks). Any special-purpose tool will only prove that the backup passes the checks that that special-purpose tool checks for. The best way to test what will happen in a real restore situation is to actually simulate one. -- greg http://mit.edu/~gsstark/resume.pdf
David Fetter wrote: > > Please can you assist on following questions. > > This is an issue for pgsql-general, where I'm redirecting this. > > > > * do you any tool to check postgreSQL database integrity check? > > No more than Oracle does. We get it right in the first place. The > existence of "integrity checking" tools means the DBMS is done with > high incompetence. What about VACUUM VERBOSE? Pre 8.4, it will touch every block and get problems if the block contains garbage. From 8.4 on, maybe a VACUUM FULL is necessary. Of course this will not check stuff the integrity of foreign key constraints, but it should detect most block corruptions, right? > > * how do we confirm that dump file is proper data? > > See above. How about "restore the dump and look if there are errors"? Yours, Laurenz Albe
2009/6/23 David Fetter <david@fetter.org>
On Tue, Jun 23, 2009 at 03:38:35PM +0800, Prasad, Venkat wrote:This is an issue for pgsql-general, where I'm redirecting this.
> Hello,
>
> Please can you assist on following questions.>No more than Oracle does. We get it right in the first place. The
> * do you any tool to check postgreSQL database integrity check?
existence of "integrity checking" tools means the DBMS is done with
high incompetence.
It depends on what you mean by integrity. If you mean "Does the database have the data I THINK it's supposed to have".. that's all up to you when you design your schema (FK's, Unique, etc..) and your app developers:
'Where thou wouldst type foo, someone else would surely type supercalafragilisticexpialadocious'
(paraphrased)
If you're asking "Does the database have the ability to verify that whatever is in a block is what was put into that block", then Oracle has block check-summing (I'm not sure if this got into PG 8.4 or not...) . I'm not sure if there is a checksum on the datafiles themselves, but I would guess in the affirmative when talking Oracle.
> * how do we confirm that dump file is proper data?
This is talking about "Does the database have what I think it should?" and can't really be done unless you had some auditing system setup to compare it to.
--Scott
On Wed, Jun 24, 2009 at 11:45 PM, Scott Mead<scott.lists@enterprisedb.com> wrote: > > If you're asking "Does the database have the ability to verify that > whatever is in a block is what was put into that block", then Oracle has > block check-summing (I'm not sure if this got into PG 8.4 or not...) It didn't, due to technical issues. However you should be aware that checksumming doesn't necessarily provide a broad guarantee either. It can only protect against damage that occurs between the time the checksum is generated and when it's verified. If you have bad memory, for example, it can corrupt the data before the checksum is calculated, so the block may be stored happily with a valid checksum for the corrupt data. -- greg http://mit.edu/~gsstark/resume.pdf