Changing Database Provider

You may need to change the database provider for your RStudio Package Manager installation:

  • When moving from one environment to another (e.g., physical to virtual or on-prem to cloud)
  • To facilitate HA setup with more than one node
  • To move production data to a test environment

Package Manager includes a migrate command for migrating data from one database to another.

The migrate utility:

  • Is installed at /opt/rstudio-pm/bin/migrate. It uses the configuration defined in /etc/rstudio-pm/rstudio-pm.gcfg unless you specify an alternate configuration file with the --config flag.
  • Must be run by a member of the rstudio-pm Unix group, or the same group that is configured to use the CLI. The group that determines access can be customized.
  • Can only be run when the service is stopped. See the section on Stopping and Starting RSPM.

If you are also migrating your RSPM installation to a new server, see the Server Migrations section.

Database Migration Checklist

Use this checklist to guide your migration process:

  1. Shutdown the RSPM service
  2. Backup your data
  3. Ensure that you have defined both Postgres and Sqlite configuration sections - see the Postgres Database section
  4. Run the migration - see the Migration CLI section
  5. Update the Database.Provider configuration setting to point to the new database - see the Database section in the appendix
  6. Restart Package Manager

Configuration Requirements

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

If your server is configured to store usage data, then you must define PostgreSQL and SQLite databases for the main database and the usage data. The migration utility will migrate data from/to both databases.

Migration Utility

The migrate utility assists system administrators in migrating from one database to another or in transitioning RStudio Package Manager to a new server. For a high-level overview of the steps necessary to migrate from one database to another, see the section on changing database providers. For the high-level steps involved in completing a server migration, see the Server Migrations section.

Commands

The migrate utility supports two commands

  • database: Migrate data between databases
  • help: Displays help

Flags

Configuration for migrate

  • --config: The full or relative path to a Package Manager configuration file (.gcfg). Defaults to /etc/rstudio-pm/rstudio-pm.gcfg.

Flags for the migrate database command

  • --verify: Verify migration only.
  • --drop-all: Drop all existing data in the target before migrating.
  • --from: Database to migrate from (defaults to sqlite3).
  • --to: Database to migrate to (defaults to pgx).
  • --progress: Display progress bars while migrating (experimental).

By default, the migrate database command will copy the data from the SQLite (sqlite3) database into PostgreSQL (pgx), and verify the migration. We assume that the destination database does not contain any data unless the --drop-all flag is included.

Data migration copies data from the source database to the target database. Data in the source 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 Package Manager is started prior to the completion of data verification. Please ensure that Package Manager remains down until the data migration and verification are complete.

Examples

Display help:

/opt/rstudio-pm/bin/migrate help

Migrate SQLite data to an empty PostgreSQL database:

sudo /opt/rstudio-pm/bin/migrate database

Migrate SQLite data to a PostgreSQL database, first dropping all data in the PostgreSQL database:

sudo /opt/rstudio-pm/bin/migrate database --drop-all

Perform data verification only:

sudo /opt/rstudio-pm/bin/migrate database --verify

Migrate PostgreSQL data to SQLite:

sudo /opt/rstudio-pm/bin/migrate database --from=pgx --to=sqlite3

Specify a custom configuration file:

sudo /opt/rstudio-pm/bin/migrate --config /etc/rstudio-pm/mycustomconfig.gcfg database