Sounds logical, Thanks
-- Original Message --
Subject: Re: [Qgis-user] Backing up GIS Data
Date: Sat, 16 Jul 2016 08:54:56 +0200
To: Micha Silver, Qgis-user
From: Bo Victor Thomsen
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.
I'm normally using the "Custom/binary" format, because it's
the fastest method to do the backup/restore cycle.
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.
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.
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.
Regards
Bo Victor Thomsen
AestasGIS
Denmark
Den 15/07/16 kl. 15:23 skrev Micha
Silver:
Hi
-- 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
As an old GIS database dog -
It's a wise and smart decision to use Postgres/PostGis
for storing and using spatial data.
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.
Regards
Bo Victor Thomsen
AestasGIS
Denmark
Den 14/07/16 kl. 21:26 skrev Jeff
McKenna:
Hi Tyler,
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.
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
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?
Thanks
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.
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.
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.
Happy QGIS-ing,
-jeff
