I'm currently developing a small-scale web app using Rails, and have been looking into the best way to go about keeping backups of the database. I've decided to use SQLite3 for the database as it's small-scale enough that it makes sense (there will ideally be barely any traffic to the website), but as the application needs to be accessible on-demand 24/7, I want to make sure that any backup method doesn't interrupt things too much.
I've found a fair few old resources online that suggest just copying the database file, but this has obvious locking problems if the file is written to whilst the copy happens. The SQLite built-in .backup command seems to be what I'm after to avoid this, but I can't seem to find a way to trigger that properly from within Rails. Using the ActiveRecord connection.execute('.backup') doesn't work because it's not valid SQL syntax, and whilst there are appropriate methods to call the backup from inside the SQLite3 gem, I'm not sure if it's possible to get down to that object level from within ActiveRecord?
I could just set up a cron job/script that runs the sqlite command-line tool and executes the backup command, but I'm worried that running that concurrently with the Rails server could still potentially present concurrency issues?
