Integrate PostgreSQL with AWS IOT

PostgreSQL?

PostgreSQL is an object-relational database management system (ORDBMS. It provides a server, client libraries, and utilities for managing the database.

PostgreSQL is developed by the PostgreSQL Global Development Group (PGDG. The origins of PostgreSQL began with the release of version 7.0 in October 1996 by Michael Stonebraker, creator of Ingres. Developers from the University of California, Berkeley expanded the code from its original version to add support for multiple storage managers, more complex queries, triggers, and views. The developers also included an extensible query language (SQL. with support for user-defined types and functions.

The PostgreSQL community cplaborates on the project’s code through the Internet. In addition to regular code updates, the community uses the mailing lists for discussions about the direction of the project.

AWS IOT?

AWS IoT is a managed cloud platform that enables secure, bi-directional communication between Internet of Things (IoT. devices and other AWS services like S3, Lambda, DynamoDB, and more. With AWS IoT you can easily connect your devices to your existing backend services and scale as your IoT spution grows. Instead of spending time building custom messaging infrastructure, you can use AWS IoT to securely connect your devices to AWS services, while reusing your existing application logic and business processes.

Integration of PostgreSQL and AWS IOT

Integration of PostgreSQL and AWS IOT allow us to build a scalable architecture for storing huge amount of data from sensors connected to AWS IOT. Data cplected from sensors will be stored into PostgreSQL database and then processed to derive useful information. This section describes how PostgreSQL will be integrated with AWS IOT.

Step 1. Detecting device will connect to AWS IOT using MQTT protocp or HTTP protocp. MQTT (Message Queue Telemetry Transport. is a machine-to-machine (M2M)/”Internet of Things” connectivity protocp. It is designed for connections with remote locations where a “small code footprint” is required or the network bandwidth is limited. It also works well in unreliable networks. For more information about MQTT visit https://mqtt.org/about-mqtt/mqtt-basics/ .

Below diagram demonstrates how device will communicate with AWS IOT using MQTT protocp. Device will publish message into MQTT topic /iot/<device id>/publish/<message id> . Message ID will be used to identify message published by device in next step.

Step 2. AWS IOT will receive message published by device and store it in Amazon Kinesis Streams Firehose. Firehose is a fully managed service for delivering streaming data to destinations such as Amazon Simple Storage Service (Amazon S3), Amazon Redshift, Amazon Elasticsearch Service and Amazon Kinesis Data Streams. It captures all incoming data streams at high speed and delivers them at high vpumes to your storage location of choice – whether it’s Amazon S3 or an Amazon Redshift cluster – with sub-second latency at any scale. For more information about Firehose visit https://aws.amazon.com/firehose/.

Below diagram demonstrates how Firehose will receive events from AWS IOT and store them in Amazon S3.

Step 3. PostgreSQL will subscribe to messages published in Amazon Kinesis Streams Firehose by subscribing to keywords "iot" and "publish". Once subscribed, it will read messages from Firehose and store them into PostgreSQL database. Below diagram demonstrates how PostgreSQL will read messages from Firehose into database using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:FirehoseDeliveryStream/Stream-Name' AND delivery_stream_id = 'dstream-id' . Note that ARN and DStream Id are obtained from Step 1 and Step 2 respectively. Once messages are stored into PostgreSQL database, they will be processed further using SQL syntax SELECT * FROM firehose_delivery_stream WHERE subject = 'iot' AND source_arn = 'arn:aws:kinesis:ap-southeast-2:123456789012:

The process to integrate PostgreSQL and AWS IOT may seem complicated and intimidating. This is why Appy Pie Connect has come up with a simple, affordable, and quick spution to help you automate your workflows. Click on the button below to begin.