pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
>
>
>

view thread (13+ messages)  latest in thread

Message-ID: <AANLkTimR3HXVar+U4BXindhRQOeorRz467xQbLdhf5FC@mail.gmail.com>
Permalink:  ../AANLkTimR3HXVar+U4BXindhRQOeorRz467xQbLdhf5FC@mail.gmail.com/
Also on:    postgresql.org/message-id/AANLkTimR3HXVar+U4BXindhRQOeorRz467xQbLdhf5FC@mail.gmail.com

 · 

reply

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