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(dot)duester(at)bd(dot)so(dot)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/