Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B91029F9ED9 for ; Wed, 15 Aug 2007 08:40:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 30196-09 for ; Wed, 15 Aug 2007 08:40:21 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 0E53A9F9B94 for ; Wed, 15 Aug 2007 08:40:28 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1ILHEg-0004YD-Ct for pgsql-admin@postgresql.org; Wed, 15 Aug 2007 13:40:26 +0200 Received: from pirus.securax.be ([213.16.46.130]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 15 Aug 2007 13:40:26 +0200 Received: from milen by pirus.securax.be with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 15 Aug 2007 13:40:26 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: "Milen A. Radev" Subject: Re: Owner Issue When Migrating Database to New Server Date: Wed, 15 Aug 2007 14:40:16 +0300 Lines: 46 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: pirus.securax.be User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/172 X-Sequence-Number: 26503 Campbell, Lance написа: > PostgreSQL: 8.2.4 > > OS: Linux Red Hat 4.x 64 bit > > > > I have a database running on serverA with postgres user userA. I want > to migrate the database to ServerB with a postgres user userB. Notice > the users are not the same. I did the following to put the database on > the new server: > > > > 1) I created the database on serverB with userB. > > 2) I then did pg_dump -h serverA -U userA dbname | psql -h serverB > -U userB dbname > > > > This of course failed. The user userA was not in the database found on > serverB. I then did the following: > > > > 1) I created the database on serverB with userB. > > 2) On serverB I did pg_dump -h serverA -U userA dbname > > postgreSQL.out > > 3) I then did a find and replace on userA with userB through NANO > on the file postgreSQL.out > > 4) I then did psql -h serverB -U userB dbname -f postgreSQL.out > > > > Is there a better way to do this? > Yes - use the "--no-owner" option for "pg_dump" when creating the dump file. -- Milen A. Radev