pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore error
26+ messages / 14 participants
[nested] [flat]
* pg_restore error
@ 2002-09-03 22:13 Laurette Cisneros <laurette@nextbus.com>
0 siblings, 1 reply; 26+ messages in thread
From: Laurette Cisneros @ 2002-09-03 22:13 UTC (permalink / raw)
To: pgsql-admin
Hi all,
When doing a pg_restore from a pg_dump of a database, I am seeing the
following error:
pg_restore: [archiver (db)] could not execute query: ERROR: stat failed on
file '/usr/local/pgsql-7.2/lib/plpgsql': No such file or directory
I am currently on 7.2.1 and the software location has changed (to
/usr/local/pgsql). And, further research shows that the pg_proc shows:
plpgsql_call_handler | 1 | 13 | f | t | f
| f | 0 | f | 0 | |
100 |
0 | 0 | 100 | plpgsql_call_handler |
/usr/local/pgsql-7.2/lib/plpgsql
This shows /usr/local/pgsql-7.2/lib/plpgsql for the "probin" column.
How should an update of software be handled as per pg_proc? I.E. should
all the locations recorded in pg_proc be updated to use the new location of
the pgsql software? Is there an update script that will do this already?
Are there other parts that will need updating due to the change in the
software (directory)?
Thanks,
--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2002-09-03 22:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurette Cisneros <laurette@nextbus.com>
0 siblings, 2 replies; 26+ messages in thread
From: Tom Lane @ 2002-09-03 22:27 UTC (permalink / raw)
To: Laurette Cisneros <laurette@nextbus.com>; +Cc: pgsql-admin
Laurette Cisneros <laurette@nextbus.com> writes:
> I am currently on 7.2.1 and the software location has changed (to
> /usr/local/pgsql). And, further research shows that the pg_proc shows:
> This shows /usr/local/pgsql-7.2/lib/plpgsql for the "probin" column.
> How should an update of software be handled as per pg_proc?
The preferred contents of probin are now like
$libdir/plpgsql
(the dollar sign is literal text here). As of 7.2 the server will
substitute a suitable directory name for "$libdir". You probably
have a database that was reloaded from an old dump without taking
advantage of this feature --- before 7.2 you could only use an
absolute path in probin. Now, absolute paths are deprecated for exactly
the reason that they make version updates hard.
Note the lack of a ".so" in that probin entry, too. This is also
a recommended new practice in 7.2 --- no reason to put
platform-dependent assumptions about shared library extensions into
your dumps.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2002-09-03 22:33 Laurette Cisneros <laurette@nextbus.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 26+ messages in thread
From: Laurette Cisneros @ 2002-09-03 22:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Ah. Makes sense. Since this is but our "restore" system, I will re-do the
server from scratch to ensure the right probin entries.
Thanks for the help and info.
L.
On Tue, 3 Sep 2002, Tom Lane wrote:
> Laurette Cisneros <laurette@nextbus.com> writes:
> > I am currently on 7.2.1 and the software location has changed (to
> > /usr/local/pgsql). And, further research shows that the pg_proc shows:
> > This shows /usr/local/pgsql-7.2/lib/plpgsql for the "probin" column.
>
> > How should an update of software be handled as per pg_proc?
>
> The preferred contents of probin are now like
> $libdir/plpgsql
> (the dollar sign is literal text here). As of 7.2 the server will
> substitute a suitable directory name for "$libdir". You probably
> have a database that was reloaded from an old dump without taking
> advantage of this feature --- before 7.2 you could only use an
> absolute path in probin. Now, absolute paths are deprecated for exactly
> the reason that they make version updates hard.
>
> Note the lack of a ".so" in that probin entry, too. This is also
> a recommended new practice in 7.2 --- no reason to put
> platform-dependent assumptions about shared library extensions into
> your dumps.
>
> regards, tom lane
>
--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2002-09-04 20:15 Laurette Cisneros <laurette@nextbus.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Laurette Cisneros @ 2002-09-04 20:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
A followup question on this...
What about user create functions (i.e. create function...as '<path>')
can the path have $libdir/ on it as well?
Thanks,
L.
On Tue, 3 Sep 2002, Tom Lane wrote:
> Laurette Cisneros <laurette@nextbus.com> writes:
> > I am currently on 7.2.1 and the software location has changed (to
> > /usr/local/pgsql). And, further research shows that the pg_proc shows:
> > This shows /usr/local/pgsql-7.2/lib/plpgsql for the "probin" column.
>
> > How should an update of software be handled as per pg_proc?
>
> The preferred contents of probin are now like
> $libdir/plpgsql
> (the dollar sign is literal text here). As of 7.2 the server will
> substitute a suitable directory name for "$libdir". You probably
> have a database that was reloaded from an old dump without taking
> advantage of this feature --- before 7.2 you could only use an
> absolute path in probin. Now, absolute paths are deprecated for exactly
> the reason that they make version updates hard.
>
> Note the lack of a ".so" in that probin entry, too. This is also
> a recommended new practice in 7.2 --- no reason to put
> platform-dependent assumptions about shared library extensions into
> your dumps.
>
> regards, tom lane
>
--
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
----------------------------------
A wiki we will go...
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2002-09-04 21:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurette Cisneros <laurette@nextbus.com>
0 siblings, 0 replies; 26+ messages in thread
From: Tom Lane @ 2002-09-04 21:41 UTC (permalink / raw)
To: Laurette Cisneros <laurette@nextbus.com>; +Cc: pgsql-admin
Laurette Cisneros <laurette@nextbus.com> writes:
> What about user create functions (i.e. create function...as '<path>')
> can the path have $libdir/ on it as well?
Sure, if you put them where $libdir points to...
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* pg_restore error
@ 2004-10-28 15:48 Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 0 replies; 26+ messages in thread
From: Düster Horst @ 2004-10-28 15:48 UTC (permalink / raw)
To: pgsql-admin
Hallo
I dumped a database with large objects with the command pg_dump -Fc -b
dbname > dbname.dmp
To restore the DB I use pg_restore -d newdb dbname.dmp.
The restore process breakes with the following error message:
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"tablename" does not exist.
I'm a little bit confused about that, because I thought that this error
could not happen, due to pg_restore seems to generate the items in an
optimal order. Isn't it??
I appreciate for any hints.
With best regards.
Dr. Horst Düster
GIS-Koordinator
Kanton Solothurn
Amt für Geoinformation
Abteilung SO!GIS Koordination
Werkhofstr. 65
CH-4509 Solothurn
Tel.: ++41 (0)32 627 25 32
Fax: ++41 (0)32 627 22 14
horst.duester@bd.so.ch
www.sogis.so.ch
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2004-10-29 10:32 Vishal Kashyap @ [Sai Hertz And Control Systems] <vishalonlist@gmail.com>
parent: Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 0 replies; 26+ messages in thread
From: Vishal Kashyap @ [Sai Hertz And Control Systems] @ 2004-10-29 10:32 UTC (permalink / raw)
To: Düster Horst <horst.duester@bd.so.ch>; +Cc: pgsql-admin
Hi
> I thought, that pg_restore will organize the restore-prozess in an optimal
> order. This seems not work correct!!?? I would be grateful to hear any
> hints.
pg_restore does not priortize till now idealy it must be like
restoring the functions >> tables >> views >> seqences so on...
This is in the todo
http://developer.postgresql.org/todo.php
but for mean while you would have to use pg_restore to restore the
tables one by one and then let other thing go on.
moreover if your database is not to large its best to make a text file
of the dump and run it through psql
--
With Best Regards,
Vishal Kashyap.
Did you know SaiPACS is one and only PACS
Management tool.
http://saihertz.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* pg_restore error
@ 2004-10-29 11:12 Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 1 reply; 26+ messages in thread
From: Düster Horst @ 2004-10-29 11:12 UTC (permalink / raw)
To: pgsql-admin
Hallo
I try to restore a large DB with BLOBS. I created the dump file with:
pg_dump -Fc -b <dbname> > dump.file
During the restore process startet with the following command:
pg_restore -d <dbname> dump.file
the process breakes with the error message that a table couldn't be found.
So I started the pg_restore in verbose mode. This mode offers the problem.
pg_restore tries to create a view but a referenced table does not exists. I
thought, that pg_restore will organize the restore-prozess in an optimal
order. This seems not work correct!!?? I would be grateful to hear any
hints.
My system:
Postgresql 7.4.5
SuSe 9.1 Linux
With best regards
Horst Düster
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2004-10-29 11:38 Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 1 reply; 26+ messages in thread
From: Düster Horst @ 2004-10-29 11:38 UTC (permalink / raw)
To: pgsql-admin
Vishal
Thank you for your answer. My problem is, I have to restore BLOBS. The
documentation says, it's only possible to restore blobs with pg_restore in
one part. The way you suppose does not work with BLOBS because the
restauration of single objects will not restore BOLBS.
Horst Düster
-----Ursprüngliche Nachricht-----
Von: Vishal Kashyap @ [Sai Hertz And Control Systems]
[mailto:vishalonlist@gmail.com]
Gesendet am: Freitag, 29. Oktober 2004 11:33
An: Düster Horst
Cc: pgsql-admin@postgresql.org
Betreff: Re: [ADMIN] pg_restore error
Hi
> I thought, that pg_restore will organize the restore-prozess in an optimal
> order. This seems not work correct!!?? I would be grateful to hear any
> hints.
pg_restore does not priortize till now idealy it must be like
restoring the functions >> tables >> views >> seqences so on...
This is in the todo
http://developer.postgresql.org/todo.php
but for mean while you would have to use pg_restore to restore the
tables one by one and then let other thing go on.
moreover if your database is not to large its best to make a text file
of the dump and run it through psql
--
With Best Regards,
Vishal Kashyap.
Did you know SaiPACS is one and only PACS
Management tool.
http://saihertz.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2004-10-29 12:14 Janko Richter <jankorichter@yahoo.de>
parent: Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 0 replies; 26+ messages in thread
From: Janko Richter @ 2004-10-29 12:14 UTC (permalink / raw)
To: Düster Horst <Horst.Duester@bd.so.ch>; pgsql-admin
Hi,
Try "pg_restore -l" to generate a TOC.
You may rearrange the TOC file.
Then use "pg_restore -L".
Hope it helps,
Janko Richter
Düster Horst wrote:
> Vishal
>
> Thank you for your answer. My problem is, I have to restore BLOBS. The
> documentation says, it's only possible to restore blobs with pg_restore in
> one part. The way you suppose does not work with BLOBS because the
> restauration of single objects will not restore BOLBS.
>
> Horst Düster
>
> -----Ursprüngliche Nachricht-----
> Von: Vishal Kashyap @ [Sai Hertz And Control Systems]
> [mailto:vishalonlist@gmail.com]
> Gesendet am: Freitag, 29. Oktober 2004 11:33
> An: Düster Horst
> Cc: pgsql-admin@postgresql.org
> Betreff: Re: [ADMIN] pg_restore error
>
> Hi
>
>
>>I thought, that pg_restore will organize the restore-prozess in an optimal
>>order. This seems not work correct!!?? I would be grateful to hear any
>>hints.
>
>
>
> pg_restore does not priortize till now idealy it must be like
> restoring the functions >> tables >> views >> seqences so on...
>
> This is in the todo
>
> http://developer.postgresql.org/todo.php
>
> but for mean while you would have to use pg_restore to restore the
> tables one by one and then let other thing go on.
>
> moreover if your database is not to large its best to make a text file
> of the dump and run it through psql
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2004-10-29 13:51 Düster Horst <Horst.Duester@bd.so.ch>
0 siblings, 0 replies; 26+ messages in thread
From: Düster Horst @ 2004-10-29 13:51 UTC (permalink / raw)
To: pgsql-admin
OK, the manual talk about this solution, but I do have more than 2000
objects in my DB. What is the reason that pg_dump/pg_restore are not able to
create an correct order ob DB objects with respect to their constraints. All
tables must be created before the views are created and so on?
Horst
-----Ursprüngliche Nachricht-----
Von: Janko Richter [mailto:jankorichter@yahoo.de]
Gesendet am: Freitag, 29. Oktober 2004 13:14
An: Düster Horst; pgsql-admin@postgresql.org
Betreff: Re: pg_restore error
Hi,
Try "pg_restore -l" to generate a TOC.
You may rearrange the TOC file.
Then use "pg_restore -L".
Hope it helps,
Janko Richter
Düster Horst wrote:
> Vishal
>
> Thank you for your answer. My problem is, I have to restore BLOBS. The
> documentation says, it's only possible to restore blobs with pg_restore in
> one part. The way you suppose does not work with BLOBS because the
> restauration of single objects will not restore BOLBS.
>
> Horst Düster
>
> -----Ursprüngliche Nachricht-----
> Von: Vishal Kashyap @ [Sai Hertz And Control Systems]
> [mailto:vishalonlist@gmail.com]
> Gesendet am: Freitag, 29. Oktober 2004 11:33
> An: Düster Horst
> Cc: pgsql-admin@postgresql.org
> Betreff: Re: [ADMIN] pg_restore error
>
> Hi
>
>
>>I thought, that pg_restore will organize the restore-prozess in an optimal
>>order. This seems not work correct!!?? I would be grateful to hear any
>>hints.
>
>
>
> pg_restore does not priortize till now idealy it must be like
> restoring the functions >> tables >> views >> seqences so on...
>
> This is in the todo
>
> http://developer.postgresql.org/todo.php
>
> but for mean while you would have to use pg_restore to restore the
> tables one by one and then let other thing go on.
>
> moreover if your database is not to large its best to make a text file
> of the dump and run it through psql
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2004-10-29 16:39 Lee Wu <Lwu@mxlogic.com>
0 siblings, 1 reply; 26+ messages in thread
From: Lee Wu @ 2004-10-29 16:39 UTC (permalink / raw)
To: Düster Horst <Horst.Duester@bd.so.ch>; pgsql-admin
I had the problem in 7.3.2. Postgres pg_restore reied to restore views before tables. I had to use -l to generate a dump list and
change the order of restore manually and restore with -L
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Düster Horst
Sent: Friday, October 29, 2004 7:52 AM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_restore error
OK, the manual talk about this solution, but I do have more than 2000
objects in my DB. What is the reason that pg_dump/pg_restore are not able to
create an correct order ob DB objects with respect to their constraints. All
tables must be created before the views are created and so on?
Horst
-----Ursprüngliche Nachricht-----
Von: Janko Richter [mailto:jankorichter@yahoo.de]
Gesendet am: Freitag, 29. Oktober 2004 13:14
An: Düster Horst; pgsql-admin@postgresql.org
Betreff: Re: pg_restore error
Hi,
Try "pg_restore -l" to generate a TOC.
You may rearrange the TOC file.
Then use "pg_restore -L".
Hope it helps,
Janko Richter
Düster Horst wrote:
> Vishal
>
> Thank you for your answer. My problem is, I have to restore BLOBS. The
> documentation says, it's only possible to restore blobs with pg_restore in
> one part. The way you suppose does not work with BLOBS because the
> restauration of single objects will not restore BOLBS.
>
> Horst Düster
>
> -----Ursprüngliche Nachricht-----
> Von: Vishal Kashyap @ [Sai Hertz And Control Systems]
> [mailto:vishalonlist@gmail.com]
> Gesendet am: Freitag, 29. Oktober 2004 11:33
> An: Düster Horst
> Cc: pgsql-admin@postgresql.org
> Betreff: Re: [ADMIN] pg_restore error
>
> Hi
>
>
>>I thought, that pg_restore will organize the restore-prozess in an optimal
>>order. This seems not work correct!!?? I would be grateful to hear any
>>hints.
>
>
>
> pg_restore does not priortize till now idealy it must be like
> restoring the functions >> tables >> views >> seqences so on...
>
> This is in the todo
>
> http://developer.postgresql.org/todo.php
>
> but for mean while you would have to use pg_restore to restore the
> tables one by one and then let other thing go on.
>
> moreover if your database is not to large its best to make a text file
> of the dump and run it through psql
>
>
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2004-11-01 01:45 Iain <iain@mst.co.jp>
parent: Lee Wu <Lwu@mxlogic.com>
0 siblings, 0 replies; 26+ messages in thread
From: Iain @ 2004-11-01 01:45 UTC (permalink / raw)
To: Lee Wu <Lwu@mxlogic.com>; Düster Horst <Horst.Duester@bd.so.ch>; pgsql-admin
I have had some problems with this in the past too, but the good news is
that after you do that, later dump restores tend to go much more smoothly as
the internal ordering will then match the (successful) load order used.
Dumping and restoring databases under development can be a real chore, but a
stable production system should be relatively OK. Just my experience anyway.
regards
Iain
----- Original Message -----
From: "Lee Wu" <Lwu@mxlogic.com>
To: "Düster Horst" <Horst.Duester@bd.so.ch>; <pgsql-admin@postgresql.org>
Sent: Saturday, October 30, 2004 1:39 AM
Subject: Re: [ADMIN] pg_restore error
>I had the problem in 7.3.2. Postgres pg_restore reied to restore views
>before tables. I had to use -l to generate a dump list and
> change the order of restore manually and restore with -L
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Düster Horst
> Sent: Friday, October 29, 2004 7:52 AM
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_restore error
>
> OK, the manual talk about this solution, but I do have more than 2000
> objects in my DB. What is the reason that pg_dump/pg_restore are not able
> to
> create an correct order ob DB objects with respect to their constraints.
> All
> tables must be created before the views are created and so on?
>
> Horst
>
> -----Ursprüngliche Nachricht-----
> Von: Janko Richter [mailto:jankorichter@yahoo.de]
> Gesendet am: Freitag, 29. Oktober 2004 13:14
> An: Düster Horst; pgsql-admin@postgresql.org
> Betreff: Re: pg_restore error
>
> Hi,
>
> Try "pg_restore -l" to generate a TOC.
> You may rearrange the TOC file.
> Then use "pg_restore -L".
>
> Hope it helps,
> Janko Richter
>
>
> Düster Horst wrote:
>> Vishal
>>
>> Thank you for your answer. My problem is, I have to restore BLOBS. The
>> documentation says, it's only possible to restore blobs with pg_restore
>> in
>> one part. The way you suppose does not work with BLOBS because the
>> restauration of single objects will not restore BOLBS.
>>
>> Horst Düster
>>
>> -----Ursprüngliche Nachricht-----
>> Von: Vishal Kashyap @ [Sai Hertz And Control Systems]
>> [mailto:vishalonlist@gmail.com]
>> Gesendet am: Freitag, 29. Oktober 2004 11:33
>> An: Düster Horst
>> Cc: pgsql-admin@postgresql.org
>> Betreff: Re: [ADMIN] pg_restore error
>>
>> Hi
>>
>>
>>>I thought, that pg_restore will organize the restore-prozess in an
>>>optimal
>>>order. This seems not work correct!!?? I would be grateful to hear any
>>>hints.
>>
>>
>>
>> pg_restore does not priortize till now idealy it must be like
>> restoring the functions >> tables >> views >> seqences so on...
>>
>> This is in the todo
>>
>> http://developer.postgresql.org/todo.php
>>
>> but for mean while you would have to use pg_restore to restore the
>> tables one by one and then let other thing go on.
>>
>> moreover if your database is not to large its best to make a text file
>> of the dump and run it through psql
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 26+ messages in thread
* pg_restore ERROR
@ 2007-08-08 18:41 George Wilk <gwilk@ellacoya.com>
0 siblings, 0 replies; 26+ messages in thread
From: George Wilk @ 2007-08-08 18:41 UTC (permalink / raw)
To: pgsql-admin
After running pg_dumpall on my database cluster like this:
$PGBIN/pg_dumpall -c -U ipscdb | gzip > /var/db/pgsql_bkp/pg_dump_file.gz
I subsequently delete the PGDATA directory for the backed up database and
initialize a new database. Next I attempt to run the restore using
initialized database like this:
cat /var/db/pgsql_bkp/pg_dump_file.gz | gunzip | $PGBIN/psql postgres >
/dev/null
This results in a couple of error messages:
ERROR: current user cannot be dropped
ERROR: role "ipscdb" already exists
Since I ran initdb to initialize my database after the pg_dumpall as
"ipscdb" user, and subsequently am running the restore as "ipscdb" user, it
does make sense that I see these errors. The first one is caused by the -c
switch on pg_dumpall which generates the script to drop all database objects
before restore is done. The second is a consequence of the first one -
script is attempting to create a role, which already exists.
My questions are:
1) What would be the right way to do this? (other than pg_restore
with the create option)
2) Does the restore go forward despite the two errors shown above -
meaning that I can pretty much ignore these errors?
Thanks in advance,
~george
^ permalink raw reply [nested|flat] 26+ messages in thread
* pg_restore error
@ 2007-12-13 17:06 Kevin Kempter <kevin@kevinkempterllc.com>
0 siblings, 2 replies; 26+ messages in thread
From: Kevin Kempter @ 2007-12-13 17:06 UTC (permalink / raw)
To: pgsql-admin
Hi list;
We're seeing this error whern trying to restore a pg_dump file:
---------------------------------------------------------------------------------------------------------
pg_restore: ERROR: invalid byte sequence for encoding "UTF8": 0x9f CONTEXT:
COPY log_customer_api, line 4551 pg_restore: [archiver (db)] error returned
by PQendcopy: ERROR: invalid byte sequence for encoding "UTF8": 0x9f
CONTEXT: COPY log_customer_api, line 4551 pg_restore: *** aborted because of
error
---------------------------------------------------------------------------------------------------------
Any thoughts?
Thx in advance
/Kevin
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2007-12-13 20:10 Ivo Rossacher <rossacher@bluewin.ch>
parent: Kevin Kempter <kevin@kevinkempterllc.com>
1 sibling, 0 replies; 26+ messages in thread
From: Ivo Rossacher @ 2007-12-13 20:10 UTC (permalink / raw)
To: pgsql-admin
Am Donnerstag, 13. Dezember 2007 18.06:23 schrieb Kevin Kempter:
> Hi list;
>
> We're seeing this error whern trying to restore a pg_dump file:
>
> ---------------------------------------------------------------------------
>------------------------------ pg_restore: ERROR: invalid byte sequence for
> encoding "UTF8": 0x9f CONTEXT: COPY log_customer_api, line 4551 pg_restore:
> [archiver (db)] error returned by PQendcopy: ERROR: invalid byte sequence
> for encoding "UTF8": 0x9f CONTEXT: COPY log_customer_api, line 4551
> pg_restore: *** aborted because of error
> ---------------------------------------------------------------------------
>------------------------------
I got similar messages for several reasons:
-Restore of data dump from an older server version on a newer one. The UTF8
handling was changed several times, where mostly the older servers did accept
worng byte codings which newer servers do not anymore.
-The server was created with an other encoding then the one which created the
dump.
-The dump was edited and with it some wrong encodings did enter.
If you could provide some more details what happened I might come up with some
more ideas.
Best regards
Ivo
>
>
> Any thoughts?
>
>
> Thx in advance
>
> /Kevin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2007-12-16 20:05 Usama Dar <munir.usama@gmail.com>
parent: Kevin Kempter <kevin@kevinkempterllc.com>
1 sibling, 0 replies; 26+ messages in thread
From: Usama Dar @ 2007-12-16 20:05 UTC (permalink / raw)
To: Kevin Kempter <kevin@kevinkempterllc.com>; +Cc: pgsql-admin
On Dec 13, 2007 10:06 PM, Kevin Kempter <kevin@kevinkempterllc.com> wrote:
> Hi list;
>
> We're seeing this error whern trying to restore a pg_dump file:
>
>
> ---------------------------------------------------------------------------------------------------------
> pg_restore: ERROR: invalid byte sequence for encoding "UTF8": 0x9f
> CONTEXT:
> COPY log_customer_api, line 4551 pg_restore: [archiver (db)] error
> returned
> by PQendcopy: ERROR: invalid byte sequence for encoding "UTF8": 0x9f
> CONTEXT: COPY log_customer_api, line 4551 pg_restore: *** aborted because
> of
> error
>
> ---------------------------------------------------------------------------------------------------------
>
>
> Any thoughts?
>
>
Which version are you trying to restore into? Which version did you dump
from?
--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar
^ permalink raw reply [nested|flat] 26+ messages in thread
* pg_restore error
@ 2010-04-17 16:31 Bryan White <nicktook@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: Bryan White @ 2010-04-17 16:31 UTC (permalink / raw)
To: pgsql-admin
I am trying to load a data base using pg_restore. We are migrating to
a new server and I want to see if I can make the load go faster if I
load with pg_restore instead of psql.
The old server is:
CentOS upgraded to 5.4 64bit with the stock PostgreSQL 8.1.
The dump file was created with:
pg_dump --format=c --file=ec.restore ec
where ec is the name of the database. It is encoded SQL_ASCII. I
plan on converting to UTF-8 but I can't do that quite yet.
The new server is
CentOS 5.4 64 bit with PostgreSQL 8.4 installed from the YUM repo on
the PostgreSQL site.
The restore command is:
createdb -E SQL_ASCII -T template0 ec
pg_restore -dec -j2 ec.restore
I get this error:
pg_restore: [custom archiver] dumping a specific TOC data block out of
order is not supported without ID on this input stream (fseek
required)
pg_restore: [archiver] worker process failed: exit code 1
It seems to run if I ommit the -j2 option. (Though as of this moment
is has not completed). Is there a known problem with using parallel
loading in 8.4 from a file created with an 8.1 database?
--
Bryan White
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2010-04-17 17:48 Steve Crawford <scrawford@pinpointresearch.com>
parent: Bryan White <nicktook@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Steve Crawford @ 2010-04-17 17:48 UTC (permalink / raw)
To: Bryan White <nicktook@gmail.com>; +Cc: pgsql-admin
Bryan White wrote:
> I am trying to load a data base using pg_restore...... Is there a known problem with using parallel
> loading in 8.4 from a file created with an 8.1 database?
>
It was unclear from your email how you are creating the dump. Did you
create the dump with the 8.1 or the 8.4 version of pg_dump? When
upgrading, the docs recommend using the newer version of the dump
utility: "It is recommended that you use the pg_dump and pg_dumpall
programs from the newer version of PostgreSQL, to take advantage of any
enhancements that might have been made in these programs. Current
releases of the dump programs can read data from any server version back
to 7.0."
How many cores are available on the new server?
Cheers,
Steve
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2010-04-17 18:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bryan White <nicktook@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Tom Lane @ 2010-04-17 18:46 UTC (permalink / raw)
To: Bryan White <nicktook@gmail.com>; +Cc: pgsql-admin
Bryan White <nicktook@gmail.com> writes:
> I get this error:
> pg_restore: [custom archiver] dumping a specific TOC data block out of
> order is not supported without ID on this input stream (fseek
> required)
I can't duplicate that here using latest 8.1.x pg_dump and 8.4.x
pg_restore ... what versions are you using exactly? Also, it might
be dependent on the set of objects in your database --- would you
be willing to show us the output of "pg_restore --list ec.restore"?
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2010-04-17 20:07 Bryan White <nicktook@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 26+ messages in thread
From: Bryan White @ 2010-04-17 20:07 UTC (permalink / raw)
To: pgsql-admin
On Sat, Apr 17, 2010 at 2:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bryan White <nicktook@gmail.com> writes:
>> I get this error:
>> pg_restore: [custom archiver] dumping a specific TOC data block out of
>> order is not supported without ID on this input stream (fseek
>> required)
>
> I can't duplicate that here using latest 8.1.x pg_dump and 8.4.x
> pg_restore ... what versions are you using exactly? Also, it might
> be dependent on the set of objects in your database --- would you
> be willing to show us the output of "pg_restore --list ec.restore"?
I think it was an error on my part. I am retring the whole operation now.
--
Bryan White
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2010-04-17 20:08 Bryan White <nicktook@gmail.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 1 reply; 26+ messages in thread
From: Bryan White @ 2010-04-17 20:08 UTC (permalink / raw)
To: pgsql-admin
> It was unclear from your email how you are creating the dump. Did you create
> the dump with the 8.1 or the 8.4 version of pg_dump? When upgrading, the
> docs recommend using the newer version of the dump utility: "It is
> recommended that you use the pg_dump and pg_dumpall programs from the newer
> version of PostgreSQL, to take advantage of any enhancements that might have
> been made in these programs. Current releases of the dump programs can read
> data from any server version back to 7.0."
I messed up. I sent the stderr output of the dump to a text file and
never looked at it. There was some sort of deadlock condition on a
particular table. Another process tries to drop and recreate a table
during the time of the dump.
I was using the pg_dump from 8.1. I have now dumped the database with
the 8.4 version. It will be a couple hours before I have any results.
> How many cores are available on the new server?
The new server is a dual socket Nahalem. 8 cores, 16 threads, 48 GB
ram, 2 WAL drives in RAID1 and 12 database drives in RAID10.
--
Bryan White
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2010-04-17 21:53 Steve Crawford <scrawford@pinpointresearch.com>
parent: Bryan White <nicktook@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Steve Crawford @ 2010-04-17 21:53 UTC (permalink / raw)
To: Bryan White <nicktook@gmail.com>; +Cc: pgsql-admin
Bryan White wrote:
> ...
> The new server is a dual socket Nahalem. 8 cores, 16 threads, 48 GB
> ram, 2 WAL drives in RAID1 and 12 database drives in RAID10.
>
I would try setting -j higher - in your case try 8 for starters. Also
turn off fsync and autovacuum (and turn them back on !!!). See these and
more tips at:
http://it.toolbox.com/blogs/database-soup/using-84-parallel-restore-with-your-83-or-82-database-3157...
Cheers,
Steve
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_restore error
@ 2010-04-17 22:09 Bryan White <nicktook@gmail.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 26+ messages in thread
From: Bryan White @ 2010-04-17 22:09 UTC (permalink / raw)
To: pgsql-admin
On Sat, Apr 17, 2010 at 5:53 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> Bryan White wrote:
>>
>> ...
>> The new server is a dual socket Nahalem. 8 cores, 16 threads, 48 GB
>> ram, 2 WAL drives in RAID1 and 12 database drives in RAID10.
>>
>
> I would try setting -j higher - in your case try 8 for starters. Also turn
> off fsync and autovacuum (and turn them back on !!!). See these and more
> tips at:
> http://it.toolbox.com/blogs/database-soup/using-84-parallel-restore-with-your-83-or-82-database-3157...
>
Success. The -j6 run completed. The time to restore went from 152
minutes to 45 minutes. I was already doing some of the things on the
above page. I plan on implementing others and than do a few test runs
to find the optimal number of jobs.
Thanks!
--
Bryan White
^ permalink raw reply [nested|flat] 26+ messages in thread
* pg_restore error
@ 2024-02-13 20:08 M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 26+ messages in thread
From: M Sarwar @ 2024-02-13 20:08 UTC (permalink / raw)
To: pgsql-admin
Sent from my Galaxy
Hello,
PS H:\> pg_restore -v -h bZonxdb1.cXgunmbej9Yk.us-gov-west-1.rds.amazonaws.com -U db_sarwar -d bronxdb1 -n bronx_mig -t errors_dev_inspection_20240214 C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Files_MetErr_20240213-01.DUMP
pg_restore: error: too many command-line arguments (first is "bronxdb1")
Try "pg_restore --help" for more information.
PS H:\>
The same command was working earlier. What could be the issue here.
Thanks,
Sarwar
^ permalink raw reply [nested|flat] 26+ messages in thread
* RE: pg_restore error
@ 2024-02-13 20:27 M Sarwar <sarwarmd02@outlook.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 26+ messages in thread
From: M Sarwar @ 2024-02-13 20:27 UTC (permalink / raw)
To: pgsql-admin
I found the issue. Powershell interface issue. The same command is working from the dos prompt.
Thanks,
Sent from my Galaxy
-------- Original message --------
From: M Sarwar <sarwarmd02@outlook.com>
Date: 2/13/24 3:08 PM (GMT-05:00)
To: pgsql-admin@postgresql.org
Subject: pg_restore error
Sent from my Galaxy
Hello,
PS H:\> pg_restore -v -h bZonxdb1.cXgunmbej9Yk.us-gov-west-1.rds.amazonaws.com -U db_sarwar -d bronxdb1 -n bronx_mig -t errors_dev_inspection_20240214 C:\Mohammed\Work\NGC\Apps\Bronx\Backups\Dumps\data\backup_Dev_Files_MetErr_20240213-01.DUMP
pg_restore: error: too many command-line arguments (first is "bronxdb1")
Try "pg_restore --help" for more information.
PS H:\>
The same command was working earlier. What could be the issue here.
Thanks,
Sarwar
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2024-02-13 20:27 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-03 22:13 pg_restore error Laurette Cisneros <laurette@nextbus.com>
2002-09-03 22:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-09-03 22:33 ` Laurette Cisneros <laurette@nextbus.com>
2002-09-04 20:15 ` Laurette Cisneros <laurette@nextbus.com>
2002-09-04 21:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-28 15:48 pg_restore error Düster Horst <Horst.Duester@bd.so.ch>
2004-10-29 11:12 pg_restore error Düster Horst <Horst.Duester@bd.so.ch>
2004-10-29 10:32 ` Vishal Kashyap @ [Sai Hertz And Control Systems] <vishalonlist@gmail.com>
2004-10-29 11:38 Re: pg_restore error Düster Horst <Horst.Duester@bd.so.ch>
2004-10-29 12:14 ` Janko Richter <jankorichter@yahoo.de>
2004-10-29 13:51 Re: pg_restore error Düster Horst <Horst.Duester@bd.so.ch>
2004-10-29 16:39 Re: pg_restore error Lee Wu <Lwu@mxlogic.com>
2004-11-01 01:45 ` Iain <iain@mst.co.jp>
2007-08-08 18:41 pg_restore ERROR George Wilk <gwilk@ellacoya.com>
2007-12-13 17:06 pg_restore error Kevin Kempter <kevin@kevinkempterllc.com>
2007-12-13 20:10 ` Ivo Rossacher <rossacher@bluewin.ch>
2007-12-16 20:05 ` Usama Dar <munir.usama@gmail.com>
2010-04-17 16:31 pg_restore error Bryan White <nicktook@gmail.com>
2010-04-17 17:48 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-04-17 20:08 ` Bryan White <nicktook@gmail.com>
2010-04-17 21:53 ` Steve Crawford <scrawford@pinpointresearch.com>
2010-04-17 22:09 ` Bryan White <nicktook@gmail.com>
2010-04-17 18:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-04-17 20:07 ` Bryan White <nicktook@gmail.com>
2024-02-13 20:08 pg_restore error M Sarwar <sarwarmd02@outlook.com>
2024-02-13 20:27 ` M Sarwar <sarwarmd02@outlook.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