Обсуждение: Privileges where not restored
I want to migrate a DB from PG 7.4.6 to PG 8.0.0. Thus I dumped a PG 7.4.6 DB with the command pg_dump -Fc -h host -U user dbname > db.dmp (pg_dump Version (PostgreSQL) 8.0.0) after the dump I create a TOC list file with pg_restore -l db.dmp > db.lst (pg_restore Version (PostgreSQL) 8.0.0) This step is nessesary to reorder the DB objects for correct import. After reordering the DB objects I restore the DB with pg_restore -L db.lst -v db.dmp | psql -d db All DB objects where restored but not their user previleges. I did the above described steps successful with PG 7.4.6. The listfile created with PG 7.4.6 shows many records with the ACL keyword. These records are missing in the PG 8.0.0 created listfile. I would be grateful for any hint. With best regards Horst Düster --------------------------------- Dr. Horst Düster GIS-Koordinator /Amtschef Stv. 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
On Wed, Jan 19, 2005 at 05:33:37PM +0100, Düster Horst wrote: > I want to migrate a DB from PG 7.4.6 to PG 8.0.0. Thus I dumped a PG 7.4.6 > DB with the command > > pg_dump -Fc -h host -U user dbname > db.dmp (pg_dump Version (PostgreSQL) > 8.0.0) > > after the dump I create a TOC list file with > > pg_restore -l db.dmp > db.lst (pg_restore Version > (PostgreSQL) 8.0.0) > > This step is nessesary to reorder the DB objects for correct import. Are you sure reordering was necessary? Did you try to restore without reordering the objects? > After reordering the DB objects I restore the DB with > pg_restore -L db.lst -v db.dmp | psql -d db You could also specify the database as an option to pg_restore and skip piping the output into psql, unless perhaps you have a specific reason for using psql. > All DB objects where restored but not their user previleges. Hmmm...I see the same behavior. The following restores objects with their original permissions: pg_restore -d testdb db.dump The following restores objects without their permissions: pg_restore -l db.dump > db.list pg_restore -d testdb -L db.list db.dump > I did the above described steps successful with PG 7.4.6. The listfile > created with PG 7.4.6 shows many records with the ACL keyword. These records > are missing in the PG 8.0.0 created listfile. I see that as well; I'll mention it on pgsql-bugs. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Thank you for your response. >Are you sure reordering was necessary? Did you try to restore >without reordering the objects? Yes I tried the restore without reordering, but it doesn't work due to the fact that pg_restore tries to restore some objects of my DB before it is allowed to restore them. (View before Table and so on). Thus it is necessary to reorder. I discussed this item last autumn without a satisfactory solution. It seems to be not possible detect the correct order of DB objects for pg_dump. Horst --------------------------------- Dr. Horst Düster GIS-Koordinator /Amtschef Stv. 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 -----Ursprüngliche Nachricht----- Von: Michael Fuhr [mailto:mike@fuhr.org] Gesendet am: Donnerstag, 20. Januar 2005 05:07 An: Düster Horst Cc: 'pgsql-admin@postgresql.org' Betreff: Re: [ADMIN] Privileges where not restored On Wed, Jan 19, 2005 at 05:33:37PM +0100, Düster Horst wrote: > I want to migrate a DB from PG 7.4.6 to PG 8.0.0. Thus I dumped a PG 7.4.6 > DB with the command > > pg_dump -Fc -h host -U user dbname > db.dmp (pg_dump Version (PostgreSQL) > 8.0.0) > > after the dump I create a TOC list file with > > pg_restore -l db.dmp > db.lst (pg_restore Version > (PostgreSQL) 8.0.0) > > This step is nessesary to reorder the DB objects for correct import. Are you sure reordering was necessary? Did you try to restore without reordering the objects? > After reordering the DB objects I restore the DB with > pg_restore -L db.lst -v db.dmp | psql -d db You could also specify the database as an option to pg_restore and skip piping the output into psql, unless perhaps you have a specific reason for using psql. > All DB objects where restored but not their user previleges. Hmmm...I see the same behavior. The following restores objects with their original permissions: pg_restore -d testdb db.dump The following restores objects without their permissions: pg_restore -l db.dump > db.list pg_restore -d testdb -L db.list db.dump > I did the above described steps successful with PG 7.4.6. The listfile > created with PG 7.4.6 shows many records with the ACL keyword. These records > are missing in the PG 8.0.0 created listfile. I see that as well; I'll mention it on pgsql-bugs. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Thu, Jan 20, 2005 at 08:47:13AM +0100, Düster Horst wrote: > >Are you sure reordering was necessary? Did you try to restore > >without reordering the objects? > > Yes I tried the restore without reordering, but it doesn't work due to the > fact that pg_restore tries to restore some objects of my DB before it is > allowed to restore them. (View before Table and so on). Thus it is necessary > to reorder. Did you try with pg_dump and pg_restore from 8.0.0? I think they've improved in this area. If you still have problems, then could you provide a test case that demonstrates incorrect ordering? I'm sure the developers would like to fix such problems if possible. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
There exists a second reason to walk my way. I work with PostGIS. To update PostGIS you need to replace different functions, operators etc. for it it exists an update process which expect the use of a TOC list. I think the best way is to patch this pg_restore bug --------------------------------- Dr. Horst Düster GIS-Koordinator /Amtschef Stv. 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 -----Ursprüngliche Nachricht----- Von: Michael Fuhr [mailto:mike@fuhr.org] Gesendet am: Donnerstag, 20. Januar 2005 09:02 An: Düster Horst Cc: 'pgsql-admin@postgresql.org' Betreff: Re: [ADMIN] Privileges where not restored On Thu, Jan 20, 2005 at 08:47:13AM +0100, Düster Horst wrote: > >Are you sure reordering was necessary? Did you try to restore > >without reordering the objects? > > Yes I tried the restore without reordering, but it doesn't work due to the > fact that pg_restore tries to restore some objects of my DB before it is > allowed to restore them. (View before Table and so on). Thus it is necessary > to reorder. Did you try with pg_dump and pg_restore from 8.0.0? I think they've improved in this area. If you still have problems, then could you provide a test case that demonstrates incorrect ordering? I'm sure the developers would like to fix such problems if possible. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Thu, Jan 20, 2005 at 10:33:00AM +0100, Düster Horst wrote: > I work with PostGIS. To update PostGIS you need to replace different > functions, operators etc. for it it exists an update process which > expect the use of a TOC list. I think the best way is to patch this > pg_restore bug I'm sure the developers would like to fix any bugs in pg_restore, but they'll need to see a test case so they can find out what's wrong. We've identified the ACL issue, but if there's an ordering problem as well then the developers will need to know what circumstances cause it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/