Re: pg_upgrade if 'postgres' database is dropped

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > Robert Haas wrote:
> >> that if you're doing something to the database that someone might
> >> object to, you oughtn't be doing it to the postgres database either.
> >> You should create a database just for pg_upgrade's use and install its
> >> crap in there.
>
> > It installs crap in all databases to set oids on system tables,
>
> It seems like you're both confusing the source and target clusters.
> None of that stuff gets installed in the source, does it?

Right, only in the target.

Let me summarize:

The postgres database is required in the source because pg_upgrade likes
to have a 1-1 database mapping of old and new clusters.  This can be
changed, but it makes pg_upgrade slightly more complex.

The postgres database is required on the target because pg_upgrade
creates the support functions first in that database.  That can be
changed, but pg_dumpall restores roles in the postgres database by
default, not template1.  Again, this can be changed.

Because of this 'postgres' new cluster requirement, you can't just
delete the postgres database from the new cluster and run pg_upgrade.

Tom wants pg_upgrade to work if the old cluster doesn't have a postgres
database.  I see two solutions --- either remove the 1-1 mapping of
old/new databases, or remove the pg_upgrade and pg_dumpall dependence on
the postgres database and tell users to remove postgres from the new
cluster before the upgrade.

They already get a clear error message about the problem, which I think
is why we haven't seen more problem reports.  My guess is they are just
creating the postgres database on the old cluster before the upgrade
after they get the error.

I have applied the attached patch to at least clarify that they need the
postgres database in the old cluster, rather than them trying to remove
the postgres database from the new cluster.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 5b9b4cd..e400814
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** check_old_cluster_has_new_cluster_dbs(vo
*** 403,410 ****
                         new_cluster.dbarr.dbs[new_dbnum].db_name) == 0)
                  break;
          if (old_dbnum == old_cluster.dbarr.ndbs)
!             pg_log(PG_FATAL, "New cluster database \"%s\" does not exist in the old cluster\n",
!                    new_cluster.dbarr.dbs[new_dbnum].db_name);
      }
  }

--- 403,415 ----
                         new_cluster.dbarr.dbs[new_dbnum].db_name) == 0)
                  break;
          if (old_dbnum == old_cluster.dbarr.ndbs)
!         {
!             if (strcmp(new_cluster.dbarr.dbs[new_dbnum].db_name, "postgres") == 0)
!                 pg_log(PG_FATAL, "The \"postgres\" database must exist in the old cluster\n");
!             else
!                 pg_log(PG_FATAL, "New cluster database \"%s\" does not exist in the old cluster\n",
!                        new_cluster.dbarr.dbs[new_dbnum].db_name);
!         }
      }
  }