Re: DB Import Error...

On Fri, Apr 1, 2011 at 8:34 PM, Senthil Kumar G <senthil(at)zoniac(dot)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
>
>
>