Database

Each Posit Team product (Posit Workbench, Posit Connect, and Posit Package Manager) stores its operational data in its own database. The products operate independently and never share a database. By default, each product uses an embedded SQLite database, which is only suitable for a single node. For a summary of what each product keeps in its database, see the shared infrastructure table in the Posit Team reference architecture.

Each product requires PostgreSQL whenever it runs across more than one node, because the products cannot share an embedded SQLite database across nodes. This includes load-balanced, multi-node, Slurm, Kubernetes, and container deployments.

Each product reads its PostgreSQL connection settings from its own configuration file. Follow each product’s own documentation, linked below, for the relevant settings and advanced connection options.

Provision PostgreSQL

Posit Team products do not provide a PostgreSQL server. Before you deploy a product with PostgreSQL, provision a server and create the databases that the products need. To stand up a managed or self-managed server, refer to your platform’s documentation:

For guidance on sizing the database and the rest of your deployment, refer to Sizing recommendations.

Create a database for each product

Give each product its own database and a dedicated service user (role) on the shared PostgreSQL server. Separate databases and users stop one product from reading or writing another product’s data and limit the damage from a leaked password. Run separate PostgreSQL instances for staging and production so that non-production work cannot reach production data.

On PostgreSQL 15 and later, a newly created role cannot create tables in the public schema by default. Without that grant, the product fails at first start with a permission error. Each product’s documentation gives the exact CREATE DATABASE, role, and grant statements, including this schema grant:

Secure the database password

Store the database password securely rather than in plain text. Each product can encrypt the password or read it from a protected file or environment variable. For example, Workbench encrypts the password with the rstudio-server encrypt-password helper, and Connect and Package Manager accept the password in a separate setting instead of the connection URL. Refer to each product’s documentation, linked below, for the exact mechanism.

Databases that each product uses

Each product uses a database for its core operations, and each product can also record secondary data, such as usage metrics or audit events, in an additional database. The following tabs summarize the databases each product uses and link to the configuration details.

  • Core database: stores session metadata and Workbench state.
  • Audit database: records user and administrative activity. Workbench can write audit events to a separate database.

For configuration details, refer to the Workbench database documentation. For deployment topologies, refer to the AWS and Azure reference architectures.

  • Core database: stores application metadata, content, and configuration.
  • Instrumentation database: stores operational metrics. Connect writes metrics to the core database by default and can write them to a separate database instead.

For configuration details, refer to the Connect database documentation. For deployment topologies, refer to the AWS and Azure reference architectures.

  • Core database: stores repository and package metadata.
  • Usage database: stores package usage data, such as download counts. With SQLite, Package Manager always keeps usage data in a separate database. With PostgreSQL, it can share the core database or use a separate one.

For configuration details, refer to the Package Manager database documentation. For deployment topologies, refer to the AWS and Azure reference architectures.

Important

Each product requires its own database. Do not point more than one product at the same database. When a single PostgreSQL server hosts multiple products, create a separate database and a separate user for each one.

Back to top