Moving a database AND changing the superuser

[Rich Cullingford]

All,
I did a pg_dumpall in preparation for moving one of our databases from
PG7.3 to PG7.4, but I just realized I have another problem: that DB
(which has served us faithfully for some time) was created for superuser
'postgres,' whilst our new DBs use a superuser name that's aligned with
our product. I'm expecting conflicts when I try to restore the data into
a 'new' DB. Am I right, and if so, what can I do? (I know I could just
try it, but the old database machine will be disappearing soon, and if I
need to do a different kind of dump -- per DB, for example -- I need to
know that soonest.)
Thanks,
Rich Cullingford
System Detection, Inc.
***@sysd.com


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

http://archives.postgresql.org

[Marek Florianczyk]

Post by Rich Cullingford
All,
I did a pg_dumpall in preparation for moving one of our databases from
PG7.3 to PG7.4, but I just realized I have another problem: that DB
(which has served us faithfully for some time) was created for superuser
'postgres,' whilst our new DBs use a superuser name that's aligned with
our product. I'm expecting conflicts when I try to restore the data into
a 'new' DB. Am I right, and if so, what can I do? (I know I could just
try it, but the old database machine will be disappearing soon, and if I
need to do a different kind of dump -- per DB, for example -- I need to
know that soonest.)

Well can't you just change all appearance of 'postgres' word to some
other eg.
cat dump.sql|sed -e 's/postgres/new_admin/g' > new_dump.sql

of course you must check first if word 'postgres' is used in any other
context, and maybe do some correction by hand.

greetings
Marek


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

http://archives.postgresql.org

[Bruno LEVEQUE]

In my opinion, the only thing you need to change is the name of the
superuser in your dump file.

Bruno

Post by Rich Cullingford
All,
I did a pg_dumpall in preparation for moving one of our databases from
PG7.3 to PG7.4, but I just realized I have another problem: that DB
(which has served us faithfully for some time) was created for
superuser 'postgres,' whilst our new DBs use a superuser name that's
aligned with our product. I'm expecting conflicts when I try to
restore the data into a 'new' DB. Am I right, and if so, what can I
do? (I know I could just try it, but the old database machine will be
disappearing soon, and if I need to do a different kind of dump -- per
DB, for example -- I need to know that soonest.)
Thanks,
Rich Cullingford
System Detection, Inc.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org

--
Bruno LEVEQUE
System Engineer
SARL NET6D
***@net6d.com
http://www.net6d.com



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Post by Rich Cullingford
I did a pg_dumpall in preparation for moving one of our databases from
PG7.3 to PG7.4, but I just realized I have another problem: that DB
(which has served us faithfully for some time) was created for superuser
'postgres,' whilst our new DBs use a superuser name that's aligned with
our product.

If you used 7.4 pg_dump, I believe that the dump script does not assume
any particular superuser name (it says RESET SESSION AUTHENTICATION
when it wants to get back into superuser state, so as long as you start
it as a superuser, you're golden).

IIRC, 7.3 pg_dump had an option to specify the superuser name to use.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Rich Cullingford]

Post by Tom Lane

Post by Rich Cullingford
I did a pg_dumpall in preparation for moving one of our databases from
PG7.3 to PG7.4, but I just realized I have another problem: that DB
(which has served us faithfully for some time) was created for superuser
'postgres,' whilst our new DBs use a superuser name that's aligned with
our product.

If you used 7.4 pg_dump, I believe that the dump script does not assume
any particular superuser name (it says RESET SESSION AUTHENTICATION
when it wants to get back into superuser state, so as long as you start
it as a superuser, you're golden).

Hmmm, how do you use 7.4 utilities against a 7.3 DB? Run them out of the
7.4 bin dir, but with PGDATA, etc., pointing to the old database (which
must be running, right?)?

Post by Tom Lane
IIRC, 7.3 pg_dump had an option to specify the superuser name to use.

I'm assuming that you don't mean:

****
-S username
--superuser=username
****

which seems to be for disabling triggers, but perhaps the following as a
parameter to pg_dumpall:

******
-X use-set-session-authorization
--use-set-session-authorization

Normally, if a (plain-text mode) script generated by pg_dump must
alter the current database user (e.g., to set correct object
ownerships), it uses the psql \connect command. This command actually
opens a new connection, which might require manual interaction (e.g.,
passwords). If you use the -X use-set-session-authorization option, then
pg_dump will instead output SET SESSION AUTHORIZATION commands. This has
the same effect, but it requires that the user restoring the database
from the generated script be a database superuser. This option
effectively overrides the -R option.

Since SET SESSION AUTHORIZATION is a standard SQL command, whereas
\connect only works in psql, this option also enhances the theoretical
portability of the output script.

This option is only meaningful for the plain-text format. For the
other formats, you may specify the option when you call pg_restore.
*****

Others on the list have suggested a global replace of 'postgres' with my
superuser name, but it's hard to see what effects that would have in a
2.3G dump file.

Thanks for your help,
Rich C.


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Post by Rich Cullingford
Hmmm, how do you use 7.4 utilities against a 7.3 DB?

pg_dump can dump from prior-release DBs (back to 7.0 at the moment).
Just point it at the older DB's port.

This is a bit tricky when installing from RPMs, since there's no really
easy way to install just pg_dump and libpq without overwriting your old
server executable, which you don't want to do just yet.

Post by Rich Cullingford
--superuser=username
which seems to be for disabling triggers, but perhaps the following as a
--use-set-session-authorization

Yeah, the latter is really the important part I think. In 7.4 that is
the default (and only) behavior, but you can get it in 7.3 by using the
switch. So the 7.3 pg_dump should be good enough for this purpose.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Rich Cullingford]

Post by Tom Lane

Post by Rich Cullingford
Hmmm, how do you use 7.4 utilities against a 7.3 DB?

pg_dump can dump from prior-release DBs (back to 7.0 at the moment).
Just point it at the older DB's port.
This is a bit tricky when installing from RPMs, since there's no really
easy way to install just pg_dump and libpq without overwriting your old
server executable, which you don't want to do just yet.

Post by Rich Cullingford
--superuser=username
which seems to be for disabling triggers, but perhaps the following as a
--use-set-session-authorization

Yeah, the latter is really the important part I think. In 7.4 that is
the default (and only) behavior, but you can get it in 7.3 by using the
switch. So the 7.3 pg_dump should be good enough for this purpose.

Well, I was gonna use pg_dumpall to avoid the tedium of individual
dumps, but (gotcha!) pg_dumpall doesn't accept
--use-set-session-authorization (tho' it does run pg_dump!). If I use
pg_dumpall, will pg_restore (with --use-set-session-authorization) be
smart enough to ignore the \connect calls in the dump?

I would try all this stuff myself, but unfortunately my PG 7.4 machine
isn't available yet, so I'm grasping at straws...
Thanks,
Rich Cullingford


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Post by Rich Cullingford
Well, I was gonna use pg_dumpall to avoid the tedium of individual
dumps, but (gotcha!) pg_dumpall doesn't accept
--use-set-session-authorization (tho' it does run pg_dump!). If I use
pg_dumpall, will pg_restore (with --use-set-session-authorization) be
smart enough to ignore the \connect calls in the dump?

Nope, because pg_dumpall only outputs plain-text scripts, which you
can't feed to pg_restore. 7.3's pg_dumpall was a few bricks shy of a
load as far as supporting all the possibly-useful options of pg_dump :-(
so I think you're kinda stuck here. You can either hack up the
pg_dumpall sources to add this option, or go to 7.4.

regards, tom lane

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

http://archives.postgresql.org

[Peter Eisentraut]

Post by Rich Cullingford
Well, I was gonna use pg_dumpall to avoid the tedium of individual
dumps, but (gotcha!) pg_dumpall doesn't accept
--use-set-session-authorization (tho' it does run pg_dump!).

It supplies that option automatically.

Post by Rich Cullingford
If I use
pg_dumpall, will pg_restore (with --use-set-session-authorization) be
smart enough to ignore the \connect calls in the dump?

pg_dumpall output cannot be restored with pg_restore.

Post by Rich Cullingford
I would try all this stuff myself, but unfortunately my PG 7.4
machine isn't available yet, so I'm grasping at straws...

In PG 7.4, --use-set-session-authorization is the default and only
option.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org