From: Senthil Kumar G <senthil@zoniac.com>
To: 'raghu ram' <raghuchennuru@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: DB Import Error...
Date: Fri, 1 Apr 2011 21:15:59 +0530
Message-ID: <201104011545.p31Fjqax000615@rs73.luxsci.com> (raw)
In-Reply-To: <AANLkTimR3HXVar+U4BXindhRQOeorRz467xQbLdhf5FC@mail.gmail.com>
Thanks raghu ram for your immediate response,
i will try this path too along with my team and i will come back if any
issues.
Thanks
Senthil
_____
From: raghu ram [mailto:raghuchennuru@gmail.com]
Sent: Friday, April 01, 2011 8:57 PM
To: Senthil Kumar G
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] DB Import Error...
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: senthil@zoniac.com, raghuchennuru@gmail.com
Subject: Re: DB Import Error...
In-Reply-To: <201104011545.p31Fjqax000615@rs73.luxsci.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