> 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.
>
Good analysis. I have fixed the awk code to skip the COPY for
pg_shadow. Also added the -z mention in the docs.
So, I guess pg_upgrade works?
-- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026