I have an SQLite database gapongasi.db configured using WAL mode and I want to have daily archive with tar(1). With WAL, there are two files being used (1) gapongasi.db-wal and (2)gapongasi.db-shm, are these 2 files relevant to be archived?
-
Why would you want to archive it with tar if it's external to SQLite and thus risks getting corrupted archive if a write transaction was incomplete at the time of archival? SQLite has backup API which you can use.– Alexandr ZarubkinMar 22 at 7:54
Add a comment
|
1 Answer
Yes, the WAL file should be archived along with the .db, but if you archive them when the application is closed, there should be no wal file at all.
From the docs:
While a database connection is open on a WAL-mode database, SQLite maintains an extra journal file called a "Write Ahead Log" or "WAL File". The name of this file on disk is usually the name of the database file with an extra "-wal" suffix.
The WAL file exists for as long as any database connection has the database open. Usually, the WAL file is deleted automatically when the last connection to the database closes. However, if the last process to have the database open exits without cleanly shutting down the database connection, or if the SQLITE_FCNTL_PERSIST_WAL file control is used, then the WAL file might be retained on disk after all connections to the database have been closed. The WAL file is part of the persistent state of the database and should be kept with the database if the database is copied or moved. If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted. The only safe way to remove a WAL file is to open the database file using one of the sqlite3_open() interfaces then immediately close the database using sqlite3_close().
The shm file, instead, doesn't need to be archived:
The wal-index file or "shm" file is not actually used as a file. Rather, individual database clients mmap the shm file and use it as shared memory for coordinating access to the database and as a cache for quickly locating frame within the wal file. The name of the shm file is the main database file name with the four characters "-shm" appended. The shm does not contain any database content and is not required to recover the database following a crash.
