Message-ID: <4D2E2132.2050409@hindu.org>
Date: Wed, 12 Jan 2011 11:46:26 -1000
From: Sivakatirswami <katir@hindu.org>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.8) Gecko/20100802 Lightning/1.0b2 Thunderbird/3.1.2
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Help with Restoring 8.01 databases to 9.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

  I'm in some deep trouble here...

We've been running 8.1 on our CentOS 5 linux web server for many years. 
Not having some of the features of 9.0 was starting to become a serious 
issue, so I put in an order to the ServerPath data team to upgrade to 
PostGreSQL 9.0

This worked, was a bit rocky but they did it. Now I'm on my own as their 
services really are hosting and the OS and when it to higher level 
things, that falls back on us.

First: they they forgot to restore /var/lib/psql/data/pg_hba.conf

Of course I had a back up of that and so when I restore it then at least 
my controlPanel (VirtualMin-WebMin) was able to log into psgl

so then I set about try to restore databases from .sql dumps from 8.1 
via the command line, I log in su postgres and try:

psql -d postgres -f /home/dbase-backups/htde.sql

So at first I'm told "role sadhu is missing" so I go to WebMin and 
create the PostGres user "sadhu
  and try again..

Now I'm getting the following and at this point I totally do not know 
what to do next.

I tried to restore a few others and most are throwing this duplicate key 
value violation.

Any help will be very much appreciated...

psql:/home/dbase-backups/htde.sql:191: ERROR:  relation "templates" 
already exists
ALTER TABLE
psql:/home/dbase-backups/htde.sql:355726: ERROR:  invalid byte sequence 
for encoding "UTF8": 0x80
CONTEXT:  COPY event, line 157477
psql:/home/dbase-backups/htde.sql:355752: ERROR:  duplicate key value 
violates unique constraint "event_type_pkey"
DETAIL:  Key (event_code)=(AU) already exists.
CONTEXT:  COPY event_type, line 1: "AU    Authenticate. Logged when the 
user authenticates with the server."
psql:/home/dbase-backups/htde.sql:359550: ERROR:  duplicate key value 
violates unique constraint "items_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY items, line 1: "1    Global Dharma    pdf    226    
http://www.hinduismtoday.com/archives/2009/7-9/pdf/Hinduism-Today_Jul-Aug-Se..."
psql:/home/dbase-backups/htde.sql:359571: ERROR:  duplicate key value 
violates unique constraint "links_pkey"
DETAIL:  Key (id)=(1) already exists.
CONTEXT:  COPY links, line 1: "1    Himalayan Academy    
http://www.himalayanacademy.com    Himalayan Academy    2009-11-09 15:31:15"
psql:/home/dbase-backups/htde.sql:359811: ERROR:  duplicate key value 
violates unique constraint "magazines_pkey"
DETAIL:  Key (id)=(224) already exists.
CONTEXT:  COPY magazines, line 1: "224    2009-01-01    
cover_2009-01-01.jpg    ?    2009-11-09 15:31:15    2009    
1-January/February/March"
psql:/home/dbase-backups/htde.sql:372564: ERROR:  duplicate key value 
violates unique constraint "subscriber_pkey"
DETAIL:  Key (user_id)=(44) already exists.
CONTEXT:  COPY subscriber, line 1: "44    Anil    Badhwar    
abadhwar@gmail.com    Birth month    April    NO    2006-03-27\N    
\N    2006-03-27 13:52:49-08    \N    \..."
psql:/home/dbase-backups/htde.sql:372577: ERROR:  duplicate key value 
violates unique constraint "templates_pkey"
DETAIL:  Key (title)=(Youtube) already exists.
CONTEXT:  COPY templates, line 1: "Youtube    video/youtube 
<html>\n<head>\n</head>\n<body bgcolor="#EFE3C5">\n<center>\n<br 
/>\n<div id="..."
psql:/home/dbase-backups/htde.sql:372585: ERROR:  multiple primary keys 
for table "event_type" are not allowed
psql:/home/dbase-backups/htde.sql:372593: ERROR:  multiple primary keys 
for table "items" are not allowed
psql:/home/dbase-backups/htde.sql:372601: ERROR:  multiple primary keys 
for table "links" are not allowed
psql:/home/dbase-backups/htde.sql:372609: ERROR:  multiple primary keys 
for table "magazines" are not allowed
psql:/home/dbase-backups/htde.sql:372617: ERROR:  multiple primary keys 
for table "subscriber" are not allowed
psql:/home/dbase-backups/htde.sql:372625: ERROR:  multiple primary keys 
for table "templates" are not allowed
psql:/home/dbase-backups/htde.sql:372633: ERROR:  constraint "$1" for 
relation "event" already exists
psql:/home/dbase-backups/htde.sql:372641: ERROR:  constraint "$2" for 
relation "event" already exists
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT

Aum Shanti,

Sivakatirswami
Kauai's Hindu Monastery

www.himalayanacademy.com
www.hinduismtoday.com