Thank you both for your responses, We are not running into the #7748 problem. We are running into the following: Pg_restore seems to be performing the restore in a single transaction so, if one object is dependent on another, than the second object restore will fail b/c the creation of the first one has not been committed yet, in our case these two objects are in two different schemas, so if we restore two schemas separately, in the correct order the first object will be created and committed by then.
The thing we have not figured out is why each data owner has to be a superuser. Sincerely, Kasia From: McKinzie, Alan (Alan) [mailto:[email protected]] Sent: Tuesday, May 21, 2013 5:05 AM To: Craig James; Kasia Tuszynska Cc: [email protected] Subject: RE: [ADMIN] pg_restore Does your database restore happen to perform a "Drop owned by" command as part of the restore process? If so, then PostgreSQL Bug #7748 is probably what you are referring to, and this is fixed in the latest patch version of 9.0 (and 9.1 I assume). Alan From: [email protected]<mailto:[email protected]> [mailto:[email protected]] On Behalf Of Craig James Sent: Monday, May 20, 2013 7:45 PM To: Kasia Tuszynska Cc: [email protected]<mailto:[email protected]> Subject: Re: [ADMIN] pg_restore On Mon, May 20, 2013 at 3:57 PM, Kasia Tuszynska <[email protected]<mailto:[email protected]>> wrote: Hi Everybody, Has anyone ran into issues running pg_restore? It seems that between 8.3.8 and 9.0.5, 9.1.3 the behavior of pg_restore has changed. Previously I was able to have several data owners with their own schemas and running a pg_restore as one superuser was able to restore the objects in those schemas without an issue. At 9.0.5, I found that I had to restore each schema of a data owner separately. At 9.1.3, I found that in addition to that I need to make each of those data owners superusers. I am fully aware that the dev work for core replication was occurring at this time, but I have been unable to find any documentation about the potential changes to the basic pg_restore functionality. I recently restored a database using 9.2.1 that has a number of schemas, each with a separate owner, and had no problems at all. Craig Anyone else noticed or had issues with this? Sincerely, Kasia
