From: raghu ram <raghuchennuru@gmail.com>
To: Senthil Kumar G <senthil@zoniac.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: DB Import Error...
Date: Fri, 1 Apr 2011 20:57:11 +0530
Message-ID: <AANLkTimR3HXVar+U4BXindhRQOeorRz467xQbLdhf5FC@mail.gmail.com> (raw)
In-Reply-To: <201104011504.p31F4K7q030205@rs73.luxsci.com>
References: <201104011504.p31F4K7q030205@rs73.luxsci.com>
On Fri, Apr 1, 2011 at 8:34 PM, Senthil Kumar G <senthil@zoniac.com> 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” for relation "xxxx” 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 <dump file
location> <database name>
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 => port number of old cluster
-Fc => compressed format
-v => verbose output
-f => 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
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: raghuchennuru@gmail.com, senthil@zoniac.com
Subject: Re: DB Import Error...
In-Reply-To: <AANLkTimR3HXVar+U4BXindhRQOeorRz467xQbLdhf5FC@mail.gmail.com>
References: <201104011504.p31F4K7q030205@rs73.luxsci.com> <AANLkTimR3HXVar+U4BXindhRQOeorRz467xQbLdhf5FC@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox