Re: pg_verify_checksums -d option (was: Re: pg_verify_checksums -r option)

On Mon, 27 Aug 2018 21:05:33 +0900
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp> wrote:

> On Mon, 27 Aug 2018 13:34:12 +0200
> Michael Banck <michael(dot)banck(at)credativ(dot)de> wrote:
>
> > Hi,
> >
> > On Mon, Aug 27, 2018 at 07:53:36PM +0900, Yugo Nagata wrote:
> > > On Fri, 24 Aug 2018 18:01:09 +0200
> > > Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> > > > I'm curious about this option:
> > > >
> > > > -r RELFILENODE check only relation with specified relfilenode
> > > >
> > > > but there is no facility to specify a database.
> > > >
> > > > Also, referring to the relfilenode of a mapped relation seems a bit
> > > > inaccurate.
> > > >
> > > > Maybe reframing this in terms of the file name of the file you want
> > > > checked would be better?
> > >
> > > If we specified 1234 to -r option, pg_verify_shceksums checks not only 1234
> > > but also 1234_vm, 1234_fsm, and 1234.1, 1234.2, ... and so on, so I think
> > > it makes senses to allow to specify a relfilenode instead of a file name.
> > >
> > > I think it is reasonable to add a option to specify a database, although
> > > I don't know which character is good because both -d and -D are already used....
> >
> > Maybe the -d (debug) option should be revisited as well. Mentioning
> > every scanned block generates a huge amount of output which might be
> > useful during development but does not seem very useful for a stable
> > release. AFAICT there is no other debug output for now.
> >
> > So it could be renamed to -v (verbose) and only mention each scanned
> > file, e.g. (errors/checksum mismatches are still reported of course).
> >
> > Then -d could (in the future, I guess that is too late for v11) be used
> > for -d/--dbname (or make that only a long option, if the above does not
> > work).
>
> I realized after sending the previous post that we can not specify a database
> by name because pg_verify_checksum is run in offline and this can not get the
> OID from the database name. Also, there are global and pg_tblspc directories
> not only base/<database OID>. So, it seems to me good to specify a directories
> to scan which is under PGDATA. We would be able to use -d ( or --directory ?)
> for this purpose.

Attached is a patch to allow pg_verity_checksums to specify a database
to scan. This is usefule for users who want to verify checksums of relations
in a specific database. We can specify a database by OID using -d or --dboid option.
Also, when -g or --global-only is used only shared relations are scaned.

Regards,
--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>