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@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