verify checksums / CREATE DATABASE

On Wed, Jun 5, 2019 at 4:47 PM Karsten Hilbert <Karsten.Hilbert@gmx.net>
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.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

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

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
adrian.klaver@aklaver.com

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

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

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

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
adrian.klaver@aklaver.com

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

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
adrian.klaver@aklaver.com