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

Fri Aug 5 14:07:45 PDT 2011

Vishal,

Did you install PostGIS in the new database you created before trying to
restore? From your errors doesn't sound like you did that.  Please give that
a try and let us know if you have trouble.

We have instructions documented here:

http://www.postgis.org/documentation/manual-svn/postgis_installation.html#ha
rd_upgrade

For windows, the included batch script should allow you to create a new
spatially enabled database.  Just have to change the lines to fit your
platform.  I've just been creating new databases with the script instead of
using a template_postgis since postgis 2.0 is still in flux.  So though the
script has a template_postgis specified, you can use to build any postgis
enabled database by changing the name of the database in the batch script.

We compiled a new windows build yesterday so is very up to date

http://www.postgis.org/download/windows/experimental.php#PostGIS_2_0_0

As far as your permission questions, probably best to ask that on the
PostgreSQL general group.  For PostgreSQL 9.0, we usually just use the new
default permissions features to set the permissions of the database and
schemas before we restore anything.

Hope that helps,

Regina and Leo

http://www.postgis.us <http://www.postgis.us/> 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Vishal
Mehta
Sent: Friday, August 05, 2011 2:50 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] help with backing up and restoring apostgis
database

Hi all,

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

Information:

-      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.

Thanks,

Vishal

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:

http://algoesalgo.wordpress.com/tag/postgis2-0/

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, 

Thanks,

Vishal

 <http://sei-us.org/about/staff_person/19> Vishal K. Mehta, Ph.D.

Staff Scientist,

Stockholm Environment Institute-US

400 F St, Davis, CA 95616

 <http://sei-us.org/> http://sei-us.org/

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