<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0in;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Arial","sans-serif";
color:navy;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
span.EmailStyle23
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:1290747380;
mso-list-type:hybrid;
mso-list-template-ids:1536559434 1942648854 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
{mso-level-start-at:0;
mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:Calibri;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l1
{mso-list-id:1701316295;
mso-list-type:hybrid;
mso-list-template-ids:61082188 1490986344 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l1:level1
{mso-level-number-format:bullet;
mso-level-text:-;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Calibri","sans-serif";
mso-fareast-font-family:Calibri;}
@list l1:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l1:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l1:level4
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l1:level5
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l1:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
@list l1:level7
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Symbol;}
@list l1:level8
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:"Courier New";}
@list l1:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;
font-family:Wingdings;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='color:#1F497D'>Regina and Leo,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Thanks for your reply. <o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Fyi, how I installed postgis2.0 on windows is in my blogpost from earlier:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><a href="http://algoesalgo.wordpress.com/tag/postgis2-0/">http://algoesalgo.wordpress.com/tag/postgis2-0/</a> following advice from various other blogs that are also listed there.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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. <o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Vishal<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> postgis-users-bounces@postgis.refractions.net [mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Paragon Corporation<br><b>Sent:</b> Friday, August 05, 2011 2:08 PM<br><b>To:</b> 'PostGIS Users Discussion'<br><b>Subject:</b> Re: [postgis-users] help with backing up and restoring apostgis database<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'>Vishal,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'>We have instructions documented here:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><a href="http://www.postgis.org/documentation/manual-svn/postgis_installation.html#hard_upgrade">http://www.postgis.org/documentation/manual-svn/postgis_installation.html#hard_upgrade</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'>We compiled a new windows build yesterday so is very up to date<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><a href="http://www.postgis.org/download/windows/experimental.php#PostGIS_2_0_0">http://www.postgis.org/download/windows/experimental.php#PostGIS_2_0_0</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'>Hope that helps,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'>Regina and Leo<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><a href="http://www.postgis.us/">http://www.postgis.us</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div class=MsoNormal align=center style='text-align:center'><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><hr size=2 width="100%" align=center></span></div><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> postgis-users-bounces@postgis.refractions.net [mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Vishal Mehta<br><b>Sent:</b> Friday, August 05, 2011 2:50 PM<br><b>To:</b> PostGIS Users Discussion<br><b>Subject:</b> Re: [postgis-users] help with backing up and restoring apostgis database</span><span style='font-size:12.0pt;font-family:"Times New Roman","serif"'><o:p></o:p></span></p></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span style='color:#1F497D'>Hi all,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>I can provide a specific workflow describing my problems with backing up and restoring a postgis2.0 database.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Information:<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>Database I want to restore is called ‘bangalore’; I want to restore it into an empty database called ‘empty’<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>There are 3 schemas under this database<o:p></o:p></span></p><p class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l0 level2 lfo2'><![if !supportLists]><span style='font-family:"Courier New";color:#1F497D'><span style='mso-list:Ignore'>o<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>‘Public’ was created on creation of Bangalore database. This contains both raster and vector tables<o:p></o:p></span></p><p class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l0 level2 lfo2'><![if !supportLists]><span style='font-family:"Courier New";color:#1F497D'><span style='mso-list:Ignore'>o<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>‘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:p></o:p></span></p><p class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l0 level2 lfo2'><![if !supportLists]><span style='font-family:"Courier New";color:#1F497D'><span style='mso-list:Ignore'>o<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>‘myschema’ was a chema I created which has 4 vector tables which I would like to test for this backup.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>What I did:<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>Created an empty database in the psql console:<o:p></o:p></span></p><p class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l0 level2 lfo2'><![if !supportLists]><span style='font-family:"Courier New";color:#1F497D'><span style='mso-list:Ignore'>o<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>CREATE DATABASE empty TEMPLATE=template0;<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>Backup using pg_dump like so:<o:p></o:p></span></p><p class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l0 level2 lfo2'><![if !supportLists]><span style='font-family:"Courier New";color:#1F497D'><span style='mso-list:Ignore'>o<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>Pg_dump.exe –Fc –n “myschema” –v bangalore > “backupfc.dump”<o:p></o:p></span></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><span style='color:#1F497D'><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>Tried to restore like so:<o:p></o:p></span></p><p class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l0 level2 lfo2'><![if !supportLists]><span style='font-family:"Courier New";color:#1F497D'><span style='mso-list:Ignore'>o<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]><span style='color:#1F497D'>Pg_restore.exe –dbname “empty” –verbose “backupfc.dump”<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>I got errors after myschema was created, upon the first table creation: the first few error lines are:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Error while processing TOC:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Error from TOC entry 2551: 1251 etc<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Could not execute query type “public.geometry does not exist<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>When I investigated the sql in the backup (froma separate plain sql dump) I see this:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>CREATE TABLE borewells (<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> gid integer NOT NULL,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> "UTM_X_" double precision,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> "UTM_Y_" double precision,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> "Code_No" character varying(254),<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> "Water_Leve" double precision,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'> geom public.geometry(Point,4326)<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>);<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Its failing on the geom column. I am not an expert with databases <b>but clearly there is no geometry table in the public schema (which I am not wanting to backup anyway).<o:p></o:p></b></span></p><p class=MsoNormal><b><span style='color:#1F497D'><o:p> </o:p></span></b></p><p class=MsoNormal><span style='color:#1F497D'>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?<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>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..<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Even some general advice towards abandoning postgres9.0/postgis2.0 and going back to posgres8.3/postgis1.5 could help.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Vishal<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> postgis-users-bounces@postgis.refractions.net [mailto:postgis-users-bounces@postgis.refractions.net] <b>On Behalf Of </b>Vishal Mehta<br><b>Sent:</b> Thursday, August 04, 2011 5:11 PM<br><b>To:</b> postgis-users@postgis.refractions.net<br><b>Subject:</b> [postgis-users] help with backing up and restoring a postgis database<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Hi all,<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I am working with windows7/ postgres9.0.4/postgis2.0 which I recently installed and started testing like so:<o:p></o:p></p><p class=MsoNormal><a href="http://algoesalgo.wordpress.com/tag/postgis2-0/">http://algoesalgo.wordpress.com/tag/postgis2-0/</a><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>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<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>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:<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo4'><![if !supportLists]><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span><![endif]>Pg_dump qns:<o:p></o:p></p><p class=MsoListParagraph>is it best to skip owner and priviliges (if say I want to havemy colleague recreate my database sitting on localhost on my machine)<o:p></o:p></p><p class=MsoListParagraph>is it best to backup data only, skipping schema (same as above)<o:p></o:p></p><p class=MsoListParagraph>any other flags that are best set a certain way?<o:p></o:p></p><p class=MsoListParagraph><o:p> </o:p></p><p class=MsoListParagraph><o:p> </o:p></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo4'><![if !supportLists]><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span><![endif]> Psql dbname<infile, and pg_restore qns<o:p></o:p></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo4'><![if !supportLists]><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span><![endif]>I tried to create an empty database first, based on template0<o:p></o:p></p><p class=MsoListParagraph style='margin-left:1.0in'>Qn: Should this be based on template_postgis instead?<o:p></o:p></p><p class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l1 level2 lfo4'><![if !supportLists]><span style='font-family:"Courier New"'><span style='mso-list:Ignore'>o<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]>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),<o:p></o:p></p><p class=MsoListParagraph style='margin-left:1.0in'>I got errors of various sorts, mainly:<o:p></o:p></p><p class=MsoListParagraph style='margin-left:1.0in'>Public.geometry does not exist; myschema already exists<o:p></o:p></p><p class=MsoListParagraph style='margin-left:1.0in'><o:p> </o:p></p><p class=MsoListParagraph style='margin-left:1.0in;text-indent:-.25in;mso-list:l1 level2 lfo4'><![if !supportLists]><span style='font-family:"Courier New"'><span style='mso-list:Ignore'>o<span style='font:7.0pt "Times New Roman"'> </span></span></span><![endif]> 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.<o:p></o:p></p><p class=MsoListParagraph><o:p> </o:p></p><p class=MsoListParagraph style='text-indent:-.25in;mso-list:l1 level1 lfo4'><![if !supportLists]><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span><![endif]>Finally I tried to just bring in the sql into the empty database using the query window:<o:p></o:p></p><p class=MsoListParagraph style='margin-left:.75in;text-indent:-.25in;mso-list:l1 level1 lfo4'><![if !supportLists]><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span><![endif]><o:p> </o:p></p><p class=MsoListParagraph style='margin-left:.75in;text-indent:-.25in;mso-list:l1 level1 lfo4'><![if !supportLists]><span style='mso-list:Ignore'>-<span style='font:7.0pt "Times New Roman"'> </span></span><![endif]><o:p> </o:p></p><p class=MsoListParagraph style='margin-left:.75in'>ERROR: syntax error at or near "1"<o:p></o:p></p><p class=MsoListParagraph style='margin-left:.75in'>LINE 282: 1 779330 1437291 BN1 6.5499999999999998 0101000020E6100000A3...<o:p></o:p></p><p class=MsoNormal style='margin-left:.25in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:.25in'>Which is referring to:<o:p></o:p></p><p class=MsoNormal style='margin-left:.25in'>COPY borewells (gid, "UTM_X_", "UTM_Y_", "Code_No", "Water_Leve", geom) FROM stdin;<o:p></o:p></p><p class=MsoNormal style='margin-left:.25in'>1 779330 1437291 BN1 6.5499999999999998 0101000020E6100000A3FE0F26CC645340553700202FFA2940<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I could really use some help, <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Thanks,<o:p></o:p></p><p class=MsoNormal>Vishal<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><a href="http://sei-us.org/about/staff_person/19"><i>Vishal K. Mehta</i></a><i>, Ph.D.<o:p></o:p></i></p><p class=MsoNormal><i>Staff Scientist,<o:p></o:p></i></p><p class=MsoNormal><i>Stockholm Environment Institute-US<o:p></o:p></i></p><p class=MsoNormal><i>400 F St, Davis, CA 95616<o:p></o:p></i></p><p class=MsoNormal><a href="http://sei-us.org/"><i>http://sei-us.org/</i></a><i><o:p></o:p></i></p><p class=MsoNormal><i><o:p> </o:p></i></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p></div></div></body></html>