The Postgres ETL integration exports data from Singular automatically to a Postgres database. If ETL is enabled for your account, you can follow the instructions below to set the integration up.
For general information about Singular's ETL offering, see the Singular ETL FAQ.
Note: Singular recommends storing Singular data in a new schema within the same database.
If you choose to push the data into a separate database you won't be able to join Singular data with your internal data sets.
1. Create Schema and User in Postgres
Create a new schema with the following command:
CREATE SCHEMA singular;
Create a new user with the following command:
CREATE USER singular WITH PASSWORD '<password>';
Grant the user permissions as follows:
GRANT ALL ON SCHEMA singular TO singular;
GRANT ALL ON ALL TABLES IN SCHEMA singular TO singular;
Note: This allows Singular to create new tables in the provided database & schema. It does not allow Singular to read or change other schemas or tables in the database.
2. Whitelist Singular Server IPs
If you are using a firewall and only allow certain IPs to connect to your Postgres instance, you need to add Singular’s servers to the whitelisted IPs in the policy. These are the IPs:
3. Add a Postgres Data Connector
To add a "Postgres Destination" data connector in Singular:
- In your Singular account, go to Settings > Data Connectors and click Add New Connector.
Type in "Postgres Destination".
In the window that opens, fill in the relevant details:
|Username||The username and password you created in Step 1.|
|Hostname||The hostname of your Postgres instance.|
|Database Name||The database that you granted Singular permissions to.|
|Schema Name||Enter a name for the schema that Singular will create and write to. Default: "singular".|
|Table Name||Enter a name for the table that Singular will create and write to. Default: "marketing_data".|