PostgreSQL to Amazon Redshift: Migration and data sync guide

PostgreSQL to Amazon Redshift: What this process means

Migration from PostgreSQL to Amazon Redshift does not mean replacing a database with a more suitable database management system. Amazon Redshift is a database, but its structure is fundamentally different from transactional databases. It is based on PostgreSQL and allows querying and manipulating data with SQL, but it cannot replace PostgreSQL, MySQL, Oracle, or another database that powers standard applications.

Data migration from PostgreSQL to Amazon Redshift usually means transferring data from a local PostgreSQL server or a cloud-based platform like Amazon RDS for PostgreSQL, Amazon Aurora PostgreSQL, or another PostgreSQL-compatible database into Amazon Redshift to establish a dedicated analytics platform for warehousing and reporting. Meanwhile, PostgreSQL databases remain active and continue supporting transactional workloads.

PostgreSQL vs Amazon Redshift: Key differences

Although Redshift is based on PostgreSQL, it works differently because it is designed for a different purpose—OLAP, BI, aggregations, large scans, and columnar data storage. It does not have many essential features of PostgreSQL, but it compensates for that with scalability and advanced analytics capabilities for massive data volumes.

The table below summarizes the most essential differences between PostgreSQL and Amazon Redshift.

When should you move data from PostgreSQL to Redshift?

Moving data from PostgreSQL to Amazon Redshift usually takes place when you need analytical workloads that PostgreSQL can no longer support efficiently. Amazon Redshift is optimized for scanning and analysis of massive datasets, while PostgreSQL excels in transactions for business applications. Thus, modern workflows may dictate separating the transactional and analytical workflows.

The following issues are the most common factors causing teams to move data to Redshift.

Negative impact of reporting queries on production performance

Performance degradation is one of the most common reasons to migrate. For instance, complex JOIN operations, aggregations, and sophisticated data reports consume memory and disk resources, and as reporting activities grow, transactional performance suffers. By moving analytics and reporting workflows to Amazon Redshift, organizations can run the related resource-intensive operations without affecting business applications.

Slow dashboards

Every time you refresh the BI dashboards, they have to execute numerous complex queries. As datasets grow, PostgreSQL starts struggling. As a result, the task takes too long, especially when you need to retrieve fresh data from several dashboards simultaneously.

Amazon Redshift, on the other hand, is designed specifically for this type of workload. Its architecture makes it possible to analyze hundreds of millions of rows in a much more efficient manner than PostgreSQL would allow.

Data volume growth

This factor is the key in both use cases described earlier. PostgreSQL can handle large databases efficiently, but in large organizations, eventually, tables become huge, reports take too long to complete, and analytical workloads outgrow the existing capabilities. Besides, when companies integrate professional BI tools like Tableau, Power BI, Looker, or Amazon QuickSight, the number of specific analytical queries increases tremendously.

Therefore, it becomes a logical decision to move those billions of rows to Amazon Redshift, which can handle them in a much more efficient way, and use the BI tools with Redshift.

Use case examples

As the importance of business analytics and reporting grows, so does the workload they place on databases. Let us consider the most common and demanding scenarios where you may face complications running regular analytical operations and therefore seek a more powerful alternative to run such tasks.

Daily operational reporting

Many businesses generate daily recurring reports that summarize activity from the previous day or week, e.g., daily sales reports, order summaries, inventory reports, financial reports, operational KPIs, etc. Instead of calculating these reports directly on PostgreSQL, organizations often choose to transfer them to Amazon Redshift.

Historical trend analysis

Operational databases focus on current business operations, while analysts usually need historical trends over months or years. For instance, examination of sales trends over multiple years, seasonal demand analysis, product performance over time, etc. are necessary to develop reliable financial forecasting. Amazon Redshift is optimized for querying large historical datasets.

Customer behavior analysis

Marketing and product teams need to analyze customer behavior, including their purchase patterns, user engagement, customer segmentation, etc. It requires processing large volumes of data about transactions and customer behavior, including complex JOINs to combine and relate the data portions. A warehouse is a better option than a PostgreSQL database.

Revenue and financial reporting

Finance departments require consolidated reports with the information from several essential sources. As a rule, such reports include revenue breakdowns, profitability analysis, customer lifecycles, cost analysis, and other metrics. To achieve that, large aggregations are necessary, and Amazon Redshift processes such queries more efficiently.

Cross-source analytics

The analysis of data from different systems is a necessity now. An organization usually uses such sources as CRM platforms, marketing platforms, web analytics tools, PostgreSQL applications, and, in addition, raw data stored in CSV files in cloud storage. By loading these datasets into Amazon Redshift, you can obtain a single platform for data analysis and reporting.

Building a centralized data warehouse

With time, organizations mature and need more options for data storage and processing. While PostgreSQL may remain the system to handle transactions, teams need a data warehouse to serve as a central repository for analytical data from all business aspects. Amazon Redshift is one of the most popular and convenient platforms for such scenarios.

When PostgreSQL may be enough

Despite all its advantages, Amazon Redshift is not the universal solution that suits any scenario. It can be suitable and even optimal for large organizations that deal with huge datasets and require powerful tools that process loads of data, analyze it with complex queries, and create detailed and professional reports.

On the other hand, if you don't need to handle billions of rows daily, your datasets are moderate in size, and the query performance is acceptable for your purposes, there is no need to add a commercial Redshift solution. You can achieve all your goals with PostgreSQL.

Main ways to move data from PostgreSQL to Amazon Redshift

To move data from PostgreSQL to Amazon Redshift, you can use several methods. The choice of the right one depends on the data volume to transfer, complexity, and specific requirements for data cleansing and transformation. Let us review the most common ways.

S3 + Redshift COPY

One of the most common and simplest methods is to export data from PostgreSQL into files (e.g., CSV), upload those data files to Amazon S3, and then load them into Amazon Redshift using the COPY command.

This method is suitable for large datasets due to the use of the COPY command (it is optimized for bulk loading). There is no need for inserting rows one by one, as Amazon Redshift can read multiple files in parallel from Amazon S3.

As a rule, this method is applied for batch imports, complex daily imports (including historical data processing), and initial data migrations. It requires exporting data as a separate step, but overall, it is the preferred solution.

AWS Database Migration Service (AWS DMS)

AWS DMS is a web service for migrating data between two data stores (endpoints). It is possible to migrate data using the same database engine or between different engines (e.g., from SQL Server to Oracle). However, to use it, you need to have at least one endpoint on AWS – it can't migrate data between on-premises databases.

AWS DMS is a suitable solution for transferring data from on-premises or cloud PostgreSQL databases to Amazon Redshift. It supports full load to copy the existing data and ongoing replication to also replicate the INSERT, UPDATE, and DELETE actions as soon as they take place. By using this method, you can synchronize Redshift with your PostgreSQL operational database.

Using AWS DMS is helpful for hybrid environments, especially when you deal with near-real-time analytical jobs.

AWS Glue

AWS Glue is a serverless ETL service that helps you prepare, move, and integrate data from over 70 data sources, allowing you to create, run, and monitor ETL pipelines visually. Besides, it simplifies the process of integrating data across the architecture because AWS Glue integrates with Amazon S3 and AWS analytics services.

This method helps you prepare the data for transfer – clean inconsistencies, filter unnecessary records, aggregate and join datasets, standardize data formats, add comments or more information manually, etc. Usually, AWS Glue becomes the chosen option if organizations need more than just copying data to the new target location, like Amazon Redshift, but also require implementing complex business logic and specific data transformations.

Third-party ETL/ELT tools

Many organizations use commercial or free tools to move data from PostgreSQL to Amazon Redshift. These popular tools include Fivetran, Stitch, Hevo, Airbyte, RudderStack, etc.

These tools provide built-in data connectors, allow scheduling data migration tasks, and can even replicate PostgreSQL data into Redshift. The primary advantage of such tools is that they can reduce the amount of manual work, especially manual SQL coding, and replace it with GUI tools which simplify the job significantly.

How to prepare PostgreSQL data before loading it into Redshift

Data migration from PostgreSQL to Amazon Redshift is not just copying tables from a database in PostgreSQL to Redshift. Since Redshift has significant structural differences, PostgreSQL schemas and data types cannot always be transferred directly to it. You need to prepare data appropriately before loading it into Redshift, and this process involves several steps.

Review schema and data types

PostgreSQL and Amazon Redshift have a similar foundation, but they are not fully compatible. Therefore, when planning the migration of data from PostgreSQL to Amazon Redshift, you need to review the database schema and data types used in your PostgreSQL databases. In particular, pay attention to the following aspects:

• Data types: Some PostgreSQL data types are not supported natively by Redshift (arrays, HSTORE, BYTEA, composite types, enumerated types, and geometric types). Depending on the data, you may need to cast these values to VARCHAR, convert them to Redshift-specific types, or redesign the entire data structure before loading it to Redshift.
• Very large text fields: Review columns that contain exceptionally large text values. You may need to reduce their size, split the data across multiple columns or tables, or restructure it to meet the limitations and requirements of Redshift.
• JSON columns: Redshift does not provide a native JSON or JSONB type. However, it has implemented a new SUPER data type for storing and querying semi-structured data. SUPER natively stores nested objects and arrays and can be queried with PartiQL (which supports object and array navigation, array unnesting, and dynamic typing). Therefore, if you need to query or analyze nested JSON data, migrate PostgreSQL JSON/JSONB columns to SUPER. This approach is better than converting those values to VARCHAR. However, note that a SUPER value is limited to 16 MB per row, while individual key-value pairs are limited to 64 KB. If your source JSON documents exceed these limits, you may need to redesign the entire schema.
• Timestamp and date columns: Redshift supports date and timestamp data types natively; the difference is in precision and supported formats. You may need to adjust the data for migration. Review high-precision timestamp values and make sure to convert them correctly.
• Array data types: PostgreSQL arrays are not directly supported as PostgreSQL-style array types in Redshift. If your database relies on arrays, you may need to normalize your data into separate rows or tables, or convert it to an appropriate Redshift-supported type.
• Spatial data types: PostGIS or native geometric types such as POINT, LINE, or POLYGON are not supported by Redshift. You need to convert these data types into Redshift's own GEOMETRY and GEOGRAPHY representations.
• PostgreSQL extensions: Redshift does not support PostgreSQL extensions, so you need to use Redshift’s native options to obtain the required functionality. For instance, if the business logic depends on PostGIS, you should redesign it using spatial capabilities of Redshift.

In addition to schema and data types, review all PostgreSQL-specific functions, operators, and SQL syntax used by the application. Make sure to identify all incompatible elements and rewrite them to use the equivalents supported by Redshift. This step is crucial to ensure the database logic will work correctly after the migration.

Clean and normalize data

Problems and incorrect results in data analytics are often caused by the initial transfer of poor-quality data. You need to review the data you plan to migrate to Amazon Redshift before you start loading it. Check and fix the following aspects:

• Replace unexpected NULLs
• Check and remove duplicate rows
• Standardize inconsistent date and time formats
• Correct invalid numeric values
• Convert the unsupported column data types to those supported by Redshift
• Ensure that the values to migrate match the business rules and ranges

Prepare tables for analytics

Amazon Redshift performs better if your models require fewer JOINs. If you need Redshift's reporting capabilities and plan to use them extensively, you may need to create other types of tables with data, such as those listed below.

• Fact tables that store sales, orders, transactions, and other measurable business items.
• Dimension tables that describe entities such as customers, products, locations, or time periods.
• Reporting tables that combine or summarize data for common business reports.
• Denormalized or partially aggregated tables that simplify complex queries and improve dashboard performance.

Designing such tables specifically for analytics requires extra effort, but it helps Amazon Redshift process large datasets and perform complex analytical and reporting tasks more efficiently.

Use dbForge Studio for PostgreSQL to prepare and validate data

dbForge Studio for PostgreSQL is a multifeatured IDE with AI integration that is designed to perform all database tasks in PostgreSQL, Amazon Redshift, and related cloud platforms. The Studio allows you to connect to Redshift and manage databases and database objects visually, as well as run queries and do other essential tasks faster and more accurately than via manual coding.

It is important to note that dbForge Studio for PostgreSQL is not a universal Redshift-loading tool, and it can't replace AWS DMS, Glue, or specialized third-party ETL solutions. However, it can help you prepare the data for migration and do such tasks as inspect tables, edit data, export it for further use with dedicated tools, compare schemas and data, and create and rewrite the necessary tables and other database objects with the help of AI Assistant.

Step-by-step workflow: PostgreSQL to Amazon Redshift using dbForge Studio

Let us see how to use dbForge Studio for PostgreSQL for migrating data from a PostgreSQL database to Amazon Redshift. In our case, we are going to export selected data to CSV from the PostgreSQL database.

Step 1: Connect to the PostgreSQL database

Open dbForge Studio for PostgreSQL and navigate to Database > New Connection. In the Database Connection Properties window, enter the necessary details.

Click Test Connection to check if everything is correct, and then click Connect.

You will see the list of databases in the Database Explorer pane on the left.

Step 2: Select the data for export

For our test scenario, we use the sakila test database that is available for PostgreSQL. Navigate to the necessary schema, expand the sakila node, then the Tables node, and select the table to export data from. You can view the table data first to make sure that it matches your requirements. Also, you can analyze the data and edit it directly in the table.

Step 3: Export data to CSV

You can use dbForge Studio for PostgreSQL to export data in 14 different formats. As mentioned earlier, it is possible to upload data as CSV files to Amazon S3 and load it then into Amazon Redshift using the standard COPY command. dbForge Studio makes it easy to export data to CSV and automate the process for recurring data updates.

To export data, open the Database menu and select Tasks > Export Data.

The Data Export wizard will open, displaying the list of available export formats. Select CSV and click Next.

On the Source page, choose the connection, database, schema, and the table or view you want to export. You can export data from a single table or multiple tables in one operation.

On the Output Settings page, configure the export options to suit your needs. You can export data to a single file or separate files, specify the destination folder, and enable file compression if required.

We have created the tutorial on exporting PostgreSQL data to CSV, so refer to it for detailed step-by-step instructions on configuring the export process. With the visual data export wizard, the entire process takes just a few clicks.

Once the export is complete, upload the CSV files to Amazon S3 and use the COPY command to load them into Redshift.

Alternatively, you can import data directly into Amazon Redshift tables using the Data Import functionality in dbForge Studio, which fully supports Amazon Redshift. Simply follow the wizard to map the source and destination objects and complete the import. However, this feature is not a replacement for dedicated ETL tools or high-volume data loading solutions.

dbForge Studio for PostgreSQL

A feature-rich and versatile IDE for database development and management, as well as data management, analysis, and reporting

Download for free

Full load, incremental sync, or CDC: Which approach to choose?

The appropriate method depends primarily on how frequently the data must be updated. To ensure frequent updates and real-time data for analytics, you need to synchronize it between Amazon Redshift and PostgreSQL.

Full load

A full load copies the entire PostgreSQL dataset into Amazon Redshift in a single operation. It is the simplest and most commonly used method in the following cases:

• Initial migrations
• Loading historical data
• Working with small or static datasets

However, the full load method usually means that you won't update data frequently. Therefore, if you need to query actual data in real time, full load is not the best option.

Incremental sync

Incremental synchronization is the method where you transfer only the changes, such as newly added records or records modified since the previous load.

The best-fit scenarios for incremental synchronization are as follows.

• Regular data refreshes
• Loading data from large databases in parts if the full-load method is too slow or expensive
• Reporting and analytical environments that require up-to-date data and continuous replication

Change Data Capture (CDC)

Change Data Capture (CDC) is the method that captures database changes continuously and replicates them all, including inserts, updates, and deletes, with minimal delay. AWS DMS, which was mentioned earlier in this article, is a popular solution for using CDC when migrating data from PostgreSQL to Redshift.

Common use cases include:

• Near-real-time analytics
• Dashboards with immediate reflection of operational data
• Data pipelines for synchronization between PostgreSQL and Redshift

CDC provides the freshest data, but it is also the most sophisticated approach that requires additional expertise and effort to implement and maintain it.

Conclusion

Migrating a database or its data to a new platform is not a simple job. It requires planning, preparation, and careful attention at every stage of this process. This article explores the available migration methods, explains the preparation steps required for a successful migration, and highlights common challenges to help you transfer your data efficiently and get the most out of Amazon Redshift.

A tool that can simplify both the migration process and your day-to-day work with Amazon Redshift is dbForge Studio for PostgreSQL. It helps you prepare your PostgreSQL database for migration, validate the migrated data, and perform database management tasks in Amazon Redshift.

You can download a fully functional free trial of dbForge Studio for PostgreSQL to explore its features and see how it fits into your migration and database management workflows.

FAQ