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 container-based environments.

Connect includes a migrate command for changing the database provider from a SQLite database to Postgres. 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. If you are migrating both the Connect core and instrumentation databases you will also need to configure the instrumentation database URL and credentials if they differ from the core database location and 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

Note

By default both the Connect core and instrumentation databases are migrated. You may use the -data flag to only migrate one or the other to PostgreSQL. The configuration settings Postgres.InstrumentationURL and Postgres.InstrumentationPassword may be set to specify a location separate from the Connect core database location and credentials. If not provided their values will default to the configured Connect PostgreSQL database.

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>"
InstrumentationURL = "postgres://username@db.seed.co/connect_instrumentation"
InstrumentationPassword = "<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.

To migrate both the Connect instrumentation and core databases, run:

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

To migrate only the core database:

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

To migrate only the instrumentation database:

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

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.