Skip site navigation (1) Skip section navigation (2)

database restore problem

From: Chris Collins <cc745241(at)albany(dot)edu>
To: pgadmin-support(at)postgresql(dot)org
Subject: database restore problem
Date: 2010-04-22 17:22:59
Message-ID: u2w27e140281004221022p1024ed51s810cf349bc778816@mail.gmail.com (view raw or flat)
Thread:
Lists: pgadmin-support
Greetings wise and knowledgeable citizens of the interwebs,

I am a relative newbie to databases. In a nutshell, I'm having trouble
restoring my backup of a database. Some of the data was exported and put in
spreadsheets, where it is safely stored, but some was not, and I would like
to get that data back.

Now the details: I had a database that I was using to store data from a
radio reciever. It was basically installed and set up by other people, and
now I've inherited it and it's my problem. The data was radio signal
strength data from radio collared mice (I'm a biologist). All went well, and
by the end of the summer I had a nice pile of data.

We decided to move the whole system to another computer, so we created a
backup of the database, using the "backup" function in PGAdmin. I just used
the dropdown menu and foolishly assumed that if I told it to backup the
database that it would, and everything would be fine when I tried to open
the file. I didn't pay much attention to the settings (because I'm a
newbie), and probably just used the defaults. Then we installed PGAdmin and
PostgreSQL (same versions, 8.2.4 and 1.6.3 respectively) on the new
computer, and we're trying to restore the database from the backup file. The
original computer and the original database have since died, so all that is
left of the database is this backup file.

This is the error message I get:

 Quote:
  C:\Program Files\PostgreSQL\8.2\bin\pg_restore.exe -i -h localhost -p 5432
-U postgres -d postgres -a --disable-triggers -v "C:\Documents and
Settings\pineBushAdmin\Desktop\database backup\5-6-08(9-25-08).backup"
pg_restore: connecting to database for restore
pg_restore: executing SEQUENCE SET alert_requests_request_id_seq
pg_restore: executing SEQUENCE SET alert_types_type_id_seq
pg_restore: executing SEQUENCE SET alert_users_user_id_seq
pg_restore: executing SEQUENCE SET animals_id_seq
pg_restore: executing SEQUENCE SET antennatypes_id_seq
pg_restore: executing SEQUENCE SET arudeploys_id_seq
pg_restore: executing SEQUENCE SET arus_id_seq
pg_restore: executing SEQUENCE SET bursts_id_seq
pg_restore: executing SEQUENCE SET channelsettings_id_seq
pg_restore: executing SEQUENCE SET commtypes_id_seq
pg_restore: executing SEQUENCE SET deployments_id_seq
pg_restore: executing SEQUENCE SET people_id_seq
pg_restore: executing SEQUENCE SET sensors_id_seq
pg_restore: executing SEQUENCE SET species_id_seq
pg_restore: executing SEQUENCE SET transmittermodes_id_seq
pg_restore: executing SEQUENCE SET transmitters_id_seq
pg_restore: disabling triggers for alert_requests
pg_restore: restoring data for table "alert_requests"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1823; 0 26132 TABLE DATA
alert_requests postgres
pg_restore: [archiver (db)] could not execute query: ERROR: column
"user_name" of relation "alert_requests" does not exist
Command was: COPY alert_requests (request_id, type_id, animal_id, is_active,
last_event, event_time_span, alert_action, user_name, table_...
pg_restore: enabling triggers for alert_requests
pg_restore: disabling triggers for alert_types
pg_restore: restoring data for table "alert_types"
pg_restore: [archiver (db)] Error from TOC entry 1824; 0 26139 TABLE DATA
alert_types postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "pk_alert_types_1"
CONTEXT: COPY alert_types, line 1: "1 no signal"
pg_restore: enabling triggers for alert_types
pg_restore: disabling triggers for alert_users
pg_restore: restoring data for table "alert_users"
pg_restore: enabling triggers for alert_users
pg_restore: disabling triggers for animals
pg_restore: restoring data for table "animals"
pg_restore: [archiver (db)] Error from TOC entry 1826; 0 26147 TABLE DATA
animals postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "newanimals_pkey"
CONTEXT: COPY animals, line 1: "1 1 1 3"
pg_restore: enabling triggers for animals
pg_restore: disabling triggers for antennas
pg_restore: restoring data for table "antennas"
pg_restore: [archiver (db)] Error from TOC entry 1827; 0 26151 TABLE DATA
antennas postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "antennas_pkey"
CONTEXT: COPY antennas, line 1: "1 2 1 0 2007-08-31 00:00:00 1 10"
pg_restore: enabling triggers for antennas
pg_restore: disabling triggers for antennatypes
pg_restore: restoring data for table "antennatypes"
pg_restore: [archiver (db)] Error from TOC entry 1828; 0 26154 TABLE DATA
antennatypes postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "antennatypes_pkey"
CONTEXT: COPY antennatypes, line 1: "1 Omni directional - Ringo Ranger"
pg_restore: enabling triggers for antennatypes
pg_restore: disabling triggers for arucalibration
pg_restore: restoring data for table "arucalibration"
pg_restore: [archiver (db)] Error from TOC entry 1829; 0 26158 TABLE DATA
arucalibration postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "pk_arucalibration"
CONTEXT: COPY arucalibration, line 1: "1017 1017cal9.cal 1 1"
pg_restore: enabling triggers for arucalibration
pg_restore: disabling triggers for arudeploys
pg_restore: restoring data for table "arudeploys"
pg_restore: enabling triggers for arudeploys
pg_restore: disabling triggers for arulocs
pg_restore: restoring data for table "arulocs"
pg_restore: [archiver (db)] Error from TOC entry 1831; 0 26164 TABLE DATA
arulocs postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "newarulocs_pkey"
CONTEXT: COPY arulocs, line 1: "1 fall07grid pinebush 593866 4730021 18t
42.71694 -73.85360"
pg_restore: enabling triggers for arulocs
pg_restore: disabling triggers for aruopcond
pg_restore: restoring data for table "aruopcond"
pg_restore: enabling triggers for aruopcond
pg_restore: disabling triggers for arus
pg_restore: restoring data for table "arus"
pg_restore: [archiver (db)] Error from TOC entry 1833; 0 26169 TABLE DATA
arus postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "aru_pkey"
CONTEXT: COPY arus, line 1: "1017 1017"
pg_restore: enabling triggers for arus
pg_restore: disabling triggers for bursts
pg_restore: restoring data for table "bursts"
pg_restore: [archiver (db)] Error from TOC entry 1834; 0 26173 TABLE DATA
bursts postgres
pg_restore: [archiver (db)] COPY failed: ERROR: null value in column "burst"
violates not-null constraint
CONTEXT: COPY bursts, line 1: "91844 14 2000-01-01 00:04:00 \N \N \N"
pg_restore: enabling triggers for bursts
pg_restore: disabling triggers for channelsettings
pg_restore: restoring data for table "channelsettings"
pg_restore: enabling triggers for channelsettings
pg_restore: disabling triggers for commtypes
pg_restore: restoring data for table "commtypes"
pg_restore: [archiver (db)] Error from TOC entry 1836; 0 26182 TABLE DATA
commtypes postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "commtypes_pkey"
CONTEXT: COPY commtypes, line 1: "1 freewave"
pg_restore: enabling triggers for commtypes
pg_restore: disabling triggers for deployments
pg_restore: restoring data for table "deployments"
pg_restore: enabling triggers for deployments
pg_restore: disabling triggers for nosignals
pg_restore: restoring data for table "nosignals"
pg_restore: enabling triggers for nosignals
pg_restore: disabling triggers for oldantennas
pg_restore: restoring data for table "oldantennas"
pg_restore: enabling triggers for oldantennas
pg_restore: disabling triggers for oldarudeploys
pg_restore: restoring data for table "oldarudeploys"
pg_restore: enabling triggers for oldarudeploys
pg_restore: disabling triggers for olddeployments
pg_restore: restoring data for table "olddeployments"
pg_restore: enabling triggers for olddeployments
pg_restore: disabling triggers for people
pg_restore: restoring data for table "people"
pg_restore: [archiver (db)] Error from TOC entry 1842; 0 26198 TABLE DATA
people postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "newpeople_pkey"
CONTEXT: COPY people, line 1: "1 Chris grad student 2007-08-30 \N"
pg_restore: enabling triggers for people
pg_restore: disabling triggers for pulses
pg_restore: restoring data for table "pulses"
pg_restore: enabling triggers for pulses
pg_restore: disabling triggers for realfreqs
pg_restore: restoring data for table "realfreqs"
pg_restore: enabling triggers for realfreqs
pg_restore: disabling triggers for sensors
pg_restore: restoring data for table "sensors"
pg_restore: enabling triggers for sensors
pg_restore: disabling triggers for sexes
pg_restore: restoring data for table "sexes"
pg_restore: [archiver (db)] Error from TOC entry 1846; 0 26210 TABLE DATA
sexes postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "newsexes_pkey"
CONTEXT: COPY sexes, line 1: "1 male"
pg_restore: enabling triggers for sexes
pg_restore: disabling triggers for signals
pg_restore: restoring data for table "signals"
pg_restore: enabling triggers for signals
pg_restore: disabling triggers for species
pg_restore: restoring data for table "species"
pg_restore: [archiver (db)] Error from TOC entry 1848; 0 26214 TABLE DATA
species postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "species_pkey"
CONTEXT: COPY species, line 1: "1 Animalia Rodentia Peromyscus sp. Deer
Mouse"
pg_restore: enabling triggers for species
pg_restore: disabling triggers for transmittermodes
pg_restore: restoring data for table "transmittermodes"
pg_restore: [archiver (db)] Error from TOC entry 1849; 0 26218 TABLE DATA
transmittermodes postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "transmittermodes_pkey"
CONTEXT: COPY transmittermodes, line 1: "1 pulsed"
pg_restore: enabling triggers for transmittermodes
pg_restore: disabling triggers for transmitters
pg_restore: restoring data for table "transmitters"
pg_restore: [archiver (db)] Error from TOC entry 1850; 0 26222 TABLE DATA
transmitters postgres
pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates
unique constraint "transmitters_pkey"
CONTEXT: COPY transmitters, line 9: "1 109216 none 164541000 Holohil bn2nc 1
160 1 50 \N 1 3300 20 1"
pg_restore: enabling triggers for transmitters
WARNING: errors ignored on restore: 15

Process returned exit code 1.


So it seems to be a "duplicate key" problem (whatever that means...did I
mention I'm a newbie?). is this fixable? are there other ways I can get at
this data?

-- 
Christopher Collins
New York State Museum
3140 Cultural Education Center
Albany, NY 12230
Work: (518) 486 2025
Cell: (518) 605 5614

Responses

pgadmin-support by date

Next:From: Benjamin KrajmalnikDate: 2010-04-23 18:04:41
Subject: Compiling pgAgent on FreeBSD 8
Previous:From: Fernando HeviaDate: 2010-04-22 15:28:13
Subject: Re: [GENERAL] Byte order mark added by (the envelope please...) pgAdmin3 !!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group