<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)"><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;}
/* 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.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-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
font-family:"Calibri","sans-serif";}
@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:235554030;
mso-list-type:hybrid;
mso-list-template-ids:-64701398 67698713 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1
{mso-list-id:321008285;
mso-list-type:hybrid;
mso-list-template-ids:-393951430 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l1:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l1:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-.25in;}
@list l1:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l2
{mso-list-id:1701316295;
mso-list-type:hybrid;
mso-list-template-ids:61082188 1490986344 67698691 67698693 67698689 67698691 67698693 67698689 67698691 67698693;}
@list l2: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 l2: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 l2: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 l2: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 l2: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 l2: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 l2: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 l2: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 l2: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>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:l2 level1 lfo5'><![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:l2 level1 lfo5'><![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:l2 level1 lfo5'><![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:l2 level2 lfo5'><![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:l2 level2 lfo5'><![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:l2 level1 lfo5'><![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:l2 level1 lfo5'><![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:l2 level1 lfo5'><![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></body></html>