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-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Vishal Mehta
Sent: Thursday, August 04, 2011 5:11 PM
To: postgis-users@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
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/
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
