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

[Vishal Mehta]

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


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
http://sei-us.org/



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