Re: [ADMIN] Moving a database
If they are both up and running and reachable, then you likely just want to do something as simple as: pg_dumpall -h old host | psql -h new host template1 That's assuming you want to move all the DBs on the first server to the second. NOTE: pg_dumpall cannot dump large objects. If you have these, you'll have to use another method of migration. Another good method if you want to limit downtime and have primary keys on all your tables is to setup slony on both nodes, let the subscription get up to date and then change roles and bring down the old server. On Wed, 14 Dec 2005, Richard Bortolucci wrote: Hi, What are the correct steps to move an database and from an server running postgreslq 7.4.2 to another running 8.0.3? -- Richard Bortolucci -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Moving a database
What are the correct steps to move an database and from an server running postgreslq 7.4.2 to another running 8.0.3? I'll assume there are two hosts: - db7, running 7.4.2, on port 5432, and - db8, running 8.0.3 on port 5432. The simplest method would be thus: - Stop the applications accessing db7 - On db8, run the command: pg_dumpall -h db7 -p 5432 | psql -h localhost -p 5432 template1 That is likely to provide the most nearly perfect fidelity copy of the database on db8. You may discover that this takes longer than you want it to. If that proves to be the case, you can shorten the cutover time by using a replication system such as Slony-I to copy data from the one host to the other. That definitely involves more moving parts and more complexity. But it could turn an 8 hour outage into a 2 minute one... You should certainly do test runs of whatever approach you try so as to minimize the number of surprises. -- output = reverse(moc.liamg @ enworbbc) http://linuxdatabases.info/info/slony.html ...Yet terrible as Unix addiction is, there are worse fates. If Unix is the heroin of operating systems, then VMS is barbiturate addiction, the Mac is MDMA, and MS-DOS is sniffing glue. (Windows is filling your sinuses with lucite and letting it set.) You owe the Oracle a twelve-step program. --The Usenet Oracle ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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]
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
Re: [ADMIN] Moving a database
Ewan Leith [EMAIL PROTECTED] writes: An example is pg_shadow which is read on both file systems whenever someone seems to authenticate, but only updated on the new file system. I don't believe it for a minute. Please describe the actual problem you're having, rather than jumping to conclusions about the mechanism. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
