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.
The Server Migrations section contains related workflows.
Prerequisites
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.
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.Create a database for Posit Connect.
Create a database named
connect
in your PostgreSQL database.Either
root
orsudo
access on the Connect server.Reading the Connect configuration and connecting to the SQLite database requires elevated privileges.
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.