[postgis-users] help with backing up and restoring a postgis database

Vishal Mehta vishal.mehta at sei-us.org
Fri Aug 5 11:50:09 PDT 2011

Hi all,

I can provide a specific workflow describing my problems with backing up and restoring a postgis2.0 database.


-          Database I want to restore is called 'bangalore'; I want to restore it into an empty database called 'empty'

-          There are 3 schemas under this database

o   'Public' was created on creation of Bangalore database. This contains both raster and vector tables

o   'Topology' was also created automatically (this seems to be new in postgis2.0 vs postgis 1.5 and I don't know about it)

o   'myschema' was a chema I created which has 4 vector tables which I would like to test for this backup.

What I did:

-          Created an empty database in the psql console:

o   CREATE DATABASE empty TEMPLATE=template0;

-          Backup using pg_dump like so:

o   Pg_dump.exe -Fc -n "myschema" -v bangalore > "backupfc.dump"

-          Tried to restore like so:

o   Pg_restore.exe -dbname "empty" -verbose "backupfc.dump"
I got errors after myschema was created, upon the first table creation: the first few error lines are:

Error while processing TOC:
Error from TOC entry 2551: 1251 etc
Could not execute query type "public.geometry does not exist

When I investigated the sql in the backup (froma  separate plain sql dump) I see this:

CREATE TABLE borewells (
    gid integer NOT NULL,
    "UTM_X_" double precision,
    "UTM_Y_" double precision,
    "Code_No" character varying(254),
    "Water_Leve" double precision,
    geom public.geometry(Point,4326)

Its failing on the geom column. I am not an expert with databases but clearly there is no geometry table in the public schema (which I am not wanting to backup anyway).

My best guess is that, in postgis1.5 there was a separate geometry columns table. In postgis 2.0 there is a Views thing with geometry_columns in there- this is also new to me.  Has this got something to do with it?

Would appreciate some help. I am not a software developer- I just recognize the potential in postgis and have been trying to use it for awhile..

Even some general advice towards abandoning postgres9.0/postgis2.0 and going back to posgres8.3/postgis1.5 could help.


From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Vishal Mehta
Sent: Thursday, August 04, 2011 5:11 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] help with backing up and restoring a postgis database

Hi all,

I am working with windows7/ postgres9.0.4/postgis2.0 which I recently installed and started testing like so:

my previous experience has been with postgres8.3/postgis1.5, and I have never before now tried to backup and restore a postgres/postgis database

I am running into several errors when using pg_dump and pg_restore, and the postgres documentation has raised some doubts in my mind as well. My colleagues and I are trying to develop an application on one server which we know we'll need to migrate next year, so we want to nail down the best way to do this. Before I describe the kinds of errors specific to certain attempts I've made (its quite confusing to keep track of for me), I'd like to know if there are best practices regarding:

-          Pg_dump qns:

is it best to skip owner and priviliges (if say I want to havemy colleague recreate my database sitting on localhost on my machine)

is it best to backup data only, skipping schema (same as above)

any other flags that are best set a certain way?

-           Psql dbname<infile, and pg_restore qns

-          I tried to create an empty database first, based on template0

Qn: Should this be based on template_postgis instead?

o   When I tried to restore my database called 'bangalore' and a schema called 'mychema' using psql (with plain sql backup) into a couple of  empty databases (because I did not want to mess up my database),

I got errors of various sorts, mainly:

Public.geometry does not exist;  myschema already exists

o    with pg_restore and a tarred backup , I tried to restore it into the same 'bangalore' database (without deleting it first) and got errors that the tables and schema already existed.

-          Finally I tried to just bring in the sql into the empty database using the query window:



ERROR:  syntax error at or near "1"

LINE 282: 1 779330 1437291 BN1 6.5499999999999998 0101000020E6100000A3...

Which is referring to:
COPY borewells (gid, "UTM_X_", "UTM_Y_", "Code_No", "Water_Leve", geom) FROM stdin;
1      779330  1437291                BN1        6.5499999999999998       0101000020E6100000A3FE0F26CC645340553700202FFA2940

I could really use some help,


Vishal K. Mehta<http://sei-us.org/about/staff_person/19>, Ph.D.
Staff Scientist,
Stockholm Environment Institute-US
400 F St, Davis, CA 95616

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110805/7bb770db/attachment.html>

More information about the postgis-users mailing list