Re: pg_dump include/exclude data, was: verify checksums / CREATEDATABASE

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