Thread: Problem restoring database

Problem restoring database

From

"Oeschey, Lars (I/EK-142, extern)"

Date:

08 January 2008, 08:55:37

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

Re: Problem restoring database

From

Tom Lane

Date:

08 January 2008, 15:19:44

"Oeschey, Lars (I/EK-142, extern)" <extern.Lars.Oeschey@AUDI.DE> writes:
> 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.

> local restore with pg_restore, using the local made backup:
> ...
> pg_restore: creating SEQUENCE sequence
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation
> "sequence" already exists

This one is failing because you are restoring into a database that
already contains objects --- at least sequence "sequence", and maybe
others.  A likely explanation is that you've created those objects
in template1 and so they're being copied into any new database.
You should either clean out template1, or be careful to clone new
databases from template0 instead.

> using pgadmin 1.8 from my client, using the server-made backup:
> ...
> pg_restore: [archiver (db)] could not execute query: ERROR:
> unrecognized configuration parameter "standard_conforming_strings"
>     Command was: SET standard_conforming_strings = off;

This one is evidently failing because the backup was made with a version
of pg_dump that's newer than 7.4.x.  pg_dump output in general can be
loaded into a server that's *newer* than the pg_dump, but not one that's
*older*; at least not without doing manual surgery on the dump script
to get rid of any commands the older server doesn't understand.

> pg_restore: restoring large object OID 191837784
> pg_restore: [archiver] could not create large object 191837784
> pg_restore: *** aborted because of error

Probably this is also a version-skew problem, though it's hard to be
sure without knowing which pg_dump version we're dealing with.  The
other failures you show definitely fall under that category.

            regards, tom lane

Re: Problem restoring database

From

"Oeschey, Lars (I/EK-142, extern)"

Date:

10 January 2008, 14:29:21

> > using pgadmin 1.8 from my client, using the server-made backup:
                                                ^^^^^^^^^^^^^^^^^^
> > ...
> > pg_restore: [archiver (db)] could not execute query: ERROR:
> > unrecognized configuration parameter "standard_conforming_strings"
> >     Command was: SET standard_conforming_strings = off;
>
> This one is evidently failing because the backup was made
> with a version
> of pg_dump that's newer than 7.4.x.  pg_dump output in general can be
> loaded into a server that's *newer* than the pg_dump, but not
> one that's
> *older*; at least not without doing manual surgery on the dump script
> to get rid of any commands the older server doesn't understand.

this is strange then... because as I mentioned, the backup was (is) made
nightly with the pg_dump that comes with the database. Of course I
understand that there is a downward compatibility, but not an upward.
But this backup was just one of the standard nightly backups.

btw, we solved the problem meanwhile by restoring into a 8.1 DB, even a
8.1 wouldn't work....

Lars