Re: database restore problem
От | Fernando Hevia |
---|---|
Тема | Re: database restore problem |
Дата | |
Msg-id | h2xb9e2597c1004260610w41e35610w839d1f411ecaf781@mail.gmail.com обсуждение исходный текст |
Ответ на | database restore problem (Chris Collins <cc745241@albany.edu>) |
Список | pgadmin-support |
Hi Chris. It seems you preformed a data-only backup as the log provided doesn't show DDL sentences.<br />Having done so couldface you with two problems:<br />1. The destination database schema is different than the one where you performed thebackup. The error reported on table alert_request seems to confirm this.<br /> 2. You probably already have data on thedestination database which triggers the unique key contraints.<br /><br />If this proves to be the case you should checkyour schema is identical to the original one. Then truncate all tables before restoring data.<br /><br />Regards,<br/>Fernando.<br /><br /><div class="gmail_quote">On Thu, Apr 22, 2010 at 14:22, Chris Collins <span dir="ltr"><<ahref="mailto:cc745241@albany.edu">cc745241@albany.edu</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"><span>Greetingswise and knowledgeable citizens of the interwebs,<br /><br /> I am a relative newbie to databases. Ina 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.<br /><br /> 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 otherpeople, 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. <br /><br /> We decided tomove 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 everythingwould 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 andthe original database have since died, so all that is left of the database is this backup file.<br /><br /> This is theerror message I get: <br /><br /><div style="margin: 5px 20px 20px;"><div style="margin-bottom: 2px;">Quote:</div><tableborder="0" cellpadding="6" cellspacing="0" width="100%"><tbody><tr><td style="border: 1px inset;">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"<br /> pg_restore:connecting to database for restore<br /> pg_restore: executing SEQUENCE SET alert_requests_request_id_seq<br />pg_restore: executing SEQUENCE SET alert_types_type_id_seq<br /> pg_restore: executing SEQUENCE SET alert_users_user_id_seq<br/> pg_restore: executing SEQUENCE SET animals_id_seq<br /> pg_restore: executing SEQUENCE SET antennatypes_id_seq<br/> pg_restore: executing SEQUENCE SET arudeploys_id_seq<br /> pg_restore: executing SEQUENCE SET arus_id_seq<br/> pg_restore: executing SEQUENCE SET bursts_id_seq<br /> pg_restore: executing SEQUENCE SET channelsettings_id_seq<br/> pg_restore: executing SEQUENCE SET commtypes_id_seq<br /> pg_restore: executing SEQUENCE SETdeployments_id_seq<br /> pg_restore: executing SEQUENCE SET people_id_seq<br /> pg_restore: executing SEQUENCE SET sensors_id_seq<br/> pg_restore: executing SEQUENCE SET species_id_seq<br /> pg_restore: executing SEQUENCE SET transmittermodes_id_seq<br/> pg_restore: executing SEQUENCE SET transmitters_id_seq<br /> pg_restore: disabling triggersfor alert_requests<br /> pg_restore: restoring data for table "alert_requests"<br /> pg_restore: [archiver (db)]Error while PROCESSING TOC:<br /> pg_restore: [archiver (db)] Error from TOC entry 1823; 0 26132 TABLE DATA alert_requestspostgres<br /> pg_restore: [archiver (db)] could not execute query: ERROR: column "user_name" of relation "alert_requests"does not exist<br /> Command was: COPY alert_requests (request_id, type_id, animal_id, is_active, last_event,event_time_span, alert_action, user_name, table_...<br /> pg_restore: enabling triggers for alert_requests<br/> pg_restore: disabling triggers for alert_types<br /> pg_restore: restoring data for table "alert_types"<br/> pg_restore: [archiver (db)] Error from TOC entry 1824; 0 26139 TABLE DATA alert_types postgres<br /> pg_restore:[archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "pk_alert_types_1"<br /> CONTEXT:COPY alert_types, line 1: "1 no signal"<br /> pg_restore: enabling triggers for alert_types<br /> pg_restore: disablingtriggers for alert_users<br /> pg_restore: restoring data for table "alert_users"<br /> pg_restore: enabling triggersfor alert_users<br /> pg_restore: disabling triggers for animals<br /> pg_restore: restoring data for table "animals"<br/> pg_restore: [archiver (db)] Error from TOC entry 1826; 0 26147 TABLE DATA animals postgres<br /> pg_restore:[archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "newanimals_pkey"<br /> CONTEXT:COPY animals, line 1: "1 1 1 3"<br /> pg_restore: enabling triggers for animals<br /> pg_restore: disabling triggersfor antennas<br /> pg_restore: restoring data for table "antennas"<br /> pg_restore: [archiver (db)] Error from TOCentry 1827; 0 26151 TABLE DATA antennas postgres<br /> pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violatesunique constraint "antennas_pkey"<br /> CONTEXT: COPY antennas, line 1: "1 2 1 0 2007-08-31 00:00:00 1 10"<br />pg_restore: enabling triggers for antennas<br /> pg_restore: disabling triggers for antennatypes<br /> pg_restore: restoringdata for table "antennatypes"<br /> pg_restore: [archiver (db)] Error from TOC entry 1828; 0 26154 TABLE DATA antennatypespostgres<br /> pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "antennatypes_pkey"<br/> CONTEXT: COPY antennatypes, line 1: "1 Omni directional - Ringo Ranger"<br /> pg_restore: enablingtriggers for antennatypes<br /> pg_restore: disabling triggers for arucalibration<br /> pg_restore: restoring datafor table "arucalibration"<br /> pg_restore: [archiver (db)] Error from TOC entry 1829; 0 26158 TABLE DATA arucalibrationpostgres<br /> pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "pk_arucalibration"<br/> CONTEXT: COPY arucalibration, line 1: "1017 1017cal9.cal 1 1"<br /> pg_restore: enabling triggersfor arucalibration<br /> pg_restore: disabling triggers for arudeploys<br /> pg_restore: restoring data for table"arudeploys"<br /> pg_restore: enabling triggers for arudeploys<br /> pg_restore: disabling triggers for arulocs<br/> pg_restore: restoring data for table "arulocs"<br /> pg_restore: [archiver (db)] Error from TOC entry 1831;0 26164 TABLE DATA arulocs postgres<br /> pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates uniqueconstraint "newarulocs_pkey"<br /> CONTEXT: COPY arulocs, line 1: "1 fall07grid pinebush 593866 4730021 18t 42.71694-73.85360"<br /> pg_restore: enabling triggers for arulocs<br /> pg_restore: disabling triggers for aruopcond<br/> pg_restore: restoring data for table "aruopcond"<br /> pg_restore: enabling triggers for aruopcond<br /> pg_restore:disabling triggers for arus<br /> pg_restore: restoring data for table "arus"<br /> pg_restore: [archiver (db)]Error from TOC entry 1833; 0 26169 TABLE DATA arus postgres<br /> pg_restore: [archiver (db)] COPY failed: ERROR: duplicatekey violates unique constraint "aru_pkey"<br /> CONTEXT: COPY arus, line 1: "1017 1017"<br /> pg_restore: enablingtriggers for arus<br /> pg_restore: disabling triggers for bursts<br /> pg_restore: restoring data for table "bursts"<br/> pg_restore: [archiver (db)] Error from TOC entry 1834; 0 26173 TABLE DATA bursts postgres<br /> pg_restore:[archiver (db)] COPY failed: ERROR: null value in column "burst" violates not-null constraint<br /> CONTEXT: COPYbursts, line 1: "91844 14 2000-01-01 00:04:00 \N \N \N"<br /> pg_restore: enabling triggers for bursts<br /> pg_restore:disabling triggers for channelsettings<br /> pg_restore: restoring data for table "channelsettings"<br /> pg_restore:enabling triggers for channelsettings<br /> pg_restore: disabling triggers for commtypes<br /> pg_restore: restoringdata for table "commtypes"<br /> pg_restore: [archiver (db)] Error from TOC entry 1836; 0 26182 TABLE DATA commtypespostgres<br /> pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "commtypes_pkey"<br/> CONTEXT: COPY commtypes, line 1: "1 freewave"<br /> pg_restore: enabling triggers for commtypes<br/> pg_restore: disabling triggers for deployments<br /> pg_restore: restoring data for table "deployments"<br/> pg_restore: enabling triggers for deployments<br /> pg_restore: disabling triggers for nosignals<br />pg_restore: restoring data for table "nosignals"<br /> pg_restore: enabling triggers for nosignals<br /> pg_restore: disablingtriggers for oldantennas<br /> pg_restore: restoring data for table "oldantennas"<br /> pg_restore: enabling triggersfor oldantennas<br /> pg_restore: disabling triggers for oldarudeploys<br /> pg_restore: restoring data for table"oldarudeploys"<br /> pg_restore: enabling triggers for oldarudeploys<br /> pg_restore: disabling triggers for olddeployments<br/> pg_restore: restoring data for table "olddeployments"<br /> pg_restore: enabling triggers for olddeployments<br/> pg_restore: disabling triggers for people<br /> pg_restore: restoring data for table "people"<br /> pg_restore:[archiver (db)] Error from TOC entry 1842; 0 26198 TABLE DATA people postgres<br /> pg_restore: [archiver (db)]COPY failed: ERROR: duplicate key violates unique constraint "newpeople_pkey"<br /> CONTEXT: COPY people, line 1: "1Chris grad student 2007-08-30 \N"<br /> pg_restore: enabling triggers for people<br /> pg_restore: disabling triggers forpulses<br /> pg_restore: restoring data for table "pulses"<br /> pg_restore: enabling triggers for pulses<br /> pg_restore:disabling triggers for realfreqs<br /> pg_restore: restoring data for table "realfreqs"<br /> pg_restore: enablingtriggers for realfreqs<br /> pg_restore: disabling triggers for sensors<br /> pg_restore: restoring data for table"sensors"<br /> pg_restore: enabling triggers for sensors<br /> pg_restore: disabling triggers for sexes<br /> pg_restore:restoring data for table "sexes"<br /> pg_restore: [archiver (db)] Error from TOC entry 1846; 0 26210 TABLE DATAsexes postgres<br /> pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "newsexes_pkey"<br/> CONTEXT: COPY sexes, line 1: "1 male"<br /> pg_restore: enabling triggers for sexes<br /> pg_restore:disabling triggers for signals<br /> pg_restore: restoring data for table "signals"<br /> pg_restore: enablingtriggers for signals<br /> pg_restore: disabling triggers for species<br /> pg_restore: restoring data for table"species"<br /> pg_restore: [archiver (db)] Error from TOC entry 1848; 0 26214 TABLE DATA species postgres<br /> pg_restore:[archiver (db)] COPY failed: ERROR: duplicate key violates unique constraint "species_pkey"<br /> CONTEXT: COPYspecies, line 1: "1 Animalia Rodentia Peromyscus sp. Deer Mouse"<br /> pg_restore: enabling triggers for species<br />pg_restore: disabling triggers for transmittermodes<br /> pg_restore: restoring data for table "transmittermodes"<br />pg_restore: [archiver (db)] Error from TOC entry 1849; 0 26218 TABLE DATA transmittermodes postgres<br /> pg_restore: [archiver(db)] COPY failed: ERROR: duplicate key violates unique constraint "transmittermodes_pkey"<br /> CONTEXT: COPY transmittermodes,line 1: "1 pulsed"<br /> pg_restore: enabling triggers for transmittermodes<br /> pg_restore: disablingtriggers for transmitters<br /> pg_restore: restoring data for table "transmitters"<br /> pg_restore: [archiver(db)] Error from TOC entry 1850; 0 26222 TABLE DATA transmitters postgres<br /> pg_restore: [archiver (db)] COPYfailed: ERROR: duplicate key violates unique constraint "transmitters_pkey"<br /> CONTEXT: COPY transmitters, line 9:"1 109216 none 164541000 Holohil bn2nc 1 160 1 50 \N 1 3300 20 1"<br /> pg_restore: enabling triggers for transmitters<br/> WARNING: errors ignored on restore: 15<br /><br /> Process returned exit code 1. </td></tr></tbody></table></div><br/><br /> So it seems to be a "duplicate key" problem (whatever that means...did I mentionI'm a newbie?). is this fixable? are there other ways I can get at this data?</span><br clear="all" /><font color="#888888"><br/>-- <br />Christopher Collins<br />New York State Museum<br />3140 Cultural Education Center<br />Albany,NY 12230<br />Work: (518) 486 2025<br />Cell: (518) 605 5614<br /></font></blockquote></div><br />
В списке pgadmin-support по дате отправления:
Предыдущее
От: Florent THOMASДата:
Сообщение: please unsuscribe whalesuit@gmail.com for avoiding wild advertisement