Thread: verify checksums / CREATE DATABASE
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
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.
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
Aw: Re: pg_dump include/exclude data, was: verify checksums /CREATE DATABASE
From
"Karsten Hilbert"
Date:
> > 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
Re: Aw: Re: pg_dump include/exclude data, was: verify checksums /CREATE DATABASE
From
Adrian Klaver
Date:
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