Changing Database Provider

You may need to change the database provider for your Posit Package Manager installation from SQLite to PostgreSQL:

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

The migrate utility:

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

Warning

Back up your databases before you run the migrate utility, including for a verification run. If a source database is not already at the current schema version, the utility upgrades it in place before it copies anything, and it does this with the --verify flag as well. Once a database has been upgraded, an earlier version of Package Manager will not start against it.

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. If the move also places Package Manager on a new server or points it at new storage, review which files must be carried over with the database. Refer to the Moving to a New Server or New Storage section.
  5. Run the migration. Refer to the Migration CLI section.
  6. Update the Database.Provider configuration setting to point to the new database. Refer to the Database section in the appendix.
  7. Restart Package Manager.

Moving to a New Server or New Storage

Changing the database provider on the same server leaves the files Package Manager keeps on disk where they are. If you are also moving to a new server, or pointing the installation at new storage, the storage classes fall into three groups. Refer to the Storage Classes appendix for the default location of each class and for what each one holds.

Content that must be carried over. Some of this cannot be recreated at all, and some of it can be recreated but at a cost you should not pay without reason:

  • The encryption key, at encryption/rstudio-pm.key within the [Storage].Persistent location. If it is not there, Package Manager generates a new key and starts normally, and values that were encrypted with the previous key can no longer be decrypted. Refer to the Moving to a New Deployment section for the details, including the case where the key comes from an environment variable instead of a file.
  • The entire packages storage class, configured by [Storage].Packages. Everything in it originated as an upload or a local build, and nothing can recreate it. Copy the class in full rather than selected parts of it. If these files are absent, package downloads fail while the repository index continues to be served, because Package Manager rebuilds the index from the database, so the repository looks healthy in the index and in the user interface while individual downloads fail.
  • Git build logs, at git/* within the [Storage].Persistent location. These cannot be regenerated.
  • The metrics class, if usage statistics are enabled. Package Manager recomputes these summaries from the usage database, but it does so on demand, so the first Usage Stats request that covers a period with no summaries waits while they are rebuilt. That is expensive on an installation with a large usage history, and carrying the class over avoids it. Usage statistics are enabled by default, and controlled by Server.UsageDataEnabled.
Warning

Copy the packages storage class in full. Copying only part of it is worse than not copying it at all, because the scheduled packages eviction removes package files it can no longer account for, and those files cannot be downloaded again. Refer to the Storage Classes appendix.

Content that may need to be carried over:

  • Repository snapshot manifests, at rsf/* within the [Storage].Persistent location. Package Manager downloads these again from the service configured by Manifest.URL when they are absent. Carry them if the new deployment cannot reach that service, which on an air-gapped installation is an offline dataset rather than a network location.

Content that should not be carried over:

  • The cache class. Package Manager rebuilds it. Installations upgraded from older releases may also hold Git build artifacts here that retained encrypted credential material, so discarding this class is preferable to copying it.
  • The mirrored classes cran, bioconductor, binaries, pypi, and vsx. This content is downloaded again on demand. If the new deployment cannot reach the services it mirrors from, move those files as well, since they can only be replaced by downloading them again.

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 Changing Database Provider Checklist section above. 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. Run interactively, the command asks for confirmation and names the destination database before deleting anything. Run from a script or a scheduled job, where there is nobody to answer, it proceeds without asking.
  • --from: Database to migrate from (defaults to sqlite3)
  • --to: Database to migrate to (defaults to pgx, the only supported value)

The migrate database command copies the data from the SQLite (sqlite3) database into PostgreSQL (pgx), and verifies the migration. Migrating in the opposite direction, from PostgreSQL to SQLite, is not supported. 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.

Copying the Source Database

If you copy the SQLite database in order to migrate it on another host, copy every file in the database directory rather than selected files. That directory is set by Sqlite.Dir, and it holds both the main database, named rstudio-pm.db by default, and the usage data database, rstudio-pm-metrics.db.

Package Manager uses write-ahead logging by default, so recently committed data is held in a separate write-ahead log file next to the .db file. A copy that takes the .db file on its own is missing that data. The migrate utility reads the write-ahead log correctly when it is present, but it cannot detect that a copy omitted it, so the migration reports success and the missing rows are simply absent from the destination.

Stop Package Manager before copying. A clean shutdown checkpoints the database and leaves it quiescent, and copying the whole directory then captures everything, whichever files happen to be present at the time.

Databases Shared with Other Applications

After the data copy, migrate database resets the PostgreSQL sequences that issue primary keys, because copied rows carry their existing key values. Package Manager resets only the sequences that belong to its own tables, so it leaves another application’s tables in another schema of the same database untouched. It identifies its own tables the way the rest of the migration does, by the names the connection resolves, so this holds while the Package Manager schema precedes any other application’s schema on the search path configured in Postgres.URL.

Long-Running Migrations

The migration copies every row of the source database and then verifies the copy, so the time it takes grows with the amount of data you have stored. On a large installation the run can last many hours, and Package Manager must stay stopped for the whole time. Plan the maintenance window accordingly.

The migrate utility does not record its progress and cannot resume. If a run is interrupted, whether by a lost connection, a terminated session, or a host restart, the next run starts again from the beginning. Do not use the partially loaded destination database. Because the utility refuses to write into a destination that already holds data, the retry must include the --drop-all flag.

The utility sends rows to the destination in batches, and each batch is a round trip, so network latency between the Package Manager host and the destination database adds to the total time. If your PostgreSQL server is remote, consider migrating in two stages:

  1. Run migrate database against a PostgreSQL server that is local to the Package Manager host, or as close to it on the network as you can arrange.
  2. Move the resulting database to the remote PostgreSQL server with pg_dump and pg_restore.

The second stage transfers one dump file instead of issuing statements across the network, and you can retry it on its own without repeating the migration. Remember to point Postgres.URL at the final destination before restarting Package Manager.

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

Specify a custom configuration file:

Terminal
sudo /opt/rstudio-pm/bin/migrate --config /etc/rstudio-pm/mycustomconfig.gcfg database
Back to top