Database¶

RStudio Package Manager supports multiple database options. Currently, the supported databases are SQLite and PostgreSQL.

Customize the Database.Provider property with a database scheme appropriate for your organization. See the Database section in the appendix for details.

SQLite¶

SQLite is the default database provider.

RStudio Package Manager will use SQLite database if the Database.Provider setting has a value of sqlite or if Provider is not present in the configuration file.

Here is a partial configuration which chooses to use SQLite.

; /etc/rstudio-pm/rstudio-pm.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-pm/rstudio-pm.gcfg

[SQLite]
Dir = /mnt/rstudio-pm/sqlite

If this field is not specified, it will default 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.

PostgreSQL¶

PostgreSQL is an available database provider which is more powerful and performant than SQLite.

You must provide your own Postgres server which will likely be a separate server from your RStudio Package Manager server (but not required). We currently support any 9.x, 10.x, or 11.x version greater than or equal to 9.4. Your Postgres server does not have to be dedicated to RStudio Package Manager, but it must have its own dedicated database.

To use Postgres, select it as your provider with Database.Provider = postgres. You will also need to provide a fully qualified Postgres URL in Postgres.URL. The user credentials supplied in this URL must have read/write permissions to the database referenced at the end of url. Please ensure that you have already created a blank database with the name given at the end of your URL.

The user credentials can be included in the Postgres.URL URL - remember to URL-encode any special characters in the username or password. We recommend using Postgres.Password to avoid using a bare password in your configuration file. Examples using Postgres.Password are found in PostgreSQL Passwords section.

Usage Data¶

Using a separate PostgreSQL database for usage data¶

; /etc/rstudio-pm/rstudio.gcfg

[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/rstudio_pm"
UsageDataURL = "postgres://username:password@db.seed.co/rstudio_pm_usage"

Using a single database with a separate schema for usage data¶

; /etc/rstudio-pm/rstudio-pm.gcfg

[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/rstudio_pm"
UsageDataURL = "postgres://username:password@db.seed.co/rstudio_pm?search_path=metrics"

PostgreSQL Passwords¶

When your PostgreSQL connection URL requires a password, use Postgres.Password with an encrypted value to avoid credential leakage. The configuration appendix section explains encrypted settings, how to encrypt secrets like passwords, and using them in your configuration file.

Note

We do not recommend embedding passwords directly in the Postgres.URL connection URL setting.

RStudio Package Manager uses Postgres.Password when connecting to your PostgreSQL database. Do not percent-encode your Postgres.Password value. If necessary, RSPM will encode your password when it is combined with the Postgres.URL.

Note

Passwords included directly in Postgres.URL need to have special character percent-encoded. The set of characters that must be encoded within the user information component of a URL can be found in Section 3.2.1 of RFC 3986.

Here is a sample configuration using a PostgreSQL connection URL with a username and a separate, encrypted password.

; /etc/rstudio-pm/rstudio-pm.gcfg
[Postgres]
URL = "postgres://username@db.seed.co/rspm"
Password = <base64 encrypted password value>

The historical database connection URL can also be used with an encrypted password. Use Postgres.UsageDataPassword to provide the password associated with the connection URL Postgres.UsageDataURL.

Here is a sample configuration that has two separate Postgres URLs; one for the primary database and one for the usage database. Both connection URLs have separate, encrypted passwords.

; /etc/rstudio-pm/rstudio-pm.gcfg
[Postgres]
URL = "postgres://username@db.seed.co/rspm"
Password = <base64 encrypted password value>
UsageDataURL = "postgres://username@db.seed.co/rspm_metrics"
UsageDataPassword = <base64 encrypted password value>

Usage Data¶

RStudio Package Manager relies on two databases by default. The primary database stores information needed to run the service including the arrangement of repositories, sources, and packages. Another database is used to record usage data like the number of times a package was downloaded.

If using SQLite, these two databases will be created automatically in the configured directory. If using PostgreSQL, you will need to define two different databases or schemas: Postgres.URL and Postgres.UsageDataURL.

If you do not wish to track usage data, you can disable this feature by setting Server.UsageDataEnabled = false. If disabled, usage data will not be tracked or displayed. You can use the Server.UsageDataRetention setting to alter the amount of usage data you wish to retain (the default is 365 days). Increasing this value will consume more disk space for the usage data database and may negatively impact performance slightly over time for busy servers.