X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 48E459DCAC4 for ; Sat, 17 Dec 2005 00:50:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 35209-08 for ; Sat, 17 Dec 2005 00:50:47 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 30F079DC883 for ; Sat, 17 Dec 2005 00:50:46 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id jBH4ojta021825 for ; Sat, 17 Dec 2005 04:50:45 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id jBH4cNKU002328 for pgsql-admin@postgresql.org; Sat, 17 Dec 2005 04:38:23 GMT (envelope-from news) From: Christopher Browne X-Newsgroups: pgsql.admin Subject: Re: Moving a database Date: Fri, 16 Dec 2005 23:09:14 -0500 Organization: cbbrowne Computing Inc Lines: 35 Message-ID: References: <98f7d3480512140944s6763d22ao65b9565a55735459@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Jumbo Shrimp, berkeley-unix) Cancel-Lock: sha1:LwL3+Pwv2qJ9uang1tAMIODSeLQ= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=-0.957 required=5 tests=[ALL_TRUSTED=-1.44, AWL=-0.330, INFO_TLD=0.813] X-Spam-Score: -0.957 X-Spam-Level: X-Archive-Number: 200512/179 X-Sequence-Number: 19899 > 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