RStudio Connect supports multiple database options. Currently, the supported databases are SQLite and PostgreSQL.
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
[Database] Provider = sqlite
9.1 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
migrate utility must be run as
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.
9.1.1 Migration Checklist
Use this checklist to guide your migration process:
migrate utility supports two commands
db: Migrate data between databases
help: Displays help
--config: The full or relative path to a Connect configuration file (
.gcfg). Defaults to
Flags for the
migrate db command:
--verify: Verify migration only.
--drop-all: Drop all existing data in the target before migrating.
By default, the
migrate db command will perform a full migration from SQLite to PostgreSQL and verify the migration. We assume that the PostgreSQL destination does not contain any data unless the
--drop-all flag is included.
Data migration copies data from the SQLite database to the PostgreSQL database. Data in the SQLite database remains after the migration; it is not removed. A verification step runs after the data copy completes and confirms the integrity of the migration:
- Row counts for all tables are verified.
- Each record is checked for the correct values.
Data verification will fail if Connect is started prior to the completion of data verification. Please ensure that Connect remains down until the data migration and verification are complete.
9.1.4 Configuration Requirements
When migrating data, the configuration file must contain valid configuration sections for both
[Postgres]. The migration utility will connect to the SQLite and PostgreSQL databases specified in the configuration.
Migrate SQLite data to an empty PostgreSQL database:
sudo /opt/rstudio-connect/bin/migrate db
Migrate SQLite data to a PostgreSQL database, first dropping all data in the PostgreSQL database:
sudo /opt/rstudio-connect/bin/migrate db --drop-all
Perform data verification only:
sudo /opt/rstudio-connect/bin/migrate db --verify
Specify a custom configuration file:
sudo /opt/rstudio-connect/bin/migrate --config /etc/connect/mycustomconfig.gcfg db
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.
[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.
[SQLite] Dir = /mnt/connect/sqlite
If this field is not specified, it will default to
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.
[Database] Provider = postgres [Postgres] URL = "postgres://username:firstname.lastname@example.org/connect"