Changing Database Provider

Many simple Posit Connect installations use the SQLite database. This is a good option when getting started with Connect and for non-production, single-node deployments. Use PostgreSQL whenever multiple Connect nodes are involved, including Kubernetes and Docker-based environments.

Migrating from PostgreSQL back to SQLite is not supported.

Tip

The Server Migrations section contains related workflows.

Prerequisites

  1. PostgreSQL connection URL and credentials.

    Your Connect server needs access to a PostgreSQL database, which your organization needs to provision and manage. You need the database connection URL and the credentials.

  2. An encrypted PostgreSQL password.

    If your PostgreSQL database uses credentials, you should encrypt the password. Use rscadmin encrypt-config-value to create an encrypted password that you will use when configuring Connect.

  3. Create a database for Posit Connect.

    Create a database named connect in your PostgreSQL database.

  4. Either root or sudo access on the Connect server.

    Reading the Connect configuration and connecting to the SQLite database requires elevated privileges.

  5. Schedule / communicate downtime.

    Your Connect server is unavailable during the migration from SQLite to PostgreSQL. Schedule downtime and inform your stakeholders, as required by your organization.

Workflow

Completing this workflow causes Posit Connect to use PostgreSQL as its backing database. The PostgreSQL database is initialized with data previously tracked by the Connect SQLite database.

Step 1: Configure PostgreSQL

Modify your Connect configuration with PostgreSQL settings, which is used when transferring data from SQLite to PostgreSQL. Connect is still using SQLite, the default Database.Provider.

; /etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username@db.seed.co/connect"
Password = "<ENCRYPTED-VALUE>"

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

Step 2: Stop Connect

Stop the Connect server.

sudo systemctl stop rstudio-connect

Step 3: Migrate information

The migrate db command connects to both the SQLite and PostgreSQL databases and transfers Connect-managed records between the two.

sudo /opt/rstudio-connect/bin/migrate db

If this step encounters problems connecting to or transferring data to PostgreSQL, you can start Connect and continue using the SQLite database while you resolve the problem with PostgreSQL. Once solved, you can restart this workflow.

Step 4: Use PostgreSQL

Modify your Connect configuration to use the PostgreSQL database that you have just populated.

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

Step 5: Start Connect

Start the Connect server using the newly populated PostgreSQL database.

sudo systemctl start rstudio-connect

Once Connect starts using the PostgreSQL database, its information starts to deviate from what was stored by SQLite. You can no longer switch back to SQLite.