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

Fri Aug 5 14:41:58 PDT 2011

Regina and Leo,
Thanks for your reply.

Just before you wrote, I managed to restore a postgis database into a new empty database, by using template_postgis20 as the template for the empty database - I guess that effectively did what you suggested, which is to install postgis into the new database.

Fyi, how I installed postgis2.0 on windows is in my blogpost from earlier:
http://algoesalgo.wordpress.com/tag/postgis2-0/ following advice from various other blogs that are also listed there.

also fyi, I am using a windows machine right now- would prefer using my Ubuntu box, but I am not comfortable enough on linux to compile from scratch.

For now it seems to have worked. It would be better to install postgis fresh into a new database but I don't know exactly how on windows...I guess I could follow the same procedures as in my original installation.

Thanks,
Vishal

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

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#hard_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

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/20110805/f9389898/attachment.html>