Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 6C8F31337B80 for ; Fri, 1 Apr 2011 12:27:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 73836-02-9 for ; Fri, 1 Apr 2011 15:27:13 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com [209.85.216.46]) by mail.postgresql.org (Postfix) with ESMTP id 0064B1337B6E for ; Fri, 1 Apr 2011 12:27:12 -0300 (ADT) Received: by qwk3 with SMTP id 3so2210196qwk.19 for ; Fri, 01 Apr 2011 08:27:11 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type; bh=hnu5tMmJpaRfSkRa1RCEnl1LbhK4UZll6BuQH1oLqI4=; b=A/2uTIdBj+4VbG3R9R52LCa25lLNw6N+wu3QNyl2WwSZWzXVwJwQGuQf5OQpv+VuAO gsEn5a5gpx7WdJS0nKvbNhG1HQUmHnL3NY+7hvD7AG/Pr/QKov3bRho34A11Qa+OzrNh Jnsx467Dxqop4nbQwkZbVPREU39DUKt/gjlnA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=C1SQV5WhTrkRvK8xyz7A1Z0iEqSjopst6/JvKQuQoXG4zIAv51L5mrMm4kmgMCwMFO zZM0cNIjIv8DzHbmgu4+Cgd59ysC8WxeK5Ug2mX/KhS+IvM+Pp5uizNlS4ospY14J3i6 hUUsUyYTvj9tla3ZLd2Nhl44VwdPE5IRfu8n8= MIME-Version: 1.0 Received: by 10.229.126.133 with SMTP id c5mr3573887qcs.90.1301671631671; Fri, 01 Apr 2011 08:27:11 -0700 (PDT) Received: by 10.229.119.135 with HTTP; Fri, 1 Apr 2011 08:27:11 -0700 (PDT) In-Reply-To: <201104011504.p31F4K7q030205@rs73.luxsci.com> References: <201104011504.p31F4K7q030205@rs73.luxsci.com> Date: Fri, 1 Apr 2011 20:57:11 +0530 Message-ID: Subject: Re: DB Import Error... From: raghu ram To: Senthil Kumar G Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=000e0cdfcd6838530f049fdd0bd0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201104/24 X-Sequence-Number: 37205 --000e0cdfcd6838530f049fdd0bd0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable On Fri, Apr 1, 2011 at 8:34 PM, Senthil Kumar G wrote: > Hi > > > > I am upgrading my staging environment from postgresql 8.2.3 to 9.0.3. > > > > I was able to do successfully. > > > > But, when i try to import the database which is created in 8.2.0 version = to > 9.0.3 version environment, i am getting following error. > > > > ERROR: constraint "xxxx=94 for relation "xxxx=94 already exist > Based on above error message it looks like the constraint for relation already exists. Could you please follow below steps to upgrade from older version to new version:: *Step 1: * Perform the global dump on *old cluster [ i.e PostgreSQL 8.2.3]*using "pg_dumpall" binary of new PostgreSQL 9.0. /opt/PostgreSQL/9.0/bin/pg_dumpall -d postgres -U postgres *-p 5432* -g > /tmp/globaldump_oldpg823.sql *NOTE: * It Dumps only global objects i.e roles,users and tablespaces,no databases. Assuming old cluster running on the 5432 port number. *Step 2: * Take the dump of the database in *compressed format* using new version of pg_dump binary. Syntax: /opt/PostgreSQL/9.0/bin/pg_dump -Fc -v *-p 5432* -f nohup /opt/PostgreSQL/9.0/bin/pg_dump -Fc -v -p 5432 -f /tmp/demo.dmp demo>= > /tmp/dump.log 2>> /tmp/dump.log & -P =3D> port number of old cluster -Fc =3D> compressed format -v =3D> verbose output -f =3D> location of dump file to store and dump file name *NOTE: * Assuming old cluster running on the 5432 port number *Step 3: * Restore the global dump on new cluster of PostgreSQL 9.0.2 /opt/PostgreSQL/9.0/bin/psql -d postgres -U postgres -p 5433 -f /tmp/globaldump_oldpg832.sql *NOTE: * Assuming new cluster running on the 5433 port number *Step 4: * Restore the compressed dump file using new version of pg_restore binary *with parallel restore* operation. nohup /opt/PostgreSQL/9.0/bin/pg_restore -Fc -v -p 5433 -d demo -j 8 /tmp/demo.dmp >>/tmp/restore1.log 2>>/tmp/restore1.log & *NOTE: * Assuming new cluster running on the 5433 port number --Raghu Ram > > > when i query it was not found. > > > > What could be the reason? What should i do to resolve this? > > > > Appreciate your quick answer. > > > > Thanks & Regards > > Senthil > > > --000e0cdfcd6838530f049fdd0bd0 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable

On Fri, Apr 1, 2011 at 8:34 PM, Senthil = Kumar G <senthil= @zoniac.com> wrote:

Hi

=A0

I am upgrading my staging environment from p= ostgresql 8.2.3 to 9.0.3.

=A0

I was able to do successfully.=

=A0

But, when i try to import the database which= is created in 8.2.0 version to 9.0.3 version environment, i am getting following error.

=A0

ERROR:=A0 constraint= "xxxx=94 for relation "xxxx=94 already exist


Based on above error message it looks like the constraint for relation already exists.

Coul= d you please follow below steps to upgrade from older version to new version::

S= tep 1: Perform the global dump on old cluster [ i.e PostgreSQL 8.2.3] using "pg_dumpall" binary of new PostgreSQL 9.0.

/opt/PostgreSQL/9.0/bin/pg_dumpall -d postgres -U postgres -p 5432 -g > /tmp/globaldump_oldpg823.sql

N= OTE: It Dumps only global objects i.e roles,users and tablespaces,no databases.

= =20 Assuming old cluster running on the 5432 port number.

S= tep 2: Take the dump of the database in compressed format using new version of pg_dump binary.

Syntax: /opt/PostgreSQL/9.0/bin/pg_dump -Fc -v -p 5432 -f <dump file location> <database name>

nohu= p /opt/PostgreSQL/9.0/bin/pg_dump -Fc -v -p 5432 -f /tmp/demo.dmp demo>> /tmp/dump.log 2>> /tmp/dump.log &

-P =3D> port number of old cluster

-Fc =3D> compressed format

-v =3D> verbose output

-f =3D> location of dump file to store and dump file name

N= OTE: Assuming old cluster running on the 5432 port number

S= tep 3: Restore the global dump on new cluster of PostgreSQL 9.0.2

/opt= /PostgreSQL/9.0/bin/psql -d postgres -U postgres -p 5433 -f /tmp/globaldump_oldpg832.sql

N= OTE: Assuming new cluster running on the 5433 port number

S= tep 4: Restore the compressed dump file using new version of pg_restore binary with parallel restore operation.

nohu= p /opt/PostgreSQL/9.0/bin/pg_restore -Fc -v -p 5433 -d demo -j 8 /tmp/demo.dmp >>/tmp/restore1.log 2>>/tmp/restore1.log &

N= OTE: Assuming new cluster running on the 5433 port number

--= Raghu Ram



=A0

=A0

when i query it was not found.=

=A0

What could be the reason? What should i do t= o resolve this?

=A0

Appreciate your quick answer.<= /p>

=A0

Thanks & Regards=

Senthil

=A0


--000e0cdfcd6838530f049fdd0bd0--