Re: Finding database for pg_upgrade missing library

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.

I have these comments to myself from the last time I had to figure out what
[(database, [missing library,...]), ...] were involved, probably last
September.

# time /usr/pgsql-9.6/bin/pg_dump --schema-only --quote-all-identifiers --binary-upgrade --format=custom dbname=ts |grep -a postgis- |grep -wv postgis-2.2
# [pryzbyj(at)database ~]$ time sudo sh -c 'for f in /var/lib/pgsql/pg_upgrade_dump_*.custom; do x=`pg_restore "$f" |grep "postgis-2.[^4]"` && echo "$f $x"; done'

Thanks for considering,
Justin