New in Looker 7.10, Looker can use connection pools on PostgreSQL to improve query performance.
Dialects Using These Instructions
PostgreSQL, Google Cloud PostgreSQL, and Microsoft Azure PostgreSQL share database setup requirements as described on this page. However, for PostgreSQL on Heroku please see our PostgreSQL on Heroku documentation page.
Encrypting Network Traffic
Looker strongly recommends encrypting network traffic between the Looker application and your database. Consider one of the options described here.
If you’re interested in using SSL encryption, see the PostgreSQL documentation.
Users and Security
some_password_here to a unique, secure password:
If you’re using a schema other than
public, run this command to grant usage permissions to Looker:
To make sure that future tables you add to the public schema are also available to the Looker user, run these commands:
Postgres Versions pre 9.2
For Postgres versions prior to 9.2, non-superusers do not have the necessary permissions to kill connections. To work around this, a custom function created with a security definer can be used instead, as shown below (see this article for further details):
Postgres Versions pre 9.0
Additionally, for Postgres versions prior to 9.0, you cannot grant
SELECT to multiple tables at the same time.
You must execute a command for each table like this:
If you wish to
GRANT SELECT on all of your tables to Looker you can generate the statements by executing this query:
Just copy and paste the output from this query into your psql session or database GUI.
Temp Schema Setup
Create a schema owned by the Looker user:
Postgres on Amazon RDS
Create a scratch schema:
Change the ownership of the scratch schema to the Looker user:
For PostgreSQL persistent derived tables to work, you must not be using Looker on top of a “hot swap” streaming replication database. You must be able to write to the database from outside the application.
Finally, you should set an appropriate
search_path, which Looker’s SQL Runner uses to retrieve certain metadata from your database. Assuming you have created a user called
looker, and a temp schema called
looker_scratch, the command is:
Looker’s ability to provide some features depends on whether the database dialect can support them.
In the current Looker release, PostgreSQL supports the following Looker features:
In the current Looker release, Google Cloud PostgreSQL supports the following Looker features:
In the current Looker release, Microsoft Azure PostgreSQL supports the following Looker features:
After completing the database configuration, you can connect to the database from Looker using these directions.