Thread: pg_dump problem

pg_dump problem

From
Kees Leune
Date:
I have a problem with the dump&restore process. When I dump my database,
the users I have are not created in the dump. I solved this process by
adding 'create user' statements to the beginning of the dump file.

I need to dump my database with all grant/revoke permissions, since I have
a lot of tables that have modified access permissions.

Dumping this isn't a problem (-z parameter with pg_dump/pg_dumpall), only
restoring the dump is. I would have expected a number of grant and revoke
statements in the dump, but instead, the script tries to run a few
update queries on pg_class directly.

During the restore process, all I get is 'permission denied'-messages when
the script tries to execute the updates on pg_class. Apparently, users do
not have direct modification rights on pg_class in version 6.3.2 and
everything has to be done with grant and revoke permissions (is this
true?).

Do you have a suggestion on how I can fix this problem? I really do
not want to go through my dump manually since it's well over 10Gb.....

Thanks in advance,

-kees
--
Kees Leune, Researcher at the Infolab        | Email: kees@kub.nl
Tilburg University, The Netherlands          | Phone: +31 13 466 2688
http://infolab.kub.nl/people/kees/           | HAM: PA3HFB



Re: [ADMIN] pg_dump problem

From
Bruce Momjian
Date:
What version of PostgreSQL are you running.  This should work in 6.3.2.


> I have a problem with the dump&restore process. When I dump my database,
> the users I have are not created in the dump. I solved this process by
> adding 'create user' statements to the beginning of the dump file.
>
> I need to dump my database with all grant/revoke permissions, since I have
> a lot of tables that have modified access permissions.
>
> Dumping this isn't a problem (-z parameter with pg_dump/pg_dumpall), only
> restoring the dump is. I would have expected a number of grant and revoke
> statements in the dump, but instead, the script tries to run a few
> update queries on pg_class directly.
>
> During the restore process, all I get is 'permission denied'-messages when
> the script tries to execute the updates on pg_class. Apparently, users do
> not have direct modification rights on pg_class in version 6.3.2 and
> everything has to be done with grant and revoke permissions (is this
> true?).
>
> Do you have a suggestion on how I can fix this problem? I really do
> not want to go through my dump manually since it's well over 10Gb.....
>
> Thanks in advance,
>
> -kees
> --
> Kees Leune, Researcher at the Infolab        | Email: kees@kub.nl
> Tilburg University, The Netherlands          | Phone: +31 13 466 2688
> http://infolab.kub.nl/people/kees/           | HAM: PA3HFB
>
>
>
>


--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)