如何从Aurora Postgres导出结果集到AWS S3？附迁移疑问

Great question! Let’s cut to the chase first: Aurora Postgres does NOT support the SELECT ... INTO OUTFILE S3 command that’s available for Aurora MySQL—that’s a MySQL-specific feature tied to its native integration with S3. But don’t worry, there are clean, efficient alternatives that fit perfectly with your Flask/Celery/Kubernetes stack.

Recommended Alternatives for Your Workflow #

Since you’re working with a Python-based app and Kubernetes, here are the best approaches to skip intermediate volumes and export directly to S3:

1. Use Postgres COPY with Python (psycopg2 + boto3) #

This is the most flexible option for your stack. You can stream query results directly from Aurora Postgres to memory, then upload to S3 without writing to any disk volume. Perfect for Kubernetes, where avoiding persistent storage simplifies deployment.

Here’s a sample Celery task snippet:

import psycopg2
import boto3
from io import StringIO
from celery import Celery

app = Celery('export_tasks', broker='your_broker_url')

@app.task
def aurora_to_s3_to_redshift():
    # Connect to Aurora Postgres
    conn = psycopg2.connect(
        dbname="your_db",
        user="your_user",
        host="your_aurora_endpoint",
        password="your_password"
    )
    cur = conn.cursor()

    # Stream query results to an in-memory CSV
    csv_buffer = StringIO()
    # Customize your SELECT query and CSV options as needed
    cur.copy_expert(
        "COPY (SELECT id, name, created_at FROM your_table) TO STDOUT WITH (FORMAT CSV, HEADER, DELIMITER ',')",
        csv_buffer
    )
    csv_buffer.seek(0)  # Reset buffer position to start

    # Upload to S3
    s3_client = boto3.client('s3')
    s3_client.put_object(
        Bucket="your-s3-bucket",
        Key="exports/your_data.csv",
        Body=csv_buffer.getvalue()
    )

    # Add Redshift import logic here (e.g., run COPY FROM S3 via psycopg2 to Redshift)
    redshift_conn = psycopg2.connect("your_redshift_conn_string")
    redshift_cur = redshift_conn.cursor()
    redshift_cur.execute("""
        COPY your_redshift_table
        FROM 's3://your-s3-bucket/exports/your_data.csv'
        IAM_ROLE 'your_redshift_iam_role_arn'
        FORMAT CSV HEADER;
    """)
    redshift_conn.commit()

    # Cleanup connections
    cur.close()
    conn.close()
    redshift_cur.close()
    redshift_conn.close()

For large datasets, split your export into batches (using LIMIT/OFFSET or date ranges) to avoid memory pressure in your Celery worker pods.

2. Use AWS Glue (Managed ETL) #

If you prefer to offload the heavy lifting, AWS Glue can handle the entire pipeline:

• Configure a Glue crawler to discover your Aurora Postgres schema
• Create a Glue job that extracts data from Aurora, transforms it (if needed), and loads it directly to S3
• Add a second step to load the S3 data into Redshift

This is great for recurring, scheduled exports—no need to maintain custom code in your Flask/Celery app.

3. pg_dump + AWS CLI (Simpler for One-Time/Ad Hoc Exports) #

For quick, one-off exports, you can run pg_dump in your Kubernetes pod and pipe the output directly to the AWS CLI to upload to S3:

pg_dump -h your_aurora_endpoint -U your_user -d your_db --table=your_table --format=csv --header | aws s3 cp - s3://your-s3-bucket/exports/your_data.csv

You can wrap this in a Celery task using subprocess if you need to trigger it programmatically. Just ensure your K8s pod has the AWS CLI installed and IAM permissions to access S3.

Key Takeaway #

While Aurora Postgres lacks the MySQL-style OUTFILE S3 command, you have plenty of options to keep your pipeline efficient and volume-free. For your Flask/Celery/K8s setup, the COPY + Python approach is the most integrated and maintainable choice.

内容的提问来源于stack exchange，提问作者Underoos