Date: Tue, 20 Apr 2021 14:51:50 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Magnus Hagander <magnus@hagander.net>,
	Jehan-Guillaume de Rorthais <jgdr@dalibo.com>,
	Robert Haas <robertmhaas@gmail.com>,
	Thomas Munro <thomas.munro@gmail.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: when the startup process doesn't
Message-ID: <20210420185150.GL20766@tamriel.snowman.net>
References: 
 <CA+TgmoaHQrgDFOBwgY16XCoMtXxsrVGFB2jNCvb7-ubuEe1MGg@mail.gmail.com>
 <CA+hUKGKg6_h=p03vA9_+05RAJGDDJ4R0u29f5UEhfg7bZTcSog@mail.gmail.com>
 <465221.1618879471@sss.pgh.pa.us>
 <CA+TgmoaSELPiPqNcfNG_TyDe6=VVzfyh+YvhB6-GGsWoo=jc8Q@mail.gmail.com>
 <CABUevEwc_oP=kf6CYxNGM3KLMRBe3YBhM8+qu0oBbY_x89ifHw@mail.gmail.com>
 <20210420171720.56599607@firost>
 <CABUevEyTBiiDvjdKo5h=gj6W1uL6i4jOOV4nur0YZsuKvKRVXw@mail.gmail.com>
 <631849.1618942988@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="qjDzi6VqOhUaSbdf"
Content-Disposition: inline
In-Reply-To: <631849.1618942988@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210420185150.GL20766%40tamriel.snowman.net>
Precedence: bulk


--qjDzi6VqOhUaSbdf
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Magnus Hagander <magnus@hagander.net> writes:
> > On Tue, Apr 20, 2021 at 5:17 PM Jehan-Guillaume de Rorthais
> > <jgdr@dalibo.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=
=2E This
> >> would allows to watch it without tail-grep'ing logs using eg. pg_contr=
oldata.
>=20
> > 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.
>=20
> 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?
>=20
> 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

--qjDzi6VqOhUaSbdf
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJgfyLGAAoJEO1sijiDR2RV68gQAKPrH81dgP2KZz3J/B48LgEn
1lS96MDwKsfZmehcuFL0dP7n/HryMdnPplOtJztjjVH2/JESOdpDALVWz5JzEMPW
Qj6Iui7E4LzhORfzvDJSG5U3/9PgT3/cKqrtHg7PJGplb7mzwLMinkBmcRrfVg3w
CjNnujeEFFhChWnGjyA8OcYPJojzebRIdTpZh/jTZ61Z6m3bJlQbsEHk7a7phd8L
esJhk8LqiNA8Zyipnij4FSJkkHo8udDJyLiWQ7yOIy0W7Xk8WlJ1CBNaqjw2N43T
z7CgFIOdgUMSn32CbZbA/d+aROkLfkgKKFi4RDD8rXemp07XjjHLsBrJ1PIl0Xil
9MacUTD/VMT2P4k4qJOWCTB4mBj9JF8EF3B+XrEodWsR9vvgpR2+IMEKblfBYQ7N
lW/Y1Fy4Cf012x2pKJT7oDV1QqSHF5AwDdQmKgTzd9qag7u9oXlABFSzxfAzTbmE
fDZ+OgIa81rdp2AP5ovqn5msng1UTJkTvV2JG3VdM3rFQCKedtA8Rp4uTh+S+nl9
CLQfT12ZwvbOj+sowfTKUMBD6mC4G78eU+7A3CrJlM5qXBDD7fK9gverIT+nJEV6
5uRdBXNmVtyTlgoYPU6TaPe2+3E6AJwKXjNH9SLp/gCF3Ki3I8zBywEUzKKJYrnA
uQ+k1WyJWJy7kZ8fnJbt
=a2Au
-----END PGP SIGNATURE-----

--qjDzi6VqOhUaSbdf--