Re: pg_upgrade if 'postgres' database is dropped

Stephen Frost wrote:
-- Start of PGP signed section.
> * Bruce Momjian (bruce(at)momjian(dot)us) wrote:
> > Well, you would have to remove it _after_ you did the pg_upgrade. Right
> > now if you do a normal dump/restore upgrade, you also have to re-remove
> > the postgres database. We don't have any mechanism to drop a database
> > as part of pg_dumpall's restore if it didn't exist in the old cluster.
>
> Perhaps not, but it *could* be removed after the restore and all would
> be well, yes?

I am not sure how much pg_dump worries about removing system objects
during a restore --- I don't think it does that at all actually. I
thought we did that for plpgsql, but I don't see that in the C code now,
and testing doesn't show it being removed by pg_dump.

> > > Regarding pg_dumpall and pg_restore, I'm pretty sure both of those can
> > > be configured to connect to other databases instead, including for
> > > globals.
> >
> > Well, please show me the code, because the C code I showed you had the
> > '\connect postgres' string hardcoded in there.
>
> I guess there's a difference between "can be used and will work
> correctly, but might create some extra garbage" and "can't be used at
> all". pg_dumpall has a -l option for connecting to whatever *existing*
> database you have to pull the global data, and then it'll restore into a
> clean initdb'd cluster, after which you could remove postgres.

Keep in mind -l might connect to a specified database to do the dump,
but it will still connect to the 'postgres' database to recreate them.

> Admittedly, if you initdb the cluster, drop postgres, and then try a
> restore, it would fail. Personally, I'm not a big fan of that (why

Right, same with pg_upgrade.

> don't we use what was passed in to -l for that..?), but, practically,

No idea.

> it's not that big a deal. I don't know many folks who are going to
> restore a pg_dumpall dump into an existing configuration where they've
> monkied with things (that could cause all kinds of other issues anyway,
> role conflicts, etc).
>
> If I understood correctly (perhaps I didn't..), is that pg_upgrade
> doesn't have the pg_dumpall equivilant of the '-l' or '--database'
> option, and that's what is at issue here.

Well, I can modify pg_upgrade to connect to template1 easily (no need
for a switch) --- the problem is that pg_dumpall requires the 'postgres'
database to restore its dump file.

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

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