Postgres - Cube — ContextQMD

Prerequisites

The hostname for the Postgres database server
The username/password for the Postgres database server
The name of the database to use within the Postgres database server

Setup

Manual

Add the following to a .env file in your Cube project:

dotenv

CUBEJS_DB_TYPE=postgres
CUBEJS_DB_HOST=my.postgres.host
CUBEJS_DB_NAME=my_postgres_database
CUBEJS_DB_USER=postgres_user
CUBEJS_DB_PASS=**********

Environment Variables

Environment Variable	Description	Possible Values	Required
`CUBEJS_DB_HOST`	The host URL for a database	A valid database host URL	✅
`CUBEJS_DB_PORT`	The port for the database connection	A valid port number	❌
`CUBEJS_DB_NAME`	The name of the database to connect to	A valid database name	✅
`CUBEJS_DB_USER`	The username used to connect to the database	A valid database username	✅
`CUBEJS_DB_PASS`	The password used to connect to the database	A valid database password	✅
`CUBEJS_DB_SSL`	If `true`, enables SSL encryption for database connections from Cube	`true`, `false`	❌
`CUBEJS_DB_MAX_POOL`	The maximum number of concurrent database connections to pool. Default is `8`	A valid number	❌
`CUBEJS_CONCURRENCY`	The number of concurrent queries to the data source	A valid number	❌

Pre-Aggregation Feature Support

count_distinct_approx

Measures of type count_distinct_approx can only be used in pre-aggregations when using the Postgres HLL extension with Postgres as a source database.

Pre-Aggregation Build Strategies

<Info>

To learn more about pre-aggregation build strategies, head here.

</Info>

Feature	Works with read-only mode?	Is default?
Batching	✅	✅
Export Bucket	-	-

By default, Postgres uses batching to build pre-aggregations.

Batching

No extra configuration is required to configure batching for Postgres.

Export Bucket

Postgres does not support export buckets.

SSL

To enable SSL-encrypted connections between Cube and Postgres, set the CUBEJS_DB_SSL environment variable to true. For more information on how to configure custom certificates, please check out Enable SSL Connections to the Database.

Additional Configuration

AWS RDS

Use CUBEJS_DB_SSL=true to enable SSL if you have SSL enabled for your RDS cluster. Download the new certificate here, and provide the contents of the downloaded file to CUBEJS_DB_SSL_CA. All other SSL-related environment variables can be left unset. See the SSL section for more details. More info on AWS RDS SSL can be found here.

Google Cloud SQL

You can connect to an SSL-enabled MySQL database by setting CUBEJS_DB_SSL to true. You may also need to set CUBEJS_DB_SSL_SERVERNAME, depending on how you are connecting to Cloud SQL.

Heroku

Unless you're using a Private or Shield Heroku Postgres database, Heroku Postgres does not currently support verifiable certificates. Here is the description of the issue from Heroku.

As a workaround, you can set rejectUnauthorized option to false in the Cube Postgres driver:

javascript

const PostgresDriver = require("@cubejs-backend/postgres-driver")
module.exports = {
  driverFactory: () =>
    new PostgresDriver({
      ssl: {
        rejectUnauthorized: false
      }
    })
}