Created
April 25, 2025 11:38
-
-
Save darabi/c9728a84c7c87d01028d24fb5fc23c8c to your computer and use it in GitHub Desktop.
Test sqlite3 online (live) backup
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # we want to test backing up a DB which is being written to by another process | |
| # create a table (this is taken from the schema of the fine LDAP server lldap: https://github.com/lldap/lldap/) | |
| sqlite3 test.db <<EOF | |
| CREATE TABLE IF NOT EXISTS "users" ( "user_id" text(255) NOT NULL PRIMARY KEY, "email" text(255) NOT NULL, "creation_date" text NOT NULL, "password_hash" blob, "totp_secret" text(64), "mfa_type" text(64), "uuid" text(36) NOT NULL , "display_name" text(255), "lowercase_email" text(255) NOT NULL DEFAULT 'UNSET'); | |
| CREATE UNIQUE INDEX "unique-user-email" ON "users" ("email"); | |
| CREATE UNIQUE INDEX "unique-user-uuid" ON "users" ("uuid"); | |
| CREATE UNIQUE INDEX "unique-user-lower-email" ON "users" ("lowercase_email"); | |
| EOF | |
| # we create inserts with a commit after 100 inserts | |
| echo 'BEGIN TRANSACTION;' > insert-users.sql | |
| i=0 | |
| # drink a coffee for 5 minutes | |
| while (( $i < 100000 )); do | |
| echo "insert into users (user_id, email, creation_date, uuid, lowercase_email) values ( '$i', '$i', '$(date +%Y-%m-%dT%H:%M:%S.000+00)', '$i', '$i');" >> insert-users.sql | |
| (( i = i + 1 )) | |
| if (( i % 100 == 0 )) ; then | |
| printf "COMMIT;\nBEGIN TRANSACTION;\n" >> insert-users.sql | |
| fi | |
| done | |
| # start the inserts in the background | |
| ( cat insert-users.sql | sqlite3 test.db ) & | |
| # try to create backups every 1 s | |
| n=0 && while (( n < 20 )) ; do sqlite3 test.db ".backup backup-$(date +%H%M%S).db" ; sleep 1 ; (( n = n + 1 )) ; done | |
| Error: database is locked | |
| Error: database is locked | |
| Error: database is locked | |
| Error: database is locked | |
| Error: database is locked | |
| Error: database is locked | |
| Error: database is locked | |
| Error: database is locked | |
| Runtime error near line 71808: database is locked (5) | |
| Runtime error near line 71809: cannot start a transaction within a transaction | |
| Error: database is locked | |
| Runtime error near line 78132: database is locked (5) | |
| Runtime error near line 78133: cannot start a transaction within a transaction | |
| Error: database is locked | |
| Error: database is locked | |
| Error: database is locked | |
| Error: database is locked | |
| [2]- Exit 1 cat insert-users.sql | sqlite3 test.db | |
| # ll | |
| total 110444 | |
| drwxr-xr-x 2 root root 4096 Apr 25 12:28 ./ | |
| drwxr-xr-x 4 root root 4096 Apr 25 12:00 ../ | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122804.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122805.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122806.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122807.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122808.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122809.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122810.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122811.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122812.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122813.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122814.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122815.db | |
| -rw-r--r-- 1 root root 0 Apr 25 12:28 backup-122816.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122817.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122818.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122819.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122820.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122821.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122822.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 12:28 backup-122823.db | |
| -rw-r--r-- 1 root root 14882579 Apr 25 12:19 insert-users.sql | |
| -rw-r--r-- 1 root root 12271616 Apr 25 12:28 test.db | |
| # clean up | |
| sqlite3 test.db 'delete from users; vacuum; ' | |
| rm backup-*.db | |
| # let's retry with WAL https://www.sqlite.org/wal.html | |
| echo 'pragma journal_mode=WAL;' | sqlite3 test.db | |
| ( cat insert-users.sql | sqlite3 test.db ) & | |
| # try to create backups every 1 s | |
| n=0 && while (( n < 20 )) ; do sqlite3 test.db ".backup backup-$(date +%H%M%S).db" ; sleep 1 ; (( n = n + 1 )) ; done | |
| # no errors | |
| ll | |
| total 112792 | |
| -rw-r--r-- 1 root root 3764224 Apr 25 13:10 backup-131050.db | |
| -rw-r--r-- 1 root root 8331264 Apr 25 13:10 backup-131051.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131052.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131053.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131054.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131055.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 13:10 backup-131056.db | |
| -rw-r--r-- 1 root root 14882579 Apr 25 12:19 insert-users.sql | |
| -rw-r--r-- 1 root root 12271616 Apr 25 13:10 test.db | |
| # what is the largest user_id in each of these backups? | |
| for db in backup*.db ; do sqlite3 $db 'select * from users ' | tail -n 1 | cut -f 1 -d \| ; done | |
| 32099 | |
| 68499 | |
| 99999 | |
| # that's too fast, let's remove the transactions | |
| egrep -v '(BEGIN|COMMIT)' insert-users.sql > insert-users-no-transactions.sql | |
| # clean up | |
| sqlite3 test.db 'delete from users; vacuum; ' | |
| rm backup-*.db | |
| ( cat insert-users-no-transactions.sql | sqlite3 test.db ) & | |
| n=0 && while (( n < 120 )) ; do sqlite3 test.db ".backup backup-$(date +%H%M%S).db" ; sleep 1 ; (( n = n + 1 )) ; done | |
| # now it takes more than 2 minutes | |
| ll | |
| -rw-r--r-- 1 root root 36864 Apr 25 13:19 backup-131903.db | |
| -rw-r--r-- 1 root root 118784 Apr 25 13:19 backup-131904.db | |
| -rw-r--r-- 1 root root 188416 Apr 25 13:19 backup-131905.db | |
| -rw-r--r-- 1 root root 258048 Apr 25 13:19 backup-131906.db | |
| -rw-r--r-- 1 root root 327680 Apr 25 13:19 backup-131907.db | |
| -rw-r--r-- 1 root root 397312 Apr 25 13:19 backup-131908.db | |
| -rw-r--r-- 1 root root 466944 Apr 25 13:19 backup-131909.db | |
| -rw-r--r-- 1 root root 536576 Apr 25 13:19 backup-131910.db | |
| ... | |
| -rw-r--r-- 1 root root 12247040 Apr 25 13:21 backup-132143.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 13:21 backup-132145.db | |
| -rw-r--r-- 1 root root 12271616 Apr 25 13:24 backup-132443.db | |
| for db in backup*.db ; do sqlite3 $db 'select * from users ' | tail -n 1 | cut -f 1 -d \| ; done | |
| 180 | |
| 781 | |
| 1441 | |
| 2089 | |
| 2734 | |
| 3393 | |
| 4044 | |
| 4689 | |
| 5346 | |
| 5995 | |
| 6646 | |
| 7302 | |
| 7947 | |
| 8569 | |
| 9214 | |
| 9867 | |
| 10520 | |
| 11173 | |
| 11826 | |
| ... | |
| 96000 | |
| 96739 | |
| 97437 | |
| 98194 | |
| 99008 | |
| 99882 | |
| 99999 | |
| 99999 | |
| # Conclusion: it is feasible to create backups of a running sqlite DB, iff it is set to WAL mode |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment