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?

> 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