PostgreSQL

PostgreSQL is an available database provider which is more powerful and performant than SQLite.

You must provide your own Postgres server which will likely be a separate box from your Posit Connect server (but not required). We currently support any version from 13.x up through 17.x. Your Postgres server does not have to be dedicated to Posit Connect, but it must have its own dedicated database.

Configure the Database.Provider as postgres for Posit Connect to use a PostgreSQL database rather than the default SQLite database. The Postgres.URL setting is the fully-qualified connection URL for your Postgres server.

/etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect"

The user credentials can be included in the Postgres.URL URL - remember to URL-encode any special characters in the username or password. We recommend using Postgres.Password to avoid using a bare password in your configuration file. Examples using Postgres.Password are found in the Passwords section.

If your password or username contains special characters: @, #, !, $, %, /, or ?, those characters must be percent-encoded when included in Postgres.URL.

To avoid encoding issues, use Postgres.Password (which does not require percent-encoding) or use alphanumeric-only passwords for database service accounts.

Note

Connect does not support the use of IAM instance profiles or other types of machine identity credentials for PostgreSQL or other secrets. While the use of machine identity-based credentials is recommended over storing long-lived encrypted secrets on disk, this pattern weakens the overall security model of Connect because any credentials that are scoped to a VM or container could be accessible to content deployed to Connect and used to retrieve secrets stored in external services or sensitive data written by Connect into the database.

The user credentials supplied in the Postgres.URL URL must be able to create and alter database tables, in addition to read/write permissions in the database referenced as the path of the URL. A blank database with the given name MUST already exist.

SSL

Posit Connect assumes by default that SSL is enabled on the Postgres server. If SSL is not enabled on your Postgres database, add ?sslmode=disable to the Postgres.URL.

Here is an example configuration using a Postgres database with SSL connections:

/etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect"

Here is an example configuration using a Postgres database without SSL connections:

/etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect?sslmode=disable"
ImportantHosted database providers

When using a hosted PostgreSQL service such as AWS RDS or Azure Database for PostgreSQL, you should include sslmode=require in your connection URL. These providers typically require or strongly encourage encrypted connections, and without this parameter the connection may fail.

/etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@your-hosted-instance.example.com:5432/connect?sslmode=require"

Schemas

If you need to, you can tell Connect to restrict itself to keeping its tables within a specific schema. You control this by giving PostgreSQL a search path as part of the URL by adding options=-csearch_path=<schema-name> to the URL. If it’s the only item you’re adding, separate it from the rest of the URL with ? (just like the sslmode item above). Otherwise, separate it from other items with ‘&’.

Here is an example configuration that specifies a schema to use, with SSL:

/etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect?options=-csearch_path=connect_schema"

Here is an example configuration that specifies a schema to use, with SSL disabled:

/etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres

[Postgres]
URL = "postgres://username:password@db.seed.co/connect?sslmode=disable&options=-csearch_path=connect_schema"

Posit Connect will refuse to start when given a schema that does not already exist. The schema must be owned by the connecting user or by a group that contains the connecting user.

Connection pool tuning

Connect maintains a pool of connections to your PostgreSQL database and reuses them across requests. Three settings control the pool’s behavior:

/etc/rstudio-connect/rstudio-connect.gcfg
[Database]
Provider = postgres
MaxIdleConnections = 5
MaxOpenConnections = 10
ConnectionMaxLifetime = 60s

Choosing a value for idle connections

For most deployments, keep Database.MaxIdleConnections at or above its default of 5, and set it no higher than Database.MaxOpenConnections. Idle connections are a subset of open connections, so a larger idle value has no effect.

Setting Database.MaxIdleConnections to zero, or any value less-than zero, disables idle connection retention: Connect opens a new connection for every database operation and closes it immediately afterward. When Connect talks directly to PostgreSQL, this degrades performance and places unnecessary load on the database server. Avoid a value of zero for that setup.

A value of zero can be appropriate in other deployments. Some deployments route connections through a session-mode connection pooler such as PgBouncer, or connect to a server with a small max_connections limit. In those cases, an idle client connection holds a server-side slot for its entire lifetime, and releasing connections as soon as they go idle frees those slots for other clients. If you need this behavior, weigh the per-operation connection cost against the connection-slot savings for your environment.

Note

If an installation has Database.MaxIdleConnections set to zero, confirm that it is an intentional choice for your environment. For most deployments that connect directly to PostgreSQL, the default of 5 performs better.

Size the pool for your deployment

For busy servers that need more concurrent database throughput, increase Database.MaxOpenConnections and Database.MaxIdleConnections together. When sizing the pool, account for the max_connections limit of your PostgreSQL server.

In a multi-node deployment, every Connect node maintains its own connection pools, so size your PostgreSQL max_connections for the whole cluster. Each node opens up to MaxOpenConnections connections for the primary database. When operational metrics are enabled (the default), each node maintains a second pool of the same size for the operational metrics database. By default that database is the same as the primary one, so a single node may open up to roughly twice MaxOpenConnections against it. Account for this total, along with any external connection pooler limits, when configuring max_connections.

Operational metrics

Operational metrics generated by Posit Connect can be written to a separate PostgreSQL database. See the Operational Metrics section for details.

By default, instrumentation events will be written to the same database. Use Postgres.InstrumentationURL to use a separate database.

/etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username:password@db.seed.co/connect"
InstrumentationURL = "postgres://username:password@db.seed.co/connect_instrumentation"

Passwords

When your PostgreSQL connection URL requires a password, use Postgres.Password with an encrypted value to avoid credential leakage. See more about encrypted settings, how to encrypt secrets like passwords, and using them in your configuration file in the Property Types configuration appendix.

Note

We do not recommend embedding passwords directly in the Postgres.URL connection URL setting.

Posit Connect uses Postgres.Password when connecting to your PostgreSQL database. Do not percent-encode your Postgres.Password value. If necessary, Connect will encode your password when it is combined with the Postgres.URL.

Note

Passwords included directly in Postgres.URL need to have special characters percent-encoded. The set of characters that must be encoded within the user information component of a URL can be found in Section 3.2.1 of RFC 3986.

Here is a sample configuration using a PostgreSQL connection URL with a username and a separate, encrypted password.

/etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username@db.seed.co/connect"
Password = <encrypted, Base64-encoded password value>

The historical database connection URL can also be used with an encrypted password. Use Postgres.InstrumentationPassword to provide the password associated with the connection URL Postgres.InstrumentationURL.

Here is a sample configuration that has two separate Postgres URLs; one for the primary database and one for the historical database. Both connection URLs have separate, encrypted passwords.

/etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username@db.seed.co/connect"
Password = <encrypted, Base64-encoded password value>
InstrumentationURL = "postgres://username@db.seed.co/connect_instrumentation"
InstrumentationPassword = <encrypted, Base64-encoded password value>

Azure service principal authentication

As an alternative to using a password, PostgreSQL instances hosted on Azure can be configured to use an Entra ID service principal for authentication. See Use Microsoft Entra ID for authentication with Azure Database for PostgreSQL for the official documentation.

Connect can be configured to use such a service principal for its databases with something like the following:

/etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username@example.postgres.database.azure.com/connect"
AzureTenantId = "our-tenant-id"
AzureClientId = "example"
AzureClientSecret = <encrypted, Base64-encoded client secret>
Note

When using a separate database for instrumentation events, it must use the same Azure service principal for authentication as the main database.

SSL certificate authentication

SSL certificate authentication for the PostgreSQL database can be used with Posit Connect. To use certificate authentication, you must configure your PostgreSQL server for SSL connections and authentication. See the official PostgreSQL Certificate Authentication documentation for more information.

Once your certificates are set up with PostgreSQL, the connection URL needs to include sslcert, sslkey, and sslrootcert parameters.

/etc/rstudio-connect/rstudio-connect.gcfg
[Postgres]
URL = "postgres://username@db.seed.co/connect?sslcert=/etc/ssl/certs/postgresql/postgresql.crt&sslkey=/etc/ssl/certs/cacert/postgresql/postgresql.key&sslrootcert=/etc/ssl/certs/cacert/postgresql/root.crt"
Warning

The cn (Common Name) attribute of the certificate is compared to the requested database user name in Postgres.URL, and if they match the login is allowed.

If they don’t match, you can specify a map in pg_ident.conf to map cns and usernames, for example:

pg_ident.conf
posit-connect your-common-name   postgres-username

You can then update pg_hba.conf to reference the map created above:

pg_hba.conf
hostssl all            all            0.0.0.0/0                cert  map=posit-connect

For more information, see the PostgreSQL User Name Maps documentation.

Database upgrades and advisory locks

When upgrading Posit Connect with a PostgreSQL database, Connect uses PostgreSQL advisory locks to ensure only one Connect instance can perform database schema migrations at a time. This is critical in multi-node deployments where multiple Connect instances might start simultaneously during an upgrade.

How advisory locks work during upgrades

When Connect starts up with PostgreSQL:

  1. Lock acquisition: Connect attempts to acquire an advisory lock using the configured lock ID
  2. Schema migration: Only the instance that acquires the lock performs database migrations
  3. Lock release: The lock is automatically released after migrations complete
  4. Other instances wait: Other Connect instances wait for the lock to become available, then verify the schema is up to date

Timeout behavior

Connect will wait a maximum of 5 minutes (by default) to acquire the database upgrade advisory lock. If the lock cannot be acquired within this timeout, Connect will fail to start with an error message indicating another instance may be performing a migration or holding the lock.

This timeout prevents Connect from hanging indefinitely if: - Another Connect instance is performing a long-running migration - A previous migration process crashed while holding the lock - Network issues prevent lock acquisition

Troubleshooting stuck upgrades

If Connect fails to start with a message about timing out while waiting for the database upgrade lock:

  1. Check other Connect instances: Verify no other Connect instances are currently starting or performing migrations. Check their logs for migration progress.

  2. Check for stuck locks: Query PostgreSQL to see if any advisory locks are held:

    SELECT * FROM pg_locks WHERE locktype = 'advisory';

  3. Manually release stuck locks: If a lock is stuck due to a crashed migration, you can manually release it:

    SELECT pg_advisory_unlock_all();

    Warning: Only do this if you’re certain no migration is actively running.

  4. Review migration logs: Check Connect’s service logs for details about which migration step may have failed or is taking longer than expected.

Best practices for multi-node upgrades

  1. Stop all nodes first: Before upgrading, stop all Connect instances in your cluster
  2. Upgrade sequentially: Upgrade one node at a time, allowing each to complete its startup
  3. Monitor logs: Watch the logs for the first node to confirm migrations complete successfully
  4. Start remaining nodes: Once the first node is running, start the remaining nodes

For more information about upgrades, see the Upgrade Posit Connect documentation.