Integrity check
Hello,
Please can you assist on following questions.
* do you any tool to check postgreSQL database integrity check?
* how do we confirm that dump file is proper data?
* do you any doc to check the integrity of psql db?
Venkat Prasad
CREDIT SUISSE
SCM Systems Support
The signature level 4
Changi Business Park
Singapore 486066
Phone +65 6306 0310
==============================================================================
Please access the attached hyperlink for an important electronic communications disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
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.
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:
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.
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.