Re: Retoring non-administrative user databases - Mailing list pgsql-general

Quoting "Joshua D. Drake" <jd@commandprompt.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@gmx.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@commandprompt.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