Обсуждение: pg_restore error
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
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
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
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 > >
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 > >
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 dumplist 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
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)