Thread: Retoring non-administrative user databases
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
Keith C. Perry writes: > What am I missing? A reproduceable test case. -- Peter Eisentraut peter_e@gmx.net
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
"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
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
>>>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
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
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)
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