Re: Finding database for pg_upgrade missing library

On Fri, Jul 13, 2018 at 10:15:34PM -0500, Justin T Pryzby wrote:
> On Fri, Jul 13, 2018 at 12:28:15PM -0400, Bruce Momjian wrote:
> > I received a private pg_upgrade feature request to report the database
> > name for missing loadable libraries. Currently we report "could not
> > load library" and the library file name, e.g. $libdir/pgpool-regclass.
> >
> > The request is that we report the _database_ name that contained the
> > loadable library reference. However, that isn't easy to do because we
> > gather all loadable library file names, sort them, and remove
> > duplicates, for reasons of efficiency and so we check libraries in a
> > predictable alphabetical order.
> >
> > Is it worth modifying pg_upgrade to report the first or all databases
> > that contain references to missing loadable libraries? I don't think
> > so, but I wanted to ask here.
>
> Yes please, with a preference for the "all databases" option.
>
> We typically have only 4 DBs, including postgres and template1,2. It's
> annoying enough when an upgrade process breaks because pg_repack or
> pg_stat_buffercache installed into postgres DB. But it's a veritable pain when
> you discover in the middle of an upgrade that postgis had been somehow loaded
> into template1, needs to be uninstalled (or upgraded from 22 to 23 to allow
> upgrade), old postgis package was already removed.. Maybe you find that one
> library was installed one place, fix it and restart the upgrade process. Then
> it fails because the old library was also installed some other place..
>
> When I've had to figure this out in the past, I ended up grepping the dumps to
> figure out what old library was where.

OK, we now have three people who want this so we will get it into PG 12.
Thanks.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +