|||

Video Transcript

X

Heroku Postgres Update: Configuration, Credentials, and CI

At the core of Heroku’s data services sits Postgres, and today, we are making it even easier to bend Heroku Postgres to the very unique needs of your application’s stack. With these new features, you can easily customize Postgres, making it more powerful and configurable, while retaining all the automation and management capabilities of Heroku Postgres you know and love. By changing Postgres settings, creating and working with database credentials, and providing tight integrations to Heroku and Heroku CI, you now have the ability to further tune your Postgres database to your team’s needs.

More Flexible Postgres with PGSettings

As we start peeling back the layers of Heroku Postgres, the ability to change the default behavior is the first step in making Heroku Postgres more flexible. Using the Heroku CLI, any developer can use the PGSettings feature to change portions of the default Heroku Postgres configuration. One of the more acute areas to change behavior is around database logging. As Heroku Postgres databases start getting sufficiently large, this could be in terms of the number of transactions, data volumes, or connections, these databases are generating large amount of logs that could hamper performance of the database.

$ heroku pg:settings postgresql-large-1234 -a sushi
=== postgresql-large-1234
log-lock-waits:             true
log-min-duration-statement: 2000
log-statement:              ddl

With log-statements, for example, you can change this setting to none and the database won’t log any information besides slow queries and errors. For databases on Heroku Postgres that experience a large amount of thrashing in the schema, for example, temp tables that come and go, this can save lots of space and increase performance of the system by not having to write so many log statements.

$ heroku pg:settings:log-statement ddl -a sushi
log-statement has been set to ddl for postgresql-large-1234.
All data definition statements, such as CREATE, ALTER and DROP, will be logged in your application's logs.

PGSettings is available for Heroku Postgres databases running on a current production plan, Standard, Premium, Private, or Shield, on Postgres version 9.6 or above.

Manage Access Permissions with Heroku Postgres Credentials

One of the benefits of Heroku Postgres is how the relationship between a database and its associated application are automatically maintained. When a database needs to be re-instantiated, or its credentials have changed, these values are automatically reflected to the application.

With the advent of sharable add-ons (and databases), a single database can now be used by multiple applications. With Heroku Postgres Credentials, each of those applications can now have a unique connection to each database, so the same automated credential management can now carry across multiple applications.

In addition, as these credentials are scoped to a Heroku application, this feature provides an easy and powerful way to manage the scoping of database access to discrete groups of users in a way that’s tightly integrated to the existing Heroku permissions model. For example, if your team has a data scientist or analyst, a best practice would be to create a read-only credential and give that individual access via that scoped credential which they can retrieve by accessing the environment information for a specific application. This way, the risk to changing the database has been mitigated.

Credentials can be created via the Heroku Data dashboard or the Heroku CLI, and are available for all non-legacy production plans, Standard, Premium, Private and Shield, on Postgres 9.6 and above.

postgres

Tight Integration With Heroku CI and Pipelines

You can now auto-provision Heroku Postgres instances for all your Heroku CI test runs and Heroku Pipelines Review apps with zero configuration. This allows Heroku Postgres users seamless, automated environment management for testing the code of any pull request, Git merge, or arbitrary build against a free, disposable Heroku Postgres hobby instance. You can optionally populate these instances with test data in the "Release Phase" script fully support by Heroku Pipelines. Each ephemeral Heroku Postgres DB is designed to instantiate (and self-destroy) quickly and cleanly -- and you pay only for the dynos during duration of the test run, or the existence of the Review app. As always, progress and results are reported to your development team in Slack, in GitHub, and your Heroku Dashboard.

Integrating all the bits!

All of these features - PGSettings, Heroku Postgres Credentials and Integration with Heroku CI and Pipelines - are available today. We are exploring new ways to make Postgres even more versatile and powerful, and if you have any new settings you’d like to see exposed, or any ideas on integrations with Heroku, email us at postgres@heroku.com.

Originally published: August 08, 2017

Browse the archives for news or all blogs Subscribe to the RSS feed for news or all blogs.