SQLite

SQLite is the default database provider.

SQLite is an appropriate database for single-server Posit Connect environments. SQLite data files must be on a local file system; they cannot reside on a networked file system like NFS.

Note

SQLite allows only one process at a time to interact with its data files. It relies on file locking, which can be slow and unreliable over NFS. Using SQLite when its data files reside on a networked file system can lead to database corruption, which requires a restore from backup.

Use PostgreSQL in multi-node environments.

Connect uses a SQLite database when the Database.Provider setting has a value of sqlite or if Provider is not present in the configuration file.

; /etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = sqlite

You can also specify the directory to store the SQLite file on your file system. This can be done by specifying SQLite.Dir in the configuration file.

; /etc/rstudio-connect/rstudio-connect.gcfg
[SQLite]
Dir = /mnt/posit-connect/sqlite

If this field is not specified, it defaults to {Server.DataDir}/db. This location must exist on local storage.

If the location for Server.DataDir is not local storage but a networked location over NFS, configure the SQLite.Dir setting so it still resides on some local volume.

; /etc/rstudio-connect/rstudio-connect.gcfg
[Server]
DataDir = /mnt/nfs/posit-connect/data

[SQLite]
Dir = /mnt/local/posit-connect/sqlite

SQLite backups

The SQLite.Backup configuration setting is enabled by default, which ensures that Posit Connect periodically backs up its database while running.

You can adjust the default backup behavior with a configuration like the following:

; /etc/rstudio-connect/rstudio-connect.gcfg
[SQLite]
Backup = true
BackupFrequency = 24h
BackupRetentionLimit = 7

This configuration performs an online backup operation every 24 hours. Posit Connect retains up to 7 of those backups. If an eigth backup is created, the oldest of the previous backups is deleted. This provides a grace period for an administrator implementing a data retention policy, such as a practice of copying backups to tape periodically.

Backups are stored in the same directory as the main database file: /var/lib/rstudio-connect/db by default. Backups are lexically sortable, because they are timestamped with the UNIX epoch time padded to 11 digits. For example, if the server’s database file is /var/lib/rstudio-connect/db/connect.db, a backup of that database could be /var/lib/rstudio-connect/db/connect.db.01508526538.

The same process is applied to the instrumentation database file. It is backed up based on the same settings as for the main database file.

Automatic SQLite backups are not a complete backup solution for Posit Connect. The Connect variable data must be kept in sync with the database. Use the backup workflow to capture both data sources.

Restoring a SQLite backup is straightforward:

  • Ensure that the backup is valid with the command:

    sqlite3 <backup-file-name> "PRAGMA integrity_check;"

  • Stop the Posit Connect service

  • Copy the current database file as well as its .wal file, if any. (If you wish to analyze them or send a copy to Posit Support)

  • Replace the current database file with the backup

  • If they exist, delete any .wal and .shm files associated with the previous database. Failure to do this could lead to further downtime and possible data corruption.

  • Start the Posit Connect service

Note also that Posit Connect has no way of restoring applications deployed or changes made since the last backup. Restoring the backup file causes these changes to be lost permanently.