<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:st1="urn:schemas-microsoft-com:office:smarttags" xmlns="http://www.w3.org/TR/REC-html40"
xmlns:ns0="http://schemas.microsoft.com/office/2004/12/omml">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (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]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="Street"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PostalCode"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="State"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="address"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="City"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="place"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--a:link
{mso-style-priority:99;}
span.MSOHYPERLINK
{mso-style-priority:99;}
a:visited
{mso-style-priority:99;}
span.MSOHYPERLINKFOLLOWED
{mso-style-priority:99;}
p.MSOLISTPARAGRAPH
{mso-style-priority:34;}
li.MSOLISTPARAGRAPH
{mso-style-priority:34;}
div.MSOLISTPARAGRAPH
{mso-style-priority:34;}
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:Calibri;}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
p.msolistparagraph, li.msolistparagraph, div.msolistparagraph
{margin-top:0in;
margin-right:0in;
margin-bottom:0in;
margin-left:.5in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:Calibri;}
span.EmailStyle18
{mso-style-type:personal;
font-family:Calibri;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:1701316295;
mso-list-type:hybrid;
mso-list-template-ids:61082188 1490986344 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l0: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;
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;}
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=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Vishal,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>P.S. – your raster notes are a good
start. Haven’t had a chance to read thru to see if I can help with your questions
at the end.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>I would add a link to the PostGIS Raster
manual section of the PostGIS docs since that includes descriptions and
examples for most of the functions. It’s still not quite complete
but about 90% so.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a
href="http://www.postgis.org/documentation/manual-svn/RT_reference.html">http://www.postgis.org/documentation/manual-svn/RT_reference.html</a><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><st1:City w:st="on"><st1:place w:st="on"><font size=2
color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'>Regina</span></font></st1:place></st1:City><font size=2
color=navy face=Arial><span style='font-size:10.0pt;font-family:Arial;
color:navy'><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><a href="http://www.postgis.us/">http://www.postgis.us</a><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></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'><font size=3
face="Times New Roman"><span style='font-size:12.0pt;font-family:"Times New Roman"'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <b><span
style='font-weight:bold'>On Behalf Of </span></b>Vishal Mehta<br>
<b><span style='font-weight:bold'>Sent:</span></b> Thursday, August 04, 2011
8:11 PM<br>
<b><span style='font-weight:bold'>To:</span></b> postgis-users@postgis.refractions.net<br>
<b><span style='font-weight:bold'>Subject:</span></b> [postgis-users] help with
backing up and restoring a postgisdatabase</span></font><font size=3
face="Times New Roman"><span style='font-size:12.0pt;font-family:"Times New Roman"'><o:p></o:p></span></font></p>
</div>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Hi
all,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>I
am working with windows7/ postgres9.0.4/postgis2.0 which I recently installed
and started testing like so:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><a
href="http://algoesalgo.wordpress.com/tag/postgis2-0/">http://algoesalgo.wordpress.com/tag/postgis2-0/</a><o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>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></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>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></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 face=Calibri><span style='font-size:11.0pt'><span style='mso-list:Ignore'>-<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]>Pg_dump qns:<o:p></o:p></p>
<p class=msolistparagraph><font size=2 face=Calibri><span style='font-size:
11.0pt'>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></span></font></p>
<p class=msolistparagraph><font size=2 face=Calibri><span style='font-size:
11.0pt'>is it best to backup data only, skipping schema (same as above)<o:p></o:p></span></font></p>
<p class=msolistparagraph><font size=2 face=Calibri><span style='font-size:
11.0pt'>any other flags that are best set a certain way?<o:p></o:p></span></font></p>
<p class=msolistparagraph><font size=2 face=Calibri><span style='font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=msolistparagraph><font size=2 face=Calibri><span style='font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 face=Calibri><span style='font-size:11.0pt'><span style='mso-list:Ignore'>-<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]> Psql dbname<infile, and
pg_restore qns<o:p></o:p></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 face=Calibri><span style='font-size:11.0pt'><span style='mso-list:Ignore'>-<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![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'><font size=2 face=Calibri><span
style='font-size:11.0pt'>Qn: Should this be based on template_postgis instead?<o:p></o:p></span></font></p>
<p class=msolistparagraph style='margin-left:1.0in;text-indent:-.25in;
mso-list:l0 level2 lfo2'><![if !supportLists]><font size=2 face="Courier New"><span
style='font-size:11.0pt;font-family:"Courier New"'><span style='mso-list:Ignore'>o<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![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'><font size=2 face=Calibri><span
style='font-size:11.0pt'>I got errors of various sorts, mainly:<o:p></o:p></span></font></p>
<p class=msolistparagraph style='margin-left:1.0in'><font size=2 face=Calibri><span
style='font-size:11.0pt'>Public.geometry does not exist; myschema already
exists<o:p></o:p></span></font></p>
<p class=msolistparagraph style='margin-left:1.0in'><font size=2 face=Calibri><span
style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=msolistparagraph style='margin-left:1.0in;text-indent:-.25in;
mso-list:l0 level2 lfo2'><![if !supportLists]><font size=2 face="Courier New"><span
style='font-size:11.0pt;font-family:"Courier New"'><span style='mso-list:Ignore'>o<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]> with pg_restore and a tarred
backup , I tried to restore it into the same ‘<st1:City w:st="on"><st1:place
w:st="on">bangalore</st1:place></st1:City>’ database (without deleting
it first) and got errors that the tables and schema already existed.<o:p></o:p></p>
<p class=msolistparagraph><font size=2 face=Calibri><span style='font-size:
11.0pt'><o:p> </o:p></span></font></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 face=Calibri><span style='font-size:11.0pt'><span style='mso-list:Ignore'>-<font
size=1 face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![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:l0 level1 lfo2'><![if !supportLists]><font size=2 face=Calibri><span
style='font-size:11.0pt'><span style='mso-list:Ignore'>-<font size=1
face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]><o:p> </o:p></p>
<p class=msolistparagraph style='margin-left:.75in;text-indent:-.25in;
mso-list:l0 level1 lfo2'><![if !supportLists]><font size=2 face=Calibri><span
style='font-size:11.0pt'><span style='mso-list:Ignore'>-<font size=1
face="Times New Roman"><span style='font:7.0pt "Times New Roman"'>
</span></font></span></span></font><![endif]><o:p> </o:p></p>
<p class=msolistparagraph style='margin-left:.75in'><font size=2 face=Calibri><span
style='font-size:11.0pt'>ERROR: syntax error at or near "1"<o:p></o:p></span></font></p>
<p class=msolistparagraph style='margin-left:.75in'><font size=2 face=Calibri><span
style='font-size:11.0pt'>LINE 282: 1 779330 1437291 BN1 6.5499999999999998
0101000020E6100000A3...<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.25in'><font size=2 face=Calibri><span
style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.25in'><font size=2 face=Calibri><span
style='font-size:11.0pt'>Which is referring to:<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.25in'><font size=2 face=Calibri><span
style='font-size:11.0pt'>COPY borewells (gid, "UTM_X_",
"UTM_Y_", "Code_No", "Water_Leve", geom) FROM
stdin;<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.25in'><font size=2 face=Calibri><span
style='font-size:11.0pt'>1 779330
1437291
BN1 6.5499999999999998
0101000020E6100000A3FE0F26CC645340553700202FFA2940<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>I
could really use some help, <o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Thanks,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'>Vishal<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><a
href="http://sei-us.org/about/staff_person/19"><i><span style='font-style:italic'>Vishal
K. Mehta</span></i></a><i><span style='font-style:italic'>, Ph.D.<o:p></o:p></span></i></span></font></p>
<p class=MsoNormal><i><font size=2 face=Calibri><span style='font-size:11.0pt;
font-style:italic'>Staff Scientist,<o:p></o:p></span></font></i></p>
<p class=MsoNormal><st1:City w:st="on"><st1:place w:st="on"><i><font size=2
face=Calibri><span style='font-size:11.0pt;font-style:italic'>Stockholm</span></font></i></st1:place></st1:City><i><span
style='font-style:italic'> Environment Institute-US<o:p></o:p></span></i></p>
<p class=MsoNormal><st1:address w:st="on"><st1:Street w:st="on"><i><font
size=2 face=Calibri><span style='font-size:11.0pt;font-style:italic'>400 F St</span></font></i></st1:Street><i><span
style='font-style:italic'>, <st1:City w:st="on">Davis</st1:City>, <st1:State
w:st="on">CA</st1:State> <st1:PostalCode w:st="on">95616</st1:PostalCode></span></i></st1:address><i><span
style='font-style:italic'><o:p></o:p></span></i></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><a
href="http://sei-us.org/"><i><span style='font-style:italic'>http://sei-us.org/</span></i></a><i><span
style='font-style:italic'><o:p></o:p></span></i></span></font></p>
<p class=MsoNormal><i><font size=2 face=Calibri><span style='font-size:11.0pt;
font-style:italic'><o:p> </o:p></span></font></i></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Calibri><span style='font-size:11.0pt'><o:p> </o:p></span></font></p>
</div>
</div>
</body>
</html>