Обсуждение: Problem restoring with pg_dump
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
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 > > > >
Peter Wilson <petew@yellowhawk.co.uk> 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
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 <adamr@informatyka.gdansk.pl>/* 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 > <http://us.rd.yahoo.com/evt=43257/*http://advision.webevents.yahoo.com/mailbeta> > - Fire up a more powerful email and get things done faster.
Tom Lane wrote: > Peter Wilson <petew@yellowhawk.co.uk> 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
Peter Wilson <petew@yellowhawk.co.uk> 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
Peter Wilson <petew@yellowhawk.co.uk> 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
Tom Lane wrote:
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.
Pete
The dump was data-only :Peter Wilson <petew@yellowhawk.co.uk> 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...
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.
Thanksregards, tom lane
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. |
Peter Wilson <petew@yellowhawk.co.uk> 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
Tom Lane wrote: > Peter Wilson <petew@yellowhawk.co.uk> 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 >