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

Sat, 24 Oct 1998 16:32:54 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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, Pennsylvania 19026

Sat, 24 Oct 1998 22:47:47 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_upgrade bug report
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> So, I guess pg_upgrade works?

FWIW, I tried it again after you fixed the -z problem, and it seemed to
work this time.  I haven't beat hard on the resulting database yet...

			regards, tom lane

Tue, 27 Oct 1998 14:53:37 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] pg_upgrade bug report 