Re: when the startup process doesn't

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > On Tue, Apr 20, 2021 at 5:17 PM Jehan-Guillaume de Rorthais
> > <jgdr(at)dalibo(dot)com> wrote:
> >> Two another options:
> >> 1. if this is limited to local access only, outside of the log entries, the
> >> status of the startup could be updated in the controldata file as well. This
> >> would allows to watch it without tail-grep'ing logs using eg. pg_controldata.
>
> > I think doing so in controldata would definitely make things
> > complicated for no real reason. Plus controldata has a fixed size (and
> > has to have), whereas something like this would probably want more
> > variation than that makes easy.
>
> Also, given that pg_control is as critical a bit of data as we have,
> we really don't want to be writing it more often than we absolutely
> have to.

Yeah, don't think pg_control fiddling is what we want. I do agree with
improving the logging situation around here, certainly.

> > There could be a "startup.status" file I guess which would basically
> > contain the last line of what would otherwise be in the log. But if it
> > remains a textfile, I'm not sure what the gain is -- you'll just have
> > to have the dba look in more places than one to find it? It's not like
> > there's likely to be much other data written to the log during these
> > times?
>
> Yeah, once you are talking about dumping stuff in a file, it's not
> clear how that's better than progress-messages-in-the-log. People
> already have a lot of tooling for looking at the postmaster log.

Agreed.

> I think the point of Robert's other proposal is to allow remote
> checks of the restart's progress, so local files aren't much of
> a substitute anyway.

Yeah, being able to pick up on this remotely seems like it'd be quite
nice. I'm not really thrilled with the idea, but the best I've got
offhand for this would be a new role that's "pg_recovery_login" where an
admin can GRANT that role to the roles they'd like to be able to use to
login during the recovery process and then, for those roles, we write
out flat files to allow authentication without access to pg_authid,
whenever their password or such changes. It's certainly a bit grotty
but I do think it'd work. I definitely don't want to go back to having
all of pg_authid written as a flat file and I'd rather that existing
tools and libraries work with this (meaning using the same port and
speaking the PG protocol and such) rather than inventing some new thing
that listens on some other port, etc.

On the fence about tying this to 'pg_monitor' instead of using a new
predefined role. Either way, I would definitely prefer to see the admin
have to create a role and then GRANT the predefined role to that role.
I really dislike the idea of having predefined roles that can be used to
directly log into the database.

Thanks,

Stephen