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

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(dot)klaver(at)aklaver(dot)com