pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with Restoring 8.01 databases to 9.0
2+ messages / 2 participants
[nested] [flat]
* Help with Restoring 8.01 databases to 9.0
@ 2011-01-12 21:46 Sivakatirswami <katir@hindu.org>
0 siblings, 1 reply; 2+ messages in thread
From: Sivakatirswami @ 2011-01-12 21:46 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help with Restoring 8.01 databases to 9.0
@ 2011-01-13 22:23 DM <dm.aeqa@gmail.com>
parent: Sivakatirswami <katir@hindu.org>
0 siblings, 0 replies; 2+ messages in thread
From: DM @ 2011-01-13 22:23 UTC (permalink / raw)
To: Sivakatirswami <katir@hindu.org>; +Cc: 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@hindu.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@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
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-01-13 22:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-12 21:46 Help with Restoring 8.01 databases to 9.0 Sivakatirswami <katir@hindu.org>
2011-01-13 22:23 ` DM <dm.aeqa@gmail.com>
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