How to Backup Redshift in PostgreSQL

Galaxy Glossary

How do I back up an Amazon Redshift cluster or individual tables?

“Backup Redshift” creates a point-in-time snapshot or exports tables to S3 so data can be restored or re-created later.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why back up an Amazon Redshift cluster?

Backups let you recover from accidental deletes, failed deployments, or region-wide outages. Manual snapshots and UNLOAD exports provide fine-grained restore points beyond Redshift’s automatic retention.

What SQL creates a manual snapshot?

Run CREATE SNAPSHOT in any database. The command stores a cluster-wide backup in Amazon S3 without interrupting queries.

How do I copy data for table-level backups?

UNLOAD exports query results to compressed files in an S3 bucket you own. Grant iam_role or aws_access_key_id/secret_access_key before running.

Step-by-step UNLOAD example

1.Choose bucket s3://acme-backups/redshift/.
2. Add server-side encryption if needed.
3. Partition files with PARALLEL ON to speed export on large tables.

How do I restore from a snapshot?

Use the AWS Console or aws redshift restore-from-cluster-snapshot CLI to spin up a new cluster.Point apps to the restored endpoint once data validates.

Best practices for Redshift backups

• Schedule nightly CREATE SNAPSHOT via EventBridge.
• Export critical fact tables weekly with UNLOAD.
• Tag snapshots by environment for lifecycle policies.
• Test restores quarterly.

Common mistakes and solutions

Ignoring IAM roles: Always attach an IAM_ROLE with S3 write access to avoid AccessDenied errors.
Exporting without compression: Add GZIP to cut storage costs and speed transfers.

Related questions

• Can I automate snapshot deletion?
• How long do automatic snapshots last?
• Does UNLOAD impact cluster performance?

.

Why How to Backup Redshift in PostgreSQL is important

How to Backup Redshift in PostgreSQL Example Usage


-- Back up all May orders with UNLOAD
UNLOAD (
  'SELECT o.*, p.name, p.price, oi.quantity
   FROM Orders o
   JOIN OrderItems oi ON o.id = oi.order_id
   JOIN Products p   ON p.id = oi.product_id
   WHERE order_date BETWEEN ''2023-05-01'' AND ''2023-05-31'''
) TO 's3://acme-backups/redshift/may_orders_'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Access'
GZIP PARALLEL ON;

How to Backup Redshift in PostgreSQL Syntax


-- Cluster-wide snapshot
CREATE SNAPSHOT my_may_01_snapshot;

-- List snapshots
SELECT snapshotid, createdate, status
FROM stl_snapshots;

-- Restore snapshot to new cluster (CLI)
aws redshift restore-from-cluster-snapshot \
  --cluster-identifier restored-prod \
  --snapshot-identifier my_may_01_snapshot

-- Table-level backup with UNLOAD
UNLOAD ('SELECT * FROM Customers')
TO 's3://acme-backups/redshift/customers_'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Access'
GZIP PARALLEL ON ALLOWOVERWRITE;

Common Mistakes

Relying solely on automatic snapshots: These expire after 35 days. Schedule manual snapshots for long-term retention or compliance.
Exporting data without an IAM role: Redshift needs explicit S3 write permissions; attach <code>IAM_ROLE</code> or pass keys to avoid failures.

Frequently Asked Questions (FAQs)

How long does a manual snapshot take?

Most snapshots finish in minutes because Redshift copies only changed blocks (block-level incremental). Very large clusters may take longer but remain online.

Does UNLOAD lock tables?

No. UNLOAD is fully concurrent; queries and writes continue, though heavy exports can consume I/O. Use off-peak windows for multi-terabyte tables.

Can I encrypt exports?

Yes. Add ENCRYPTED or specify KMS_KEY_ID in the UNLOAD command, or rely on default S3 SSE-S3 encryption.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie