What content do -shm and -wal files contain after running checkpoint?

Question

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.

Selfconscious

Answer 1

After searching through numerous sources, I believe the following to be true:

1. The -shm file contains an index to the -wal file. The -shm file improves performance when reading the -wal file.
2. If the -shm file gets deleted, it get created again during next database access.
3. If checkpoint is run, the -wal file can be deleted.

To perform safe backups:

1. It is recommended that you use SQLite backup functions for making backups. SQLite library can even make backups of an online database.
2. 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.
3. 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.

Selfconscious

Answer 2

The -shm file does not contain any permanent data.

When the last connection is closed, the database is automatically checkpointed, and the -wal file is then deleted. This implies that after a checkpoint, and if no other connections exist, the -wal file does not contain any important data.

If possible, you should close the connection before taking the backup.

Reggy

Would -wal file contain anything after running checkpoint. Consider that connection is not closed. – Selfconscious

In the 10.15.5 macOS Messages app, I signed out then quit. Both the -shm and -wal file are still there. – Cerise

I would never delete those files manually. There is nothing to lose other than adding minor complexity and you can lose your data. If you want them deleted: disable the methods that create them. – Langbehn