Thread: pg_restore
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.
Anyone else noticed or had issues with this?
Sincerely,
Kasia
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
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: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Craig James
Sent: Monday, May 20, 2013 7:45 PM
To: Kasia Tuszynska
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_restore
On Mon, May 20, 2013 at 3:57 PM, Kasia Tuszynska <ktuszynska@esri.com> 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
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:alanmck@avaya.com]
Sent: Tuesday, May 21, 2013 5:05 AM
To: Craig James; Kasia Tuszynska
Cc: pgsql-admin@postgresql.org
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: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Craig James
Sent: Monday, May 20, 2013 7:45 PM
To: Kasia Tuszynska
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_restore
On Mon, May 20, 2013 at 3:57 PM, Kasia Tuszynska <ktuszynska@esri.com> 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