<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>I haven't any extensive experience with moving databases from
windows to linux or vice versa, but I've been moving
(backup/restore) databases between windows hundred of times. <br>
</p>
<ul>
<li>I'm normally using the "Custom/binary" format, because it's
the fastest method to do the backup/restore cycle.</li>
<li>When I'm creating/ structuring a new spatial database, I
always leave the "public" schema alone and put data in another
schema created for that purpose.</li>
<li>When doing a backup for the purpose of moving a database, I
only backup the aforementioned *data* schema, *not* the "public"
schema, thus avoiding taking backup of hundreds of PostGIS
functions residing in schema "public". This makes it easier to
move spatial data from one PostGIS-enabled database to another
without annoying errors.</li>
<li>And - just as you - I use the "plain" format when it's
necessary to make some changes to the structure or fields with
a text editor during the move of the database.<br>
</li>
</ul>
Regards <br>
<br>
Bo Victor Thomsen<br>
AestasGIS<br>
Denmark<br>
<br>
<div class="moz-cite-prefix">Den 15/07/16 kl. 15:23 skrev Micha
Silver:<br>
</div>
<blockquote cite="mid:5788E3CE.9090005@arava.co.il" type="cite">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
<div id="smartTemplate4-template">
<div id="my_message">Hi <br>
</div>
</div>
<br>
<br>
<div id="smartTemplate4-quoteHeader">------ Original Message
------ Subject: Re: [Qgis-user] Backing up GIS Data Date: Fri,
15 Jul 2016 07:04:20 +0200 To: Qgis-user From: Bo Victor Thomsen</div>
<blockquote
cite="mid:46e99e20-8b2b-44ef-1a2a-c905cac25153@gmail.com"
type="cite">
<meta content="text/html; charset=utf-8"
http-equiv="Content-Type">
<p>As an old GIS database dog -</p>
<ul>
<li>It's a wise and smart decision to use Postgres/PostGis for
storing and using spatial data.<br>
</li>
<li>As for backup: Do *exactly* as Jeff writes :-). "Point in
time" backups are nice, but not the best backup solution for
Postgres databases. Jeff's solution is. <br>
</li>
</ul>
<br>
Regards<br>
<br>
Bo Victor Thomsen<br>
AestasGIS<br>
Denmark<br>
<br>
<p><br>
</p>
<p><br>
</p>
<p><br>
</p>
<br>
<div class="moz-cite-prefix">Den 14/07/16 kl. 21:26 skrev Jeff
McKenna:<br>
</div>
<blockquote
cite="mid:cc0bcf05-924e-8f8f-9a4c-21de230d634b@gatewaygeomatics.com"
type="cite">Hi Tyler, <br>
<br>
This is a good question, and an important one, and don't feel
bad about posting it here - likely we can all learn from this
discussion, as it definitely involves the whole QGIS
community. <br>
<br>
I have quite a lot of experience backing up databases,
especially PostgreSQL/PostGIS databases. I can tell you that
it is for sure important to run "pg_dump" as a daily backup
(in addition to your whole server image/backup) - that pg_dump
has saved me and my clients hundreds of times, and it is very
portable and easy to access (as opposed to your whole
image/machine backup). One very important point (that's I've
learned from experience) when using pg_dump is to *always* use
the custom binary/compressed output format (the "--format=c"
commandline switch for pg_dump). I've had </blockquote>
</blockquote>
<br>
I have always used the default "plain" format for pg_dump backups.
When time comes to migrate data to a new installation, it allows
me to edit the SQL backup file: restore only some of the tables,
change owners, schema names, even change the database name. This
is just a minor convenience. Am I making a mistake? Should I move
to the binary format to insure reliability? <br>
<br>
Thanks<br>
<blockquote
cite="mid:46e99e20-8b2b-44ef-1a2a-c905cac25153@gmail.com"
type="cite">
<blockquote
cite="mid:cc0bcf05-924e-8f8f-9a4c-21de230d634b@gatewaygeomatics.com"
type="cite">terrible times with the other output format types,
especially when restoring a database from a Windows server to
a Linux server etc (with hardcoded paths inside the backup).
I live by that format, swear by it, from experience, moving so
many client databases from one machine to another. <br>
<br>
Another mailing list to keep in mind is the PostGIS mailing
list, where these backup topics also pop up from time to time
- and discussions are more geo-related, so are very helpful,
than just the generic PostgreSQL mailing list. <br>
<br>
So, definitely implement an additional backup process using
pg_dump (you can experiment restoring it through the
"pg_restore" command), you won't regret the effort spent. <br>
<br>
Happy QGIS-ing, <br>
<br>
-jeff <br>
<br>
<br>
</blockquote>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Qgis-user mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
List info: <a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
Unsubscribe: <a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a></pre>
</blockquote>
<div class="moz-signature">
<div dir="LTR"> Micha Silver<br>
Arava Drainage Authority<br>
+972-523-665918 </div>
</div>
</blockquote>
<br>
</body>
</html>