9 Database

RStudio Connect 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 Section A.5 for details

Here is a partial configuration which chooses to use SQLite

Provider = sqlite

9.1 SQLite

SQLite is the default database provider.

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

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.

Dir = /mnt/connect/sqlite

If this field is not specified, it will default to {Server.DataDir}/db.

9.2 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 box from your RStudio Connect server (but not required). We currently support any 9.x version greater than or equal to 9.2. Your Postgres server does not have to be dedicated to RStudio Connect, 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.

Provider = postgres

URL = "postgres://username:password@db.seed.co/connect"

9.3 Changing Database Provider

Connect includes a migrate command for migrating data from one database to another.

The migration utility is installed at /opt/rstudio-connect/bin/migrate. It uses the configuration defined in /etc/rstudio-connect/rstudio-connect.gcfg unless you specify an alternate configuration file with the --config flag.

The migrate utility must be run as root.

The migrate utility can only be run when Connect is stopped. See Section 5.1 for information on stopping and restarting Connect.

Note: Migration from PostgreSQL to SQLite is not supported at this time.

If you are also migrating your RStudio Connect installation to a new server, see 4.8.

9.3.1 Database Migration Checklist

Use this checklist to guide your migration process:

  1. Shut down Connect - 5.1
  2. Back up your data - 4.7
  3. Ensure that you have a Postgres configuration section - 9.2
  4. Run the migration - B.2
  5. Update the Database.Provider configuration setting to point to the new database - A.5
  6. Restart Connect - 5.1

9.3.2 Configuration Requirements

When migrating data, the configuration file must contain valid configuration sections for both SQLite and Postgres. The migration utility will connect to the SQLite and PostgreSQL databases specified in the configuration.