Problem restoring with pg_dump

Problem restoring with pg_dump

am 28.11.2006 11:00:37 von Peter Wilson

I posted this to 'general' yesterday but with no luck. This is probably a better
group.

I've just got the following message while trying to restore a database :

pg_restore : [custom archiver] Dumping a specific TOC data block out of order is
not supported without ID on this input stream (fseek required).

The command was :
pg_restore -L /tmp/toc --dbname=whitebeam --disable-triggers --username=postgres
/var/backups/restore-db.psql

/tmp/toc is a re-orderd output from :

pg_restore -l -a /var/backups/restore-db.psql > /tmp/toc

Using Postgres 8.1.4 on
Linux version 2.6.15-1.2054_FC5

The dump itself was made on another machine running 8.1.4 on Fedora core 4.

I've run similar commands on this machine before with no problems. Not sure why
I should start to have these problems now?

Anyone else seen anything similar? Thoughts much appreciated!

Pete

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Re: Problem restoring with pg_dump

am 28.11.2006 14:26:35 von Adam Radlowski

I had problems, like that. I've omitted them using "plain" format (SQL
script file) of dump file. I restore data from "plain" format with psql
- using command "\i FILE". It work always. If You gzip this "plain" file
- You become simillar volume of file using binary dump format.
Greetings
Adam

Peter Wilson wrote:

>I posted this to 'general' yesterday but with no luck. This is probably a better
>group.
>
>I've just got the following message while trying to restore a database :
>
>pg_restore : [custom archiver] Dumping a specific TOC data block out of order is
>not supported without ID on this input stream (fseek required).
>
>The command was :
>pg_restore -L /tmp/toc --dbname=whitebeam --disable-triggers --username=postgres
>/var/backups/restore-db.psql
>
>/tmp/toc is a re-orderd output from :
>
>pg_restore -l -a /var/backups/restore-db.psql > /tmp/toc
>
>Using Postgres 8.1.4 on
>Linux version 2.6.15-1.2054_FC5
>
>The dump itself was made on another machine running 8.1.4 on Fedora core 4.
>
>I've run similar commands on this machine before with no problems. Not sure why
>I should start to have these problems now?
>
>Anyone else seen anything similar? Thoughts much appreciated!
>
>Pete
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: 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 5: don't forget to increase your free space map settings

Re: Problem restoring with pg_dump

am 28.11.2006 16:46:56 von Tom Lane

Peter Wilson writes:
> pg_restore : [custom archiver] Dumping a specific TOC data block out of order is
> not supported without ID on this input stream (fseek required).

Could you show us the unmodified output from pg_restore -l, as well as
the edits you made to produce the -L script?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Problem restoring with pg_dump

am 29.11.2006 00:31:24 von Peter Wilson

Tom Lane wrote:
> Peter Wilson writes:
>> pg_restore : [custom archiver] Dumping a specific TOC data block out of order is
>> not supported without ID on this input stream (fseek required).
>
> Could you show us the unmodified output from pg_restore -l, as well as
> the edits you made to produce the -L script?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
Raw output from pg_restore -l is as follows:
=================================
;
; Archive created at Mon Nov 27 01:35:56 2006
; dbname: whitebeam
; TOC Entries: 24
; Compression: 9
; Dump Version: 1.10-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 8.1.4
; Dumped by pg_dump version: 8.1.4
;
;
; Selected TOC Entries:
;
1656; 0 0 SEQUENCE SET public audit_id_seq whitebeam
1657; 0 0 SEQUENCE SET public collection_id_seq whitebeam
1658; 0 0 SEQUENCE SET public contacts_contact_id_seq whitebeam
1659; 0 0 SEQUENCE SET public files_file_id_seq whitebeam
1660; 0 0 SEQUENCE SET public items_item_id_seq whitebeam
1661; 0 0 SEQUENCE SET public searchtree_node_id_seq whitebeam
1654; 0 89555 TABLE DATA public audit whitebeam
1652; 0 43231 TABLE DATA public collection_types whitebeam
1653; 0 43243 TABLE DATA public collections whitebeam
1639; 0 43140 TABLE DATA public contact_att whitebeam
1649; 0 43203 TABLE DATA public contact_item whitebeam
1650; 0 43208 TABLE DATA public contact_node whitebeam
1638; 0 43131 TABLE DATA public contacts whitebeam
1646; 0 43185 TABLE DATA public files whitebeam
1643; 0 43165 TABLE DATA public item_files whitebeam
1641; 0 43154 TABLE DATA public item_leafs whitebeam
1647; 0 43194 TABLE DATA public item_prices whitebeam
1644; 0 43169 TABLE DATA public itemdata whitebeam
1645; 0 43178 TABLE DATA public items whitebeam
1648; 0 43199 TABLE DATA public node_leafs whitebeam
1640; 0 43146 TABLE DATA public searchtree whitebeam
1651; 0 43213 TABLE DATA public site whitebeam
1642; 0 43158 TABLE DATA public stockdata whitebeam

=================================
The re-ordered version is:
=================================
;
; Archive created at Mon Nov 27 01:35:56 2006
; dbname: whitebeam
; TOC Entries: 24
; Compression: 9
; Dump Version: 1.10-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 8.1.4
; Dumped by pg_dump version: 8.1.4
;
;
; Selected TOC Entries:
;
1656; 0 0 SEQUENCE SET public audit_id_seq whitebeam
1657; 0 0 SEQUENCE SET public collection_id_seq whitebeam
1658; 0 0 SEQUENCE SET public contacts_contact_id_seq whitebeam
1659; 0 0 SEQUENCE SET public files_file_id_seq whitebeam
1660; 0 0 SEQUENCE SET public items_item_id_seq whitebeam
1661; 0 0 SEQUENCE SET public searchtree_node_id_seq whitebeam
1638; 0 43131 TABLE DATA public contacts whitebeam
1645; 0 43178 TABLE DATA public items whitebeam
1639; 0 43140 TABLE DATA public contact_att whitebeam
1649; 0 43203 TABLE DATA public contact_item whitebeam
1650; 0 43208 TABLE DATA public contact_node whitebeam
1646; 0 43185 TABLE DATA public files whitebeam
1643; 0 43165 TABLE DATA public item_files whitebeam
1647; 0 43194 TABLE DATA public item_prices whitebeam
1644; 0 43169 TABLE DATA public itemdata whitebeam
1640; 0 43146 TABLE DATA public searchtree whitebeam
1648; 0 43199 TABLE DATA public node_leafs whitebeam
1641; 0 43154 TABLE DATA public item_leafs whitebeam
1642; 0 43158 TABLE DATA public stockdata whitebeam
1652; 0 43231 TABLE DATA public collection_types whitebeam
1653; 0 43243 TABLE DATA public collections whitebeam
1654; 0 89555 TABLE DATA public audit whitebeam
1651; 0 43213 TABLE DATA public site whitebeam
=================================

Pete

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Problem restoring with pg_dump

am 29.11.2006 00:48:56 von Adam Radlowski

I'm sorry, but I have experiences only with LATNI2 and WIN1250 databases
on PostgreSQL 8.0.3, 8.0.6, 8.0.7, 8.1.3 .
I'm afraid, that without having Your SQL script file Your problem can be
difficult to resolve.
Adam

Jessica Richard wrote:
> can you please be more specific?
>
> I used pg_dump db to dump the 8.0.8 unicode db, and used
> psql db < dbdump to load the db to 8.1.4 server (on this version unicode
> becomes utf8) and got the error:
>
> ERROR: invalid byte sequence for encoding "UTF8": 0x92
>
> thanks
>
> */Adam Radlowski /* wrote:
>
> I had problems, like that. I've omitted them using "plain" format (SQL
> script file) of dump file. I restore data from "plain" format with psql
> - using command "\i FILE". It work always. If You gzip this "plain"
> file
> - You become simillar volume of file using binary dump format.
> Greetings
> Adam
>
> Peter Wilson wrote:
>
> >I posted this to 'general' yesterday but with no luck. This is
> probably a better
> >group.
> >
> >I've just got the following message while trying to restore a
> database :
> >
> >pg_restore : [custom archiver] Dumping a specific TOC data block
> out of order is
> >not supported without ID on this input stream (fseek required).
> >
> >The command was :
> >pg_restore -L /tmp/toc --dbname=whitebeam --disable-triggers
> --username=postgres
> >/var/backups/restore-db.psql
> >
> >/tmp/toc is a re-orderd output from :
> >
> >pg_restore -l -a /var/backups/restore-db.psql > /tmp/toc
> >
> >Using Postgres 8.1.4 on
> >Linux version 2.6.15-1.2054_FC5
> >
> >The dump itself was made on another machine running 8.1.4 on
> Fedora core 4.
> >
> >I've run similar commands on this machine before with no problems.
> Not sure why
> >I should start to have these problems now?
> >
> >Anyone else seen anything similar? Thoughts much appreciated!
> >
> >Pete
> >
> >---------------------------(end of
> broadcast)---------------------------
> >TIP 1: 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 5: don't forget to increase your free space map settings
>
>
> ------------------------------------------------------------ ------------
> Check out the all-new Yahoo! Mail beta
>
> - Fire up a more powerful email and get things done faster.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Problem restoring with pg_dump

am 29.11.2006 05:08:36 von Tom Lane

Peter Wilson writes:
> Tom Lane wrote:
>> Could you show us the unmodified output from pg_restore -l, as well as
>> the edits you made to produce the -L script?

> Raw output from pg_restore -l is as follows:

Hm, only data entries? What options did you use in the original
pg_dump?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Problem restoring with pg_dump

am 01.12.2006 20:32:45 von Tom Lane

Peter Wilson writes:
> Tom Lane wrote:
>> Could you show us the unmodified output from pg_restore -l, as well as
>> the edits you made to produce the -L script?

> Raw output from pg_restore -l is as follows:

Hm, this shows only data entries. Was the original pg_dump made with -a,
or did you use -a in the pg_restore -l command? If the latter, could we
see the full -l output? I didn't have any luck trying to reproduce this
behavior, so I'm supposing it depends on something you haven't shown us...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Re: Problem restoring with pg_dump

am 02.12.2006 01:30:43 von Peter Wilson

This is a multi-part message in MIME format.
--------------020901060003050503080907
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> Peter Wilson writes:
>
>> Tom Lane wrote:
>>
>>> Could you show us the unmodified output from pg_restore -l, as well as
>>> the edits you made to produce the -L script?
>>>
>
>
>> Raw output from pg_restore -l is as follows:
>>
>
> Hm, this shows only data entries. Was the original pg_dump made with -a,
> or did you use -a in the pg_restore -l command? If the latter, could we
> see the full -l output? I didn't have any luck trying to reproduce this
> behavior, so I'm supposing it depends on something you haven't shown us...
>
The dump was data-only :
pg_dump -data-only -U user --format=c --compress=9 whitebeam

I have a script that builds the empty database on the restore machine
then fills it from the pg_dump file.

This approach has worked perfectly for a year or so.

Partly the question might be 'what does the error message mean?' - it
almost looks like do_restore doesn't think it has a real file underneath
it, instead something that only provides sequential access.

I installed from RPMs. Since I originally posted I've tried the following :
+ installed the 8.1.5 RPMs - same result
+ build 8.1.5 from source - same result
+ run pg_restore from another machine - connecting to the server having
problems - same result
+ Tried in a different machine - same result

To make sure there isn't a difference between the database structures
I've now done a pg_dump --schema-only --format=p and then used that to
create the database before importing the data. Same result again. The
data file itself is just about 1.2 Gbytes in size.

Tonights backup I'm going to remove the --data-only to see whether I
have any more luck.
> regards, tom lane
>
Thanks
Pete



--
------------------------------------------------------------ ------------
Peter Wilson
T: 01414 160505
M: 07796 656566
http://www.yellowhawk.co.uk The information in this email is
confidential and is intended for the addressee/s only. Access to this
email by anyone else is unauthorised. If you are not the intended
recipient, you must not read, use or disseminate the information
contained in or attached to this email.


--
------------------------------------------------------------ ------------
Peter Wilson
T: 01414 160505
M: 07796 656566
http://www.yellowhawk.co.uk The information in this email is
confidential and is intended for the addressee/s only. Access to this
email by anyone else is unauthorised. If you are not the intended
recipient, you must not read, use or disseminate the information
contained in or attached to this email.


--------------020901060003050503080907
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit








Tom Lane wrote:


Peter Wilson  writes:


Tom Lane wrote:


Could you show us the unmodified output from pg_restore -l, as well as
the edits you made to produce the -L script?






Raw output from pg_restore -l is as follows:



Hm, this shows only data entries. Was the original pg_dump made with -a,
or did you use -a in the pg_restore -l command? If the latter, could we
see the full -l output? I didn't have any luck trying to reproduce this
behavior, so I'm supposing it depends on something you haven't shown us...


The dump was data-only :

    pg_dump -data-only -U user --format=c --compress=9 whitebeam



I have a script that builds the empty database on the restore machine
then fills it from the pg_dump file.



This approach has worked perfectly for a year or so.



Partly the question might be 'what does the error message mean?' - it
almost looks like do_restore doesn't think it has a real file
underneath it, instead something that only provides sequential access.



I installed from RPMs. Since I originally posted I've tried the
following :

+  installed the 8.1.5 RPMs - same result

+  build 8.1.5 from source - same result

+  run pg_restore from another machine - connecting to the server
having problems - same result

+ Tried in a different machine - same result



To make sure there isn't a difference between the database structures
I've now done a pg_dump --schema-only --format=p and then used that to
create the database before importing the data. Same result again. The
data file itself is just about 1.2 Gbytes in size.



Tonights backup I'm going to remove the --data-only to see whether I
have any more luck.



regards, tom lane


Thanks

Pete







--




cellspacing="2">






Peter Wilson

T: 01414 160505

M: 07796 656566


The information in this email is
confidential and is intended for the addressee/s only. Access to this
email by anyone else is unauthorised. If you are not the intended
recipient, you must not read, use or disseminate the information
contained in or attached to this email.





--




cellspacing="2">






Peter Wilson

T: 01414 160505

M: 07796 656566


The information in this email is
confidential and is intended for the addressee/s only. Access to this
email by anyone else is unauthorised. If you are not the intended
recipient, you must not read, use or disseminate the information
contained in or attached to this email.






--------------020901060003050503080907--

Re: Problem restoring with pg_dump

am 02.12.2006 01:51:42 von Tom Lane

Peter Wilson writes:
> Partly the question might be 'what does the error message mean?' - it
> almost looks like do_restore doesn't think it has a real file underneath
> it, instead something that only provides sequential access.

That is certainly what the code thinks; as to what is actually going on,
it's pretty hard to guess without a test case to trace through. Is there
any chance you could cut your data down to something reasonable to
provide a copy of the dump file?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Problem restoring with pg_dump

am 02.12.2006 02:18:09 von Peter Wilson

Tom Lane wrote:
> Peter Wilson writes:
>> Tom Lane wrote:
>>> Could you show us the unmodified output from pg_restore -l, as well as
>>> the edits you made to produce the -L script?
>
>> Raw output from pg_restore -l is as follows:
>
> Hm, this shows only data entries. Was the original pg_dump made with -a,
> or did you use -a in the pg_restore -l command? If the latter, could we
> see the full -l output? I didn't have any luck trying to reproduce this
> behavior, so I'm supposing it depends on something you haven't shown us...

I may end up duplicating myself here - seem to be having lots of problems with
the Postgres new server so apologies.

The data file is from a live server and has been steadily growing. It's
1.2Gbytes(ish) in size. It was built with --compress=9. Does Postgres uncompress
this to something bigger than 2Gbytes before processing and busting what can be
referenced in a 32 bit seek value? What happens (in Linux) when you try to open
a file that is bigger than 2Gbytes - do you loose the ability to seek?

I've just taken apart the schema def. from pg_dump --schema-only and inserted
the data-restore after the tables are created but before
indices/constraints/rules are replied. in this case I don't have to re-order the
tables. Restore doesn't seem to be having problems in this case - although the
restore will take a good while to complete. This is with the same dump file that
failed before.

Pete

>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: 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 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate