Re: Retoring non-administrative user databases

Quoting "Joshua D. Drake" <jd(at)commandprompt(dot)com>:

>
> >>>What am I missing?
> >>>
> >>>
> >>A reproduceable test case.
> >>
> >>
> >>
> It is reproduceable for him Peter.
>
> Keith could you provide a little more information?
>
> Who is the user doing the dump?
> Who is the user doing the restore?
> Are these users superusers?

I actually thought pg_dumpall could only be done by a superuser but I am using
(for the dump and restore) "postgres" which is my database superuser.

> Either way, my suggestion would be to dump the schema only, restore the
> schema only.
> Then dump the data only, and restore the data only.

I'll try that-

> 7.1.3 has some oddities that don't always make a clean restore to a
> newere version (at
> leat not 7.3 series)
>
> Sincerely,
>
> Joshua Drake

It has also occurred to try was Tom suggested- using a higher version of
pg_dumpall but I've missed a library the 7.1.3 server so I either have to
compile 7.4 on there or recompile the current 7.4 server without shared
libraries. I haven't done that yet but I did find my problem document in the
man pages of pg_dump at the -R option:

Prohibit pg_dump from outputting a script that
would require reconnections to the database while
being restored. An average restoration script usu-
ally has to reconnect several times as different
users to set the original ownerships of the
objects. This option is a rather blunt instrument
because it makes pg_dump lose this ownership infor-
mation, unless you use the -X use-set-session-
authorization option.

That is the problem or rather the difference between the two pg_dumpall
programs. Apparently in 7.1.3, the "set session authorization" method to set
database ownerships is not used as a default. Additionally, the 7.1.3 pg_dump
program does not have a -X option. It does look like the -O option will work to
dump without ownership so that might be an option (a tedious option) for my
older servers at this point.

At least I know what going on now so thanks to everyone for the useful feedback.

>
> >>--
> >>Peter Eisentraut peter_e(at)gmx(dot)net
> >>
> >>
> >>
> >
> >???
> >
> >Ok, lets try the question this way...
> >
> >What is a method of dumping and restoring a complete database cluster when
> that
> >cluster contains users that are NOT allowed to create databases.
> >
> >
> >
>
> --
> Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
> Postgresql support, programming, shared hosting and dedicated hosting.
> +1-503-222-2783 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
> PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org
>
>

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com