How to connect PostgreSQL

Data supports streaming the data to Postgres data storage.

Creating a Database in PostgreSQL

To create a database in your PostgreSQL server. Log into your PgAdmin tool. Select the server that you want to create the database in it.

Postgres Serverand then create a new database from the database section

Creating Postgres Database

Name your database and then click on save.

Getting your data to PostgreSQL is an easy task to do with Dataddo

First, you need to create a Data Flow. You can do this either by navigating to Sources, picking the Source that you want to use and clicking on Add to flow

2. Creating flow form source

OR by navigating to Data Flows.

1. Data flow button

And clicking on New flow.

3. Data Flows PageAdd the required source by clicking on Add Source.

4. Adding Source for Flow

Select the source that you want to connect to your destination. 

5. Choosing Source for flow

Now click on Add Destination, to select a destination from below.

6. Selecting DestinationClick on create a new destination to create a new PostgreSQL destination.

7. Creating New Destination

Now select PostgreSQL

Choosing Postgres

Fill the configuration boxes based on your own PostgreSQL storage database, host and credentials, then click on Connection Check. 

NOTE:

Network setup required. Make sure to white list the following IP address to allow incoming connection from the Dataddo application to your server. 

34.248.161.212 

52.17.0.197

52.49.81.223

Postgres Checking Connection

Finally, once all the information is correct, you can click on Create Flow. 

Creating flow for Postgres

Free consultation available!
Feel free to contact us if you encounter a problem during configuration. We're happy to help you configure your integrations with your dashboarding app of choice!

 

Delivering data to storage

Append-only

When data delivery is scheduled, Dataddo gets the actual contents of the data source and perform a set of INSERT operations (one or multiple) against your storage. Neither DELETE nor UPDATE operation is performed, therefore the data are inserted in append-only mode.

Deleting historic rows

In certain cases, the append-only solution might have drawbacks due to the growing size of the database. To cope with this, the best solution is to define an AFTER INSERT trigger that triggers a stored procedure that deletes the historic data. In the example below, the stored procedure deletes all the rows meeting the insert_date or dataddo_insert_date < NOW() - INTERVAL '1 days' condition. Dataddo connectors allow to extract of various date information, so the retention period can be flexibly set.

  • In the case the source has insert_date as timestamp
CREATE FUNCTION delete_old_rows() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM table_name WHERE insert_date < NOW() - INTERVAL '1 days';
RETURN NULL;
END;
$$;
  • In the case the source has dataddo_insert_date as timestamp
CREATE FUNCTION delete_old_rows() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM table_name WHERE dataddo_insert_date < NOW() - INTERVAL '1 days';
RETURN NULL;
END;
$$;

The associated trigger triggers the stored procedure after every insert operation on the table_name.

CREATE TRIGGER trigger_delete_old_rows
AFTER INSERT ON table_name
EXECUTE PROCEDURE delete_old_rows();