Re: [ADMIN] Moving a database AND changing the superuser
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] 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. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Moving a database AND changing the superuser
W licie 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Moving a database AND changing the superuser
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. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruno LEVEQUE System Engineer SARL NET6D [EMAIL PROTECTED] http://www.net6d.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Moving a database AND changing the superuser
Rich Cullingford [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] Moving a database AND changing the superuser
Tom Lane wrote: Rich Cullingford [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Moving a database AND changing the superuser
Rich Cullingford [EMAIL PROTECTED] 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 ---(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
Re: [ADMIN] Moving a database AND changing the superuser
Tom Lane wrote: Rich Cullingford [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Moving a database AND changing the superuser
Rich Cullingford [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
