X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E0B915E46DC for ; Tue, 24 Aug 2004 14:33:36 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82163-07 for ; Tue, 24 Aug 2004 17:33:32 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 32E035E46C2 for ; Tue, 24 Aug 2004 14:33:32 -0300 (ADT) Received: (qmail 24919 invoked by uid 500); 24 Aug 2004 17:42:08 -0000 Date: Tue, 24 Aug 2004 12:42:08 -0500 From: Bruno Wolff III To: Mario Thomas Cc: pgsql-admin@postgresql.org Subject: Re: PostgreSQL Migration of databases 7.2 to 7.4 Message-ID: <20040824174208.GA24597@wolff.to> Mail-Followup-To: Mario Thomas , pgsql-admin@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/234 X-Sequence-Number: 14629 On Tue, Aug 24, 2004 at 07:25:46 +0100, Mario Thomas wrote: > Hello All, > > We have a server in our datacentre running PostgreSQL 7.2 with about 50 > databases set up (for different customers). We have just commissioned a > much better specified server which is running PostgreSQL 7.4. > > We need to migrate the databases from 7.2 to 7.4. The smallest database > contains about 200K of data whilst the largest database weighs in at > about 100Mb. > > What's the best way to migrate the data? Would you suggest using the > pgdump tools? We've found them to be quite problematic in use and are > not entirely sure of the best command line switches to use. If you use the pg_dump(all) client from 7.4 to dump the 7.2 database, you will probably find that it works a lot better with handling dependencies than the 7.2 pg_dump(all) client did. > > We need to make sure the data and permissions get migrated, as the data > is being used for live web sites. So obviously speed of migration is > important as we will have a period of downtime to complete the > migrations. You might look at Slony I, though I think it only works on versions back to 7.3. If it works back to 7.2, then it could be used to sync your new system up, allowing for a very short cutover time. > > Any suggestions/product ideas would be greatly appreciated. > Thanks in advance, > > Mario