Getting Credentials from Heroku
To connect to your PostgreSQL database on Heroku, you’ll need the following information, which you’ll input in the Connections page of Looker:
Encrypting Network Traffic
Looker strongly recommends encrypting network traffic between the Looker application and your database. In the case of Heroku Postgres, the only option available is to use SSL encryption. SSL must be enabled in your Looker connection in order for Heroku Postgres to function properly.
Users and Security
Looker will use the default user and password provided by Heroku to access the database. New users cannot be added to the Heroku Postgres instance, per their documentation.
Choosing the Type of Analytics Database
We never recommend connecting Looker directly to a production database. Instead, choose one of the two following options:
Streaming Replica “Hot Swap” Connection
In this setup there is no writable access for the use of Persistent Derived Tables since Heroku’s hot swap architecture doesn’t allow the writing capabilities needed for PDTs.
This is best option for real-time analytics because it’s connected to the streaming replica. This allows transactions to be written as the production database records them.
Forked Version of the Production DB
(requires development resources from your organization)
This setup allows for the use of Persistent Derived Tables, but is best for historical analysis (i.e., analysis not performed in real time).
If you use the standard Heroku process to create a fork your database credentials will change. This will require you to update information in Looker every time you create a fork. To avoid this problem, you’ll need to create a cron job that will fork the database on whichever interval is needed for historical reporting. This will allow you to maintain consistent database credentials.
If you are using Postgres version earlier than 9.2, you will not be able to kill queries through your database’s SQL console or through Looker
Temp Schema Setup (forked databases only)
Create a schema owned by the default user on the forked database:
CREATE SCHEMA looker_scratch AUTHORIZATION <default_user>;
This scratch schema will be used for persistent derived tables. Please note this will only function properly when you’re using a forked version of your database. You must NOT be using Looker on top of a “hot swap” streaming replication database, because you must be able to write to the database from outside the application.
For Looker to support some features, your database dialect must also support them.
In Looker 21.0, PostgreSQL supports the following Looker features:
After completing the database configuration, you can connect to the database from Looker using these directions.