pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Privileges where not restored
6+ messages / 2 participants
[nested] [flat]

* Privileges where not restored
@ 2005-01-19 16:33  Düster Horst <Horst.Duester@bd.so.ch>
  0 siblings, 1 reply; 6+ messages in thread

From: Düster Horst @ 2005-01-19 16:33 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Privileges where not restored
@ 2005-01-20 04:07  Michael Fuhr <mike@fuhr.org>
  parent: Düster Horst <Horst.Duester@bd.so.ch>
  0 siblings, 0 replies; 6+ messages in thread

From: Michael Fuhr @ 2005-01-20 04:07 UTC (permalink / raw)
  To: Düster Horst <Horst.Duester@bd.so.ch>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Privileges where not restored
@ 2005-01-20 07:47  Düster Horst <Horst.Duester@bd.so.ch>
  0 siblings, 1 reply; 6+ messages in thread

From: Düster Horst @ 2005-01-20 07:47 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Privileges where not restored
@ 2005-01-20 08:02  Michael Fuhr <mike@fuhr.org>
  parent: Düster Horst <Horst.Duester@bd.so.ch>
  0 siblings, 0 replies; 6+ messages in thread

From: Michael Fuhr @ 2005-01-20 08:02 UTC (permalink / raw)
  To: Düster Horst <Horst.Duester@bd.so.ch>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Privileges where not restored
@ 2005-01-20 09:33  Düster Horst <Horst.Duester@bd.so.ch>
  0 siblings, 1 reply; 6+ messages in thread

From: Düster Horst @ 2005-01-20 09:33 UTC (permalink / raw)
  To: Michael Fuhr <mike@fuhr.org>; Düster Horst <Horst.Duester@bd.so.ch>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Privileges where not restored
@ 2005-01-20 16:54  Michael Fuhr <mike@fuhr.org>
  parent: Düster Horst <Horst.Duester@bd.so.ch>
  0 siblings, 0 replies; 6+ messages in thread

From: Michael Fuhr @ 2005-01-20 16:54 UTC (permalink / raw)
  To: Düster Horst <Horst.Duester@bd.so.ch>; +Cc: pgsql-admin

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/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-01-20 16:54 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-19 16:33 Privileges where not restored Düster Horst <Horst.Duester@bd.so.ch>
2005-01-20 04:07 ` Michael Fuhr <mike@fuhr.org>
2005-01-20 07:47 Re: Privileges where not restored Düster Horst <Horst.Duester@bd.so.ch>
2005-01-20 08:02 ` Michael Fuhr <mike@fuhr.org>
2005-01-20 09:33 Re: Privileges where not restored Düster Horst <Horst.Duester@bd.so.ch>
2005-01-20 16:54 ` Michael Fuhr <mike@fuhr.org>

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