The above needs more information: 1) Are the dev_* databases on a different cluster?
2) If so did you run: CREATE ROLE prod_user; CREATE ROLE prod_and_dev_user; on that cluster first? I happened to put them all on the same cluster for my test case, in order to reproduce the unexpected behavior I encountered in the wild, where the prod and dev dbs happened to live on different clusters. In short, as long as you make sure the *prod_user* exists on the source cluster at the time when the dump is taken, and doesn't exist on the target cluster when the restore is carried out, you get the behavior I saw. Also if so: In the restores below are you sure you are pointed at the same cluster in each case? Yes, I am sure. Both for the test case I was creating for the mailing list, and for the script where I first encountered this in the wild. Worked like a charm when I used *psql*, didn't do what I expected when I used *pg_restore*. What do you see if you do: pg_restore -f prod_dump_restore.sql prod_dump.bin and look in prod_dump_res?tore.sql? This is exactly what I did when I was first trying to figure out what was going on. I see GRANT USAGE ON SCHEMA test TO prod_and_dev_user; GRANT ALL ON SCHEMA test TO prod_user; If I then use *psql* to load *prod_dump_restore.sql* to a cluster that doesn't have the *prod_user *role, I get the expected behavior ( *prod_and_dev_user* has usage on the schema *test*), because *psql* treats each of those statements as a separate command. *pg_restore* seems to treat them as a single command, judging by the error message and the behavior. Best, Sherrylyn
