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

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