Problem restoring database

From: "Oeschey, Lars (I/EK-142, extern)" <extern(dot)Lars(dot)Oeschey(at)AUDI(dot)DE>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Problem restoring database
Date: 2008-01-08 09:51:11
Message-ID: 17A09E9509BB354E95C2FEE5102B0F5F0242084F@audiinsx0036.audi.vwg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

I have a big problem restoring a database. I have two backups, one made
with pgadmin from my client, and one that is made nightly on the server
with the local pg_dump. The PG version is 7.4.17 on RHEL4.

this is what I tried:

------------------------------------------------------------------------
---------
local restore with pg_restore, using the local made backup:

pg_restore -F t -d team -h localhost -v -p 5432 -U master
/home/backup/pg/current/22\:07-postgresql_database-team-backup
pg_restore: connecting to database for restore
Password:
pg_restore: creating SCHEMA team
pg_restore: creating ACL public
pg_restore: creating ACL team
pg_restore: creating TABLE t_costcentre
pg_restore: creating ACL t_costcentre
pg_restore: creating TABLE t_equipment
pg_restore: creating ACL t_equipment
pg_restore: creating TABLE t_equipmentchange
pg_restore: creating ACL t_equipmentchange
pg_restore: creating TABLE t_general_app_data
pg_restore: creating ACL t_general_app_data
pg_restore: creating TABLE t_historynotes
pg_restore: creating ACL t_historynotes
pg_restore: creating TABLE t_location
pg_restore: creating ACL t_location
pg_restore: creating TABLE t_person
pg_restore: creating ACL t_person
pg_restore: creating TABLE t_software
pg_restore: creating ACL t_software
pg_restore: creating TABLE t_softwarechange
pg_restore: creating ACL t_softwarechange
pg_restore: creating TABLE t_stoerung
pg_restore: creating ACL t_stoerung
pg_restore: creating TABLE t_system
pg_restore: creating ACL t_system
pg_restore: creating TABLE t_system_person
pg_restore: creating ACL t_system_person
pg_restore: creating TABLE t_system_software
pg_restore: creating ACL t_system_software
pg_restore: creating TABLE t_system_supplier
pg_restore: creating ACL t_system_supplier
pg_restore: creating TABLE t_systemnotes
pg_restore: creating ACL t_systemnotes
pg_restore: creating TABLE t_systemtyp
pg_restore: creating ACL t_systemtyp
pg_restore: creating TABLE t_webio
pg_restore: creating ACL t_webio
pg_restore: creating SEQUENCE costcentre_id_sequence
pg_restore: creating ACL costcentre_id_sequence
pg_restore: creating SEQUENCE equipment_id_sequence
pg_restore: creating ACL equipment_id_sequence
pg_restore: creating SEQUENCE equipmentchange_id_sequence
pg_restore: creating ACL equipmentchange_id_sequence
pg_restore: creating SEQUENCE general_app_data_id_sequence
pg_restore: creating ACL general_app_data_id_sequence
pg_restore: creating SEQUENCE historynotes_id_sequence
pg_restore: creating ACL historynotes_id_sequence
pg_restore: creating SEQUENCE location_id_sequence
pg_restore: creating ACL location_id_sequence
pg_restore: creating SEQUENCE software_id_sequence
pg_restore: creating ACL software_id_sequence
pg_restore: creating SEQUENCE softwaretakein_id_sequence
pg_restore: creating ACL softwaretakein_id_sequence
pg_restore: creating SEQUENCE stoerung_id_sequence
pg_restore: creating ACL stoerung_id_sequence
pg_restore: creating SEQUENCE system_id_sequence
pg_restore: creating ACL system_id_sequence
pg_restore: creating SEQUENCE systemnotes_id_sequence
pg_restore: creating ACL systemnotes_id_sequence
pg_restore: creating SEQUENCE systemtyp_id_sequence
pg_restore: creating ACL systemtyp_id_sequence
pg_restore: creating SEQUENCE t_person_person_id_seq
pg_restore: creating ACL t_person_person_id_seq
pg_restore: creating SEQUENCE webio_id_sequence
pg_restore: creating ACL webio_id_sequence
pg_restore: creating SEQUENCE sequence
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"sequence" already exists
pg_restore: *** aborted because of error

-----------------------------------------------------------------

using pgadmin 1.8 from my client, using the server-made backup:

C:\Programme\pgAdmin III\1.8\pg_restore.exe -i -h
audiinsaw182a.in.audi.vwg -p 5432 -U master -d team -v
"C:\tempi\postgresql_database-team_server.backup"
pg_restore: connecting to database for restore
pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR:
unrecognized configuration parameter "standard_conforming_strings"
Command was: SET standard_conforming_strings = off;
pg_restore: [archiver (db)] could not execute query: ERROR:
unrecognized configuration parameter "escape_string_warning"
Command was: SET escape_string_warning = off;
pg_restore: creating SCHEMA team
pg_restore: creating TABLE t_costcentre
pg_restore: [archiver] Error while PROCESSING TOC:
pg_restore: [archiver] Error from TOC entry 51; 0 190736896 TABLE
t_costcentre team
pg_restore: [archiver] could not set default_with_oids: ERROR:
unrecognized configuration parameter "default_with_oids"
pg_restore: creating TABLE t_equipment
pg_restore: creating TABLE t_equipmentchange
pg_restore: creating TABLE t_general_app_data
pg_restore: creating TABLE t_historynotes
pg_restore: creating TABLE t_location
pg_restore: creating TABLE t_person
pg_restore: creating TABLE t_software
pg_restore: creating TABLE t_softwarechange
pg_restore: creating TABLE t_stoerung
pg_restore: creating TABLE t_system
pg_restore: creating TABLE t_system_person
pg_restore: creating TABLE t_system_software
pg_restore: creating TABLE t_system_supplier
pg_restore: creating TABLE t_systemnotes
pg_restore: creating TABLE t_systemtyp
pg_restore: creating TABLE t_webio
pg_restore: creating SEQUENCE costcentre_id_sequence
pg_restore: creating SEQUENCE equipment_id_sequence
pg_restore: creating SEQUENCE equipmentchange_id_sequence
pg_restore: creating SEQUENCE general_app_data_id_sequence
pg_restore: creating SEQUENCE historynotes_id_sequence
pg_restore: creating SEQUENCE location_id_sequence
pg_restore: creating SEQUENCE software_id_sequence
pg_restore: creating SEQUENCE softwaretakein_id_sequence
pg_restore: creating SEQUENCE stoerung_id_sequence
pg_restore: creating SEQUENCE system_id_sequence
pg_restore: creating SEQUENCE systemnotes_id_sequence
pg_restore: creating SEQUENCE systemtyp_id_sequence
pg_restore: creating SEQUENCE t_person_person_id_seq
pg_restore: creating SEQUENCE webio_id_sequence
pg_restore: creating SEQUENCE sequence
pg_restore: [archiver (db)] Error from TOC entry 49; 0 190736976
SEQUENCE sequence team
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"sequence" already exists
Command was: CREATE SEQUENCE "sequence"
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
pg_restore: creating TABLE t_location_subarea
pg_restore: creating TABLE t_person_group
pg_restore: creating TABLE t_personroles
pg_restore: creating TABLE t_planer_arrangements
pg_restore: creating TABLE t_planer_date
pg_restore: creating TABLE t_planer_holidays
pg_restore: creating TABLE t_planer_person
pg_restore: creating TABLE t_planer_person_code
pg_restore: creating TABLE t_system_service
pg_restore: creating TABLE t_system_service_charging_rate
pg_restore: creating TABLE t_system_service_charging_rate_year
pg_restore: creating TABLE t_system_service_parts
pg_restore: creating TABLE t_system_service_standard
pg_restore: creating TABLE t_system_warranty_data
pg_restore: creating TABLE t_system_warranty_header
pg_restore: restoring data for table "t_costcentre"
pg_restore: restoring data for table "t_equipment"
pg_restore: restoring data for table "t_equipmentchange"
pg_restore: restoring data for table "t_general_app_data"
pg_restore: restoring data for table "t_historynotes"
pg_restore: restoring data for table "t_location"
pg_restore: restoring data for table "t_person"
pg_restore: restoring data for table "t_software"
pg_restore: restoring data for table "t_softwarechange"
pg_restore: restoring data for table "t_stoerung"
pg_restore: restoring data for table "t_system"
pg_restore: restoring data for table "t_system_person"
pg_restore: restoring data for table "t_system_software"
pg_restore: restoring data for table "t_system_supplier"
pg_restore: restoring data for table "t_systemnotes"
pg_restore: restoring data for table "t_systemtyp"
pg_restore: restoring data for table "t_webio"
pg_restore: restoring data for table "t_location_subarea"
pg_restore: restoring data for table "t_person_group"
pg_restore: restoring data for table "t_personroles"
pg_restore: restoring data for table "t_planer_arrangements"
pg_restore: restoring data for table "t_planer_date"
pg_restore: restoring data for table "t_planer_holidays"
pg_restore: restoring data for table "t_planer_person"
pg_restore: restoring data for table "t_planer_person_code"
pg_restore: restoring data for table "t_system_service"
pg_restore: restoring data for table "t_system_service_charging_rate"
pg_restore: restoring data for table
"t_system_service_charging_rate_year"
pg_restore: restoring data for table "t_system_service_parts"
pg_restore: restoring data for table "t_system_service_standard"
pg_restore: restoring data for table "t_system_warranty_data"
pg_restore: restoring data for table "t_system_warranty_header"
pg_restore: restoring BLOBS
pg_restore: restoring large object OID 191837784
pg_restore: [archiver] could not create large object 191837784
pg_restore: *** aborted because of error

Process returned exit code 1.

------------------------------------------------------------------------
-

using pgadmin 1.8 from my client, using the pgadmin-made backup:

C:\Programme\pgAdmin III\1.8\pg_restore.exe -i -h
audiinsaw182a.in.audi.vwg -p 5432 -U master -d team -v
"C:\tempi\team_pgadmin.backup"
pg_restore: connecting to database for restore
pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR:
unrecognized configuration parameter "standard_conforming_strings"
Command was: SET standard_conforming_strings = off;
pg_restore: [archiver (db)] could not execute query: ERROR:
unrecognized configuration parameter "escape_string_warning"
Command was: SET escape_string_warning = off;
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating SCHEMA team
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 6; 16595 190736895
SCHEMA team team
pg_restore: [archiver (db)] could not execute query: ERROR: syntax
error at or near "OWNER"
LINE 1: ALTER SCHEMA team OWNER TO team;
^
Command was: ALTER SCHEMA team OWNER TO team;
pg_restore: creating SEQUENCE sequence
pg_restore: [archiver (db)] Error from TOC entry 1200; 1259 190736976
SEQUENCE sequence team
pg_restore: [archiver (db)] could not execute query: ERROR: relation
"sequence" already exists
Command was: CREATE SEQUENCE sequence
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
pg_restore: executing SEQUENCE SET sequence
pg_restore: creating SEQUENCE costcentre_id_sequence
pg_restore: executing SEQUENCE SET costcentre_id_sequence
pg_restore: creating SEQUENCE equipment_id_sequence
pg_restore: executing SEQUENCE SET equipment_id_sequence
pg_restore: creating SEQUENCE equipmentchange_id_sequence
pg_restore: executing SEQUENCE SET equipmentchange_id_sequence
pg_restore: creating SEQUENCE general_app_data_id_sequence
pg_restore: executing SEQUENCE SET general_app_data_id_sequence
pg_restore: creating SEQUENCE historynotes_id_sequence
pg_restore: executing SEQUENCE SET historynotes_id_sequence
pg_restore: creating SEQUENCE location_id_sequence
pg_restore: executing SEQUENCE SET location_id_sequence
pg_restore: creating SEQUENCE software_id_sequence
pg_restore: executing SEQUENCE SET software_id_sequence
pg_restore: creating SEQUENCE softwaretakein_id_sequence
pg_restore: executing SEQUENCE SET softwaretakein_id_sequence
pg_restore: creating SEQUENCE stoerung_id_sequence
pg_restore: executing SEQUENCE SET stoerung_id_sequence
pg_restore: creating SEQUENCE system_id_sequence
pg_restore: executing SEQUENCE SET system_id_sequence
pg_restore: creating SEQUENCE systemnotes_id_sequence
pg_restore: executing SEQUENCE SET systemnotes_id_sequence
pg_restore: creating SEQUENCE systemtyp_id_sequence
pg_restore: executing SEQUENCE SET systemtyp_id_sequence
pg_restore: creating TABLE t_costcentre
pg_restore: [archiver] Error from TOC entry 1169; 1259 190736896 TABLE
t_costcentre team
pg_restore: [archiver] could not set default_tablespace to "": ERROR:
unrecognized configuration parameter "default_tablespace"
pg_restore: [archiver] could not set default_with_oids: ERROR:
unrecognized configuration parameter "default_with_oids"
pg_restore: creating TABLE t_equipment
pg_restore: creating TABLE t_equipmentchange
pg_restore: creating TABLE t_general_app_data
pg_restore: creating TABLE t_historynotes
pg_restore: creating TABLE t_location
pg_restore: creating TABLE t_location_subarea
pg_restore: creating TABLE t_person
pg_restore: creating TABLE t_person_group
pg_restore: creating SEQUENCE t_person_person_id_seq
pg_restore: executing SEQUENCE SET t_person_person_id_seq
pg_restore: creating TABLE t_personroles
pg_restore: creating TABLE t_planer_arrangements
pg_restore: creating TABLE t_planer_date
pg_restore: creating TABLE t_planer_holidays
pg_restore: creating TABLE t_planer_person
pg_restore: creating TABLE t_planer_person_code
pg_restore: creating TABLE t_software
pg_restore: creating TABLE t_softwarechange
pg_restore: creating TABLE t_stoerung
pg_restore: creating TABLE t_system
pg_restore: creating TABLE t_system_person
pg_restore: creating TABLE t_system_service
pg_restore: creating TABLE t_system_service_charging_rate
pg_restore: creating TABLE t_system_service_charging_rate_year
pg_restore: creating TABLE t_system_service_parts
pg_restore: creating TABLE t_system_service_standard
pg_restore: creating TABLE t_system_software
pg_restore: creating TABLE t_system_supplier
pg_restore: creating TABLE t_system_warranty_data
pg_restore: creating TABLE t_system_warranty_header
pg_restore: creating TABLE t_systemnotes
pg_restore: creating TABLE t_systemtyp
pg_restore: creating TABLE t_webio
pg_restore: creating SEQUENCE webio_id_sequence
pg_restore: executing SEQUENCE SET webio_id_sequence
pg_restore: restoring data for table "t_costcentre"
pg_restore: restoring data for table "t_equipment"
pg_restore: restoring data for table "t_equipmentchange"
pg_restore: restoring data for table "t_general_app_data"
pg_restore: restoring data for table "t_historynotes"
pg_restore: restoring data for table "t_location"
pg_restore: restoring data for table "t_location_subarea"
pg_restore: restoring data for table "t_person"
pg_restore: restoring data for table "t_person_group"
pg_restore: restoring data for table "t_personroles"
pg_restore: restoring data for table "t_planer_arrangements"
pg_restore: restoring data for table "t_planer_date"
pg_restore: restoring data for table "t_planer_holidays"
pg_restore: restoring data for table "t_planer_person"
pg_restore: restoring data for table "t_planer_person_code"
pg_restore: restoring data for table "t_software"
pg_restore: restoring data for table "t_softwarechange"
pg_restore: restoring data for table "t_stoerung"
pg_restore: restoring data for table "t_system"
pg_restore: restoring data for table "t_system_person"
pg_restore: restoring data for table "t_system_service"
pg_restore: restoring data for table "t_system_service_charging_rate"
pg_restore: restoring data for table
"t_system_service_charging_rate_year"
pg_restore: restoring data for table "t_system_service_parts"
pg_restore: restoring data for table "t_system_service_standard"
pg_restore: restoring data for table "t_system_software"
pg_restore: restoring data for table "t_system_supplier"
pg_restore: restoring data for table "t_system_warranty_data"
pg_restore: restoring data for table "t_system_warranty_header"
pg_restore: restoring data for table "t_systemnotes"
pg_restore: restoring data for table "t_systemtyp"
pg_restore: restoring data for table "t_webio"
pg_restore: restoring BLOBS
pg_restore: [archiver] could not create large object 191837784
pg_restore: *** aborted because of error

Process returned exit code 1.

------------------------------------------------------------------------
-----

So it seems my real problem is that large object, but what could I do
about it?

Lars

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2008-01-08 16:19:32 Re: Problem restoring database
Previous Message Greg Cocks 2008-01-07 22:52:45 Re: autoincrement question