Dear SQLite3 experts, I am using the SQLite online backup API as referenced from: https://www.sqlite.org/backup.html I opt to design a C daemon that can Backup the database file in real-time, which means whenever the python (or some other logging program) writes to the source database, the daemon will replicate the newly inserted data to the backup database incrementally rather than copying the whole database file. (insert XXX -> daemon -> backup database also shows XXX)

My C program looks like this:

    int rc;
    sqlite3 *pSrc;
    sqlite3 *pDst;
    sqlite3_backup *pBackup;

    rc = sqlite3_open(database_src, &pSrc);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Can't open source database, errmsg=%s\n", sqlite3_errmsg(pSrc));
        return rc;
    }

    rc = sqlite3_open(database_dst, &pDst);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Can't open destination errmsg=%s\n", sqlite3_errmsg(pDst));
        return rc;
    }

    pBackup = sqlite3_backup_init(pDst, "main", pSrc, "main");
    if (pBackup == NULL) {
        fprintf(stderr, "Can't initialize backup, errmsg=%s\n", sqlite3_errmsg(pDst));
        return rc;
    }
    while (!stop_daemon) {
        rc = sqlite3_backup_step(pBackup, page_size);
        if (rc == SQLITE_OK || rc == SQLITE_BUSY || rc == SQLITE_LOCKED) {
            auto tnow_t = chrono::system_clock::to_time_t(chrono::system_clock::now());
            cout << "SQLite backup sleep due to lock on time=" << ctime(&tnow_t)
                 << ", rc=" << rc << endl;
            sqlite3_sleep(sqlite3_sleep_time);
        } else if (rc != SQLITE_DONE) {
            fprintf(stderr, "Failed to backup database, rc=%d\n", rc);
            return rc;
        }
    }

    rc = sqlite3_backup_finish(pBackup);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to finalize backup, errmsg=%s\n", sqlite3_errmsg(pDst));
        return rc;
    } 

My python program for continuous logging looks like this:

for i in range(1, args.rows):
    timeNow = datetime.datetime.now()
    conn.execute("INSERT or IGNORE INTO EVENTLOG (ID,EVENTTIME) VALUES (?, ?)", (i, timeNow))
    conn.commit()
    time.sleep(0.1)

I hope to see that, when python writes to the original database per 0.1 seconds, the daemon will update the backup database accordingly with what python writes also in per 0.1 second. However, whenever the python write to the original database, the daemon does not accordingly update the backup database unlike the example said "Whether or not the backup process is restarted as a result of writes to the source database mid-backup, the user can be sure that when the backup operation is completed the backup database contains a consistent and up-to-date snapshot of the original. "

Questions as follows:

(1) I'm using while(!stop_daemon) to make it always running the backup process in the daemon mode, or do I have to use sqlite3_backup_finish, and re-init again even for incremental backup?

(2) Or it is impossible to impelemnt this kind of backup mechamism in SQLite?

Thanks in advance!

sqlite3_backup_step implies a read lock like any other database read, and if the database changes before all pages are written to the backup DB it has to start over again from the first page. The only exception to this is if the backup is being performed by the same process that is writing to the main DB -- in this situation I believe the backup code is able to rewrite only the pages which have changed.

However with such frequent transactions I'm still not sure it would ever complete a full backup pass. I'd recommend stress testing your design thoroughly before going too far because small rapid transactions are unlikely to scale well.

Thanks for your kindly reply, I did solved it with full back approach mentioned in https://www.sqlite.org/backup.html