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
 * 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.


Bo Victor Thomsen

Den 15/07/16 kl. 15:23 skrev Micha Silver:

------ 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.


Bo Victor Thomsen

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?

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,


Qgis-user mailing list
List info:http://lists.osgeo.org/mailman/listinfo/qgis-user
Micha Silver
Arava Drainage Authority

Qgis-user mailing list
List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

Reply via email to