4

I'm looking at creating a simple backup routine, using standard Python, to backup my GeoPackages. The routine needs to be flexible enough to be able to backup the geopackage, whilst it is open (in read mode). Whilst I don't have a requirement for it to backup in write mode, I'm sure this will be encountered at some stage. My design will simply be to copy and zip a list of GeoPackages that I have here.

So my questions are

  • Are there any known issues with copying/zipping a GeoPackage that is currently in read mode (both from an end user perspective and a backup routine perspective.
  • As above, but if the GeoPackage is in write mode?

Additionally I'm looking at writing a simple Python routine (actually I'll probably just search for one online, simple as can be) however are there any known facilities inside of QGIS or other software that might better facilitate this requirement?

3

GeoPackages are SQLite files and so techniques for backing up SQLite files should apply here.

The SQLite docs have some information on backups here: https://sqlite.org/backup.html and gives a couple of methods:

  1. Use the SQLite backup API from C code. I'm not sure if there's Python bindings for this.
  2. Open the database and run VACUUM INTO 'backup.gpkg'; - this will copy the open database to a new one called backup.gpkg as well as vacuuming it to remove data marked as deleted.

If these methods run without error (ie no "Disk Full" or power plug removed) then the resulting output file should be a valid SQLite DB and hence a valid GeoPackage backup of the original. It should also be consistent (ie all atomic operations complete), and should have a minor impact on other processes using the database (ie it won't lock them out for long periods of time).

The VACUUM INTO method does require a newer version of SQLite tools than I had on my system just now but downloading and running the latest is really easy - I only had to get the Linux binaries which were a couple of megabytes.

It shouldn't matter if the database is being written to by another process - that process can continue writing while the backup happens, but how much of the current written content is backed up will depend on the exact point that the backup happens. In any case the consistency property will ensure that your backup is complete to the transactional level of the writing process.

2
  • great thanks for that, - I dont think expectations are that edits are backed up, given the time of day ill be running the backup, but good to know if it evolves in the future. The vacuum aspect is something I hadn't thought of, thanks for that.
    – nr_aus
    Sep 4 '20 at 8:27
  • Additional info located here regarding running backups for SQLite databases in Python w3resource.com/python-exercises/sqlite/…
    – nr_aus
    Sep 7 '20 at 1:28
3

You might get more and better answers from http://dba.stackexchange.com/ because after all GeoPackage is just a SQLite database.

A simple backup method in GIS environment would be to run just

ogr2ogr -f gpkg backup.gpkg input.gpkg.

The same can be done with GDAL Python bindings without using the ogr2ogr executable. Because data gets written into a new database it effectively vacuums the database by the same. But if the GeoPackage is in use in read-write mode and there are pending transactions I am not sure about what data gets stored into the copy.

If you rather play with files and you know that the database is opened as read-only it is safe to backup just the main db file .gpkg. All possible temporary files https://sqlite.org/tempfiles.html can be skipped.

You can also backup just the .gpkg file when the database is opened as read-write but then it is not sure what your backup will contain. A better option is to backup also the journal files. What they are depends on the journal mode that the GeoPackage db is using.

If the GeoPackage db is using rollback journals https://sqlite.org/lockingv3.html#rollback you could check if the journal file exists. If there is no journal file then .gpkg is up to date and you could backup just that. If there is a journal file you can backup also that or loop and wait until the journal disappears. Usually it is just seconds but sometimes it could mean a long wait.

If GeoPackage is set to use write-ahead logging https://sqlite.org/wal.html a sidecar wal file is created also when the database is opened as read-only. As far as I know QGIS is changing GeoPackages into WAL. The wal file disappears only once the last connection to the database is closed properly. In the rollback journal mode the .gpkg is guaranteed to contain all the changes if there is no journal file but in WAL mode that logic cannot be used. If you control the system and know that GeoPackage is opened as read-only you can skip the wal file because it newer contains any pending transactions. Otherwise you should backup .gpkg and wal and shm files together and the backup would contain a snapshot from that exact moment.

Your backup system for the read-write case could also copy the .gpkg and either the journal file or wal+shm into a temporary place and then open and close the database. By doing that the pending changes would be integrated into main database file, the sidecar files would disappear, and you would have just .gpkg file to put in the final backup.

1
  • Yes interesting approach for running it inside of QGIS! Perform a file translate to same file type. So simple! ...im not sure how robust the 'hot backups' need to be at this stage, but thanks for the detailed information.
    – nr_aus
    Sep 4 '20 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.