How to Migrate from MariaDB to Redshift in PostgreSQL

What is the fastest way to move MariaDB tables to Redshift?

Export each MariaDB table to CSV, upload the files to Amazon S3, then run Redshift’s COPY command. The process handles large datasets efficiently and preserves schema fidelity.

Which tools help automate the migration?

AWS Schema Conversion Tool (SCT) converts DDL, and AWS Database Migration Service (DMS) keeps MariaDB and Redshift in sync until cut-over. Use them when you need minimal downtime.

How do I export data from MariaDB?

Run mysqldump --tab or SELECT ... INTO OUTFILE per table. Compress outputs to reduce transfer time. Example:

mysqldump -u admin -p --tab=/tmp/ecom_dump \
--fields-terminated-by="," --fields-enclosed-by='"' \
--lines-terminated-by="\n" ecommerce Orders Products Customers OrderItems


How do I stage files in S3?

Use the AWS CLI. Keep each table in its own prefix for easier COPY commands:

aws s3 cp /tmp/ecom_dump s3://ecom-stage/ --recursive --exclude "*sql"

How do I create matching tables in Redshift?

Convert every MariaDB table to Redshift DDL. Example for Orders:

CREATE TABLE public.orders (
id INT IDENTITY(1,1) PRIMARY KEY,
customer_id INT NOT NULL,
order_date TIMESTAMP,
total_amount NUMERIC(12,2)
);


How do I load data into Redshift quickly?

Run one COPY per table. Use the DATEFORMAT and TIMEFORMAT options to match your CSV files.

COPY public.orders
FROM 's3://ecom-stage/Orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS CSV
IGNOREHEADER 1
TIMEFORMAT 'auto'
COMPUPDATE OFF;


How do I validate the migration?

Row-count checks catch most issues. Example query:

-- Redshift
SELECT COUNT(*) AS redshift_rows FROM public.orders;
-- MariaDB
SELECT COUNT(*) AS mariadb_rows FROM Orders;


Can I keep both databases in sync until cut-over?

Yes. Configure AWS DMS in "full load + ongoing replication" mode. Choose CDC based on MariaDB binary logs to stream inserts, updates, and deletes.

What are best practices for large ecommerce datasets?

Compress CSV files with GZIP, split into 1–4 GB chunks, match Redshift distribution/sort keys to query patterns, and disable constraints until all data loads.