Re: pg_upgrade if 'postgres' database is dropped

Robert Haas wrote:
> On Tue, Oct 4, 2011 at 12:11 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> > pg_upgrade doesn't work if the 'postgres' database has been dropped in the
> > old cluster:
> >
> > ~/pgsql.master$ bin/pg_upgrade -b ~/pgsql.91stable/bin -B bin/ -d
> > ~/pgsql.91stable/data -D data-upgraded/
> > Performing Consistency Checks
> > -----------------------------
> > Checking current, bin, and data directories ? ? ? ? ? ? ? ? ok
> > Checking cluster versions ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ok
> > Checking database user is a superuser ? ? ? ? ? ? ? ? ? ? ? ok
> > Checking for prepared transactions ? ? ? ? ? ? ? ? ? ? ? ? ?ok
> > Checking for reg* system OID user data types ? ? ? ? ? ? ? ?ok
> > Checking for contrib/isn with bigint-passing mismatch ? ? ? ok
> > Creating catalog dump ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ok
> > Checking for prepared transactions ? ? ? ? ? ? ? ? ? ? ? ? ?ok
> >
> > New cluster database "postgres" does not exist in the old cluster
> > Failure, exiting
> >
> >
> > That's a bit unfortunate. We have some other tools that don't work without
> > the 'postgres' database, like 'reindexdb -all', but it would still be nice
> > if pg_upgrade did.
>
> +1. I think our usual policy is to try postgres first and then try
> template1, so it would seem logical for pg_upgrade to do the same.

Well, it is a little tricky. The problem is that I am not just
connecting to a database --- I am creating support functions in the
database. Now, this is complex because template1 is the template for
new databases, except for pg_dump which uses template0.

So, it is going to be confusing to support both databases because there
is the cleanup details I have to document if I use template1.

Also, pg_dumpall unconditionally connects to the postgres database to
restore roles:

fprintf(OPF, "\\connect postgres\n\n");

We could connect to template1 for this, but I am not comfortable
changing this. And when pg_dumpall throws an error for a missing
postgres database, pg_upgrade is going to fail.

We started using the postgres database as a database for connections ---
do we want to change that? We certainly can't have the pg_dumpall
output _conditionally_ connecting to template1.

I am feeling this isn't worth pursuing.

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

+ It's impossible for everything to be true. +