Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
I am using a postgis database with qgis and managing it with pgadmin3
I have already investigated the backup/restore options within pgadmin 3 and done one test run on one database
I am wondering if this .backup file is the only/best way to do backups?
Ideally I would want to back up several postgis databases, daily and probably send the file to a remote server for offsite storage or at least email it.
Does anyone have any suggestions on backing up postgis databases beyond my current experience?
You can backup via the write ahead logs (WAL-segments in pgxlog-folder) together with a normal copy of the data folder as well. It has the advantage of being faster than restoring from a dump (depending on the last snapshot), but that does only brings an advantage for larger datasets.
Generally it is best to not store your data in the public schema as there´re the postgis related functions as well. That can lead to problems when there is a new postgis version you want to restore to.
First, you should realize that this is more of a general Postgres question, since all Postgres databases can be backup up, whether or not they are PostGIS. If you find you need more detailed answers, you should probably ask this question on dba.SE.
The standard way to back up a Postgres database is the pg_dump utility. You're question is extremely general, so I'm not going to go into detail on the options. Refer to the docs at https://www.postgresql.org/docs/9.5/static/app-pgdump.html.
There are a large number of Postgres administrative front-ends that include backup/restore features, including open source projects like pgAdmin and proprietary tools like Navicat and RazorSQL. I believe that most (possibly all) of the options you will find will just provide a GUI interface to pg_dump and pg_restore. Some of them, like Navicat, may also include scheduling, so that you can automate your backups. Many of the proprietary tools support multiple RDMBS, allowing you to manage SQL Server, Oracle, etc., in one interface, and transfer data easily between them.
There is a Postgres-specific open source project named Barman (http://www.pgbarman.org/) which manages your entire Postgres backup strategy, including full and incremental backups (WAL, or Write Ahead Log) to ensure point-in-time recovery. I don't know if this is something you need.
Using pgAdmin instead of pg_dump has some disadvantages. The dump options of pgAdmin are poorly documented. They all map directly to pg_dump options, but you have to look through the pgAdmin checkboxes and the pg_dump command line switches and figure out which is which. In addition, while the dump is running, the entire pgAdmin GUI will become unresponsive.
I tend to use pgAdmin backup for a one-off job, and pg_dump for anything that needs to be run repeatedly, as you can easily reproduce the job, or use the operating system scheduler (cron on Linux, Task Scheduler on Windows) to run it as desired.
Since you intend to back up several databases daily, the approach I would recommend is to test your pg_dump command, schedule it to run daily, and write the file directly to a remote location or to a local folder that will be automatically synced (e.g. a Dropbox folder).
