verify checksums / CREATE DATABASE

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

verify checksums / CREATE DATABASE

Karsten Hilbert
Dear all,

I cannot find documentation on whether

        CREATE DATABASE ... TEMPLATE template_db;

will verify checksums (if enabled) on template_db during
reading.

I would assume it does not, because very likely the copy
process happens at the file level. Is that correct ?

Many thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B


Reply | Threaded
Open this post in threaded view
|

Re: verify checksums / CREATE DATABASE

Magnus Hagander-2


On Wed, Jun 5, 2019 at 4:47 PM Karsten Hilbert <[hidden email]> wrote:
Dear all,

I cannot find documentation on whether

        CREATE DATABASE ... TEMPLATE template_db;

will verify checksums (if enabled) on template_db during
reading.

I would assume it does not, because very likely the copy
process happens at the file level. Is that correct ?


That is correct, it does not verify checksums when copying the template. 


--
Reply | Threaded
Open this post in threaded view
|

pg_dump include/exclude data, was: verify checksums / CREATE DATABASE

Karsten Hilbert
Now that it is established that CREATE DATABASE does not
verify checksums on the template I have a followup question.

The current canonical solution (?) for verifying checksums in
an existing database is, to may understanding, to pg_dump it
(to /dev/null, perhaps):

        pg_dump --username=... --dbname=... --compress=0 --no-sync --format=custom --file=/dev/null

as that will read and verify all blocks related to the dump
of that database.

One will be tempted to include options to speed up the
process, say:

         --data-only

which would not output schema definitions. I wonder, however,
whether doing so would allow pg_dump to skip some reads into
the catalog tables, thereby, perhaps not detecting some
corruption in those ?

This question would apply to the following list of options as
far as I can see:

        #--no-acl
        #--no-comments
        #--no-publications
        #--no-subscriptions
        #--no-security-label

Is my assumption wrong ?

I don't really expect to just be handed a full answer (unless
someone easily knows offhand) - however, I don't really know
where to look for it. Pointers would be helpful.

Is the only way to know reading the source or suitable
server logs and compare queries between runs with/without
said options ?

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump include/exclude data, was: verify checksums / CREATE DATABASE

Adrian Klaver-4
On 6/6/19 6:50 AM, Karsten Hilbert wrote:

> Now that it is established that CREATE DATABASE does not
> verify checksums on the template I have a followup question.
>
> The current canonical solution (?) for verifying checksums in
> an existing database is, to may understanding, to pg_dump it
> (to /dev/null, perhaps):
>
> pg_dump --username=... --dbname=... --compress=0 --no-sync --format=custom --file=/dev/null
>
> as that will read and verify all blocks related to the dump
> of that database.
>

The question I have is:

The above works with the existing cluster, but would you not also want
to verify that the blocks written to on the new cluster also are good?

> One will be tempted to include options to speed up the
> process, say:
>
> --data-only
>
> which would not output schema definitions. I wonder, however,
> whether doing so would allow pg_dump to skip some reads into
> the catalog tables, thereby, perhaps not detecting some
> corruption in those ?
>
> This question would apply to the following list of options as
> far as I can see:
>
> #--no-acl
> #--no-comments
> #--no-publications
> #--no-subscriptions
> #--no-security-label
>
> Is my assumption wrong ?

Not sure, though it would seem to me including the above is a relatively
small incremental cost to the overall dump, assuming a data set of any
size greater then small.

>
> I don't really expect to just be handed a full answer (unless
> someone easily knows offhand) - however, I don't really know
> where to look for it. Pointers would be helpful.
>
> Is the only way to know reading the source or suitable
> server logs and compare queries between runs with/without
> said options ?
>
> Thanks,
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump include/exclude data, was: verify checksums / CREATE DATABASE

Tom Lane-2
On 6/6/19 6:50 AM, Karsten Hilbert wrote:
>> The current canonical solution (?) for verifying checksums in
>> an existing database is, to may understanding, to pg_dump it
>> (to /dev/null, perhaps):
>> as that will read and verify all blocks related to the dump
>> of that database.

FWIW, that doesn't seem especially canonical from here.  In particular,
pg_dump will not normally result in any examination at all of indexes
on user-defined tables --- it'll just be doing seqscans of the tables
proper.  You could hope for reasonably complete coverage of the system
catalogs along with user tables, but missing out user indexes seems
like a pretty big gap.

The actual solution for this as of v11 is pg_verify_checksums
(renamed to just pg_checksums for v12).  I don't think there's
any really convincing answer before v11.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump include/exclude data, was: verify checksums / CREATE DATABASE

Karsten Hilbert
In reply to this post by Adrian Klaver-4
Dear Adrian,

On Fri, Jun 07, 2019 at 08:02:32AM -0700, Adrian Klaver wrote:

> On 6/6/19 6:50 AM, Karsten Hilbert wrote:
> > The current canonical solution (?) for verifying checksums in
> > an existing database is, to may understanding, to pg_dump it
> > (to /dev/null, perhaps):
> >
> > pg_dump --username=... --dbname=... --compress=0 --no-sync --format=custom --file=/dev/null
> >
> > as that will read and verify all blocks related to the dump
> > of that database.
>
> The above works with the existing cluster, but would you not also want to
> verify that the blocks written to on the new cluster also are good?

Sure, but that much depends on what (or rather, where) the
"new cluster" actually is.

In my case I want to make sure that - before a run of "CREATE
DATABASE new TEMPLATE old" - I can be reasonable sure that
the disk blocks underlying "old" verify against their
checksum. Since the CREATE DATABASE does not verify CRCs I
use a pg_dump into /dev/null to verify checksums while the
cluster is *online*.

The problem I hope to protect against with this approach: the
CREATE DATABASE might untaint corrupted data from a bad disk
block into a good disk block virtue of doing a file level
copy.

I hope my reasoning isn't going astray.

> > One will be tempted to include options to speed up the
> > process, say:
...
> > Is my assumption wrong ?
>
> Not sure, though it would seem to me including the above is a relatively
> small incremental cost to the overall dump, assuming a data set of any size
> greater then small.

Agreed. Good putting of things into perspective. Will do.

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump include/exclude data, was: verify checksums / CREATE DATABASE

Karsten Hilbert
In reply to this post by Tom Lane-2
Dear Tom,

On Fri, Jun 07, 2019 at 11:41:36AM -0400, Tom Lane wrote:

> On 6/6/19 6:50 AM, Karsten Hilbert wrote:
> >> The current canonical solution (?) for verifying checksums in
> >> an existing database is, to may understanding, to pg_dump it
> >> (to /dev/null, perhaps):
> >> as that will read and verify all blocks related to the dump
> >> of that database.
>
> FWIW, that doesn't seem especially canonical from here.  In particular,
> pg_dump will not normally result in any examination at all of indexes
> on user-defined tables --- it'll just be doing seqscans of the tables
> proper.  You could hope for reasonably complete coverage of the system
> catalogs along with user tables, but missing out user indexes seems
> like a pretty big gap.

I agree it would, but not in the case I hope to protect:
Within the database upgrade process, after cloning the
database via "CREATE DATABASE ... TEMPLATE ...", all indexes
are REINDEXed inside the new database. That would take care
of corrupted data having been carried over within index
related disk blocks, I would think.

> The actual solution for this as of v11 is pg_verify_checksums
> (renamed to just pg_checksums for v12).

Sure but that is not (yet) possible against a cluster that's online.

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump include/exclude data, was: verify checksums / CREATE DATABASE

Adrian Klaver-4
In reply to this post by Karsten Hilbert
On 6/11/19 12:15 AM, Karsten Hilbert wrote:
> Dear Adrian,

> Sure, but that much depends on what (or rather, where) the
> "new cluster" actually is.
>
> In my case I want to make sure that - before a run of "CREATE
> DATABASE new TEMPLATE old" - I can be reasonable sure that
> the disk blocks underlying "old" verify against their
> checksum. Since the CREATE DATABASE does not verify CRCs I
> use a pg_dump into /dev/null to verify checksums while the
> cluster is *online*.
>
> The problem I hope to protect against with this approach: the
> CREATE DATABASE might untaint corrupted data from a bad disk
> block into a good disk block virtue of doing a file level
> copy.
>
> I hope my reasoning isn't going astray.

As I understand it checksums are done on the page level using a hash(for
details: https://doxygen.postgresql.org/checksum__impl_8h_source.html).
I am not sure how a page could get un-corrupted by virtue of a file copy.


>
>>> One will be tempted to include options to speed up the
>>> process, say:
> ...
>>> Is my assumption wrong ?
>>
>> Not sure, though it would seem to me including the above is a relatively
>> small incremental cost to the overall dump, assuming a data set of any size
>> greater then small.
>
> Agreed. Good putting of things into perspective. Will do.
>
> Thanks,
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Aw: Re: pg_dump include/exclude data, was: verify checksums / CREATE DATABASE

Karsten Hilbert
> > The problem I hope to protect against with this approach: the
> > CREATE DATABASE might untaint corrupted data from a bad disk
> > block into a good disk block virtue of doing a file level
> > copy.
> >
> > I hope my reasoning isn't going astray.
>
> As I understand it checksums are done on the page level using a hash(for
> details: https://doxygen.postgresql.org/checksum__impl_8h_source.html).
> I am not sure how a page could get un-corrupted by virtue of a file copy.

Ah, no, I did not explain myself well.

Let's assume a corrupted, bad (but readable at the hardware
level) disk block B. A filesystem level copy (as in CREATE
DATABASE) would successfully read that disk block B and
copy the corrupted content into a good disk block G elsewhere
on the disk. Verifying the checksum of the page sitting on
block B before doing the database cloning would
reveal the corruption before it got cloned.

Does that make sense ?

Karsten


Reply | Threaded
Open this post in threaded view
|

Re: Aw: Re: pg_dump include/exclude data, was: verify checksums / CREATE DATABASE

Adrian Klaver-4
On 6/11/19 11:15 AM, Karsten Hilbert wrote:

>>> The problem I hope to protect against with this approach: the
>>> CREATE DATABASE might untaint corrupted data from a bad disk
>>> block into a good disk block virtue of doing a file level
>>> copy.
>>>
>>> I hope my reasoning isn't going astray.
>>
>> As I understand it checksums are done on the page level using a hash(for
>> details: https://doxygen.postgresql.org/checksum__impl_8h_source.html).
>> I am not sure how a page could get un-corrupted by virtue of a file copy.
>
> Ah, no, I did not explain myself well.
>
> Let's assume a corrupted, bad (but readable at the hardware
> level) disk block B. A filesystem level copy (as in CREATE
> DATABASE) would successfully read that disk block B and
> copy the corrupted content into a good disk block G elsewhere
> on the disk. Verifying the checksum of the page sitting on
> block B before doing the database cloning would
> reveal the corruption before it got cloned.
>
> Does that make sense ?

Yes.

>
> Karsten
>


--
Adrian Klaver
[hidden email]