Ready to get the most from your PostgreSQL data? Get started by setting up a connection between Klipfolio Klips and your PostgreSQL database and adding some data sources.
When you're finished adding PostgreSQL data sources, you'll use them to power your custom Klips.
Note: Before connecting to your PostgreSQL database, make sure you have Klipfolio's IP addresses set up in your DB security group.
This article includes:
Adding a PostgreSQL data source
To add a PostgreSQL data source:
- Click the + beside Data Sources in the left navigation sidebar.
Note: If you don’t see this option, go to the bottom of the left navigation sidebar and click your Account Name > Data Sources > Create a New Data Source instead. - On the Where is your data? page, select PostgreSQL.
- Select a pre-built or custom connection for your data source.
- On the Configure your connection page, fill in the following fields:
- Host: Enter the hostname of the server.
- Port: 5432 is entered here by default.
- Database: Enter the name of the database you want to connect to.
- Driver: Select the desired driver from the drop-down list.
- Username: Enter the PostgreSQL user name (one with a role that has access to the database).
- Password: Enter the password associated with the user name.
- SQL Query: Enter the desired SQL query, replacing the placeholder text <databaseTable> with your own database table name.
- Select whether to Include column headers.
- At Connection security, if you select Use SSL/TLS, optionally also select to Turn off certificate validation (not recommended) or Use TLS v1.2. Note: Options here depend on the selected driver.
- If the database is behind a firewall but accessible using an SSH tunnel, click Use an SSH tunnel and enter the required credentials.
- Click Get data.
- Ensure this is the data you’re looking for. Optionally select the checkbox to Model your data.
Learn more about modelling data sources. - Click Continue.
- Name, choose refresh settings, and, optionally, share your data source.
- Click Save.
The data source is added to your account and is ready to use for custom Klips. You can see all your data sources in your Data Source Library, accessed by clicking Data Sources in the left navigation sidebar.
Troubleshooting your connection
If you followed the instructions in the procedures above but still can’t connect to Klipfolio, consider the following:
- Have you added our Klipfolio IP addresses to your allow list? See the full list of allow list IP addresses.
- Is your database's external IP address publicly accessible and are you able to resolve the IP address to your database server? To test internally, ping or telnet your database's IP address. If unsuccessful, check your firewall configuration.
- Is the port open? Go to the Port forwarding tester to test your port's status. If the port is not open, it might be a bad route or the destination SQL device might not be properly configured to listen to that port.
- If you’re using an SSH tunnel, is the domain account valid?
Troubleshooting your query
Here are a few troubleshooting ideas you can try if you're having issues with your PostgreSQL query:
- Confirm that the SQL connection is working by using a query of Select 1. This should return the result of 1.
- Make sure your query isn’t trying to bring in too much data. Klipfolio supports a maximum file size of 10 MB. If you’re returning more than 10 MB of data, your query may not work. To see if this is the issue, try adding a LIMIT to your query (e.g., LIMIT 1000) to reduce the amount of data being returned.
- Confirm that it isn't taking too long to bring in your data. Klipfolio includes a global setting of 80 seconds within which to bring in data. If you think this might be the issue, try reducing the amount of your data so you stay within this range.
Next steps
Great! You've connected to your PostgreSQL data and created one (or more) data sources. Now you're ready to use those data sources in custom Klips. Up next - Build Klips and dashboards.