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

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