15 Database

RStudio Server and RStudio Server Pro support multiple database options. Currently, the supported databases are SQLite and PostgreSQL. When running RStudio Server Pro in a load balanced configuration, you must use a PostgreSQL database, as SQLite is insufficient for managing state between multiple nodes.

15.1 Configuration

In order to set up a database connection, modify the file /etc/rstudio/database.conf. The file contains documentation about how to use it, and you can simply uncomment any lines that are relevant to your configuration. Note that because the file can contain password data, this file must be user read/write only (file mask 600).

15.1.1 SQLite

By default, RStudio creates a SQLite database for you automatically under the /var/lib/rstudio-server directory. For single-node installations, this is sufficient, but as stated before, will not be sufficient for load balanced deployments.

You should never specify a SQLite directory that is on shared storage, such as NFS. Per the SQLite documentation, this can cause data corruption.

Sample configuration:

/etc/rstudio/database.conf

provider=sqlite

# Directory in which the sqlite database will be written
directory=/var/lib/rstudio-server

15.1.2 PostgreSQL

If you wish to use PostgreSQL, you must create an empty database for RStudio Server to connect to. You must not share this database with other products or services. The minimum supported PostgreSQL version is 9.5.

Sample configuration:

/etc/rstudio/database.conf

# Note: when connecting to a PostgreSQL database, a default empty rstudio database must first be created!
provider=postgresql

# Specifies the host (hostname or IP address) of the database host
host=localhost

# Specifies the database to connect to
database=rstudio

# Specifies the TCP port where the database is listening for connections
port=5432

# Specifies the database connection username
username=postgres

# Specifies the database connection password. This may be encrypted with the secure-cookie-key.
# The encrypted password can be generated using the helper command rstudio-server encrypt-password.
# It is strongly recommended that you encrypt the password!
password=postgres

PostgreSQL connection URIs are also supported if preferred. If specifying additional options than the ones provided above, such as sslmode, the use of a URI is required.

For example:

/etc/rstudio/database.conf

provider=postgresql

# Specifies the connection URL in the form of a postgresql:// connection URL. This can be used if you need
# to set special database settings that are not available with the other parameters. If set, this parameter will
# override any other postgresql parameters that have been set, with the exception of the password. A password in 
# the URI is supported as a convenience but we strongly recommend using the separate password field, which will
# always replace any password specified in the URI.
connection-uri=postgresql://postgres@localhost:5432/rstudio?sslmode=allow&options=-csearch_path=public

Note: The password in connection-uri may contain characters that may need to be URL-encoded to work properly. Avoid encoding the password by using the separate password field in the configuration.

15.1.2.1 PostgreSQL password encryption

A plain-text password in the password or connection-uri options of the /etc/rstudio/database.conf file must only be used temporarily for testing purposes. A warning will be present in RStudio log output when a plain-text password is being used.

We strongly recommend encrypting the password using the command rstudio-server encrypt-password. This way, if you have to backup your configuration, save it to a repository or share it with RStudio Support, your PostgreSQL password will be protected.

Use the following steps to encrypt the PostgreSQL password:

  • Remove the password from the connection-uri option if defined in the database.conf file.
  • Run the command rstudio-server encrypt-password (you may need sudo), and enter the PostgreSQL password.
  • Copy the resulting encrypted password printed in the terminal.
  • Add or replace the password option in the database.conf file using the encrypted password copied above.
  • Restart RStudio. Confirm it operates normally. You should no longer see a warning about plain-text password in RStudio logs.

Note: Alternatively, you can also “pipe” your password to the rstudio-server encrypt-password command to skip the prompt. Useful when the password is already stored elsewhere. For example:

cat passwordfile | rstudio-server encrypt-password

15.2 Migration

When changing database providers, you must migrate your existing database data from your current database provider to the new provider to prevent unexpected data loss.

The following steps should be taken to perform a successful migration from SQLite to PostgreSQL:

  1. Create an empty database called rstudio in PostgreSQL (or any custom name according to your RStudio configuration below). Ensure the connection credentials work for the new database.
  2. Stop RStudio Server
  3. Switch to PostgreSQL by modifying the /etc/rstudio/database.conf file. If you are storing the SQLite database in a different location be sure to keep the directory option in the file during the migration.
  4. Run the command /usr/lib/rstudio-server/bin/migrate.sh. Watch for the output, confirm that the migration was successful.
  5. Once the data has been imported to the new database, restart RStudio Server.

Note: The migration from PostgreSQL to SQLite is not currently supported.