Skillfully Establish Webhooks PostgreSQL Connect Using Hevo: Easy 4 Steps Guide

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.

What is a Webhook?

Webhooks

A webhook is an HTTP request that is triggered by an event in a source system and delivered to a destination system, that, too, frequently with a data payload. In general, Webhooks are automated, which means they are sent out when an event occurs in the originating system.

When an event occurs, one system (the source) can “talk” (HTTP request) to another system (the destination) and communicate information (request payload) about the event.

But, for what exact use case is a webhook used?

I’m sure you’re getting a sense of what webhooks are used for based on the definition above. Simply said, webhooks are used to convey the existence of an event in one system to another, and they frequently share event details. However, an example is usually better to explain, so let’s have a look at a real-world example of webhooks in action.

Assume you have a subscription to a streaming provider. When the streaming service charges your credit card, it wants to send you an email at the start of each month. That way you can keep track of your monthly expenses and spend accordingly. Based on the given explanation, I’m sure you’re getting a feel of what webhooks are used for. So, to sum up, Webhooks are used to notify another system of the occurrence of an event, and they typically provide event details.

What is PostgreSQL?

PostgreSQL

PostgreSQL is a relational database management system. The database contains data points in rows, with columns representing various data properties. It distinguishes itself by emphasizing more on integrations and extensibility. PostgreSQL database is scalable since it interacts with many different technologies and adheres to numerous database standards.

Many corporations have publicly funded the PostgreSQL project’s development in recent years. But now, let’s look at why it has become so popular and why should you prefer PostgreSQL over other RDBMS?

PostgreSQL is an enterprise-class database with advanced features such as Multi-Version Concurrency Control (MVCC), point-in-time recovery, tablespaces, asynchronous replication, nested transactions, online/hot backups, a sophisticated query planner/optimizer, and write-ahead logging for fault tolerance.

PostgreSQL is, of course, free — and compatible with the majority of common operating systems, including all Linux and Unix variants, Windows, and Mac OS X. Because it is open-source, the solution is simple to upgrade, extend, and, most important of all, learn! You can design your own data types, create custom functions, and even add code in another programming language (such as Python) without having to recompile the database in PostgreSQL.

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.

• 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.”

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.

Variant	Host
Amazon RDS PostgreSQL	postgresql-rds-1.xxxxx.rds.amazonaws.com
Azure PostgreSQL	postgres.database.azure.com
Generic PostgreSQL	10.123.10.001 or postgresql.westeros.inc
Google Cloud PostgreSQL	35.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 with the Webhooks PostgreSQL connection, click on “Test & Continue.”

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.

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

2. 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

3. 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:

• tableflowhq/db-webhooks