automating backup

esmondb · 2013-09-10 05:29:29

I'm trying to set up a backup system using TSQLRestServer.OnUpdateEvent

The update event checks a timestamp of when the server started and makes a backup if it hasn't been done recently. The problem is this falls over if a transaction is active with the error 'a vacuum can't be done inside a transaction' (using SQLite).

Would it be better to create a service on the server which gets triggered/co-ordinated on the client side instead of using OnUpdateEvent?

ab · 2013-09-10 06:45:15

Backup is not working if there are pending statements, or virtual external tables defined.
VACUUM should be in fact a no-op in this case (this is how it is handled if run from ORM methods).

I won't rely on high-level ORM methods for proper synchronize.

What you can do is use VSC and backup the file.
See http://stackoverflow.com/questions/2840 … -in-delphi
This will be safe and fast.

Or we may use official "backup API" of SQLite3 - see http://www.sqlite.org/backup.html - but which is blocking the whole DB access during process, which may be far from optimized.
Or the "update hook API" of SQlite3 - see http://www.sqlite.org/c3ref/update_hook.html - but which won't be triggered from all possible cases.

The easiest is to close the whole server, then backup the file, then relaunch the whole server.

tanghz · 2013-09-11 10:22:31

Hi AB,

I believe the performance of your mORMot is at Enterprise level for more serious application.
Therefore, closing down the server won't be an option for large scale web app/site.
Seeking third party's solution won't be better than a native mORMot one.
However, back up is critical for any serious project, hope you can provide a better your own solution to this issue.
It will make mot more competitive.

Also have you considered server load balance solutions?

Finally, how to get a notification if the thread I involved has new post in this forum.
This basic feature seems missing.

Will follow your move.
good luck.

ab · 2013-09-11 11:32:56

tanghz wrote:

Therefore, closing down the server won't be an option for large scale web app/site.

In Entreprise world, backup is to be done by dedicated IT people.

But...

tanghz wrote:

Seeking third party's solution won't be better than a native mORMot one.

Indeed, mORMot tries to follow KISS principle, so backup would definitively benefit of being integrated to its Kernel.
I've added a feature request - see http://synopse.info/fossil/tktview?name=31eaadc5a5

tanghz wrote:

Also have you considered server load balance solutions?

See "hosting" in the SAD.

tanghz wrote:

Finally, how to get a notification if the thread I involved has new post in this forum.

This is not part of the php forum code version I used.
I would need to upgrade to a newer version, but there is some dedicated piece of code within...

If we consider that external database backup is part of administrator of those database instances, we may focus only on SQLite3 database backup.

No solution has been chosen by now.
Your feedback is needed in our forums!

esmondb · 2013-09-12 10:41:00

From my point of view the official "backup API" looks best. The link above gives an example of a non-blocking way of using it at the bottom of the page. It warns that a large number of writes may stop a backup ever finishing but that is only if multiple threads are used and as mORMot only uses a single thread to access SQLite that shouldn't matter.

Not so concerned about solutions for dbs other than SQLite.

ab · 2013-09-12 12:01:21

esmondb wrote:

The link above gives an example of a non-blocking way of using it at the bottom of the page.

If I understand correctly, there may be minimal "chance" of the whole backup to be restarted in case of write to the database during the backup...

SQLite3 doc wrote:

There is one exception to this rule: If the source database is not an in-memory database, and the write is performed from within the same process as the backup operation and uses the same database handle (pDb), then the destination database (the one opened using connection pFile) is automatically updated along with the source.

One unique process with one unique DB handle is exactly how mORMot uses SQLite3.
Nice!

This is a recent update to the API, so I guess this is the right direction to go..

Thanks for the feedback and input!

ab · 2014-08-14 06:35:26

Two possibilities are emerging:
- Using the SQLite3 backup API - see http://synopse.info/fossil/tktview?name=428e45644c
- Using the InternalUpdateEvent() and a remote DB as used by http://blog.synopse.info/post/2014/06/2 … e-tracking - like a typical master/slave replication pattern - see http://synopse.info/forum/viewtopic.php … 851#p11851

ab · 2014-08-15 14:02:54

You can now use TSQLDataBase.BackupBackground() and BackupBackgroundWaitUntilFinished methods for performing asynchronous background backup of a SQLite3 database.

esmondb · 2014-08-28 09:28:13

Many thanks for implementing this. As soon as I've managed to make some money from my endeavours I'll make a donation.

ab · 2014-08-28 11:21:41

You are welcome!

And do not forget to list your app on the mORMot ShowCase list, if you can.
See http://blog.synopse.info/post/2014/08/2 … t-ShowCase

mORMot Open Source

#1 2013-09-10 05:29:29

automating backup

#2 2013-09-10 06:45:15

Re: automating backup

#3 2013-09-11 10:22:31

Re: automating backup

#4 2013-09-11 11:32:56

Re: automating backup

#5 2013-09-12 10:41:00

Re: automating backup

#6 2013-09-12 12:01:21

Re: automating backup

#7 2014-08-14 06:35:26

Re: automating backup

#8 2014-08-15 14:02:54

Re: automating backup

#9 2014-08-28 09:28:13

Re: automating backup

#10 2014-08-28 11:21:41

Re: automating backup

Board footer