PostgreSQL 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.
You must grant the CREATE, TEMPORARY privileges to the user. Otherwise, RudderStack will not be able to export the events to PostgreSQL.
Configuring PostgreSQL destination in RudderStack
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 RudderStack dashboard, set up the data source. Then, select PostgreSQL from the list of destinations.
Assign a name to your destination and click Next.
Host: Enter the host name of your PostgreSQL service.
Database: Enter your PostgreSQL database name where RudderStack will load the data.
SSH tunneling is a method of transferring data over an encrypted SSH connection. You can use it to add encryption to your legacy applications and achieve compliance with regulations like HIPAA, PCI-DSS, etc., without having to modify the existing applications.
RudderStack lets you connect to your PostgreSQL database securely over an SSH connection by configuring these settings:
SSH Connection: Enable this setting to use the SSH connection while connecting to your PostgreSQL database.
SSH Host: Enter the IP address of your bastion host.
SSH Port: Enter the port for the above host.
SSH User: Enter the username you use to access the bastion host.
SSH Public Key: Copy the public key provided in this field and add it to the authorized_keys file on your bastion host. RudderStack uses the private key corresponding to this public key to establish the connection successfully.
To enable the SSH connection for an existing PostgreSQL destination, navigate to the destination’s Configuration tab, select Edit configuration and enable the SSH connection setting.
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 SSL Modes section below.
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.
JSON Columns: Use this optional setting to specify the required JSON column paths in dot notation, separated by commas. This option applies to all incoming track events for this destination.
With the JSON columns feature, you can now ingest semi-structured event data not defined by a fixed schema. For more information on this feature, refer to the JSON Column Support guide.
Configuring the object storage
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:
Check if your database is accessible to RudderStack by Warehouse FAQ.
Ensure that all security group policies are appropriately set.
How does RudderStack handle reserved words?
There are some limitations when it comes to using reserved words 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.
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’.
How does RudderStack handle cases when loading data into PostgreSQL?
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.
For a more comprehensive FAQ list, refer to the Warehouse FAQ guide.
cookies, the cookies that are categorized as necessary are stored on your browser as they are as
for the working of basic functionalities of the website. We also use third-party cookies that
analyze and understand how you use this website. These cookies will be stored in your browser
consent. You also have the option to opt-out of these cookies. But opting out of some of these
have an effect on your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. This
category only includes cookies that ensures basic functionalities and security
features of the website. These cookies do not store any personal information.
learn more about cookies and why we use them, visit our cookie
policy. We'll assume you're ok with this, but you can opt-out if you wish Cookie Settings.