AWS Redshift同集群内数据库完整复制方案咨询

Redshift doesn't offer a native COPY DATABASE command, but you can automate the process using system tables to generate DDL and data copy statements. Below is a step-by-step guide with scripts to replicate all objects (tables, views, functions) and permissions from my_prod_db to my_dev_db.

Prerequisites #

• Superuser or equivalent permissions to create databases, roles, and modify cluster objects.
• Connect to a neutral database (e.g., the default dev database) for cluster-level operations like creating the target database.

Step 1: Create the Target Database #

First, create the empty development database:

CREATE DATABASE my_dev_db;

Step 2: Replicate Schema Objects (Tables, Views, Functions) #

Connect to my_prod_db to generate DDL statements, then execute them in my_dev_db.

Tables #

Generate CREATE TABLE statements (includes distkey, sortkey, constraints):

SELECT 'CREATE TABLE ' || schemaname || '.' || tablename || ' ' || pg_get_tabledef(schemaname || '.' || tablename) || ';'
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND tablename NOT LIKE 'pg_%';

Copy the output and run these commands in my_dev_db.

Views #

Generate CREATE VIEW statements:

SELECT 'CREATE VIEW ' || schemaname || '.' || viewname || ' AS ' || pg_get_viewdef(schemaname || '.' || viewname) || ';'
FROM pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

Execute the output in my_dev_db.

Functions & Stored Procedures #

Generate CREATE FUNCTION/CREATE PROCEDURE statements:

SELECT pg_get_functiondef(oid) || ';'
FROM pg_proc
WHERE pronamespace NOT IN (SELECT oid FROM pg_namespace WHERE nspname IN ('pg_catalog', 'information_schema'));

Run the resulting commands in my_dev_db.

Step 3: Copy Data to Target Tables #

Choose one of these methods based on table size:

Option 1: INSERT INTO (Small to Medium Tables) #

Generate INSERT statements from my_prod_db:

SELECT 'INSERT INTO my_dev_db.' || schemaname || '.' || tablename || ' SELECT * FROM my_prod_db.' || schemaname || '.' || tablename || ';'
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND tablename NOT LIKE 'pg_%';

Execute these in my_dev_db.

Option 2: UNLOAD + COPY (Large Tables, Better Performance) #

For large datasets, use Redshift's parallelism with S3:

1. UNLOAD from production:

   UNLOAD ('SELECT * FROM schema.large_table')
   TO 's3://your-bucket/path/large_table_'
   IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Role'
   FORMAT PARQUET;
   

2. COPY to development:

   COPY schema.large_table
   FROM 's3://your-bucket/path/large_table_'
   IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Role'
   FORMAT PARQUET;
   

Step 4: Replicate Roles & Permissions #

Roles are cluster-wide; recreate them if they don't exist, then grant permissions on the new database objects.

Create Roles (If Missing) #

Connect to any database and run:

SELECT 'CREATE ROLE ' || rolname || ' WITH ' || 
       CASE WHEN rolsuper THEN 'SUPERUSER ' ELSE '' END ||
       CASE WHEN rolcreaterole THEN 'CREATEROLE ' ELSE '' END ||
       CASE WHEN rolcreatedb THEN 'CREATEDB ' ELSE '' END ||
       CASE WHEN rolcanlogin THEN 'LOGIN ' ELSE '' END ||
       'PASSWORD ''' || rolpassword || ''';'
FROM pg_roles
WHERE rolname NOT IN ('rdsdb', 'public')
  AND rolname NOT LIKE 'pg_%';

Grant Permissions #

Connect to my_dev_db and generate grant statements:

• Schema permissions:

  SELECT 'GRANT ' || privilege_type || ' ON SCHEMA ' || schemaname || ' TO ' || grantee || ';'
  FROM information_schema.schema_privileges
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
  

• Table permissions:

  SELECT 'GRANT ' || privilege_type || ' ON TABLE ' || schemaname || '.' || tablename || ' TO ' || grantee || ';'
  FROM information_schema.table_privileges
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
  

• Function permissions:

  SELECT 'GRANT EXECUTE ON FUNCTION ' || schemaname || '.' || proname || '(' || pg_get_function_arguments(oid) || ') TO ' || grantee || ';'
  FROM pg_proc
  JOIN information_schema.routine_privileges ON pg_proc.proname = routine_privileges.routine_name
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
  

Automation Script Example #

Wrap all steps into a bash script using psql for full automation:

#!/bin/bash

# Configuration
REDSHIFT_HOST="your-cluster.redshift.amazonaws.com"
REDSHIFT_PORT="5439"
REDSHIFT_USER="superuser"
REDSHIFT_PASSWORD=$(aws secretsmanager get-secret-value --secret-id redshift-superuser --query SecretString --output text | jq -r '.password')
PROD_DB="my_prod_db"
DEV_DB="my_dev_db"

# Step 1: Create target database
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d dev -c "CREATE DATABASE $DEV_DB;"

# Step 2: Generate and execute table DDL
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d $PROD_DB -t -c "SELECT 'CREATE TABLE ' || schemaname || '.' || tablename || ' ' || pg_get_tabledef(schemaname || '.' || tablename) || ';' FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename NOT LIKE 'pg_%';" > table_ddl.sql
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d $DEV_DB -f table_ddl.sql

# Step 3: Generate and execute view DDL
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d $PROD_DB -t -c "SELECT 'CREATE VIEW ' || schemaname || '.' || viewname || ' AS ' || pg_get_viewdef(schemaname || '.' || viewname) || ';' FROM pg_views WHERE schemaname NOT IN ('pg_catalog', 'information_schema');" > view_ddl.sql
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d $DEV_DB -f view_ddl.sql

# Step 4: Copy data (use UNLOAD/COPY for large tables)
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d $PROD_DB -t -c "SELECT 'INSERT INTO $DEV_DB.' || schemaname || '.' || tablename || ' SELECT * FROM $PROD_DB.' || schemaname || '.' || tablename || ';' FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND tablename NOT LIKE 'pg_%';" > data_copy.sql
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d $DEV_DB -f data_copy.sql

# Step 5: Replicate permissions
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d $DEV_DB -t -c "SELECT 'GRANT ' || privilege_type || ' ON TABLE ' || schemaname || '.' || tablename || ' TO ' || grantee || ';' FROM information_schema.table_privileges WHERE schemaname NOT IN ('pg_catalog', 'information_schema');" > grant_table.sql
psql -h $REDSHIFT_HOST -p $REDSHIFT_PORT -U $REDSHIFT_USER -d $DEV_DB -f grant_table.sql

# Cleanup
rm table_ddl.sql view_ddl.sql data_copy.sql grant_table.sql

Key Considerations #

• Performance: Use UNLOAD/COPY for large tables to leverage Redshift's parallel processing.
• Dependencies: Ensure objects are created in the correct order (tables before dependent views).
• Security: Store credentials securely (e.g., AWS Secrets Manager) instead of hardcoding.
• Off-Peak Execution: Run the script during low-traffic hours to avoid impacting production.

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