PostgreSQL
Step-by-step guide on setting up PostgreSQL as a destination in RudderStack.
Last updated
Was this helpful?
Step-by-step guide on setting up PostgreSQL as a destination in RudderStack.
Last updated
Was this helpful?
is an enterprise-grade, open source database management system. It supports both SQL and JSON for relational and non-relational queries respectively. Many companies in the market use PostgreSQL as their low-cost data warehousing solution in order to deliver efficient analytics and user insights.
RudderStack lets you configure PostgreSQL as a destination to which you can seamlessly send your event data.
Refer to the guide for more information on how the events are mapped to the PostgreSQL tables.
Find the open source transformer code for this destination in the .
After setting up your PostgreSQL database, create a user with the necessary privileges to create schemas and temporary tables in this database.
Run the following query to create a new user in PostgreSQL:
Run the following query to grant permissions to the user to create new schemas and temporary tables in the database:
You must grant the CREATE, TEMPORARY privileges to the user. Otherwise, RudderStack will not be able to export the events to PostgreSQL.
To send event data to PostgreSQL, you first need to add it as a destination in RudderStack and connect it to your data source. Once the destination is enabled, events will automatically start flowing to PostgreSQL via RudderStack.
To configure PostgreSQL as a destination in RudderStack, follow these steps:
In your , set up the data source. Then, select PostgreSQL from the list of destinations.
Assign a name to your destination and click on Next.
Enter the following credentials in the Connection Credentials page:
Host: Enter the host name of your PostgreSQL service.
Database: Enter your PostgreSQL database name where RudderStack will load the data.
Password: Enter the password you set for the above user.
Port: Enter the port number associated with your PostgreSQL instance.
Namespace: Enter the schema name where RudderStack will create all the tables. If you don't specify any namespace, RudderStack will set this to the source name, by default.
Sync Frequency: Specify how often RudderStack should sync the data to your PostgreSQL database.
Sync Starting At: This optional setting lets you specify the particular time of the day (in UTC) when you want RudderStack to sync the data to the warehouse.
Exclude Window: This optional setting lets you set a time window when RudderStack will not sync the data to your database.
RudderStack lets you configure the following object storage configuration settings while setting up your PostgreSQL destination:
Use RudderStack-managed object storage: Enable this setting to use RudderStack-managed buckets for object storage.
This option is applicable only for RudderStack-hosted data planes. For self-hosted data planes, you will have to specify your own object storage configuration settings.
Choose your storage provider: If Use RudderStack-managed object storage is disabled in the dashboard, select the cloud provider for your object storage and enter the relevant settings:
Based on your security preferences, RudderStack lets you specify the SSL mode through which you can send the data to PostgreSQL.
RudderStack supports the following three SSL modes defined by PostgreSQL:
disable
No
No
SSL mode is disabled. Use it in cases where security is not an issue and you don't want the encryption overhead.
require
Yes
No
Use this mode when you want to encrypt your data and can deal with the resulting encryption overhead.
verify-ca
Yes
Dependent on the CA policy
Use this mode when you want to encrypt your data, can bear the encryption overhead, and want to be sure that you connect to a server you trust.
To use verify-ca as the SSL mode while configuring your PostgreSQL destination, you need to copy the contents of the following three files from your SSL certificate:
Client Key Pem File
Client Cert Pem File
Server CA Pem File
Although you can use an existing certificate to obtain the above credentials, it is recommend you create a new SSL certificate to avoid any issues.
In the left panel, click on Connections and go to the SECURITY tab.
Under Manage client certificates, click on CREATE CLIENT CERTIFICATE.
Assign a unique identifier for your SSL certificate and click on CREATE. Your new SSL certificate will be created, as shown:
Finally, copy the contents of the three fields and paste it in the RudderStack dashboard settings as seen below:
For other cloud providers, this procedure might vary slightly.
To enable network access to RudderStack, you will need to whitelist the following RudderStack IPs:
3.216.35.97
34.198.90.241
54.147.40.62
23.20.96.9
18.214.35.254
35.83.226.133
52.41.61.208
44.227.140.138
54.245.141.180
3.66.99.198
3.64.201.167
If your deployment is in the EU region, you can whitelist only the following two IPs:
3.66.99.198
3.64.201.167
All the outbound traffic is routed through these RudderStack IPs.
Ensure that all the security group policies are appropriately set.
Also, it is important to note that integers are not allowed at the start of the schema or table name. Hence, RudderStack prefixes such schema, column or table names with a _.
For instance, '25dollarpurchase' will be changed by RudderStack to '_25dollarpurchase'.
RudderStack converts the event keys into the lower case before exporting the data into PostgreSQL, so that it does not create multiple tables if the event name is written in different cases.
User: Enter the name of the user created in the section above.
SSL Mode: Choose the SSL mode through which RudderStack will connect to your PostgreSQL instance. RudderStack provides three options - disable, require, and verify-ca. For more information on these options, refer to the section below.
For more information, refer to the documentation.
The following steps demonstrate how you can create a new SSL certificate in and obtain the above-mentioned credentials:
Go to your PostgreSQL instance in your .
Firstly, check the status of your data sync in the .
Make sure you have set up the for your PostgreSQL instance.
Check if your database is accessible to RudderStack by .
There are some limitations when it comes to using in a schema, table, or column names. If such words are used as event names, traits or properties, they will be prefixed with a _ when RudderStack creates tables or columns for them in your schema.
For queries on any of the sections covered in this guide, you can or start a conversation in our community.
