| From: | DM <dm(dot)aeqa(at)gmail(dot)com> |
|---|---|
| To: | Sivakatirswami <katir(at)hindu(dot)org> |
| Cc: | pgsql-admin(at)postgresql(dot)org |
| Subject: | Re: Help with Restoring 8.01 databases to 9.0 |
| Date: | 2011-01-13 22:23:22 |
| Message-ID: | AANLkTi=+TGRyvxqpA_hWPfMUKideULdf0ZdsP56BhnyU@mail.gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
Based on the below error message it looks like the Database and tables
already exists. Please check the database.
When you do a regular pg_dump, it won't include all the roles. You to
include roles have should always use pg_dumpall -l ( you can read up more
using #man pg_dumpall).
Good luck!
~Deepak
On Wed, Jan 12, 2011 at 1:46 PM, Sivakatirswami <katir(at)hindu(dot)org> wrote:
> 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(at)gmail(dot)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
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Chris Browne | 2011-01-13 22:52:47 | Re: preventing transaction wraparound |
| Previous Message | Devrim GÜNDÜZ | 2011-01-13 22:17:24 | Re: rpm failure |