Skip to content

Changing Database Provider#

You may need to change the database provider for your Posit Package Manager installation from SQLite to Postres or vice versa:

  • 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 provider 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. Refer to the Stopping and Starting section for instructions

If you are also migrating your Package Manager installation to a new server, see the Server Migration section.

Changing Database Provider Checklist#

Use this checklist to guide your process for changing database provider:

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

Configuration Requirements#

When changing database provider, 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 changing from one database provider to another. For a high-level overview of the steps necessary to migrate from one database to another, reference the section on changing database providers. For the high-level steps involved in completing a server migration, reference the Server Migration section.

Commands#

The migrate utility supports two commands:

  • database: Migrates data between database providers
  • 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:

Terminal
$ /opt/rstudio-pm/bin/migrate help

Migrate SQLite data to an empty PostgreSQL database:

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

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

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

Perform data verification only:

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

Migrate PostgreSQL data to SQLite:

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

Specify a custom configuration file:

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