SQLite Forum

Dump and online backupjexclude tables
Login

Dump and online backupjexclude tables

(1) By anonymous on 2020-09-28 03:47:52 [link]

Hello, 

I believe with .dump, one can specify a list of tables. For such, and backup, it seems not possible to exclude. Or is it? Or might such be considered? I'm unsure of what others might find such useful, but for me, as I keep rotated backups, and they can accumulate in size, excluding FTS that can be rebuilt would save a lot of space. With backup, I can drop a table yet maybe the backup time and needed space during would be quicker and less.

(2) By Rowan Worth (sqweek) on 2020-09-29 02:31:09 in reply to 1 [link]

The sqlite3_backup API operates on the page level, and I'd wager it treats the data within each page as opaque rather than parsing any of it. As such, I'd expect it doesn't _know_ which table a page belongs to, or anything about tables at all.

Theoretically it wouldn't be a huge effort for sqlite to infer which pages are relevant for a given set of tables/indices and setup a partial-backup based on that subset, however unless you start rewriting page data (and the embedded references to other pages) you'd still end up with a database of the same size just with a bunch of empty pages (which is still potentially advantageous if you use a compressed filesystem).

OTOH if you do start parsing and rewriting page data, this is sounding less like a backup and more like a VACUUM INTO.

Of course with any approach that subsets you end up with a backup which cannot be directly swapped back in to replace the live database, ie. you must have some recovery procedure in place to regenerate whatever was filtered out of the backup. As such, what is the downside to the .dump {relevant-tables} approach?

(3) By TripeHound on 2020-09-29 07:06:17 in reply to 2

> As such, what is the downside to the .dump {relevant-tables} approach?

My assumption (without casting a vote for or against) is that the OP wants something like `.dump --except <table-name>` so that it will continue working "as is" if new tables (that they **do** want dumping) are added. Currently (as I understand it), if they add a table, they'll have to remember to add it to the "dump list". Of course, with their proposed enhancement, if they add a new FTS table, they'd have to remember to add _it_ to the exclusion list.

(4) By Donald Griggs (dfgriggs) on 2020-09-29 13:12:05 in reply to 3 [link]

Since disk space is a concern with the OP, I wondered if a regimen of:

1. Weekly full backup with output of  .dump
2. Daily incremental backup of .dump

would result (especially if compressed) in smaller disk requirements even if the fts table was included.