pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore
25+ messages / 14 participants
[nested] [flat]
* pg_restore
@ 2002-02-06 15:47 Srinivasa Rao Chava <s.chava@wcom.com>
0 siblings, 2 replies; 25+ messages in thread
From: s.chava@wcom.com @ 2002-02-06 15:47 UTC (permalink / raw)
To: pgsql-admin
Hey,
I have upgraded my pgostgresql from 7.1.2 to 7.1.3 as the pg_restore
is not working properly in 7.1.2
I took the database backup with pg_dump.
After upgrading to 7.1.3, I have restored the database with
pg_restore.
It is restoring , but giving the following error with sequences and
recovery is failing
Archiver(db): Could not execute query. Code = 7. Explanation from
backend: 'ERROR: mysequence.setval: you don't have permissions to set
sequence mysequence
Why it is giving the error that the perssion is not there. Actually, I
'm restoring from pgsql super user account.
Can anybody help me how to fix it. Thanks in advance
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2002-02-06 16:23 Manuel Trujillo <manueltrujillo@dorna.es>
parent: Srinivasa Rao Chava <s.chava@wcom.com>
1 sibling, 0 replies; 25+ messages in thread
From: Manuel Trujillo @ 2002-02-06 16:23 UTC (permalink / raw)
To: Srinivasa Rao Chava <s.chava@wcom.com>; +Cc: pgsql-admin
On Wed, 2002-02-06 at 16:47, Srinivasa Rao Chava wrote:
> Hey,
> I have upgraded my pgostgresql from 7.1.2 to 7.1.3 as the pg_restore
> is not working properly in 7.1.2
> I took the database backup with pg_dump.
> After upgrading to 7.1.3, I have restored the database with
> pg_restore.
> It is restoring , but giving the following error with sequences and
> recovery is failing
Hi.
I'm not any guru, but I think if you make the pg_dump/pg_restore with
the -x (whitout acl's), and -O (whitout owner) an, finally, -i
(ignore-version), will possible to make this backup (I think).
Have a nice day ;-)
TooManySecrets
--
Manuel Trujillo manueltrujillo@dorna.es
Technical Engineer http://www.motograndprix.com
Dorna Sports S.L. +34 93 4702864
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2002-02-06 16:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Srinivasa Rao Chava <s.chava@wcom.com>
1 sibling, 1 reply; 25+ messages in thread
From: Tom Lane @ 2002-02-06 16:33 UTC (permalink / raw)
To: s.chava@wcom.com; +Cc: pgsql-admin
s.chava@wcom.com (Srinivasa Rao Chava) writes:
> Archiver(db): Could not execute query. Code = 7. Explanation from
> backend: 'ERROR: mysequence.setval: you don't have permissions to set
> sequence mysequence
Hmm, I think I see an oversight in pg_dump. Exactly what options did
you give to pg_dump and pg_restore?
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2002-02-06 17:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 25+ messages in thread
From: Tom Lane @ 2002-02-06 17:34 UTC (permalink / raw)
To: Srinivasa R Chava <s.chava@wcom.com>; +Cc: pgsql-admin
Srinivasa R Chava <s.chava@wcom.com> writes:
> [ permissions error when restoring a sequence ]
I've applied the attached patch to 7.2 sources to fix this problem.
You could probably adapt it to 7.1.3 pg_dump, but I haven't looked.
regards, tom lane
inline patch 1: src/bin/pg_dump/pg_backup_archiver.c, src/bin/pg_dump/pg_dump.c (+5/-4 lines) download inline-1.patch
*** src/bin/pg_dump/pg_backup_archiver.c.orig Fri Jan 18 14:18:18 2002
--- src/bin/pg_dump/pg_backup_archiver.c Wed Feb 6 12:27:50 2002
***************
*** 311,319 ****
if ((reqs & REQ_SCHEMA) != 0) /* We want the schema */
{
- /* Reconnect if necessary */
- _reconnectAsOwner(AH, NULL, te);
-
ahlog(AH, 1, "creating %s %s\n", te->desc, te->name);
_printTocEntry(AH, te, ropt, false);
defnDumped = true;
--- 311,316 ----
***************
*** 2032,2037 ****
--- 2029,2037 ----
_printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isData)
{
char *pfx;
+
+ /* Reconnect if necessary */
+ _reconnectAsOwner(AH, NULL, te);
if (isData)
pfx = "Data for ";
*** src/bin/pg_dump/pg_dump.c.orig Fri Jan 25 13:49:31 2002
--- src/bin/pg_dump/pg_dump.c Wed Feb 6 12:27:50 2002
***************
*** 4765,4771 ****
last, (called ? "true" : "false"));
ArchiveEntry(fout, tbinfo.oid, tbinfo.relname, "SEQUENCE SET", NULL,
! query->data, "" /* Del */ , "", "", NULL, NULL);
}
if (!dataOnly)
--- 4765,4772 ----
last, (called ? "true" : "false"));
ArchiveEntry(fout, tbinfo.oid, tbinfo.relname, "SEQUENCE SET", NULL,
! query->data, "" /* Del */ , "", tbinfo.usename,
! NULL, NULL);
}
if (!dataOnly)
* pg_restore
@ 2002-03-27 16:58 Jodi Kanter <jkanter@virginia.edu>
0 siblings, 0 replies; 25+ messages in thread
From: Jodi Kanter @ 2002-03-27 16:58 UTC (permalink / raw)
To: pgsql-admin
I have an empty database that we recreated from scratch in order to make some column changes. I would like to restore the data that was in the original database. Will it work given that their are some new fields that did not exist before? Am I correct in that I cannot do this from pg_restore since it is a plain text file?
The data dump was created using the following syntax:
pg_dump genex -u -a > ./genex_data.backup (I have to use the -u switch since I have implemented forced sign on through the pg_passwd utility)
How can I get this data back in? pg_restore? psql command? and if so what is the appropriate syntax becasue I have tried this and its not working.
thanks
Jodi Kanter
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
^ permalink raw reply [nested|flat] 25+ messages in thread
* pg_restore
@ 2005-08-11 19:29 D Kavan <bitsandbytes88@hotmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: D Kavan @ 2005-08-11 19:29 UTC (permalink / raw)
To: pgsql-admin
Hi all,
We are running postgres 8.03.
Getting a dump is no problem on our 6 GB database. However, the restore is
a killer, it takes 10 minutes for the dump pg_dump, but over 5 hours for a
restore. Is there a way to 'turn off transactions' on the restore to speed
things up? I've tried different restore commands, but I don't see anything
on transactions logging. What else could speed it up? oracle and sybase
can restore the same db in just 90 minutes. Does postgres just not know
how to take advantage of the resources unless different users are involved?
In other words to speed it up, should I script different users to each
restore some of the tables to a database and then after all the tables have
restored, restore keys and then indexes? Thanks for any help.
~DjK
psql dbname < dump_file
pg_restore -d test_restore_2 -Fc niehs_dump.backup
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2005-08-11 20:46 Bruno Wolff III <bruno@wolff.to>
parent: D Kavan <bitsandbytes88@hotmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Bruno Wolff III @ 2005-08-11 20:46 UTC (permalink / raw)
To: D Kavan <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin
On Thu, Aug 11, 2005 at 15:29:22 -0400,
D Kavan <bitsandbytes88@hotmail.com> wrote:
> Hi all,
>
> We are running postgres 8.03.
>
> Getting a dump is no problem on our 6 GB database. However, the restore is
> a killer, it takes 10 minutes for the dump pg_dump, but over 5 hours for a
> restore. Is there a way to 'turn off transactions' on the restore to speed
> things up? I've tried different restore commands, but I don't see
> anything on transactions logging. What else could speed it up? oracle
> and sybase can restore the same db in just 90 minutes. Does postgres
> just not know how to take advantage of the resources unless different users
> are involved? In other words to speed it up, should I script different
> users to each restore some of the tables to a database and then after all
> the tables have restored, restore keys and then indexes? Thanks for any
> help.
>
> ~DjK
>
> psql dbname < dump_file
> pg_restore -d test_restore_2 -Fc niehs_dump.backup
Some things you can do are to turn off fsync for the restore and not
restore foreign key contsraints and indexes until after the database
has been loaded.
This topic has been discussed before and you might find some more specific
information in the archives.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2005-08-11 21:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2005-08-11 21:56 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: D Kavan <bitsandbytes88@hotmail.com>; pgsql-admin
Bruno Wolff III <bruno@wolff.to> writes:
>> psql dbname < dump_file
>> pg_restore -d test_restore_2 -Fc niehs_dump.backup
> Some things you can do are to turn off fsync for the restore and not
> restore foreign key contsraints and indexes until after the database
> has been loaded.
pg_dump/pg_restore should get the ordering considerations right already.
fsync off might help some but I'm not convinced of it. I'd look at the
value of maintenance_work_mem --- increasing that to maybe a few hundred
Mb would help the speed of index builds and foreign key checks.
But really what you ought to do first is find out where it's spending
its time. Can you run the restore with query logging and duration
logging enabled?
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2005-08-12 16:30 D Kavan <bitsandbytes88@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 25+ messages in thread
From: D Kavan @ 2005-08-12 16:30 UTC (permalink / raw)
To: pgsql-admin
Thanks!
We increased work memory from 16 MB to 32 MB and maintenance work memory
from 256 MB to 768 MB. That made a huge difference. We did set true the
log.statement and duration, but where is the file those are logging too?
That did the trick. 30 minutes only for a restrore instead of 5 + hours.
I did notice that the size is 4 GB instead of 5.6 GB. Is something else
going on?
We tried a pg_restore with -Fc once and only got 4 GB, but the next time
with a standard pg_dump command and psql newdb < dumpfile command, we also
had 4 GB.
~DjK
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2005-08-12 18:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: D Kavan <bitsandbytes88@hotmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2005-08-12 18:55 UTC (permalink / raw)
To: D Kavan <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin
"D Kavan" <bitsandbytes88@hotmail.com> writes:
> That did the trick. 30 minutes only for a restrore instead of 5 + hours.
Good.
> I did notice that the size is 4 GB instead of 5.6 GB. Is something else
> going on?
Kinda sounds like you weren't vacuuming often enough in the old
installation, leading to table or index bloat ...
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2005-08-12 20:41 D Kavan <bitsandbytes88@hotmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 25+ messages in thread
From: D Kavan @ 2005-08-12 20:41 UTC (permalink / raw)
To: pgsql-admin
I do a full vacuum and analyze every night, but I'm getting this error.
chived transaction log file "000000010000001200000091"
< % %2005-08-12 06:13:49 EDT>LOG: archived transaction log file
"000000010000001200000092"
< % %2005-08-12 06:13:57 EDT>LOG: archived transaction log file
"000000010000001200000093"
< % %2005-08-12 06:17:51 EDT>LOG: checkpoints are occurring too frequently
(16 seconds apart)
< % %2005-08-12 06:17:51 EDT>HINT: Consider increasing the configuration
parameter "checkpoint_segments".
<postgres %niehs_dev %2005-08-12 06:17:52 EDT>LOG: duration: 2426771.793 ms
statement: VACUUM FULL;
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2005-08-12 21:50 Joshua D. Drake <jd@commandprompt.com>
parent: D Kavan <bitsandbytes88@hotmail.com>
0 siblings, 0 replies; 25+ messages in thread
From: Joshua D. Drake @ 2005-08-12 21:50 UTC (permalink / raw)
To: D Kavan <bitsandbytes88@hotmail.com>; +Cc: pgsql-admin
D Kavan wrote:
> I do a full vacuum and analyze every night, but I'm getting this error.
Those aren't errors. They are letting you know that you need to increase
your checkpoint_segments.
>
> chived transaction log file "000000010000001200000091"
> < % %2005-08-12 06:13:49 EDT>LOG: archived transaction log file
> "000000010000001200000092"
> < % %2005-08-12 06:13:57 EDT>LOG: archived transaction log file
> "000000010000001200000093"
> < % %2005-08-12 06:17:51 EDT>LOG: checkpoints are occurring too
> frequently (16 seconds apart)
> < % %2005-08-12 06:17:51 EDT>HINT: Consider increasing the
> configuration parameter "checkpoint_segments".
> <postgres %niehs_dev %2005-08-12 06:17:52 EDT>LOG: duration:
> 2426771.793 ms statement: VACUUM FULL;
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 25+ messages in thread
* pg_restore
@ 2006-06-16 02:37 Infor Gates <info_gates@yahoo.com>
0 siblings, 1 reply; 25+ messages in thread
From: Infor Gates @ 2006-06-16 02:37 UTC (permalink / raw)
To: pgsql-admin
Dear list
I hope you could help me understand the error message below:
While it looks straight forward, I could NOT find the duplicate key violation,
even though check through the said table (transaction by transaction). So,
I am wondering if the message would means something else.
I had tried re-indexing (force) and even modifying the primary key (manually) but
have not found any new clues.
I am using Postgresql 8.0.7 (Win32 binary version) and pgAdmin III for backup and restore.
Thank you.
CY
Error message:
--------------------
pg_restore: restoring data for table "bf_receipt"
pg_restore: ERROR: duplicate key violates unique constraint "bf_receipt_pkey"
CONTEXT: COPY bf_receipt, line 1: "2006/03/14 15:32:46.239 7 SUBANGJAYA 2005-10-15 evonne ..."
pg_restore: [archiver (db)] error returned by PQendcopy: ERROR: duplicate key violates unique constraint "bf_receipt_pkey"
CONTEXT: COPY bf_receipt, line 1: "2006/03/14 15:32:46.239 7 SUBANGJAYA 2005-10-15 evonne ..."
pg_restore: *** aborted because of error
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2006-06-17 16:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Infor Gates <info_gates@yahoo.com>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2006-06-17 16:38 UTC (permalink / raw)
To: Infor Gates <info_gates@yahoo.com>; +Cc: pgsql-admin
Infor Gates <info_gates@yahoo.com> writes:
> I hope you could help me understand the error message below:
> While it looks straight forward, I could NOT find the duplicate key violation,
Considering that it's complaining at the very first line of COPY data,
I wonder whether you aren't restoring into a table that already contains
the data you want to load. pg_restore doesn't (by default) try to
delete data that's already present.
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2006-06-18 02:55 Infor Gates <info_gates@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 25+ messages in thread
From: Infor Gates @ 2006-06-18 02:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Dear Tom
Thanks for your email.
You are right in saying table is already populated with the data. I was
testing the pg_dump and pg_restore using pg_Admin III.
I am having the impression that pg_restore would over-rides the "old"
data with the current one. Is my thinking wrong? I have checked the
documentation but it was silence on this. Does it means that whenever
I performed a pg_restore, I must first drop the "old" database.
I would be glad if you can help to clarify my understanding on
pg_dump and pg_restore functions.
Thank you.
CY
Tom Lane <tgl@sss.pgh.pa.us> wrote: Infor Gates writes:
> I hope you could help me understand the error message below:
> While it looks straight forward, I could NOT find the duplicate key violation,
Considering that it's complaining at the very first line of COPY data,
I wonder whether you aren't restoring into a table that already contains
the data you want to load. pg_restore doesn't (by default) try to
delete data that's already present.
regards, tom lane
---------------------------------
Yahoo! Groups gets better. Check out the new email design. Plus there’s much more to come.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2006-06-18 05:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Infor Gates <info_gates@yahoo.com>
0 siblings, 1 reply; 25+ messages in thread
From: Tom Lane @ 2006-06-18 05:06 UTC (permalink / raw)
To: Infor Gates <info_gates@yahoo.com>; +Cc: pgsql-admin
Infor Gates <info_gates@yahoo.com> writes:
> I am having the impression that pg_restore would over-rides the "old"
> data with the current one. Is my thinking wrong?
Yeah. By default, pg_restore will issue a CREATE TABLE (which of course
fails if the table already exists) followed by COPY (which just tries to
insert data in addition to what might be there already).
There's a command line option to ask pg_restore to try to DROP TABLE
before doing the CREATE TABLE. By and large, though, that's a bad way
to proceed unless you are specifically trying to merge two databases.
The fast and easy way to proceed is to DROP DATABASE, CREATE a fresh
empty database, and pg_restore into that.
regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2006-06-18 21:29 Aaron Bono <postgresql@aranya.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 25+ messages in thread
From: Aaron Bono @ 2006-06-18 21:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Infor Gates <info_gates@yahoo.com>; pgsql-admin
I agree. When restoring a database from back up, I do a drop database and
recreate it to make sure everything is properly intact (tables, columns,
views, triggers, foreign keys, etc...). We do this a lot for testing. We
backup the production database, copy to test server and do a restore on the
test database. It helps us test our code rollouts.
-Aaron
On 6/18/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Infor Gates <info_gates@yahoo.com> writes:
> > I am having the impression that pg_restore would over-rides the "old"
> > data with the current one. Is my thinking wrong?
>
> Yeah. By default, pg_restore will issue a CREATE TABLE (which of course
> fails if the table already exists) followed by COPY (which just tries to
> insert data in addition to what might be there already).
>
> There's a command line option to ask pg_restore to try to DROP TABLE
> before doing the CREATE TABLE. By and large, though, that's a bad way
> to proceed unless you are specifically trying to merge two databases.
> The fast and easy way to proceed is to DROP DATABASE, CREATE a fresh
> empty database, and pg_restore into that.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2006-06-19 01:02 Infor Gates <info_gates@yahoo.com>
parent: Aaron Bono <postgresql@aranya.com>
0 siblings, 0 replies; 25+ messages in thread
From: Infor Gates @ 2006-06-19 01:02 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Infor Gates <info_gates@yahoo.com>; pgsql-admin
Thanks Aaron
I am trying to do exactly the same thing. Now that my understanding of pg_restore is clearer. I was worried by my index key table was corrupted somehow and caused the error message. This is the cause by my own misunderstanding.
CY
Aaron Bono <postgresql@aranya.com> wrote: I agree. When restoring a database from back up, I do a drop database and recreate it to make sure everything is properly intact (tables, columns, views, triggers, foreign keys, etc...). We do this a lot for testing. We backup the production database, copy to test server and do a restore on the test database. It helps us test our code rollouts.
-Aaron
On 6/18/06, Tom Lane <tgl@sss.pgh.pa.us> wrote: Infor Gates <info_gates@yahoo.com> writes:
> I am having the impression that pg_restore would over-rides the "old"
> data with the current one. Is my thinking wrong?
Yeah. By default, pg_restore will issue a CREATE TABLE (which of course
fails if the table already exists) followed by COPY (which just tries to
insert data in addition to what might be there already).
There's a command line option to ask pg_restore to try to DROP TABLE
before doing the CREATE TABLE. By and large, though, that's a bad way
to proceed unless you are specifically trying to merge two databases.
The fast and easy way to proceed is to DROP DATABASE, CREATE a fresh
empty database, and pg_restore into that.
regards, tom lane
---------------------------------
Do you Yahoo!?
Next-gen email? Have it all with the all-new Yahoo! Mail Beta.
^ permalink raw reply [nested|flat] 25+ messages in thread
* pg_restore
@ 2013-05-20 22:57 Kasia Tuszynska <ktuszynska@esri.com>
0 siblings, 1 reply; 25+ messages in thread
From: Kasia Tuszynska @ 2013-05-20 22:57 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2013-05-21 00:44 Craig James <cjames@emolecules.com>
parent: Kasia Tuszynska <ktuszynska@esri.com>
0 siblings, 1 reply; 25+ messages in thread
From: Craig James @ 2013-05-21 00:44 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
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 ****
>
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2013-05-21 12:04 McKinzie, Alan (Alan) <alanmck@avaya.com>
parent: Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 25+ messages in thread
From: McKinzie, Alan (Alan) @ 2013-05-21 12:04 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
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<mailto: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
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2013-05-21 23:07 Kasia Tuszynska <ktuszynska@esri.com>
parent: McKinzie, Alan (Alan) <alanmck@avaya.com>
0 siblings, 0 replies; 25+ messages in thread
From: Kasia Tuszynska @ 2013-05-21 23:07 UTC (permalink / raw)
To: McKinzie, Alan (Alan) <alanmck@avaya.com>; Craig James <cjames@emolecules.com>; +Cc: pgsql-admin
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> [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<mailto:pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] pg_restore
On Mon, May 20, 2013 at 3:57 PM, Kasia Tuszynska <ktuszynska@esri.com<mailto: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
^ permalink raw reply [nested|flat] 25+ messages in thread
* pg_restore
@ 2019-04-11 22:09 rammohan ganapavarapu <rammohanganap@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: rammohan ganapavarapu @ 2019-04-11 22:09 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a custome database called mudb along with default databases in my
postgresql cluster, i am trying to restore mydb from a custom format
pg_dump file, what is the procedure to do it?
psql -t -h localhost -U ${RUN_USER} -c "SELECT
pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE
pg_stat_activity.datname = 'mydb' AND pid <> pg_backend_pid();"
pg_restore" -h localhost -U ${RUN_USER} -d mydb -j "${JOBS}" -x -O -C -c
--if-exist -v pgdump.dump
But i am getting below error:
pg_restore: connecting to database for restore
pg_restore: dropping DATABASE mydb
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4312; 1262 16384 DATABASE
mydb mydb
pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop
the currently open database
Command was: DROP DATABASE IF EXISTS mydb;
So what is the best way to restore? I am using 9.6 version.
Thanks,
Ram
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2019-04-11 23:12 Ron <ronljohnsonjr@gmail.com>
parent: rammohan ganapavarapu <rammohanganap@gmail.com>
0 siblings, 1 reply; 25+ messages in thread
From: Ron @ 2019-04-11 23:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 4/11/19 5:09 PM, rammohan ganapavarapu wrote:
> Hi,
>
> I have a custome database called mudb along with default databases in my
> postgresql cluster, i am trying to restore mydb from a custom format
> pg_dump file, what is the procedure to do it?
>
> psql -t -h localhost -U ${RUN_USER} -c "SELECT
> pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE
> pg_stat_activity.datname = 'mydb' AND pid <> pg_backend_pid();"
>
> pg_restore" -h localhost -U ${RUN_USER} -d mydb -j "${JOBS}" -x -O -C -c
> --if-exist -v pgdump.dump
>
> But i am getting below error:
>
> pg_restore: connecting to database for restore
> pg_restore: dropping DATABASE mydb
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 4312; 1262 16384 DATABASE
> mydb mydb
> pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop
> the currently open database
> Command was: DROP DATABASE IF EXISTS mydb;
>
> So what is the best way to restore? I am using 9.6 version.
I made the same mistake you did, by doing "-d mydb". You need to specify
the postgres database.
This works for me:
export PGUSER=${RUN_USER}
export PGHOST=localhost
DB=mydb
pg_restore -xOvcC --if-exists --jobs=${JOBS} *-d postgres*${DB} 2>
${DB}_restore.log
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 25+ messages in thread
* Re: pg_restore
@ 2019-04-11 23:16 Ron <ronljohnsonjr@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 25+ messages in thread
From: Ron @ 2019-04-11 23:16 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 4/11/19 6:12 PM, Ron wrote:
> On 4/11/19 5:09 PM, rammohan ganapavarapu wrote:
>> Hi,
>>
>> I have a custome database called mudb along with default databases in my
>> postgresql cluster, i am trying to restore mydb from a custom format
>> pg_dump file, what is the procedure to do it?
>>
>> psql -t -h localhost -U ${RUN_USER} -c "SELECT
>> pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE
>> pg_stat_activity.datname = 'mydb' AND pid <> pg_backend_pid();"
>>
>> pg_restore" -h localhost -U ${RUN_USER} -d mydb -j "${JOBS}" -x -O -C
>> -c --if-exist -v pgdump.dump
>>
>> But i am getting below error:
>>
>> pg_restore: connecting to database for restore
>> pg_restore: dropping DATABASE mydb
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> pg_restore: [archiver (db)] Error from TOC entry 4312; 1262 16384
>> DATABASE mydb mydb
>> pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop
>> the currently open database
>> Command was: DROP DATABASE IF EXISTS mydb;
>>
>> So what is the best way to restore? I am using 9.6 version.
>
> I made the same mistake you did, by doing "-d mydb". You need to specify
> the postgres database.
>
> This works for me:
> export PGUSER=${RUN_USER}
> export PGHOST=localhost
> DB=mydb
> pg_restore -xOvcC --if-exists --jobs=${JOBS} *-d postgres*${DB} 2>
> ${DB}_restore.log
But it just occurred to me that I used directory backups, not custom
backups, so this probably won't work for you.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 25+ messages in thread
end of thread, other threads:[~2019-04-11 23:16 UTC | newest]
Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-06 15:47 pg_restore Srinivasa Rao Chava <s.chava@wcom.com>
2002-02-06 16:23 ` Manuel Trujillo <manueltrujillo@dorna.es>
2002-02-06 16:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-02-06 17:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-03-27 16:58 pg_restore Jodi Kanter <jkanter@virginia.edu>
2005-08-11 19:29 pg_restore D Kavan <bitsandbytes88@hotmail.com>
2005-08-11 20:46 ` Bruno Wolff III <bruno@wolff.to>
2005-08-11 21:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-12 16:30 ` D Kavan <bitsandbytes88@hotmail.com>
2005-08-12 18:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-12 20:41 ` D Kavan <bitsandbytes88@hotmail.com>
2005-08-12 21:50 ` Joshua D. Drake <jd@commandprompt.com>
2006-06-16 02:37 pg_restore Infor Gates <info_gates@yahoo.com>
2006-06-17 16:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-18 02:55 ` Infor Gates <info_gates@yahoo.com>
2006-06-18 05:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-06-18 21:29 ` Aaron Bono <postgresql@aranya.com>
2006-06-19 01:02 ` Infor Gates <info_gates@yahoo.com>
2013-05-20 22:57 pg_restore Kasia Tuszynska <ktuszynska@esri.com>
2013-05-21 00:44 ` Craig James <cjames@emolecules.com>
2013-05-21 12:04 ` McKinzie, Alan (Alan) <alanmck@avaya.com>
2013-05-21 23:07 ` Kasia Tuszynska <ktuszynska@esri.com>
2019-04-11 22:09 pg_restore rammohan ganapavarapu <rammohanganap@gmail.com>
2019-04-11 23:12 ` Ron <ronljohnsonjr@gmail.com>
2019-04-11 23:16 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox