Webhooks allow real-time notification of events between applications. PostgreSQL is a trusted relational database. Combining the two can strengthen real-time vigilance so your system updates instantly when events occur. However, you need a reliable ETL pipeline to connect them.

This post will show you how to easily ETL Webhooks data into PostgreSQL in just minutes using Hevo. With Hevo, you can establish a Webhooks PostgreSQL connection and keep your data up-to-date without building and managing complex ETL scripts.

How to Establish Webhooks PostgreSQL Connect?

Hevo Data — a No-code Data Pipeline — helps Extract, Transform, & Load data from data sources such as Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services to 150+ Data Sources & Destinations (Webhooks PostgreSQL Connect Included). Further, Hevo also offers 40+ Free Sources to its new users.

In just 4 steps you can select the data source, provide valid credentials, and choose the destination — that’s it!

Get Started with Hevo for Free

In this section of this tutorial article, we’ll be establishing the Webhooks PostgreSQL connect using Hevo Data’s No-code Data Pipeline. Let’s all see how.

Step 1: Configuring Webhook as a Source

  • After successfully logging in to your Hevo account, select PIPELINES (by default, PIPELINES is selected in Hevo’s Asset Palette.
  • From the list of Sources, select Webhook. (In case, Webhook is not displayed click on “View All,” and check for Webhook. Webhook logo will be visible now, select it to continue.
Webhooks PostgreSQL | Step 1: configuring webhook as a source | Hevo Data
Hevo Documentation
Webhooks PostgreSQL | Step 1: configuring webhook as a source | Hevo Data
Hevo Documentation
  • A page named, “Configure your Webhook Source” will appear. Now, indicate the JSON path to the root of the Event name as well as the root of the fields in your payload.
  • Click on “CONTINUE.”
Webhooks PostgreSQL | Step 1: configuring webhook as a source | Hevo Data
Hevo Documentation

Step 2: Specifying PostgreSQL Connection Settings

Specify the following in the “Configure your PostgreSQL Source” page:

  • Pipeline Name: A unique name for your pipeline is recommended.
  • Database Host: The table listed below shows some examples of PostgreSQL hosts.
VariantHost
Amazon RDS PostgreSQLpostgresql-rds-1.xxxxx.rds.amazonaws.com
Azure PostgreSQLpostgres.database.azure.com
Generic PostgreSQL10.123.10.001 or postgresql.westeros.inc
Google Cloud PostgreSQL35.220.150.0
  • Database Port: The default value is 5432.
  • Database User: The user is listed as read-only, having permission to only read tables in your database.
  • Database Password: The password for your database for the read-only user.
  • Select Ingestion Mode: Read Ingestion Modes to know more.
  • Connection settings:
    • Connecting through SSH: Enabling this option allows you to connect to Hevo via an SSH tunnel rather than directly connecting your PostgreSQL database server to Hevo. This adds an extra layer of protection to your database by not exposing your PostgreSQL configuration to the public. Read more.
    • Use SSL: Allow it to utilize an SSL-encrypted connection. If you’re utilizing Heroku PostgreSQL databases, you also need to activate this. To activate it, enter the following:
      • CA File: This is the file that contains the SSL server certificate authority (CA).
      • Client Certificate: The public key certificate file for the client.
      • Client Key: The file containing the client’s private key.
  • Advanced Settings:
    • Load Historical Data: This option is only available for Pipelines in Logical Replication mode. If this option is enabled, the complete table data is retrieved during the Pipeline’s initial run. If this option is turned off, Hevo will only load data that was written in your database after the Pipeline was created.
    • Merge Tables: For Pipelines in Logical Replication mode. When you choose this option, Hevo combines tables with the same name from separate databases while loading data into the warehouse. With each record, Hevo populates the Database Name column. If this option is turned off, the database name is prefixed to each table name. See How Does the Merge Tables Feature Work? for more information.
    • Add New Tables to the Pipeline: Except for Custom SQL, this applies to all Ingestion modes.

2. To proceed, click on “Test & Continue.”

Webhooks PostgreSQL | Step 2: configuring PostgreSQL as a destination | Hevo Data
Hevo Documentation

Step 3: Setting up Webhook

Copy and paste the Webhook URL obtained in Step 2 above into the application from which you wish to send events to Hevo.

Here’s What Makes Hevo’s Data Pipelines Unique & User Friendly!

Aggregating & loading your data from a manual Webhooks PostgreSQL Connect, without the right set of tools, can be a mammoth task. Hevo’s automated platform empowers by automating the Webhooks PostgreSQL Connect and ensures a smooth Data Collection, Processing, and Aggregation experience. Our platform has the following in store for you that will help you decide upon Hevo-as-a-solution for Webhooks PostgreSQL ETL needs.

  • Exceptional Security: A Fault-tolerant Architecture that ensures Zero Data Loss.
  • Built to Scale: Exceptional Horizontal Scalability with Minimal Latency for Modern-data Needs.
  • Data Transformations: Process and Enrich Raw Granular Data using Hevo’s robust & built-in Transformation Layer without writing a single line of code.
  • Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the format of incoming data and replicates it to the destination schema. You can also choose between Full & Incremental Mappings to suit your Data Replication requirements.
Sign up here for a 14-Day Free Trial!

Step 4: Few Final Settings

Optionally, as part of the final settings, you can configure Transformations to purify or enhance the Source data in any way. You may also use the Schema Mapper to inspect and update the Source to Destination field mapping.

Your first pipeline with the Webhooks PostgreSQL Connect has been set up, and data intake has begun.

DB Webhooks

A Postgres add-on called DB Webhooks sets off webhooks in response to new, modified, or removed records. It makes use of database triggers to communicate with a Go programme via low-latency websocket communications. Next, this application sends a JSON payload containing predefined values from the database record to the configured webhook(s).

How It Works

  • Data is modified in a Postgres table (INSERT, UPDATE, DELETE)
  • A WebSocket message from a Postgres trigger alerts the DB Webhooks web server.
  • Data is formatted, filtered, and sent to defined webhooks using DB Webhooks.

Run DB Webhooks locally

You can run DB Webhooks locally with Docker.

git clone --depth 1 https://github.com/tableflowhq/db-webhooks.git

cd db-webhooks

docker-compose up -d

Then open http://localhost:3000 to access DB Webhooks.

Run DB Webhooks on AWS (EC2)

Option 1 (one-line install)

sudo yum update -y && \
sudo yum install -y docker && \
sudo service docker start && \
sudo usermod -a -G docker $USER && \
sudo curl -L "https://github.com/docker/compose/releases/download/v2.16.0/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose && \
sudo mv /usr/local/bin/docker-compose /usr/bin/docker-compose && \
sudo chmod +x /usr/bin/docker-compose && \
mkdir db-webhooks && cd db-webhooks && \
wget https://raw.githubusercontent.com/tableflowhq/db-webhooks/main/{.env,docker-compose.yml,.dockerignore,frontend.env} && \
sg docker -c 'docker-compose up -d'

Option 2 (guided install)

  1. To install Docker, run the following command in your SSH session on the instance terminal:
sudo yum update -y
sudo yum install -y docker
sudo service docker start
sudo usermod -a -G docker $USER
logout # Needed to close the SSH session so Docker does not have to be run as root
  1. To install docker-compose, run the following command in your ssh session on the instance terminal:
sudo curl -L "https://github.com/docker/compose/releases/download/v2.16.0/docker-compose-$(uname -s)-$(uname -m)"  -o /usr/local/bin/docker-compose
sudo mv /usr/local/bin/docker-compose /usr/bin/docker-compose
sudo chmod +x /usr/bin/docker-compose
docker-compose version
  1. Install and run DB Webhooks
mkdir db-webhooks && cd db-webhooks
wget https://raw.githubusercontent.com/tableflowhq/db-webhooks/main/{.env,docker-compose.yml,.dockerignore,frontend.env}
docker-compose up -d

Importance of Webhooks PostgreSQL Integration?

Webhooks PostgreSQL connect can be advantageous. It can help you store information almost immediately and later be used for analysis fetching the much-needed business advantage. Using the Webhooks PostgreSQL connect, you not just save information from tables and columns but also save index and data types, which is a valuable attribute when it comes to Webhooks PostgreSQL ETL needs. With this fusion, you’ll be benefited in the following ways:

  • Flexibility when it comes to choosing Data Types: All data types such as documents, primitives, geometry, structures, etc. are supported by PostgreSQL Database, making it super reliable for when big transitions are taking place in terms of database migration.
  • PostgreSQL’s Data Integrity really comes in handy: PostgreSQL ensures data integrity by imposing limits and restricting the data you contribute. You can forget about invalid or orphan records when you use PostgreSQL.
  • ETL Pipeline’s high-performance rate with little to no data latency issues: In its latest update, the feature list of  PostgreSQL has increased. The updates have been focused on boosting and optimizing the performance especially. So, nothing to worry about in that aspect.
  • Internationalization & Text Search: PostgreSQL supports international character sets for internationalization and text search. It also supports full-text search to expedite the search process and incorporates case-insensitive and accent-insensitive collations.

Conclusion

In this blog post, we have learned how to build and establish Webhooks PostgreSQL connect via Hevo’s no-code data pipeline. The established connection will let your Webhooks data flow to PostgreSQL error-free and with no delays. Further, if you want to know in detail about how to create a Hevo data pipeline, either of the two documentation links can help a great deal:

  1. PostgreSQL as a Destination
  2. Creating a WebHook Pipeline
Visit our Website to Explore Hevo

Want to give Hevo a try? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of establishing Webhooks PostgreSQL connect in the comment section below! We would love to hear your thoughts.

Reference:

Yash Arora
Former Content Manager, Hevo Data

Yash is a Content Marketing professinal with experience in data-driven marketing campaigns. He has expertise in strategic thinking, integrated marketing, and customer acquisition. She has driven growth for startups and established brands through comprehensive marketing communications, and digital strategies.

No Code Data Pipeline For PostgreSQL