Retoring non-administrative user databases

[Keith C. Perry]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Peter Eisentraut]

Keith C. Perry writes:

What am I missing?

A reproduceable test case.

--
Peter Eisentraut pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Keith C. Perry]

Quoting Peter Eisentraut <pe*****@gmx.net>:

Keith C. Perry writes:

What am I missing?

A reproduceable test case.

--
Peter Eisentraut pe*****@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

"Keith C. Perry" <ne******@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Joshua D. Drake]

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 pe*****@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.
[phone] - [e-mail] - http://www.commandprompt.com
PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Keith C. Perry]

Quoting "Joshua D. Drake" <[e-mail]>:

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 pe*****@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.
[phone] - [e-mail] - 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Alvaro Herrera]

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)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Keith C. Perry]

Quoting Alvaro Herrera <al******@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org