Date: Wed, 21 Apr 2021 14:36:24 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Andres Freund <andres@anarazel.de>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, 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: <20210421183624.GP20766@tamriel.snowman.net>
References: 
 <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>
 <20210420185150.GL20766@tamriel.snowman.net>
 <633337.1618945018@sss.pgh.pa.us>
 <20210420203642.nvxvolm37f2k6prj@alap3.anarazel.de>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="NA3krjiOO8i9xiAv"
Content-Disposition: inline
In-Reply-To: <20210420203642.nvxvolm37f2k6prj@alap3.anarazel.de>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20210421183624.GP20766%40tamriel.snowman.net>
Precedence: bulk


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

Greetings,

* Andres Freund (andres@anarazel.de) wrote:
> On 2021-04-20 14:56:58 -0400, Tom Lane wrote:
> > I wonder though whether we really need authentication here.  pg_ping
> > already exposes whether the database is up, to anyone who can reach the
> > postmaster port at all.  Would it be so horrible if the "can't accept
> > connections" error message included a detail about "recovery is X%
> > done"?
>=20
> Unfortunately I think something like a percentage is hard to calculate
> right now.  Even just looking at crash recovery (vs replication or
> PITR), we don't currently know where the WAL ends without reading all
> the WAL. The easiest thing to return would be something in LSNs or
> bytes and I suspect that we don't want to expose either unauthenticated?

While it obviously wouldn't be exactly accurate, I wonder if we couldn't
just look at the WAL files we have to reply and then guess that we'll go
through about half of them before we reach the end..?  I mean, wouldn't
exactly be the first time that a percentage progress report wasn't
completely accurate. :)

> I wonder if we ought to occasionally update something like
> ControlFileData->minRecoveryPoint on primaries, similar to what we do on
> standbys? Then we could actually calculate a percentage, and it'd have
> the added advantage of allowing to detect more cases where the end of
> the WAL was lost. Obviously we'd have to throttle it somehow, to avoid
> adding a lot of fsyncs, but that seems doable?

This seems to go against Tom's concerns wrt rewriting pg_control.
Perhaps we could work through a solution to that, which would be nice,
but I'm not sure that we need the percentage to be super accurate
anyway, though, ideally, we'd work it out so that it's always increasing
and doesn't look "stuck" as long as we're actually moving forward
through the WAL.

Maybe a heuristic of 'look at the end of the WAL files, assume we'll go
through 50% of it, but only consider that to be 90%, with the last 10%
going from half-way through the WAL to the actual end of the WAL
available."

Yes, such heuristics are terrible, but they're also relatively simple
and wouldn't require tracking anything additional and would, maybe,
avoid the concern about needing to authenticate the user..

Thanks,

Stephen

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

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

iQIcBAEBCgAGBQJggHCoAAoJEO1sijiDR2RV44oP/1R6oaDqTIVkU3aJzcoEm6nn
J2vshCEVDSI2QUOSsZh7Vww9siAgBy5zdZ1+ZOKoFaIZyvhQH/7P15UtOCKccY9p
t5iANWegBjEAwwX5TqFYIrt4Bq1nl0SdrUY0PfMMnOKe7Mu7GaHJa1YkNmYzfqZH
EIhBcAh6mrbO4vmCtwlsEBjrXd7lTM3Rroi8iLp5N0P8GDl2543usUd1I4k2Rf0M
kGV/lZMhoVkcQWPe3hGpfdLz1Q1jHM24W0LdVf6lDGJB6JG3aJF8gb6aKci8RO6B
b/OmbecZlARmoS6prvqJLFq+o58+ZEocPUtc8myJSlPyOGG9veRmWgMd1h26bCYu
VS+dYvr5FahTqS0wOb77jIzn9OFQEWOYn9hysWuMKCX0ZXw5dxxqjJHzew2dBRpZ
C1Butw9ZJWHN6uUTXU7HHLYFb1gaPbyByFhgDRks8sQwJqzO524BGe/0cYnZ/I8O
qV0TWkXcxHAzLZR9WAnjiNzDHDGOJG/b8X75OOeX0/Xg4pe9enVooY4p43P4kEFJ
OTrs1n/p0Uh0FhQAZek3PBAEvoxmmVm+/z9wj+5u2ta+AwOOb6iuGwnZ9layNtyr
woRiHxrmOy+POowy0b6FpdPw1wF/ik/NZHid4D3rkIAcu7im9Ag3XiEiCpMsuBxI
D0i2+f/CWkgs6h1C86Pj
=Q/Lh
-----END PGP SIGNATURE-----

--NA3krjiOO8i9xiAv--