Re: [HACKERS] pg_upgrade bug report

> 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026