search

zodb / relstorage

A backend for ZODB that stores pickles in a relational database.
Other
54 stars 46 forks source link

SQLite storage and backups #420

Closed pfw closed 3 years ago

pfw commented 3 years ago

This is a question rather than a bug... or maybe at most a documentation issue.

Is it safe to create a backup of a SQLite storage by just taking a backup of the the main.sqlite3 DB and ignore the oids.sqlite3 DB?

If oids.sqlite3 is needed are there any suggestions on how to ensure they are in sync at the backup time?

jamadden commented 3 years ago

Great question!

It's best to backup both databases. If no application is running when the files are copied, they will trivially be in sync.

If applications are writing to the database, then it suffices to make sure that the copy of the main database files occurs before the copy of the OID database file. Transactions against the OID database are always atomic and always run ahead of transactions against the main database, so an OID database "from the future" is acceptable, but one from the past is not. (The worst outcome of an OID database from the future is a gap in OID values going forward.) Note that not all transactions will use the OID database, so the timestamp on the file may not, in fact, be ahead of the timestamp of the main files.

If the OID database file is missing, or from the past, and the main database is opened for write transactions, new objects may get duplicate OIDs. That's bad. It would be necessary to update the OID database with the maximum used OID in the main database in order to prevent this. This can be done by using zodbconvert to copy the transactions to a new database, or it could be done manually with SQL queries.

pfw commented 3 years ago

Excellent, thanks for that.