Thread: Moving a database AND changing the superuser
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.
rculling@sysd.com
W liście z pon, 15-12-2003, godz. 16:30, Rich Cullingford pisze: > 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
In my opinion, the only thing you need to change is the name of the superuser in your dump file. Bruno Rich Cullingford wrote: > 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. > rculling@sysd.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruno LEVEQUE System Engineer SARL NET6D bruno.leveque@net6d.com http://www.net6d.com
Rich Cullingford <rculling@sysd.com> writes:
> 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
Tom Lane wrote:
> Rich Cullingford <rculling@sysd.com> writes:
>
>>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?)?
>
> 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.
Rich Cullingford <rculling@sysd.com> writes:
> 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.
> I'm assuming that you don't mean:
> --superuser=username
> which seems to be for disabling triggers, but perhaps the following as a
> parameter to pg_dumpall:
> --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
Tom Lane wrote:
> Rich Cullingford <rculling@sysd.com> writes:
>
>>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.
>
>
>>I'm assuming that you don't mean:
>>--superuser=username
>>which seems to be for disabling triggers, but perhaps the following as a
>>parameter to pg_dumpall:
>>--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
Rich Cullingford <rculling@sysd.com> writes:
> 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
Rich Cullingford wrote: > 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. > 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. > 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.