Notification

This Help Center is moving to Cloud. Learn more about the migration.

Connect to PostgreSQL

PostgreSQL is an open source relational database management system developed by a worldwide team of volunteers.

The PostgreSQL connector allows you to access data from PostgreSQL based databases within Looker Studio.

In this article:

How to connect to PostgreSQL

This connector uses the PostgreSQL JDBC driver to connect a Looker Studio data source to a single PostgreSQL database table.

Supported versions

We've tested Looker Studio on the following PostgreSQL versions:

  • 9.6
  • 10
  • 11
  • 12
  • 13
  • 14

If you experience issues using a supported version, please let us know in the Looker Studio Help Community. You can add and vote on feature requests here.

To connect

  1. Sign in to Looker Studio.
  2. On the Looker Studio home page, in the top left, click The Create icon. Create, and then select Data Source.
  3. Select the PostgreSQL connector.
  4. Configure access to your database using one of the connection options (see below).
  5. Click AUTHENTICATE.
    • You will see a list of tables in that database.
  6. Select a table.
  7. Click CONNECT.

You can now access all of the columns in the table as fields in your reports.

Connection options

BASIC

To connect with a hostname or IP address, you'll need to provide the following information:

  • Hostname or IP address
  • Port (Optional)
  • Database
  • Username
  • Password

JDBC URL

To connect via a JDBC URL, provide the following connection information:

  • JDBC URL
    Example

    jdbc:postgresql://<hostname or IP address>[:<port>]/<database>
    • Username
    • Password
If you use the JDBC hostname option, you can make sure it's accessible with public DNS lookup tools such as Whois.
To connect using an IPv6 literal address, enclose the address in square brackets. For example: jdbc:postgresql://[2001:db8:1:1:1:1:1:1]/your_db
For more information, see https://datatracker.ietf.org/doc/html/rfc2732

Enable SSL (optional)

Looker Studio supports secure (encrypted) connections to the server using the TLS (Transport Layer Security) protocol. TLS is also referred to as SSL (Secure Sockets Layer). To enable a secure connection, check Enable SSL, then provide your SSL configuration files.

Enable client authentication (optional)

When this box is checked, you can upload a client private key and a client certificate if required to access your database.

Note: Looker Studio expects a PKCS-8 client key format.

Learn more about PostgreSQL secure connections.

Custom Query

Select the CUSTOM QUERY option to provide a SQL query instead of connecting to a single table. Looker Studio uses this custom SQL as an inner select statement for each generated query to the database.

Custom SQL queries may only have a single statement.

For example, the following will not work because it has multiple SQL statements:

DECLARE cost_per_tb_in_dollar FLOAT64 DEFAULT 4.2;

SELECT total_bytes_billed / (1024 * 1024))* cost_per_tb_in_dollar)/(1024*1024))) FROM billing-table;

Note: Queries in Looker Studio may timeout after 3 to 5 minutes. If your custom queries time out, try the following approaches to resolve the issue:
  • Simplify the query so that it runs faster.
  • Run the query in your database and store the results in a separate table. Then connect to that table in your data source.

Notes

Supported data types

The PostgreSQL connector currently supports the following data types:

  • Numeric
  • Character
  • Boolean
  • Date/Time (except Intervals)

When Looker Studio encounters a column of a non-supported type, it will not create a field for that column.

Learn more about PostgreSQL data types.

Firewall and database access

To allow Looker Studio to connect to and query databases that are behind a firewall, you will need to open access to one of the IP addresses shown below. You may also need to configure your database to accept traffic from these addresses. Consult the documentation for your specific database for instructions on how to do this.

IP addresses

  • 142.251.74.0/23

  • 2001:4860:4807::/48 (Optional, for platforms that support IPv6)

Note the following networking limitations when using the PostgresSQL connector:

  • Looker Studio doesn't support private IP connections to PostgreSQL on Cloud SQL.

  • The PostgreSQL connector doesn't use the Cloud SQL proxy when connecting to Cloud SQL PostgreSQL instances. If you're connecting to a Cloud SQL PostgreSQL instance, you'll need to add the listed IPs to the authorized networks list. See the Cloud SQL documentation.

Limits of the PostgreSQL connector

  • Selecting a table outside of the public schema isn't supported. However, you can select the CUSTOM QUERY option to write a query that includes tables in any schema.
  • You can query a maximum of 150 K rows per query using this connector. If you exceed this limit, the data will be truncated.
  • Column headers (field names) must use ASCII characters only. Non-ASCII characters aren't supported.

Troubleshooting

See the Looker Studio troubleshooting guide for a list of common error messages and steps to resolve them.

Create a data source

Was this helpful?

How can we improve it?

Need more help?

Try these next steps:

Post to the help community Get answers from community members
true
What's new in Looker Studio?

Learn about new features and recent changes. Looker Studio release notes live on Google Cloud.

Search
Clear search
Close search
Google apps
Main menu
10179587420170064093
true
Search Help Center
true
true
true
true
true
102097
false
false