This add-on is operated by Boreal Information Systems Inc.

A PostgreSQL database add-on that takes security seriously.

Last updated October 24, 2024

The Borealis Isolated Postgres add-on provides private PostgreSQL-compatible database clusters as a service on Amazon RDS. PostgreSQL is a powerful open-source relational database that is well suited to a variety of transactional workloads. Amazon RDS provides a highly optimized and reliable platform for managed database clusters.

Borealis Isolated Postgres differentiates itself by segregating each database cluster within its own virtual private cloud to block all direct connection attempts from the open internet, thus providing a similar level of protection as Heroku Postgres Private and Shield plans without also requiring the extra expense of a Heroku Enterprise Agreement. Instead of using a direct connection, a client connects to an add-on database via a secure tunnel with strong authentication and encryption. Secure tunnels are managed automatically and transparently for a Heroku app by a provided buildpack.

All PostgreSQL-compatible client libraries work with Borealis Isolated Postgres for the languages supported by Heroku (Ruby, Python, Java, Node.js, Go, PHP, Clojure, and Scala).

Getting Started

Installing the Add-on

You can attach the Borealis Isolated Postgres add-on to an app using the CLI:

$ heroku addons:create borealis-pg
Creating borealis-pg on ⬢ example-app... free
borealis-pg-example-addon is being created in the background. The app will restart when complete...

Several versions of PostgreSQL are supported (12, 13, 14, 15 and 16). The default version is PostgreSQL 16. To request a specific major version of PostgreSQL, specify it with the --version option in the CLI:

$ heroku addons:create borealis-pg -- --version 15
Creating borealis-pg on ⬢ example-app... free
borealis-pg-example-addon is being created in the background. The app will restart when complete...

Provisioning of a Borealis Isolated Postgres add-on is asynchronous and can take several minutes. To wait for an add-on to finish provisioning via the CLI:

$ heroku addons:wait
Creating borealis-pg-example-addon...

After you provision Borealis Isolated Postgres, three config vars are available in your app’s settings: DATABASE_URL, DATABASE_READONLY_URL and DATABASE_TUNNEL_BPG_CONN_INFO, by default.

Use the DATABASE_URL config var in your application code to connect with read and write access to the primary node in the add-on database cluster. Use the DATABASE_READONLY_URL config var in your application code to connect with read-only access to a node in the add-on database cluster.

You can confirm the DATABASE_URL config var contains the canonical URL for read and write connections to the database cluster with the heroku config:get command:

$ heroku config:get DATABASE_URL
postgres://user:pass@host:port/dbname

Finally, the DATABASE_TUNNEL_BPG_CONN_INFO config var contains internal connection information required to establish a secure tunnel. It’s used by the companion buildpack (borealis/postgres-tunnel). You don’t need it in your application code.

Installing the Buildpack

Apps require the companion buildpack (borealis/postgres-tunnel) to connect to an add-on database. After installation, the buildpack automatically uses the connection information in the add-on’s config vars to establish a secure tunnel to the corresponding database cluster. Any client processes that connect using the DATABASE_URL or DATABASE_READONLY_URL config vars automatically send their database traffic through the secure tunnel.

The following sections describe how to apply the buildpack using either a slug (the standard option) or a Docker container (via Cloud Native Buildpacks or a Dockerfile).

Standard Deploy (Slug)

If you’re using a standard deployment, you can add the buildpack using the following CLI command:

$ heroku buildpacks:add --index 1 borealis/postgres-tunnel
Buildpack added. Next release on example-app will use borealis/postgres-tunnel.
Run git push heroku main to create a new release using this buildpack.

Cloud Native Buildpack Deploy (Docker)

You can build an image using Cloud Native Buildpacks. To build an image, you must install Pack, a tool that supports the use of Cloud Native Buildpacks.

The following example shows how to build an image with the borealis/postgres-tunnel, heroku/ruby, and heroku/procfile buildpacks. These buildpacks include support for the Borealis Isolated Postgres add-on, the Ruby language, and a Procfile, respectively:

$ pack build --buildpack https://buildpack-registry.heroku.com/cnb/borealis/postgres-tunnel --buildpack heroku/ruby --buildpack heroku/procfile --builder heroku/buildpacks example-app

The heroku/procfile buildpack is necessary if your app has a Procfile. Don’t include it otherwise.

There’s a Cloud Native Buildpack for every supported language. You can include a language’s Cloud Native Buildpack using the same name as its standard buildpack in a pack build command. For a list of buildpacks for supported languages, see Buildpacks.

To include other standard buildpacks as Cloud Native Buildpacks, you can use the Cloud Native Buildpacks shim. Simply append the buildpack’s name to the end of the URL https://buildpack-registry.heroku.com/cnb/ (for example, to include jincod/dotnetcore-buildpack, use https://buildpack-registry.heroku.com/cnb/jincod/dotnetcore-buildpack in the pack build command).

Next, tag and push the image to Heroku’s Docker registry:

$ docker tag example-app registry.heroku.com/example-app/web
$ docker push registry.heroku.com/example-app/web

Finally, release the app on Heroku:

$ heroku container:release --app example-app web

For more information about deploying and running Docker containers, see Container Registry & Runtime (Docker Deploys).

Dockerfile Deploy (Docker)

You can define your app’s Docker image manually in a Dockerfile. The following Dockerfile for Heroku’s python-getting-started app shows how to set up a secure tunnel:

FROM python:3.11

# Download, build, and move the autossh executable to the location expected by
# the buildpack's secure tunnel startup script (the /usr/bin directory)
WORKDIR /tmp
RUN wget https://www.harding.motd.ca/autossh/autossh-1.4g.tgz && \
  tar -x --file autossh-1.4g.tgz && \
  cd autossh-1.4g && \
  ./configure && \
  make && \
  mv autossh /usr/bin && \
  cd .. && \
  rm -rf autossh-1.4g autossh-1.4g.tgz

# Copy the app's files and install its dependencies
WORKDIR /app
COPY . .
RUN pip install -r requirements.txt

# Download the secure tunnel startup script from the borealis/postgres-tunnel
# buildpack
RUN wget https://raw.githubusercontent.com/OldSneerJaw/heroku-buildpack-borealis-pg-tunnel/latest/profile.d/borealis-pg-init-secure-tunnel.sh && \
  chmod u+x borealis-pg-init-secure-tunnel.sh

# Download the server shutdown wait script from the borealis/postgres-tunnel
# buildpack
RUN mkdir -p .borealis-pg && \
  wget \
    -O .borealis-pg/server-shutdown-wait.sh \
    https://raw.githubusercontent.com/OldSneerJaw/heroku-buildpack-borealis-pg-tunnel/latest/scripts/server-shutdown-wait.sh && \
  chmod u+x .borealis-pg/server-shutdown-wait.sh

# Set the command that establishes the secure tunnel and launches the app when
# the container starts
CMD ./borealis-pg-init-secure-tunnel.sh && gunicorn gettingstarted.wsgi

Follow the Container Registry & Runtime (Docker Deploys) documentation to build the image and deploy to Heroku using the heroku container:push and heroku container:release commands.

Connecting to the Database

Connecting with Ruby

Use the pg gem to connect to a PostgreSQL database in Ruby. Add it to the project’s Gemfile, then install all dependencies using bundler:

$ bundle install

Pass the DATABASE_URL config var directly to the PG.connect method to connect to the database:

require 'pg'

db_conn = PG.connect(connection_string: ENV["DATABASE_URL"])

Modern versions of the Rails framework have built-in support for the DATABASE_URL config var, so you don’t need to configure it to use it with Active Record.

For more information about using this gem, see pg.

Connecting with Python

Use the psycopg package to connect to a PostgreSQL database in Python. Add it to the project’s requirements.txt, then install all dependencies using pip:

$ pip install -r requirements.txt

Pass the DATABASE_URL config var directly to psycopg’s connect function:

import os
import psycopg

db_conn = psycopg.connect(os.environ["DATABASE_URL"])

If you’re using Django, install the dj-database-url package. Configure the database used by Django’s ORM from the DATABASE_URL config var.

For more information, see psycopg.

Connecting with Java

Use the PostgreSQL JDBC driver to connect to a PostgreSQL database in Java. The official Java buildpack automatically creates JDBC_DATABASE_URL and SPRING_DATASOURCE_URL environment variables when it detects a DATABASE_URL config var. These environment variables are suitable for many common situations. The JDBC connection info can be parsed directly from the DATABASE_URL config var when they aren’t supported.

For example, pass the JDBC_DATABASE_URL environment variable directly to the DriverManager‘s getConnection method:

var dbConn = DriverManager.getConnection(System.getenv("JDBC_DATABASE_URL"));

Connecting with Node.js

Use the pg package to connect to a PostgreSQL database in Node.js. Add it to the project’s package.json and install it in one command:

$ npm install --save pg

Pass the DATABASE_URL config var to the Client constructor:

const {Client} = require('pg');

const dbClient = new Client({connectionString: process.env.DATABASE_URL});
await dbClient.connect();

For more information, see node-postgres.

Connecting with Go

Use the pq database driver to connect to a PostgreSQL database in Go. Add it to the project’s go.mod and install it in one command:

$ go get github.com/lib/pq

Pass the DATABASE_URL config var to the sql.Open method:

import (
    "database/sql"

    _ "github.com/lib/pq"
)

func main() {
    dbConn, dbErr := sql.Open("postgres", os.Getenv("DATABASE_URL"))
    if dbErr != nil {
        log.Fatalf("Error connecting to database: %q", dbErr)
    }
}

For more information, see Go pq.

Connecting with PHP

Use the pgsql extension to connect to a PostgreSQL database in PHP:

$db_conn = pg_connect(getenv("DATABASE_URL"));

You can also use PHP Data Objects (PDO) for a slightly higher-level abstraction, which requires the use of the parse_url function to retrieve connection info:

$dbinfo = parse_url(getenv("DATABASE_URL"));
$dbname = ltrim($dbinfo["path"], "/")
$dbconn = new PDO("pgsql:host=" . $dbinfo["host"] . ";port=" . $dbinfo["port"] . ";dbname=" . $dbname . ";user=" . $dbinfo["user"] . ";password=" . $dbinfo["pass"]);

Local Connections

Connect to a Borealis Isolated Postgres add-on database cluster from your local development machine with the borealis-pg-cli plugin for the Heroku CLI.

First, install the plugin:

$ heroku plugins:install borealis-pg-cli

Next, open a secure tunnel so you can submit SQL queries to the database using a tool like psql or pgAdmin:

$ heroku borealis-pg:tunnel
Configuring read-only user session for add-on borealis-pg-example-addon... done

Secure tunnel established. Use the following values to connect to the database:
      Username: p_ro_abcxyz02468
      Password: RfCewWKTmwg7zudovFq91xSIqCIMPXWi
          Host: pg-tunnel.borealis-data.com
          Port: 5432
 Database name: d97531zyxcba
           URL: postgres://p_ro_abcxyz02468:RfCewWKTmwg7zudovFq91xSIqCIMPXWi@pg-tunnel.borealis-data.com:5432/d97531zyxcba

Press Ctrl+C to close the tunnel and exit

Append --write to the heroku borealis-pg:tunnel command to start a tunnel with read and write access.

See Running Apps Locally for general instructions on hosting/developing a Heroku app locally and using a .env file to connect it to a remote database like Borealis Isolated Postgres. When setting up your local .env file, use the full value of the URL field from the output of heroku borealis-pg:tunnel as the value of your local DATABASE_URL environment variable.

Transferring Data

If you already have a PostgreSQL database that you would like to transfer to Borealis Isolated Postgres, the preferred method is to use the standard pg_dump tool that is included with PostgreSQL to export a logical backup of your existing database and then use the standard pg_restore tool to import the backup into your Borealis Isolated Postgres database. Typically you should use these tools from your local development machine while remotely connected to the databases.

When you run pg_restore to import the logical backup into a Borealis Isolated Postgres database, you’ll have to open a secure tunnel to your Borealis Isolated Postgres database using the borealis-pg-cli plugin for Heroku’s CLI. You’ll want to run the heroku borealis-pg:tunnel --write-access command in its own terminal and then run pg_restore in a separate terminal while the tunnel remains open. The heroku borealis-pg:tunnel command outputs the session credentials you’ll need to connect to the Borealis Isolated Postgres database.

When you’re done restoring the database, it’s a good idea to reassign ownership of all database objects (tables, views, triggers, etc.) to your Heroku app’s Borealis Isolated Postgres read/write database user role so that it has full control over those objects (for example, to perform ALTER and DROP statements) instead of your own personal database user role. The Heroku app’s read/write database user role name is partly randomized, but it always begins with app_rw_ followed by a sequence of random letters and numbers. You can find it in the POSTGRES_WRITER_USERNAME field of the DATABASE_TUNNEL_BPG_CONN_INFO config var in the Heroku app’s dashboard.

To reassign object ownership, open a secure tunnel to your Borealis Isolated Postgres database with heroku borealis-pg:tunnel --write-access, then connect to your database with a query tool of your choice (for example, psql or pgAdmin) and run PostgreSQL’s REASSIGN OWNED command. For example, if your Heroku app DB user role name was “app_rw_abcdefghijklmnopqrstuvwxyz”, you would execute:

REASSIGN OWNED BY CURRENT_USER TO app_rw_abcdefghijklmnopqrstuvwxyz;

Troubleshooting

A missing borealis/postgres-tunnel buildpack causes most connection issues. The buildpack is required in order to establish a secure tunnel between your app and the database server.

Without the buildpack installed you are likely to encounter an error like this:

error: could not connect to server: Operation timed out
Is the server running on host "borealis-pg-db-server.amazonaws.com" (4.3.2.1) and accepting TCP/IP connections on port 5432?

Here is another common error when the buildpack is missing:

connection to server at "pg-tunnel.borealis-data.com" (127.0.0.1), port 65535 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?

You can verify your buildpack using the following CLI command:

$ heroku buildpacks
=== example-app Buildpack URLs
1. borealis/postgres-tunnel
2. heroku/java

If the buildpack is missing, see Installing the Buildpack for more information about how to add it to the app.

If the buildpack isn’t missing, verify that you’ve deployed a new version of the app’s code. Pushing a code change is required to force Heroku to use the latest buildpack configuration.

Finally, if problems persist, make sure you’ve configured the app to use the correct language-specific PostgreSQL connection URL config var corresponding to the add-on attachment name. By default, the add-on attachment name is DATABASE, but since Heroku’s official language buildpacks automatically provision a Heroku Postgres Mini add-on if they detect a PostgreSQL client library when your app is first deployed, the DATABASE attachment name may already be taken by Heroku Postgres. Check the name in your config vars and remember that you can drop add-ons with heroku addons:destroy and rename attachments with heroku addons:attach and heroku addons:detach. For example:

$ heroku addons:destroy heroku-postgresql
Destroying heroku-postgresql-example-addon on ⬢ example-app... done

$ heroku addons:attach --as DATABASE borealis-pg
Attaching borealis-pg-example-addon as DATABASE to ⬢ example-app... done
Setting DATABASE config vars and restarting ⬢ example-app... done

Dashboard

To access the add-on dashboard, open the Heroku Dashboard, choose an application with the add-on installed, and then select Borealis Isolated Postgres from the Installed Add-ons section.

The dashboard allows you to review details of an add-on, including storage usage, PostgreSQL version, a list of active database user roles and which PostgreSQL extensions are installed, among others. You can also fully reset add-on database credentials from the Users tab as needed.

Security

Every Borealis Isolated Postgres database cluster is segregated within its own virtual private cloud that acts as a firewall to block direct access from the open internet. All connections to an add-on database cluster must go through a secure tunnel; client apps use the companion buildpack while ad hoc connections (from your local development machine, for example) use the borealis-pg-cli plugin for Heroku’s CLI. When a secure tunnel is established, data is automatically encrypted in transit (TLS 1.2+ required) and uses a certificate signed by an Amazon RDS certificate authority. For extra security, your application can use the sslmode and sslrootcert PostgreSQL connection parameters to verify the server’s certificate against the Amazon RDS root certificates bundle.

In addition to transport layer security, all data that is stored in the database cluster is fully encrypted at rest using AES 256-bit encryption.

Heroku app credentials (database passwords, SSH keys, etc.) are rotated automatically on a weekly basis without any downtime for the Heroku app. A personal user’s (that is, a user that is tied to a Heroku user account) credentials are only valid for a single session; new credentials are generated every time a qualifying borealis-pg-cli command is executed (for example, heroku borealis-pg:tunnel and heroku borealis-pg:psql).

Each single tenant database cluster has its own dedicated SSH server in its virtual private cloud. We implement a number of measures to prevent abuse, including relying on AWS’ platform-level DDoS protection, using fail2ban to block repeated failed login attempts, automatically applying operating system and application patches to the SSH server on a daily basis, blocking all incoming requests except for SSH traffic, locking down the SSH daemon configuration to prevent users from accessing a shell or executing remote commands even when they have valid SSH credentials and expressly prohibiting SSH password authentication.

Plans with multi-tenant database clusters (for example, the dev plan) have inherently weaker security properties since they are shared by a multitude of Heroku apps and users. Strict PostgreSQL access controls prevent tenants from connecting to other databases in a multi-tenant cluster, but it is not recommended to use a multi-tenant plan to store sensitive data in production.

Data Storage Limits

Each add-on plan has a database storage limit. As an add-on’s database size approaches the storage limit for the plan, you will be sent a courtesy notice at the email address associated with your Heroku user account. Any add-on database that persistently exceeds its storage limit will eventually be made read-only (rest assured that pre-existing data will not be lost in such cases) until it is upgraded to a plan with enough storage available.

Migrating Between Plans

Since plan migrations happen in place there is no need to wait for long data copy operations, but when switching to a different database node size, the migration process can take several minutes (typically no more than 20 minutes) as the new node(s) are prepared. In such cases, your app may experience a brief period of downtime (usually less than a minute) near the end of the migration process as the database cluster switches over to the new database node(s), but the database will otherwise remain online and fully available throughout the rest of the process. However, if you are simply adding or removing replica nodes of the same size in a cluster, expect zero downtime.

Plan migration is fully asynchronous. The app config vars for the add-on are updated after the migration completes. The plan-change-started and plan-change-completed log events can be used to track progress.

Use the heroku addons:upgrade command to migrate to a new plan:

$ heroku addons:upgrade borealis-pg:newplan
Changing borealis-pg-example-addon on ⬢ example-app from borealis-pg:oldplan to borealis-pg:newplan... done
Add-on plan change is in progress. The app will restart when complete...

Backups, Restoring and Cloning

Every add-on database cluster stores six full, near-real-time copies of its data, with the copies spread evenly across three different availability zones for maximum resilience. Each copy of the data acts as an up-to-date backup that your add-on database cluster can automatically fail over to, without loss of data, in the event of a failure of the primary database instance. In addition, every night we create a full, encrypted logical backup of every multi-tenant and single tenant add-on database. These logical backups are not available for download but, alongside the built-in six-way physical data replication, nightly backups help to ensure your data is safe from disaster.

Using the borealis-pg-cli plugin for Heroku’s CLI, you can create an up-to-date clone of an add-on database. Typically a cloning operation will take about 10 to 15 minutes from start to finish (cloning time does not change with the size of the database thanks to an efficient copy-on-write storage system that lets the clone essentially continue on with its own fork without having to move/copy the data). This is a handy way to quickly provision a full copy of production data in staging, development, testing and review environments.

Here is an example of cloning an add-on database as it exists right now to another Heroku app and waiting for it to finish:

$ heroku borealis-pg:restore:execute --destination-app my-other-app --wait
Checking authorization... done
Starting clone of add-on borealis-pg-example-addon... done
Creating add-on borealis-pg-cloned-addon on ⬢ my-other-app... done

You can also use borealis-pg-cli to restore a single tenant add-on database to a specific point in time in the past. As with cloning, a point-in-time restore is provisioned as a new add-on, rather than overwriting the original add-on database. However, unlike with cloning, the time to complete a restore operation is roughly proportional to the amount of data stored in the add-on database (i.e. it takes longer to restore a bigger add-on database). This operation is typically meant for emergency recovery of unintentionally deleted/overwritten data.

Here is an example of restoring an add-on database into a new add-on on the same Heroku app at an earlier point in time and then waiting for it to finish:

$ heroku borealis-pg:restore:execute --restore-to-time 2023-03-05T15:05:17.485-08:00 --wait
Checking authorization... done
Starting restore of add-on borealis-pg-example-addon... done
Creating add-on borealis-pg-restored-addon on ⬢ example-app... done

Use the heroku borealis-pg:restore:capabilities CLI command to determine what time range is available for a point-in-time restore. While performing an emergency point-in-time restore, consider putting the app into maintenance mode. After a point-in-time restore is finished, use heroku addons:attach and heroku addons:detach to swap the original add-on with the new add-on, as needed.

Maintenance

We periodically and automatically apply PostgreSQL minor version updates (for example, PostgreSQL 16.0 -> 16.1), which include security and bug fixes, to every add-on database cluster after we’ve had a chance to thoroughly test the changes ourselves. The automatic upgrade process makes use of an internal proxy service to preserve database connections during minor version upgrades, which results in zero downtime and doesn’t require any intervention on your part. There may be a delay of about 1-10 seconds for any new SQL queries that are executed during a minor version upgrade, but automatic upgrades are always performed during off-peak hours to minimize the impact to you and your users.

We only apply automatic PostgreSQL major version updates (for example, PostgreSQL 15 -> 16) to multi-tenant database clusters (for example, the dev plan’s database clusters). If your add-on database cluster is single tenant and you wish to upgrade to a new major version of PostgreSQL, please contact our support team for assistance (see the Support section). Be warned that a PostgreSQL major version update will require several minutes of downtime.

Event Logging

Borealis Isolated Postgres automatically records events to your Heroku app’s logs. Every entry includes at least a source attribute with the add-on’s name and an event attribute with the event’s type, along with some number of context-specific attributes. For example:

2022-11-23T03:04:24.141698+00:00 app[borealis-pg]: source=borealis-pg-cubic-14331 event=app-secure-tunnel-created client_address=ec2-54-172-16-90.compute-1.amazonaws.com

The following event types are supported once an add-on has been provisioned:

• app-secure-tunnel-created: A Heroku app dyno has connected to the add-on database via a secure tunnel.
• app-secure-tunnel-destroyed: A Heroku app dyno has closed its secure tunnel to the add-on database.
• client-app-credentials-auto-rotated: The Heroku app’s add-on credentials have been rotated automatically. This does not affect personal user credentials or data integration credentials.
• data-integration-registered: A user has registered a new data integration.
• data-integration-deregistered: A user has deregistered a data integration.
• data-integration-secure-tunnel-created: A data integration has connected to the add-on database via a secure tunnel.
• data-integration-secure-tunnel-destroyed: A data integration has closed its secure tunnel to the add-on database.
• db-credentials-full-reset: A user has triggered a full reset of all add-on database user credentials via the add-on dashboard or borealis-pg-cli.
• db-failover-started: An automatic failover of the primary DB node has begun. This occurs when there is a failure of the current primary DB node and the add-on has at least one read replica node that is being promoted to primary automatically. Downtime is typically less than a minute.
• db-failover-completed: An automatic failover of the primary DB node has finished.
• db-restore-initiated: A user has triggered a full restore/clone of the add-on database to a new add-on.
• db-storage-limit-violated: The add-on database has exceeded the plan’s data storage limit.
• db-storage-limit-violation-resolved: The add-on database no longer exceeds the plan’s data storage limit.
• db-write-access-revoked: The add-on database has been made read-only. Generally this is due to a persistent data storage limit violation.
• db-write-access-restored: Write access for the add-on database has been restored.
• personal-db-password-rotated: The password for a personal database user (i.e. a database user role that is associated with a specific Heroku user account) has been changed. This happens automatically whenever a user executes certain commands via the add-on CLI.
• personal-db-user-created: A new personal database user has been created.
• personal-secure-tunnel-created: A personal user has connected to the add-on database via an ad hoc secure tunnel. This event is only recorded when the add-on database is single tenant.
• personal-secure-tunnel-destroyed: A personal user has closed their ad hoc secure tunnel to the add-on database. This event is only recorded when the add-on database is single tenant.
• personal-ssh-user-configured: The credentials for a personal SSH/secure tunnel user have been modified/created. This happens automatically whenever a user executes certain commands via the add-on CLI.
• postgresql-extension-installed: A user has installed a PostgreSQL extension.
• postgresql-extension-removed: A user has uninstalled a PostgreSQL extension.
• plan-change-started: An add-on plan change has begun.
• plan-change-completed: An add-on plan change has finished.

External Data Integrations

If you use an external service to perform extract, transform and load (ETL) and/or data warehousing, you can set up a data integration to an add-on database via a dedicated SSH tunnel (aka SSH local port forwarding). Typically such services will provide you with an SSH public key for SSH tunnel connections, which you must pass to borealis-pg-cli’s borealis-pg:integrations:register command to allow ongoing access. The CLI command output then provides you with all the connection details you will need to configure the service’s connection. For example:

$ heroku borealis-pg:integrations:register --name my_integration1 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQDvmLhFK/Ov+3YgDI7oUwsfv7jtybxk8RjM96aVn1MpxGgPfI7D+uhilafGiKQQGznuegy5jGEhYFPuZMGozxR5Y+AcLuTck0VotXPI0+ZhFTUg3ZwzXTNT/4GJVOniNKZ2X8IevmEOmJ9AEw3VCp8phGic+c29eh2/vCrk3mlHgG8FfC64Zzk8vgc2Gsbs1qFsZJnIK96S2gha0VWTaUfaEQPetqFxctMMPs17Me52gY9AiZI345ac/TZTjp1zNATNX4DPRfRVCEhSNuJawlAzgCi4kvgMx+3tsgfdFHLU2B0sU2zWpy46NU+GYCA27u/Gw+x0gwA0qNty92py4C0LM2/WEVDVAlUlhn8Tt3pa9q4tKkNIBbOL8kRpnKfys0SWj6QCM/M5xckUplPQxZvHltuoywGnLarFshNsnW2uGoWv4x9JD8Jg0J52A+X/frEqtXW1SCPG3JmDP9tl+IIpB7Jn06lqDaHfs3sFVivDM27bvMUmyvXbEumZt38FMZ7bUlx3Tj7t9HxGv2S3HoRM8k9PBz3G4Lp9wG4bun41RLSZX12lyhyTyaCgz5/eghH49xZwYgs1Q2w4Ldna0VJw4449koFd9gdtnHywL1g5kIJ/ujaX2ZcvraSim2nQ6PRO/TvWvd2aAL0YcGj4C0497DMERSwSLHGrUR9auR3FUQ=='
Registering data integration with add-on borealis-pg-example-addon... done

              Database Host: borealis-pg-db-server.amazonaws.com
              Database Port: 5432
              Database Name: dabcdef123456
          Database Username: di_ro_my_integration1
          Database Password: PwA-22SDAbUk86wxeMapPJRfkuRmVCS6
                   SSH Host: 1.2.3.4
                   SSH Port: 22
               SSH Username: di-uvwxyz098765
 SSH Server Public Host Key: ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIN51brpKPSIcgwlDjaW9KBqU+YqjJRBVxGdlHS+8OzIE

By default data integration queries are executed on an add-on database cluster’s read replica node(s), if any, to prevent unnecessary load on the primary node. In some cases a service may require write access as well, in which case you may include the --write-access option in the CLI command, but be warned that, when write access is enabled, all queries will be executed on the primary node in the add-on database cluster.

Each add-on’s SSH daemon is configured to block interactive shells and remote commands. Therefore, when building your own custom data integration that uses OpenSSH for local port forwarding, you’ll have to pass the -L and -N options to the ssh command. Here’s an example with placeholder values:

$ ssh -i <SSH_PRIVATE_KEY_FILE_PATH> -N -L pg-tunnel.borealis-data.com:5432:<DATABASE_HOST>:<DATABASE_PORT> <SSH_USERNAME>@<SSH_HOST>

Once local port forwarding is established, connect to the database using hostname pg-tunnel.borealis-data.com and port 5432.

Services

The following ETL services have been verified to work with Borealis Isolated Postgres. Other services should be compatible as long as they support connections through an SSH tunnel with public key authentication.

Integrate.io ETL (formerly Xplenty)

From the Integrate.io ETL dashboard, create a new connection and choose the “PostgreSQL” option. Set “Access type” to “SSH tunnel connection” to show the SSH connection options. The “SSH Public Key” field will be blank until after you’ve saved the initial integration, so enter dummy values for “Hostname”, “User name”, “Password”, “SSH Username” and “SSH Hostname” for now and click “Create connection”. Now click on the newly-created connection to open it again and copy the populated “SSH Public Key” value and pass it to the borealis-pg:integrations:register CLI command. For example:

$ heroku borealis-pg:integrations:register --name integrateio 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCsZQlALfJ+2pn8hrvqoYpgt2ofTbShJGbNwpWf8PlN7kUdDtaN7NhSk2VVPGdaxHYBTmchXRSB4zM1UhMY+Uu/fMannj919tj8N9HmrjmcjxSqXGTyBjfbl1tVGz5bl7VJtnU7nqR1BzhxM6+i0oXNgf3l5z+D8ZykxlxVVdsBQMXkPB7LPIBtqwnyu4xFRncyLajrFTJfMhcLxJ175BK9VOL//ZnCgTfY1NI4bHH/BQEFu9T0m2XHK20hTPWzojkM0X0H+BRseGrbcFfEaxlIh4eZ0ZsoZsDQTXr+Knt6vpKSm0qfH/6uWDEmMeMzxBczbggiD9KUEsgYSHOGM6oh'

Next, replace the dummy values with the values from the CLI command’s output and click “Test connection”. If the values were entered correctly, it should indicate the test succeeded. Click “Save changes” to finish.

See Integrate.io ETL’s docs for help.

Fivetran

From the Fivetran dashboard, add a new connector and select the “Postgres RDS” option. Choose the “Connect via an SSH tunnel” option to reveal the SSH connection options. Copy the “Public Key” value it shows and pass it to the borealis-pg:integrations:register CLI command. For example:

$ heroku borealis-pg:integrations:register --write-access --name fivetran 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQCO1ktF6o7ncuD1XUmsAXauOKefnU9VVyiAJ1PcF9y6zBa/EiWyd7aCYFxfWp/IoIm3vY4sh+iuJEl9AjQQLCKmVOGlf6c+xkXdwFDL6ZAR+b9UPZ4PjvydwS6VIkbIcvUFHGGmtFpFl/x949Szg7XKlqq8dRrbju0Vd1Ca4M1eBG7ySWUeegUxhmwriYGgYbKroX2Z3K+SOegnWiTGMFkStJysfndsDvYBdz1FCiaA0XcRkhlENQ3dlQQ3akg23RopY/HgkvZXVb7bRGo7afdyWf4lmPTtBacfIrhJhhrGreKPZBiXv/f5kmSoVF1LBiWflIyYfD02db6JdmZQwKo2448RkkyNqst31MNpqq+EIZWd6zgBIbDQDzRKCB5sVo70rbUnX0ncgNRQ+ydfjqliQVe70ypnuciIxizkSmWehQCr/RroDfbSJDtxtdfbZNCsjX6pCt1sQzeAMiYpxYAIVJD2gU7oHGbJ9/n9TVOOznPlMqbWbNwL1tNipvkYg0diZST8z6wI8Ap1EJurfolnhGLlHN4E82USmukZbIYGtkOuNtBTNzm8Hjgg/65IQJgU641UpJgMdojpj1CH0rW2Ov5tj3vhnZuWR1vDEW0Nie1CWtNc9ZuCcsfrfAVP9rnvfs0yAL7mfw6Ra5t0KQVHKncI8ayyHvyfhNEd94Oo3w=='

Next, use the CLI command’s output to populate the “Host” (i.e. database host), “Port” (i.e. database port), “User” (i.e. database username), “Password” (i.e. database user password), “Database” (i.e. database name), “SSH Host”, “SSH Port” and “SSH User” fields. Ensure that “Require TLS through Tunnel” is enabled, choose either the “Detect Changes via XMIN” or “Detect Changes via Fivetran Teleport Sync” option and click “Save & Test”. Click “Confirm” when prompted to verify the SSH fingerprint. Select the root certificate and click “Confirm” when prompted to confirm the database server TLS certificate. If the values were entered correctly, it should indicate the test succeeded. Click “Continue”, select the tables and columns you’d like to replicate and then start the initial sync.

See Fivetran’s docs for help.

Stitch

From the Stitch dashboard, create a new integration and choose the “PostgreSQL” option. Tick the “Connect using SSH tunnel” checkbox to show the SSH connection options. Copy the “Stitch Public Key” value and pass it to the borealis-pg:integrations:register CLI command. For example:

$ heroku borealis-pg:integrations:register --name stitch 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQDSXtbgO5nwP7N/bHXxz36GH63xtvOdKePG7e+95BUtKs3tw2VHwfz+HIMLviSBQR+CrrgzDnod3/finQegueV8++9lKNEI4uv2/9OxtWLrzDYnATCs8aVaWYIBdzU2aO2YyAtUDFJ2tNtwBihhyOU9Xf5ECfDGtPpRvfTA2uwtahOYSK8WylGNotwlUJ9YNCW85DR5Gc+K1kbUfmS4gx24kWe++Ri7dCzKgjE8ZgxrMyN4qRvDDYjyoHa8xD/qID4X0BbfbKSpkENZ3dRbwvKKxBshd67wxnkxMYT/rHglxyjATP11b0Dx7AoqZkdeFAhc6BlomzteSfQFx+Q2/DckM0VnAVqHcGp4PfNILHm9yNGdrBOxWJmVf/9OER4JJpOjSJe8NolvC7TKVnJc/w7cP7YJSQaCaaDHpUi4PpwZcmTuZv+0lxh7T2VlsnWlAOUlQL1S+7rztYYjSpdxC3qQYTouzJHS8qLCMP6sXvUMV2vdxnt2ZxyBexhkmq1Uve4jiD6WgYsHzxnifhlkNlYkZfB3JeV/eBs0NYqQnlS/W5NNnyKdClnWzgSbd1FEEFM23xHKWs6aGLMqhjrlhVzkBzAG9AFeAwWov4GF6kHcxJh/KrE3PJqY65195ai44gQWWVkZwMhjinEKd4/QIYxNMOHHRw3ovqr6IqN7y60dLQ=='

Next, use the CLI command’s output to populate the “Host (Endpoint)” (i.e. database host), “Port” (i.e. database port), “User” (i.e. database username), “Password” (i.e. database user password), “Database Name”, “SSH Host”, “SSH Port” and “SSH User” fields. Ensure that “Connect using SSL” is enabled and that “Log-Based Replication” is disabled and click “Check and Save”. If the values were entered correctly, it should indicate the test succeeded. Finally, when configuring which data to replicate, remember that “Log-Based Incremental Replication” is not supported; we recommend using “Key-Based Incremental Replication” in most cases.

See Stitch’s docs for help.

Removing the Add-on

You can remove Borealis Isolated Postgres via the CLI:

$ heroku addons:destroy borealis-pg
Destroying borealis-pg-example-addon on ⬢ example-app... done

Before removing Borealis Isolated Postgres, you can export your data using any standard PostgreSQL data export tool (for example, pg_dump).

Support

Submit all Borealis Isolated Postgres support and runtime issues using one of the Heroku Support channels (see the Add-ons page on Heroku Help to create a support ticket, for example). Any non-support related issues or product feedback is welcome at support@borealis-data.com.

Attribution

Add-on service owned and operated by Boreal Information Systems Inc.

Postgres, PostgreSQL, and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada.

Amazon Web Services and the “Powered by AWS” logo are trademarks of Amazon.com, Inc. or its affiliates in the United States and/or other countries.

Icons are provided by Icons8.