<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"/>
<o:SmartTagType namespaceuri="urn:schemas-microsoft-com:office:smarttags"
name="PersonName"/>
<!--[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;
font-family:Calibri;
color:#1F497D;}
span.EmailStyle20
{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: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;
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;
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=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'><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'>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></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'>We have instructions documented here:<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/postgis_installation.html#hard_upgrade">http://www.postgis.org/documentation/manual-svn/postgis_installation.html#hard_upgrade</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'>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></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'>We compiled a new windows build yesterday so
is very up to date<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/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></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'>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></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'>Hope that helps,<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'> and Leo<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> Friday, August 05, 2011 2:50
PM<br>
<b><span style='font-weight:bold'>To:</span></b> <st1:PersonName w:st="on">PostGIS
Users Discussion</st1:PersonName><br>
<b><span style='font-weight:bold'>Subject:</span></b> Re: [postgis-users] help
with backing up and restoring apostgis database</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 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>Hi all,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;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></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>Information:<o:p></o:p></span></font></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 color="#1f497d" face=Calibri><span style='font-size:11.0pt;color:#1F497D'><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]><font
color="#1f497d"><span style='color:#1F497D'>Database I want to restore is
called ‘<st1:City w:st="on"><st1:place w:st="on">bangalore</st1:place></st1:City>’;
I want to restore it into an empty database called ‘empty’<o:p></o:p></span></font></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 color="#1f497d" face=Calibri><span style='font-size:11.0pt;color:#1F497D'><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]><font
color="#1f497d"><span style='color:#1F497D'>There are 3 schemas under this
database<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 color="#1f497d"
face="Courier New"><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'><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]><font color="#1f497d"><span
style='color:#1F497D'>‘Public’ was created on creation of <st1:City
w:st="on"><st1:place w:st="on">Bangalore</st1:place></st1:City> database. This
contains both raster and vector tables<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 color="#1f497d"
face="Courier New"><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'><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]><font color="#1f497d"><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></font></p>
<p class=msolistparagraph style='margin-left:1.0in;text-indent:-.25in;
mso-list:l0 level2 lfo2'><![if !supportLists]><font size=2 color="#1f497d"
face="Courier New"><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'><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]><font color="#1f497d"><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></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>What I did:<o:p></o:p></span></font></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 color="#1f497d" face=Calibri><span style='font-size:11.0pt;color:#1F497D'><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]><font
color="#1f497d"><span style='color:#1F497D'>Created an empty database in the
psql console:<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 color="#1f497d"
face="Courier New"><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'><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]><font color="#1f497d"><span
style='color:#1F497D'>CREATE DATABASE empty TEMPLATE=template0;<o:p></o:p></span></font></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 color="#1f497d" face=Calibri><span style='font-size:11.0pt;color:#1F497D'><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]><font
color="#1f497d"><span style='color:#1F497D'>Backup using pg_dump like so:<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 color="#1f497d"
face="Courier New"><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'><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]><font color="#1f497d"><span
style='color:#1F497D'>Pg_dump.exe –Fc –n “myschema”
–v <st1:City w:st="on"><st1:place w:st="on">bangalore</st1:place></st1:City>
> “backupfc.dump”<o:p></o:p></span></font></p>
<p class=msolistparagraph style='text-indent:-.25in;mso-list:l0 level1 lfo2'><![if !supportLists]><font
size=2 color="#1f497d" face=Calibri><span style='font-size:11.0pt;color:#1F497D'><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]><font
color="#1f497d"><span style='color:#1F497D'>Tried to restore like so:<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 color="#1f497d"
face="Courier New"><span style='font-size:11.0pt;font-family:"Courier New";
color:#1F497D'><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]><font color="#1f497d"><span
style='color:#1F497D'>Pg_restore.exe –dbname “empty”
–verbose “backupfc.dump”<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;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></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>Error while processing TOC:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>Error from TOC entry 2551: 1251 etc<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>Could not execute query type
“public.geometry does not exist<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>When I investigated the sql in the
backup (froma separate plain sql dump) I see this:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>CREATE TABLE borewells (<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'> gid integer NOT NULL,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'> "UTM_X_" double
precision,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'> "UTM_Y_"
double precision,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'> "Code_No"
character varying(254),<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>
"Water_Leve" double precision,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'> geom
public.geometry(Point,4326)<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>);<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>Its failing on the geom column. I am not
an expert with databases <b><span style='font-weight:bold'>but clearly there is
no geometry table in the public schema (which I am not wanting to backup
anyway).<o:p></o:p></span></b></span></font></p>
<p class=MsoNormal><b><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D;font-weight:bold'><o:p> </o:p></span></font></b></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;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></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;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></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;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></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>Thanks,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'>Vishal<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color="#1f497d" face=Calibri><span
style='font-size:11.0pt;color:#1F497D'><o:p> </o:p></span></font></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<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
5: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 postgis database<o:p></o:p></span></font></p>
</div>
</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:l1 level1 lfo4'><![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:l1 level1 lfo4'><![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:l1 level1 lfo4'><![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:l1 level2 lfo4'><![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:l1 level2 lfo4'><![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:l1 level1 lfo4'><![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:l1 level1 lfo4'><![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:l1 level1 lfo4'><![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>