RE: PG9.1 migration to PG9.6, dump/restore issues

Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot(dot)Kreienkamp(at)la-z-boy(dot)com
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, September 12, 2018 10:40 AM
> To: Scot Kreienkamp <Scot(dot)Kreienkamp(at)la-z-boy(dot)com>
> Cc: pgsql-general(at)lists(dot)postgresql(dot)org
> Subject: Re: PG9.1 migration to PG9.6, dump/restore issues
>
> Scot Kreienkamp <Scot(dot)Kreienkamp(at)la-z-boy(dot)com> writes:
> > Restore completes successfully, but I noticed that the schema
> > permissions are missing, possibly others as well (Is this a known
> > issue?).
>
> If you're talking about custom modifications you made to the permissions
> of the "public" schema in particular, then yeah, that won't be tracked
> (IIRC, it will be with newer source server versions, but not 9.1).
> Otherwise, no, that's not expected. Would you provide more detail?
>
Yes, it's permissions on the public schema. They were completely empty. I didn't check the other schemas as the very first thing I noticed was the permissions changed on the public schema, but I believe they were empty as well.

> > These are the commands I'm using now:
> > pg_dump -sh $OLDSERVER $DATABASE -f
> $BACKUPPATH/$DATABASE.schema.sql
> > pg_dump -vj 4 -F d -h $OLDSERVER $DATABASE -f
> $BACKUPPATH/DATABASE --no-synchronized-snapshots
> > createdb $DATABASE
> > psql -d $DATABASE -f $BACKUPPATH/$DATABASE.schema.sql
> > pg_restore -evj 4 -d $DATABASE $BACKUPPATH/$DATABASE -a --disable-
> triggers
>
> Also note that this recipe does not copy "global" objects (users and
> tablespaces), nor does it restore any database-level properties.
> You'd need to use pg_dumpall to transfer those things automatically.
> (Possibly "pg_dumpall -g" would be a good starting point here.)
>
> regards, tom lane

I notice the pg_dumpall -g doesn't bring over the schema permissions either. The only way I can get them to come over is pg_dumpall -s, which creates the databases and tables as well. I could drop the databases and create empty ones to do the restore I guess, it would only take a few extra seconds.

This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.