Heroku for PostgreSQL to Redshift: 3 Easy Ways to Connect

on Activate, Amazon Redshift, Data Integration, Data Warehouses, Database Management Systems, ETL, ETL Tutorials, Heroku, Hevo Activate, PostgreSQL, Reverse ETL • June 15th, 2022 • Write for Hevo

Heroku for PostgreSQL to Redshift_FI

Amazon Redshift and Heroku Postgres are categorized as “Big Data as a Service” and “PostgreSQL as a Service” solutions.

Heroku is a cloud hosting platform that provides support and services to assist you with infrastructure management. Amazon Redshift is a cloud data warehouse service that processes, stores, and analyzes enormous amounts of data. Administrators create role-based permissions for all systems in your multi-cloud environment with Heroku for PostgreSQL to Redshift connection, offering one-click access to anything you need.

In this blog, you’ll learn about Heroku for PostgreSQL to Redshift Connection and an overview of the two platforms.

Table of Contents

What is Heroku?

Heroku for PostgreSQL to Redshift - Heroku logo
Image Source

Heroku is a cloud-based platform as a service (PaaS) that supports a variety of programming languages. Heroku, one of the first cloud platforms has been in development since June 2007, initially supporting only the Ruby programming language, but has subsequently expanded to include Java, Node.js, Scala, Clojure, Python, PHP, and Go.

As a result, Heroku is known as a polyglot platform since it allows developers to build, execute, and scale apps in various languages in a comparable manner. Heroku Postgres is Heroku’s cloud database (DBaaS) service based on PostgreSQL. Heroku Postgres supports continuous protection, rollback, and high availability, as well as forks, followers, and data clips.

Key Features of Heroku

Heroku is now recognized as one of the most dependable platforms as a service provider for enterprises of all sizes. Take a peek at the features of this popular platform:

  • Free to Begin: Users can get started with Heroku’s free tier, which has some limitations and basic capabilities. It is a good plan for individuals who want to build a simple cloud app.
  • Simple to Use: Heroku, a renowned platform as a service provider, is known for its simplicity. Heroku’s free edition is best suited for small development projects.
  • Developer-Centric: Heroku is dedicated to assisting developers in honing their skills to construct feature-rich applications. The experience is beneficial for developers because they have access to several important tools to speed up fundamental developmental processes.
  • Simple to Scale: Heroku PaaS runs the code written by developers on a Dyno. Each dyno is essentially a container that houses resources like memory and CPU.
  • Excellent Plugins and Third-Party Support: The Heroku ecosystem includes numerous cloud services that might be quite useful to developers. These are very useful for developing, administering, and running applications.

What is Amazon Redshift?

Heroku for PostgreSQL to Redshift - Amazon Redshift logo
Image Source

Amazon Redshift is a petabyte-scale data warehouse system powered by Amazon Web Services. It also utilizes large database migrations because it facilitates data management.

Amazon Redshift’s architecture is built on Massively Parallel Processing (MPP). Amazon Redshift Databases are Column-Oriented Databases that are designed to connect to SQL-based clients and BI tools. This provides users with continuous access to data (structured and unstructured) and supports the execution of Complex Analytic queries. Amazon Redshift also supports normal ODBC and JDBC connections.

Customers benefit from a fully-managed Data Warehouse solution because it automates administrative tasks, allowing them to focus on data-driven business decisions rather than repetitive tasks. The Client Application and the Data Warehouse Cluster must be able to communicate reliably with one another. Each Cluster in an Amazon Redshift Data Warehouse has its own set of computing resources and its own Amazon Redshift Engine running at least one Database.

Key Features of Amazon Redshift

Here are some of Amazon Redshift’s features:

  • Integrated Analytics Ecosystem: AWS’s built-in ecosystem services simplify End-to-End Analytics workflow management while avoiding compliance and operational stumbling obstacles.
  • SageMaker Support: It enables users to construct and train Amazon SageMaker models for Predictive Analytics utilizing data from their Amazon Redshift Warehouse, making it an essential tool for today’s Data Professionals.
  • Use ML for Maximum Performance: Amazon Redshift features powerful Machine Learning (ML) capabilities that allow for high throughput and speed. Its advanced algorithms predict forthcoming questions based on specific factions, allowing vital tasks to be prioritized.

Explore these Methods to Connect Heroku for PostgreSQL to Redshift

Due to business requirements, organizations need to migrate data from Heroku for PostgreSQL to Redshift databases. Here, you will learn 3 methods that will help you migrate your data from Heroku for PostgreSQL to Redshift:

Method 1: Heroku for PostgreSQL to Redshift Using Hevo

Hevo Data, an Automated No-code Data Pipeline, helps you load data from Heroku PostgreSQL to Redshift in real-time and provides you with a hassle-free experience. You can easily ingest data using Hevo’s Data Pipelines and replicate it to your desired Data Warehouse without writing a single line of code. 

GET STARTED WITH HEVO FOR FREE

Method 2: Heroku for PostgreSQL to Redshift with SSL Validation

SSL is a cryptographic protocol that encrypts and validates all web requests from beginning to end. Enable SSL on apps that send sensitive data to ensure that all information is sent securely.

Heroku SSL is a feature set that enables SSL for all Heroku apps. Heroku SSL employs Server Name Indication (SNI), a popular extension to the TLS protocol.

Method 3: Heroku for PostgreSQL to Redshift without SSL Validation

By default, most clients will connect via SSL, this method can save a little bit of time but you’ll end up manually setting up pipelines!

Heroku for PostgreSQL to Redshift Connection

You can set up Heroku PostgreSQL and transfer data from Heroku to Redshift in a variety of ways to perform insightful analysis. You can accomplish this using either of the three methods listed below:

Method 1: Heroku for PostgreSQL to Redshift Using Hevo

Heroku for PostgreSQL to Redshift -Hevo Banner
Image Source

Hevo provides an Automated No-code Data Pipeline that helps you move your Heroku PostgreSQL data swiftly to Redshift. Hevo is fully-managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Manually mapping fields from source to destination, as mentioned in the previous method, is a time-consuming process. Hevo has a feature called Automapping that can help you avoid this hassle. When you enable Automapping, Hevo will automatically create a table in the destination and map it to the source.

It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using BI tools.

You just need to follow a simple process to connect Heroku for PostgreSQL to Redshift using Hevo:

Step 1: Obtain PostgreSQL Database Credentials

  • Log in to your Heroku account.
  • Select the app containing the PostgreSQL database and open the databases dashboard.
  • Access the DATA tab and click on the PostgreSQL database you want to use.
Heroku for PostgreSQL to Redshift - PostgreSQL Database Credentials
Image Source
  • Click Settings, and then, View Credentials.
Heroku for PostgreSQL to Redshift - Heroku Settings
Image Source
  • Use the credentials provided below while setting up your Heroku PostgreSQL Source in Hevo.

Step 2: Set up Amazon Redshift as a Destination

Perform the following steps to configure Amazon Redshift as a Destination in Hevo:

  • Click DESTINATIONS in the Asset Palette.
  • Click + CREATE in the Destinations List View.
  • On the Add Destination page, select Amazon Redshift.
  • In the Configure your Amazon Redshift Destination page, specify the following:
Heroku for PostgreSQL to Redshift - Redshift as a destination
Image Source

You can easily connect Quickbooks to Redshift using Hevo by following the steps outlined above!

Here are more reasons to try Hevo:

  • Smooth Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to your schema in the desired Data Warehouse.
  • Exceptional Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Fexibilty designed for everyone.
  • Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
  • Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Try Hevo to easily connect Heroku for PostgreSQL to Redshift!

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

Method 2: Heroku for PostgreSQL to Redshift with SSL Validation

Step 1: Creating a Heroku Database

Heroku for PostgreSQL to Redshift - Setting up Database
Image Source

If you want to give each app its database and credentials. To do so, you’ll use the psql binary to connect to the RDS instance remotely (make sure port 5432 on this instance is open for 0.0.0.0/0 via its security group):

#Heroku for PostgreSQL to Redshift 
$ psql -U $RDS_ROOT_USER -h $NAME.$ID.$DATACENTER.rds.amazonaws.com --dbname=postgresql

It will request your $ROOT_PASSWORD. On the RDS Dashboard, you can find your amazonaws.com URL, and the $ROOT_USER is the one you specified when you created this instance. If you leave this field blank when creating the RDS instance, the dbname is PostgreSQL.

Once connected, you can examine the existing databases and users:

#Heroku for PostgreSQL to Redshift
psql$ list
psql$ du

OK, let’s make a new user and a new database, granting this user full access. Assume your Heroku app is called whiteunicorn1234. To begin, run the following command on your laptop to generate a URL-friendly password:

$ openssl rand -base64 32 | tr -d '=/+'

Create a PG user and a dedicated database in the psql prompt. Do not simply copy and paste.

#Heroku for PostgreSQL to Redshift
psql$ create role whiteunicorn1234 with password 'PASTE_P'W'D_HERE' login;
psql$ create database whiteunicorn1234;
psql$ grant all on database whiteunicorn1234 to whiteunicorn1234;
psql$ q

Step 2: Adding RDS SSL Certificate for your Application

#Heroku for PostgreSQL to Redshift 
$ cd your_app
$ mkdir -p config
$ curl https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem > ./config/rds-combined-ca-bundle.pem
$ git add config/rds-combined-ca-bundle.pem
$ git commit -m "Add RDS certificate to app files"
$ git push heroku master

Step 3: Dump and Load it on RDS

This will force your application to go offline.

$ cd your_app
$ heroku maintenance:on
$ heroku pg:backups capture
$ curl -o /tmp/latest.dump `heroku pg:backups public-url`

Before changing the DATABASE URL environment variable on Heroku, the goal is to send all data stored on Heroku to Amazon RDS.

$ pg_restore --verbose --clean --no-acl --no-owner 
    -h $NAME.$ID.$DATACENTER.rds.amazonaws.com 
    -U whiteunicorn1234 
    -d whiteunicorn1234 
    /tmp/latest.dump

#Heroku for PostgreSQL to Redshift

This will prompt you for the whiteunicorn1234 password you generated earlier with the OpenSSL command.

To see if the pg restore command was successful, open a new psql prompt and run the following commands:

#Heroku for PostgreSQL to Redshift 

$ psql -U whiteunicorn1234 -h $NAME.$ID.$DATACENTER.rds.amazonaws.com
psql$ SELECT
        nspname AS schemaname,relname,reltuples
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE
        nspname NOT IN ('pg_catalog', 'information_schema') AND
        relkind='r'
      ORDER BY reltuples DESC;

Step 4: Testing Heroku for PostgreSQL to Redshift

You must destroy your Heroku Database on Heroku because Heroku does not allow you to override the DATABASE_URL. This can be frightening, so double-check that the pg_restore command worked.

#Heroku for PostgreSQL to Redshift 
$ heroku addons:destroy heroku-postgresql
$ heroku config:set 
    DATABASE_URL="postgres://whiteunicorn1234:$PASSWORD@$NAME.$ID.$DATACENTER.rds.amazonaws.com/whiteunicorn1234?sslca=config/rds-combined-ca-bundle.pem"
$ heroku maintenance:off

That’s it! The Heroku app seems to be using a PostgreSQL database on Amazon Redshift!

Method 3: Heroku for PostgreSQL to Redshift without SSL Validation

Create a data source connection that corresponds to the data source vendor if you want to connect to Heroku Postgres. In this case, you intend to work with PostgreSQL, so you must establish a connection to it.

SSL is required for the connection to Heroku Postgres (like above). However, to establish a successful SSL connection, you must first upload a certificate to your Heroku application. Each Heroku application requires its SSL certificate. See the Heroku SSL article for instructions on how to configure these certificates.

If you do not intend to add the certificate to the key store, you can use the NonValidatingFactory option to bypass server validation and establish an encrypted connection.

  1. Create an application in your Heroku account using the Heroku Postgres add-on.
  2. Get the database credentials from the Heroku Postgres add-settings. on’s
  3. Access the data source properties. You can access data source properties by selecting one of the following:
    • Go to File | Data Sources.
    • Ctrl+Alt+Shift+S is the shortcut.
    • Click the Data Source Properties icon in the Database Explorer (View | Tool Windows | Database Explorer). The icon for Data Source Properties.
  4. Click the Add icon (The Add icon) in the Data Sources and Drivers dialog and select PostgreSQL.
  5. Click the Download missing driver files link at the bottom of the data source settings area.
  6. Click the Advanced tab in data source settings and modify the following properties:
    • To validate both the SSL certificate and the server, use ssl:true
    • sslmode:verify-ca: checks the certificate chain up to the root certificate stored on the client to verify the server.
    • To allow SSL connections without validating the server certificate, use sslfactory:org.postgresql.ssl.NonValidatingFactory.
  7. In the Host, Database, User, Password, and Port fields, enter the Heroku Postgres add-on connection information.

Click the Test Connection link to ensure that the connection to the data source is successful.

Integration with Heroku Postgres
Image Source

Benefits of Heroku for PostgreSQL to Redshift

Here are some benefits of Heroku for PostgreSQL to Redshift Connection:

  • Easy Fetch: You can map a single Heroku Postgres table to multiple Amazon Redshift tables.
  • Simple Complexity: It allows complex mathematical and string operations and conditions to be used for data mapping
  • Lookup: Lookup mapping can be used to map target columns to values retrieved from other target objects based on the source data.
  • Constant: When integrating data, you can use constant values to fill some Heroku Postgres fields that don’t have corresponding Amazon Redshift fields (or vice versa).

Conclusion

Redshift is fantastic, but when selecting a data warehouse, you must sometimes optimize for different factors. Some people prefer RDBMSs that use similar SQL syntaxes, such as Google BigQuery, PostgreSQL, Snowflake, Microsoft Azure SQL Data Warehouse, or Panoply, which works with Redshift instances. Others opt for a data lake, such as Amazon S3 or Databricks’ Delta Lake.

Don’t be alarmed if all of this sounds a little overwhelming. If you have all of the skills required to go through this process, chances are that creating and maintaining a script like this isn’t the best use of your time.

There are various Data Sources that organizations leverage to capture a variety of valuable data points. But, transferring data from these sources into a Data Warehouse for a holistic analysis is a hectic task.

An Automated Data Pipeline helps in solving this issue and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations that you can choose from.

visit our website to explore hevo

Hevo can help you integrate data from 100+ data sources and load them into a destination to analyze real-time data at an affordable price. It will make your life easier and Data Migration hassle-free. It is user-friendly, reliable, and secure.

SIGN UP for a 14-day free trial and see the difference!

Share your experience on Heroku for PostgreSQL to Redshift in the comments section below.

No-code Data Pipeline for Amazon Redshift