What content do -shm and -wal files contain after running checkpoint?
c, sqlite, sqlite-journal-mode, unix
Solution
After searching through numerous sources, I believe the following to be true:
- The `-shm` file contains an index to the `-wal` file. The `-shm` file improves performance when reading the `-wal` file.
- If the `-shm` file gets deleted, it get created again during next database access.
- If `checkpoint` is run, the `-wal` file can be deleted.
To perform safe backups:
- It is recommended that you use SQLite backup functions for making backups. SQLite library can even make backups of an online database.
- If you don't want to use (1), then the best way is to close the database handles. This ensures a clean and consistent state of the database file, and deletes the `-shm` and `-wal` files. A backup can then be made using `cp`, `scp` etc.
- If the SQLite database file is intended to be transmitted over a network, then the vacuum command should be run after `checkpoint`. This removes the fragmentation in the database file thereby reducing its size, so you transfer less data through network.
Problem
I am taking the backup of SQLite DB using cp command after running wal_checkpoint(FULL). The DB is being used in WAL mode so there are other files like -shm and -wal in my folder. When I run wal_checkpoint(FULL), the changes in WAL file get committed to the database. I was wondering whether -wal and -shm files get deleted after running a checkpoint. If not, then what do they contain ? I know my backup process is not good since I am not using SQLite backup APIs. This is a bug in my code.