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

[Gunnar Oehmichen]

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