I'm still not buying the theory that the dump/restore method has
something to do with it. You might try the test yourself: run
pg_restore's output into a text file and compare with the text dump.
regards, tom lane
Haven't figured out exactly why yet, but I did find the difference as to
why text based restore works for me but native format doesn't. In the
text based restore permissions are granted to all restored tables and
the schema itself. Using the native format permissions are granted to
restored tables, but NOT the schema, which makes all the tables in the
restored schema inaccessible to all but the postgres user. This is of
course when restoring only the schema and its contents, not the entire
database.
My text based method does a dump to a file from the production database
immediately prior to the restore, then cats the file to psql for the
restore. My native format restore I'm simply restoring from last nights
native format backup of the database and using the schema only switch to
pg_restore. Not sure if that makes the difference or not.
Thanks for the help in trying to figure this out. I'm still looking
into it.
Scot Kreienkamp
