Sherrylyn Branchaw <sbranchaw@xxxxxxxxx> 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
