Set up a Postgres data destination to export data from Singular automatically to a Postgres database.
Note: Data destinations are an enterprise feature (learn more).
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 and 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 Destination
To add a Postgres data destination in Singular:
- In your Singular account, go to Settings > Data Destinations and click Add a new destination.
Type in "Postgres".
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".|