I wanted to preserve table ownerships and access permissions in my
database, so I tried using pg_upgrade with a script made by
"pg_dumpall -s -z", rather than just "pg_dumpall -s" as suggested
in the man page.
Didn't work. It *would've* worked, except that pg_dumpall tries to
reload the pg_shadow table via "COPY FROM stdin" ... and pg_upgrade very
carefully removes that command from the dump script before executing it.
So the database still has only the postgres superuser as an allowed
user, and psql's attempts to connect as other users fail.
Even if I weren't trying to preserve table ownerships, I would consider
it a bug in pg_upgrade that the contents of pg_shadow are lost.
I think probably the right answer is for pg_upgrade just to execute
the given script, not try to alter it in any way. If you want to defend
against people feeding full dumps rather than -s dumps to pg_upgrade,
may I suggest that we just grep the script to see whether it contains
any copy commands other than the one directed to pg_shadow?
I didn't apply such a fix myself, since someone else might have a better
idea. I did patch pg_upgrade to notice if the psql command fails,
however. Plowing ahead with modifying the data directories after a
script failure is definitely a bad idea. (Good thing I made a regular
dump file before I tried this ;-))
BTW, I'd suggest that once this glitch is fixed, the man page for
pg_upgrade should recommend -s -z not just -s as the standard flags
to pg_dumpall.
regards, tom lane