On Fri, Apr 1, 2011 at 8:34 PM, Senthil Kumar G <[email protected]> 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 > > >
