Thread: Retoring non-administrative user databases

Retoring non-administrative user databases

From
"Keith C. Perry"
Date:
I was going a test run through of of moving my 7.1.3 databases to 7.4RC1 and I
have a problem with creating databases for my users that do not have
administrative accounts.  By that I mean, these users are NOT allow to create
databases.  So the process was this:

On the 7.1.3 server:
pg_dumpall -c > dump.db

On the 7.4RC1 server:
psql -f dump.db template1 or psql < dump.db

Either style has the same result.  Also, I usually don't use pg_retore but in
this case I tried:

bin/pg_restore -d template1 --ignore-version --use-set-session-authorization dump.db

the error I got was:

pg_restore: [archiver] input file does not appear to be a valid archive


I'm also tried pg_restore with a 7.3.4 database file and the result was the same
on the 7.4 server.


What am I missing?


--
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

Re: Retoring non-administrative user databases

From
Peter Eisentraut
Date:
Keith C. Perry writes:

> What am I missing?

A reproduceable test case.

--
Peter Eisentraut   peter_e@gmx.net


Re: Retoring non-administrative user databases

From
"Keith C. Perry"
Date:
Quoting Peter Eisentraut <peter_e@gmx.net>:

> Keith C. Perry writes:
>
> > What am I missing?
>
> A reproduceable test case.
>
> --
> 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.

--
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

Re: Retoring non-administrative user databases

From
Tom Lane
Date:
"Keith C. Perry" <netadmin@vcsn.com> writes:
> On the 7.1.3 server:
> pg_dumpall -c > dump.db

You would probably have better luck using the 7.4 installation's pg_dump
and pg_dumpall to extract data from the 7.1 server; there are three
releases worth of bug-fixes in those that are not in the 7.1 dump tools.

Given the lack of detail about the actual problem in your posting, it's
hard to make any other specific recommendations.

            regards, tom lane

Re: Retoring non-administrative user databases

From
Peter Eisentraut
Date:
Keith C. Perry writes:

> What is a method of dumping and restoring a complete database cluster when that
> cluster contains users that are NOT allowed to create databases.

There is nothing special you need to do, except of course not actually
restoring the dump as one of those unprivileged users.  A pg_dumpall dump
must be restored as a superuser.

--
Peter Eisentraut   peter_e@gmx.net


Re: Retoring non-administrative user databases

From
"Joshua D. Drake"
Date:
>>>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?


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.

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



>>--
>>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



Re: Retoring non-administrative user databases

From
"Keith C. Perry"
Date:
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

Re: Retoring non-administrative user databases

From
Alvaro Herrera
Date:
On Sun, Nov 16, 2003 at 07:56:01PM -0500, Keith C. Perry wrote:

> 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:

Remember that if you have another machine with 7.4 running, you can use
pg_dump/pg_dumpall over the network if you don't want to mess with the
server's libraries.  (Though it should be quite straightforward to install
on isolation without disturbing anything else.)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"A wizard is never late, Frodo Baggins, nor is he early.
He arrives precisely when he means to."  (Gandalf, en LoTR FoTR)

Re: Retoring non-administrative user databases

From
"Keith C. Perry"
Date:
Quoting Alvaro Herrera <alvherre@dcc.uchile.cl>:

> On Sun, Nov 16, 2003 at 07:56:01PM -0500, Keith C. Perry wrote:
>
> > 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:
>
> Remember that if you have another machine with 7.4 running, you can use
> pg_dump/pg_dumpall over the network if you don't want to mess with the
> server's libraries.  (Though it should be quite straightforward to install
> on isolation without disturbing anything else.)
>
> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "A wizard is never late, Frodo Baggins, nor is he early.
> He arrives precisely when he means to."  (Gandalf, en LoTR FoTR)
>

You read my mind Alvaro.  That is exactly what I ended up doing from my test
server.  I LOVE that feature.

'Course I still have to compile 7.4 eventually so I compiled it anyway  :)

--
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