Warehouses and Lakes

How to load data from PostgreSQL to Redshift destination

Learn how to use Airbyte to synchronize your PostgreSQL data into Redshift destination within minutes.


This can be done by building a data pipeline manually, usually a Python script (you can leverage a tool as Apache Airflow for this). This process can take more than a full week of development. Or it can be done in minutes on Airbyte in three easy steps:

  1. set up PostgreSQL as a source connector (using Auth, or usually an API key)
  2. set up Redshift destination as a destination connector
  3. define which data you want to transfer and how frequently

You can choose to self-host the pipeline using Airbyte Open Source or have it managed for you with Airbyte Cloud.

This tutorial’s purpose is to show you how.

What is PostgreSQL

An object-relational database management system, PostgreSQL is able to handle a wide range of workloads, supports multiple standards, and is cross-platform, running on numerous operating systems including Microsoft Windows, Solaris, Linux, and FreeBSD. It is highly extensible, and supports more than 12 procedural languages, Spatial data support, Gin and GIST Indexes, and more. Many webs, mobile, and analytics applications use PostgreSQL as the primary data warehouse or data store.

What is Redshift destination

A fully managed data warehouse service in the Amazon Web Services (AWS) cloud, Amazon Redshift is designed for storage and analysis of large-scale datasets. Redshift allows businesses to scale from a few hundred gigabytes to more than a petabyte (a million gigabytes), and utilizes ML techniques to analyze queries, offering businesses new insights from their data. Users can query and combine exabytes of data using standard SQL, and easily save their query results to their S3 data lake.


  1. A PostgreSQL account to transfer your customer data automatically from.
  2. A Redshift destination account.
  3. An active Airbyte Cloud account, or you can also choose to use Airbyte Open Source locally. You can follow the instructions to set up Airbyte on your system using docker-compose.

Airbyte is an open-source data integration platform that consolidates and streamlines the process of extracting and loading data from multiple data sources to data warehouses. It offers pre-built connectors, including PostgreSQL and Redshift destination, for seamless data migration.

When using Airbyte to move data from PostgreSQL to Redshift destination, it extracts data from PostgreSQL using the source connector, converts it into a format Redshift destination can ingest using the provided schema, and then loads it into Redshift destination via the destination connector. This allows businesses to leverage their PostgreSQL data for advanced analytics and insights within Redshift destination, simplifying the ETL process and saving significant time and resources.

Step 1: Set up PostgreSQL as a source connector

1. Open your PostgreSQL database and create a new user with the necessary permissions to access the data you want to replicate.

2. Obtain the hostname or IP address of your PostgreSQL server and the port number it is listening on.

3. Create a new database in PostgreSQL that will be used to store the replicated data.

4. Obtain the name of the database you just created.

5. In Airbyte, navigate to the PostgreSQL source connector and click on "Create Connection".

6. Enter a name for your connection and fill in the required fields, including the hostname or IP address, port number, database name, username, and password.

7. Test the connection to ensure that Airbyte can successfully connect to your PostgreSQL database.

8. Select the tables or views you want to replicate and configure any necessary settings, such as the replication frequency and the replication method.

9. Save your configuration and start the replication process.

10. Monitor the replication process to ensure that it is running smoothly and troubleshoot any issues that arise.

Step 2: Set up Redshift destination as a destination connector

1. First, log in to your Airbyte account and navigate to the "Destinations" tab on the left-hand side of the screen.
2. Click on the "Add Destination" button and select "Redshift" from the list of available connectors.
3. Enter your Redshift database credentials, including the host, port, database name, username, and password.
4. Choose the schema you want to use for your data in Redshift.
5. Select the tables you want to sync from your source connector to Redshift.
6. Map the fields from your source connector to the corresponding fields in Redshift.
7. Choose the sync mode you want to use, either "append" or "replace."
8. Set up any additional options or filters you want to use for your sync.
9. Test your connection to ensure that your data is syncing correctly.
10. Once you are satisfied with your settings, save your configuration and start your sync.

Step 3: Set up a connection to sync your PostgreSQL data to Redshift destination

Once you've successfully connected PostgreSQL as a data source and Redshift destination as a destination in Airbyte, you can set up a data pipeline between them with the following steps:

  1. Create a new connection: On the Airbyte dashboard, navigate to the 'Connections' tab and click the '+ New Connection' button.
  2. Choose your source: Select PostgreSQL from the dropdown list of your configured sources.
  3. Select your destination: Choose Redshift destination from the dropdown list of your configured destinations.
  4. Configure your sync: Define the frequency of your data syncs based on your business needs. Airbyte allows both manual and automatic scheduling for your data refreshes.
  5. Select the data to sync: Choose the specific PostgreSQL objects you want to import data from towards Redshift destination. You can sync all data or select specific tables and fields.
  6. Select the sync mode for your streams: Choose between full refreshes or incremental syncs (with deduplication if you want), and this for all streams or at the stream level. Incremental is only available for streams that have a primary cursor.
  7. Test your connection: Click the 'Test Connection' button to make sure that your setup works. If the connection test is successful, save your configuration.
  8. Start the sync: If the test passes, click 'Set Up Connection'. Airbyte will start moving data from PostgreSQL to Redshift destination according to your settings.

Remember, Airbyte keeps your data in sync at the frequency you determine, ensuring your Redshift destination data warehouse is always up-to-date with your PostgreSQL data.

Use Cases to transfer your PostgreSQL data to Redshift destination

Integrating data from PostgreSQL to Redshift destination provides several benefits. Here are a few use cases:

  1. Advanced Analytics: Redshift destination’s powerful data processing capabilities enable you to perform complex queries and data analysis on your PostgreSQL data, extracting insights that wouldn't be possible within PostgreSQL alone.
  2. Data Consolidation: If you're using multiple other sources along with PostgreSQL, syncing to Redshift destination allows you to centralize your data for a holistic view of your operations, and to set up a change data capture process so you never have any discrepancies in your data again.
  3. Historical Data Analysis: PostgreSQL has limits on historical data. Syncing data to Redshift destination allows for long-term data retention and analysis of historical trends over time.
  4. Data Security and Compliance: Redshift destination provides robust data security features. Syncing PostgreSQL data to Redshift destination ensures your data is secured and allows for advanced data governance and compliance management.
  5. Scalability: Redshift destination can handle large volumes of data without affecting performance, providing an ideal solution for growing businesses with expanding PostgreSQL data.
  6. Data Science and Machine Learning: By having PostgreSQL data in Redshift destination, you can apply machine learning models to your data for predictive analytics, customer segmentation, and more.
  7. Reporting and Visualization: While PostgreSQL provides reporting tools, data visualization tools like Tableau, PowerBI, Looker (Google Data Studio) can connect to Redshift destination, providing more advanced business intelligence options. If you have a PostgreSQL table that needs to be converted to a Redshift destination table, Airbyte can do that automatically.

Wrapping Up

To summarize, this tutorial has shown you how to:

  1. Configure a PostgreSQL account as an Airbyte data source connector.
  2. Configure Redshift destination as a data destination connector.
  3. Create an Airbyte data pipeline that will automatically be moving data directly from PostgreSQL to Redshift destination after you set a schedule

With Airbyte, creating data pipelines take minutes, and the data integration possibilities are endless. Airbyte supports the largest catalog of API tools, databases, and files, among other sources. Airbyte's connectors are open-source, so you can add any custom objects to the connector, or even build a new connector from scratch without any local dev environment or any data engineer within 10 minutes with the no-code connector builder.

We look forward to seeing you make use of it! We invite you to join the conversation on our community Slack Channel, or sign up for our newsletter. You should also check out other Airbyte tutorials, and Airbyte’s content hub!

Data engineers are tasked to move data between storage systems like applications, databases, data warehouses, and data lakes. One of the most popular data replication use cases includes moving data from a relational database to a data warehouse to enable analytics. This article shows how to build an ELT pipeline to replicate data from Postgres to Redshift using Airbyte, an open-source data integration platform.

Postgres and Redshift store data differently. To replicate data between the two systems, you will need at least to extract the Postgres data, transform it to a format that is compatible with Redshift and load it to Redshift.

Airbyte does not only provide connectors for various sources and destinations (including Postgres and Redshift), but it is also an ELT framework with monitoring, scheduling, logging, and support for multiple data replication modes.

Why replicate data from Postgres to Redshift

One question that might come to your mind is, “Why to move data from Postgres to Redshift?”. To answer this question, it’s important to understand the differences between Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP) databases.

OLTP databases capture and store transactional-oriented data in real-time. It is mostly used for the effective running of the day-to-day activities of an organization. When OLTP databases are used for complex analysis, it slows down the website's operations. Examples of OLTP databases are Postgres, MySQL, and MySQL.

OLAP databases are mostly used by data scientists, analysts, or business intelligence experts for analysis, research, insights generation, and reporting. These types of databases allow fast execution of complex analytical queries. These types of databases are commonly referred to as data warehouses and can store large volumes of data and run complex queries on them to generate insights. OLAP data warehouses are AWS Redshift, Google BigQuery, Azure Synapse, and Snowflake.

This tutorial aims to show how to replicate data from a transactional database (Postgres) to an analytical data warehouse (Redshift) in a fast and effective way, using Airbyte.


  1. You will need to deploy Airbyte. Airbyte Cloud comes with free credits to get started. You can sign up for Airbyte Cloud or deploy Airbyte Open Source.
  2. You will need an AWS account.
  3. You will need an instance of the Postgres database, which you can remotely connect to. One of the very essential data engineering skills you will learn in this article is also how to host your local Postgres database on an Amazon RDS database instance.
  4. You will need an instance of the Amazon Redshift cluster that you can connect to remotely.

Set up an Amazon RDS database

To set up a Postgres database as an Airbyte source, you must host your local Postgres database in the cloud. To do this, there are many options. You can use Heroku, ElephantSQL, HelioHost, Amazon RDS, CloudSQL, and many other database cloud hosting platforms or run your Postgres database on a machine you will secure. For this tutorial, you will use the Amazon RDS instance to host your Postgres database.

Before creating an Amazon RDS instance, the first thing you need to do is to create a security group. This is important because it will allow Airbyte’s Cloud IP and other IP addresses you specify, to connect to your Amazon RDS database.

To do this, you go to EC2 >> Security Groups >> Create Security Group. In the Inbound Rules section, you will allow two IP addresses from your Postgres instance.

In the above image, the security group was given the tag name of Airbyte-Rule. This tag name will come in handy when you want to allocate your Amazon RDS database instance to the security group. The first inbound rule allows Airbyte Cloud IP ( at the time of writing) to connect to the remote Amazon RDS instance. The second inbound rule allows your local IP address to connect to the Amazon RDS instance.

The next thing you will do is create an Amazon RDS database instance. To do this, search your Amazon console for Amazon RDS.

You then go to Databases >> Create database.

After clicking on create database, you configure your database. You choose the standard to create method and the Postgres engine option.

You will also have to allocate the security group you just created to the Amazon RDS instance. To do this, you go to Connectivity >> Security group. You will then attach the Airbyte-Rule security group.

It is important to grant your database public access while creating it. This will enable your local Postgres database to communicate with the remote Postgres database hosted on AWS.

After granting the database public access, create the database. You may have to wait for a while because it takes some time to create the database and allocate compute resources. Once the database becomes available, you will be able to see endpoints you can use to connect to your remote Postgres database.

Create a Postgres table and populate the table

In the previous section, you created an Amazon RDS instance which will enable you to run a Postgres database on the cloud. In this section, you will connect to the Amazon RDS instance you just created. You will also create a table in your Postgres database and populate the table with data.

Locally you can install pgAdmin to connect to the Amazon RDS instance with the endpoints from the remote database instance.

In the connection tab, you specify your hostname, port number, username, database name, and password. All these parameters were configured by you when creating the Amazon RDS database instance. Once saved, you have successfully connected to your remote instance.

The dataset used in this tutorial can be downloaded here. The dataset contains the details of all consumer complaints from a store. The image below shows the SQL query for creating a schema and a table. This table will be populated with data from the CSV file in the link above.

create schema rds_schema;
create table rds_schema.complaints("Date Received" VARCHAR(50),
   "Product Name" VARCHAR(100),
   "Sub Product" VARCHAR(100),
   "Issue" VARCHAR(100),
   "Sub Issue" VARCHAR(100),
   "Consumer Complaint Narrative" TEXT,
   "Company Public Response" VARCHAR(200),
   "Company" VARCHAR(100),
   "State Name" VARCHAR(100),
   "Zip Code" VARCHAR(100),
   "TAGS" VARCHAR(50),
   "Consumer Consent Provided" VARCHAR(50),
   "Submitted via" VARCHAR(50),
   "Date Sent to Company" VARCHAR(50),
   "Company Response to Consumer" VARCHAR(100),
   "Timely Response" VARCHAR(100),
   "Consumer Disputed" VARCHAR(50),
   "Complaint ID" INT);

In the code block above, you created a schema called rds_schema and a table called complaints. This table will be populated with the records from the CSV file. To populate the table with the CSV file, you will head over to psql terminal and run the command:

\copy rds_schema.complaints FROM '{path_to_the_csv_file}' with (format csv, header true, delimiter ',');

After executing this code block, the data will be successfully loaded into the Postgres database.  You can preview the loaded data by executing the code:

select * from rds_schema.complaints limit 10:

You can also get information on the number of rows in the table.

In the image above, notice that the table contains about 65,499 records.

Set up a Postgres source

To create the Postgres source, head over to Airbyte Cloud and create a new source. Enter the source name, host address, database name, the schema you created (rds_schema), username and password. All these details were used when creating your Amazon RDS instance.

If you use the default SSL mode of disable, your test might fail. You will get an error message of Unsecured connection. To solve this error, you will have to set SSL Modes to require. SSL Modes are ways of ensuring a secure browser connection, and Amazon RDS supports SSL encryption by default. All data connections in Airbyte Cloud are encrypted.

Set up a Redshift destination

To set up a Redshift destination, you need a Redshift cluster. You can create a Redshift cluster by following the steps in the link here. Once your cluster is created, head over to Properties >> Network and security settings tab.

In the VPC security group section, you will see the security group of your Redshift cluster. You want to allow Airbyte Cloud's remote IP address to connect to the Redshift cluster. To do this, modify the security group and allow inbound connections from Airbyte.

Another thing you would have to do is to enable public accessibility from Redshift. This will allow Airbyte Cloud to connect to your Amazon Redshift cluster easily. To enable public accessibility, go to Redshift cluster >> Actions >> Modify public accessible setting.

If you go to your Redshift cluster information, you will see some connection endpoints. These endpoints are used to connect Airbyte Cloud to the Amazon Redshift cluster.

To set up a Redshift destination, go to Airbyte Cloud >> Destinations. Enter your Amazon Redshift hostname. Note that your hostname is your endpoint listed at the top of the page. Note that your endpoint URL must end with amazon.com.

The remaining parameters such as database, username, and password were set up during your Amazon Redshift cluster configuration. To check your database configurations, go to the Properties tab in Redshift.

Redshift, by default, comes with a database called dev and an admin user called awsuser. You can configure this while setting up the cluster. For simplicity's sake, leave the settings as default.

Now, to set up an Amazon Redshift destination, go to Airbyte Cloud and select Redshift as your destination. You also have to enter your hostname, database name, username, and password.

Set up a Postgres to Redshift connection

Once the source and destination settings have been configured, the next thing to do is to go to Airbyte Cloud and set up a connection.

Once you set up your source and destination, you will automatically see the schema created and the complaints table. The Full Refresh sync mode is the easiest to get started. There are four types of sync modes in Airbyte.

  • Full refresh - append: Full refresh receives all the available data from the source, regardless of whether it has been previously synced. The full refresh - append takes all the available data from a particular sync and appends it to the previously synced data. This makes each sync create duplicated records of the already existing data.
  • Full refresh - overwrite: This is another type of sync in Airbyte. In full refresh - overwrite mode, new syncs will overwrite all the data in the destination table and push the new data.
  • Incremental sync - append:. In incremental sync, only new or modified data will be synced to its destination. In incremental append mode, refetching data you have already synced is not allowed. This prevents duplicated data, as in the case of full refresh append. Here, each update creates a new row with the updated data.
  • Incremental sync - deduped history: This is similar to incremental sync append. The only difference is that here, each change updates the rows that have been modified instead of creating a new row containing the modified data.

Run the sync

After setting up a connection, the next thing you have to do is to run the sync.

From the image above, the sync was successful, and exactly 65,499 records were synced from Postgres to Redshift. The time it took for about sixty-five thousand records was about three minutes. Now let us check the Redshift query editor to see if the tables were fully loaded.

Verify the results

In your Redshift query editor, let’s preview the data and display the first five records.

When you run the query, the first five records of the source data in Postgres get displayed in Amazon Redshift.

Logically, if the process works correctly,  the total number of records in your source database is meant to be the total number of records in your target database. Let us run some validity checks to see if this process works. Recall, the total number of records from your Postgres database was 65,499. Let us confirm if that is the total number of records in your Amazon Redshift data warehouse.

From this, you can validate that all the data was synced from your source database to your destination data warehouse.


So far, you have seen how easy it is to set up Airbyte and use it for your ETL tasks. All it takes is just for you to set up a source system and a destination system. After setting this up, you can grab a coffee and relax, and let Airbyte do the rest for you. In this article, you learned how to replicate data from Postgres to Amazon Redshift by:

  1. Configuring a Postgres source
  2. Configuring a Redshift destination
  3. Setting up a connection
  4. Running the sync

Speed and precision are vital for your business to run smoothly and effectively. Data replication should not be a drawback to your organization. As you have seen, it’s easy to get started with Airbyte Cloud to replicate your Postgres database to Redshift for analytics. Join the conversation at Airbyte’s Community Slack to share your experience and challenges with other data engineers.

Frequently Asked Questions

What data can you extract from PostgreSQL?

PostgreSQL gives access to a wide range of data types, including:  

1. Numeric data types: This includes integers, floating-point numbers, and decimal numbers.  

2. Character data types: This includes strings, text, and character arrays.  

3. Date and time data types: This includes dates, times, and timestamps.  

4. Boolean data types: This includes true/false values.  

5. Network address data types: This includes IP addresses and MAC addresses.  

6. Geometric data types: This includes points, lines, and polygons.  

7. Array data types: This includes arrays of any of the above data types.  

8. JSON and JSONB data types: This includes JSON objects and arrays.  

9. XML data types: This includes XML documents.  

10. Composite data types: This includes user-defined data types that can contain multiple fields of different data types.  

Overall, PostgreSQL's API provides access to a wide range of data types, making it a versatile and powerful tool for data management and analysis.

What data can you transfer to Redshift destination?

You can transfer a wide variety of data to Redshift destination. This usually includes structured, semi-structured, and unstructured data like transaction records, log files, JSON data, CSV files, and more, allowing robust, scalable data integration and analysis.

What are top ETL tools to transfer data from PostgreSQL to Redshift destination?

The most prominent ETL tools to transfer data from PostgreSQL to Redshift destination include:

  • Airbyte
  • Fivetran
  • Stitch
  • Matillion
  • Talend Data Integration

These tools help in extracting data from PostgreSQL and various sources (APIs, databases, and more), transforming it efficiently, and loading it into Redshift destination and other databases, data warehouses and data lakes, enhancing data management capabilities.