Sherrylyn Branchaw <sbranchaw@gmail.com> writes:
> I'm restoring the schema from one database (prod) to another (dev). There
> are users that exist in prod that don't exist in dev. When the restore job
> tries to grant privileges to nonexistent users in dev, I would like it to
> generate an error, which is safe to ignore, but still correctly grant
> privileges to any user that does exist in dev.
> That's the behavior I see when I dump to a plain file and restore it
> using *psql
> -f*, but not the behavior I see when I do a *pg_dump -Fc* followed by
> *pg_restore.* *pg_restore *seems to treat all the *GRANT* statements for a
> single object as a single statement, and when one errors out, they all
> error out, meaning I'm left with no privileges on the object in question.
Yeah, this is a known issue --- the various GRANTs for a specific object
are stored in a single "TOC entry" in the archive, which pg_restore will
send to the server in a single PQexec call, causing them to be effectively
one transaction. The easiest way to deal with it is to not send
pg_restore's output directly to the target server, but feed it through
psql, something like
pg_restore ... | psql [connection parameters]
There's been some discussion of a real fix, but it seems messy.
pg_restore doesn't have a parser that would be adequate to separate
out multiple SQL commands in a TOC entry, and we'd rather not try
to give it one (mainly because of fear of cross-version compatibility
issues).
regards, tom lane