[postgis-users] Trying to restore a .backup file

[Humberto Cereser Ibanez]

Dear Gunnar,

I suggest to you recreate your MI database, add spatial extensions and
restore your backup: 
1) drop MI database (dropdb) and create it from scratch (createdb)
2) execute the steps that are showed in "2.6 Creating a spatial database
using EXTENSIONS" at
http://postgis.net/docs/manual-2.0/postgis_installation.html#create_new_db_extensions , and
3) run your pg_restore
Best regards,

Humberto Cereser Ibanez

Em Seg, 2013-06-24 às 17:05 +0200, Gunnar Oehmichen escreveu:
> Dear Participants,
> 
> As part of my thesis (environmental sciences) I recently started working 
> with PostgreSQL (newbie here). The georeferenced database has been 
> transferred from a local Windows7 desktop machine to me.
> 
> I am using Ubuntu 12.04 64 bit with Xubuntu as a Desktop-environment
> 
> Postgres Version 9.1.9, Postgis 2.0.1.2, PGAdminIII as GUI.
> 
> To restore the .backup file I built a new Database "MI" in the GUI 
> without using any template from the definitions tab:
> 
> /usr/bin/pg_restore --host localhost --port 5432 --username "postgres" 
> --dbname "MI" --no-password  --verbose 
> "/home/gunnaroeh/Dokumente/Laenderdaten/Database/LaenderStand_02-20.backup"
> pg_restore: connecting to database for restore
> pg_restore: creating TABLE mzb
> pg_restore: creating TABLE samplsites_mzb
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 200; 1259 51728 TABLE 
> samplsites_mzb postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  type 
> "geometry" does not exist
> LINE 14:     east_north geometry(Point,31467)
>                          ^
>      Command was: CREATE TABLE samplsites_mzb (
>      land character varying(50),
>      samplsite_id integer NOT NULL,
>      samplsite character var...
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
> "public.samplsites_mzb" does not exist
>      Command was: ALTER TABLE public.samplsites_mzb OWNER TO postgres;
> 
> 
> pg_restore: creating TABLE samplsites_phch
> pg_restore: restoring data for table "mzb"
> pg_restore: restoring data for table "samplsites_mzb"
> pg_restore: [archiver (db)] Error from TOC entry 3147; 0 51728 TABLE 
> DATA samplsites_mzb postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
> "samplsites_mzb" does not exist
>      Command was: COPY samplsites_mzb (land, samplsite_id, samplsite, 
> samplsite_name, rechtswert, hochwert, easting, northing, latitude, longi...
> pg_restore: restoring data for table "samplsites_phch"
> pg_restore: creating CONSTRAINT id
> pg_restore: [archiver (db)] Error from TOC entry 3145; 2606 59403 
> CONSTRAINT id postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  relation 
> "samplsites_mzb" does not exist
>      Command was: ALTER TABLE ONLY samplsites_mzb
>      ADD CONSTRAINT id PRIMARY KEY (samplsite_id);
> 
> 
> 
> pg_restore: setting owner and privileges for TABLE mzb
> pg_restore: setting owner and privileges for TABLE samplsites_mzb
> pg_restore: setting owner and privileges for TABLE samplsites_phch
> pg_restore: setting owner and privileges for TABLE DATA mzb
> pg_restore: setting owner and privileges for TABLE DATA samplsites_mzb
> pg_restore: setting owner and privileges for TABLE DATA samplsites_phch
> pg_restore: setting owner and privileges for CONSTRAINT id
> WARNING: errors ignored on restore: 4
> 
> Prozess beendete mit Exitcode 1.
> 
> If I add the extensions "postgis" and "postgis_topology" to the new db 
> "MI" at least the following lines are missing from the messages:
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  type 
> "geometry" does not exist.
> 
> Also noteworthy:
> 
> postgres=# SELECT PostGIS_version();
> ERROR:  function postgis_version() does not exist
> LINE 1: SELECT PostGIS_version();
>                 ^
> HINT:  No function matches the given name and argument types. You might 
> need to add explicit type casts.
> 
> By the way, the template postgis is not available in the definitions-tab 
> using the GUI.
> 
> I am also posting to this list as I do not know, wether the problem is 
> postgis related or not,
> 
> Thank you very much,
> 
> Gunnar
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users