[PATCH] Disable bgworkers during servers start in pg_upgrade - Mailing list pgsql-hackers

Hello,

We found an issue in pg_upgrade on a cluster with a third-party
background worker. The upgrade goes fine, but the new cluster is then in
an inconsistent state. The background worker comes from the PoWA
extension but the issue does not appear to related to this particular
code.

Here is a shell script to reproduce the issue (error at the end):

  OLDBINDIR=/usr/lib/postgresql/11/bin
  NEWBINDIR=/usr/lib/postgresql/13/bin
  
  OLDDATADIR=$(mktemp -d)
  NEWDATADIR=$(mktemp -d)
  
  $OLDBINDIR/initdb -D $OLDDATADIR
  echo "unix_socket_directories = '/tmp'" >> $OLDDATADIR/postgresql.auto.conf
  echo "shared_preload_libraries = 'pg_stat_statements, powa'" >> $OLDDATADIR/postgresql.auto.conf
  $OLDBINDIR/pg_ctl -D $OLDDATADIR -l $OLDDATADIR/pgsql.log start
  $OLDBINDIR/createdb -h /tmp powa
  $OLDBINDIR/psql -h /tmp -d powa -c "CREATE EXTENSION powa CASCADE"
  $OLDBINDIR/pg_ctl -D $OLDDATADIR -m fast stop
  
  $NEWBINDIR/initdb -D $NEWDATADIR
  cp $OLDDATADIR/postgresql.auto.conf $NEWDATADIR/postgresql.auto.conf
  
  $NEWBINDIR/pg_upgrade --old-datadir $OLDDATADIR --new-datadir $NEWDATADIR --old-bindir $OLDBINDIR
  
  $NEWBINDIR/pg_ctl -D $NEWDATADIR -l $NEWDATADIR/pgsql.log start
  $NEWBINDIR/psql -h /tmp -d powa -c "SELECT 1 FROM powa_snapshot_metas"
  # ERROR:  MultiXactId 1 has not been created yet -- apparent wraparound

(This needs PoWA to be installed; packages are available on pgdg
repositories as postgresql-<pgversion>-powa on Debian or
powa_<pgversion> on RedHat or directly from source code at
https://github.com/powa-team/powa-archivist).

As far as I currently understand, this is due to the data to be migrated
being somewhat inconsistent (from the perspective of pg_upgrade) when
the old cluster and its background workers get started in pg_upgrade
during the "checks" step. (The old cluster remains sane, still.)

As a solution, it seems that, for similar reasons that we restrict
socket access to prevent accidental connections (from commit
f763b77193), we should also prevent background workers to start at this
step.

Please find attached a patch implementing this.

Thanks for considering,
Denis