Migrating Amazon Redshift Data Sharing to New Clusters for Cross-Region DR

How to Migrate Amazon Redshift Data Sharing Configuration to New Clusters During Cross-Region Disaster Recovery

Dan Jin

Published Jun 16, 2026

Last modified Jun 16, 2026

Overview

When a Disaster Recovery (DR) event forces you to fail over Amazon Redshift clusters to a different AWS Region, the data sharing configuration between producer and consumer clusters is not automatically migrated. Datashares, shared objects, consumer grants, and consumer-side databases must all be recreated on the new clusters.

This post walks through a repeatable, automated approach to:

Capture the full data sharing configuration from the original clusters before (or during) DR.
Store that configuration as a historical dictionary in S3 (Parquet format via Redshift Spectrum).
Generate the exact SQL statements needed to rebuild data sharing on the new clusters, with namespace remapping.
Execute the generated SQL on the new producer and consumer clusters.

This approach is especially valuable when you have many clusters with complex, multi-directional data sharing topologies.

Prerequisites

RA3 node-type clusters or Redshift Serverless (data sharing is not supported on DC2/DS2 node types)
AWS CLI v2 configured with appropriate IAM permissions
Redshift Data API access (redshift-data:ExecuteStatement, redshift-data:DescribeStatement, redshift-data:GetStatementResult)
An S3 bucket for storing the configuration dictionary
A local schema (e.g., governance) for the dictionary tables, and a Redshift Spectrum external schema (e.g., ext_governance) that reads from that S3 bucket
IAM role attached to Redshift with S3 read/write access

Architecture

Step 1: Create Dictionary Tables

First, create local tables to store the captured data sharing configuration, and external (Spectrum) tables pointing to S3 for cross-cluster access after DR.

Local Dictionary Tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Stores datashare metadata (producer & consumer sides)
CREATE TABLE governance.tb_datashares_hist (
    reg_dt        INTEGER ENCODE az64,
    cluster       VARCHAR(50) ENCODE zstd,
    share_name    VARCHAR(200) ENCODE zstd,
    share_id      BIGINT ENCODE az64,
    source_database    VARCHAR(200) ENCODE zstd,
    consumer_database  VARCHAR(200) ENCODE zstd,
    share_type    VARCHAR(100) ENCODE zstd,
    createdate    VARCHAR(50) ENCODE zstd,
    producer_account   VARCHAR(50) ENCODE zstd,
    producer_namespace VARCHAR(500) ENCODE zstd
) DISTSTYLE EVEN;

-- Stores objects (schemas, tables, views, functions) added to each datashare
CREATE TABLE governance.tb_datashare_objects_hist (
    reg_dt        INTEGER ENCODE az64,
    cluster       VARCHAR(50) ENCODE zstd,
    share_type    VARCHAR(100) ENCODE zstd,
    share_name    VARCHAR(200) ENCODE zstd,
    object_type   VARCHAR(100) ENCODE zstd,
    object_name   VARCHAR(1000) ENCODE zstd,
    producer_account   VARCHAR(50) ENCODE zstd,
    producer_namespace VARCHAR(100) ENCODE zstd,
    include_new   VARCHAR(30)
) DISTSTYLE EVEN;

-- Stores consumer namespace grants per datashare
CREATE TABLE governance.tb_datashare_consumers_hist (
    reg_dt        INTEGER ENCODE az64,
    cluster       VARCHAR(50) ENCODE zstd,
    share_name    VARCHAR(200) ENCODE zstd,
    consumer_account   VARCHAR(50) ENCODE zstd,
    consumer_namespace VARCHAR(100) ENCODE zstd,
    share_date    VARCHAR(50) ENCODE zstd
) DISTSTYLE EVEN;

-- Stores database-level privileges on consumer databases
CREATE TABLE governance.tb_ds_database_privs_list (
    reg_dt        INTEGER ENCODE az64,
    cluster       VARCHAR(50) ENCODE zstd,
    grant_type    VARCHAR(50) ENCODE zstd,
    grant_list    VARCHAR(200) ENCODE zstd,
    database_name VARCHAR(100) ENCODE zstd,
    grant_target  VARCHAR(200) ENCODE zstd,
    grant_text    VARCHAR(300) ENCODE zstd
) DISTSTYLE EVEN;

External (Spectrum) Tables

Create corresponding external tables in a Spectrum schema so the dictionary is accessible from any cluster after DR:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE EXTERNAL TABLE ext_governance.tb_datashares_hist (
    reg_dt INTEGER, cluster VARCHAR(50), share_name VARCHAR(200), share_id BIGINT,
    source_database VARCHAR(200), consumer_database VARCHAR(200), share_type VARCHAR(100),
    createdate VARCHAR(50), producer_account VARCHAR(50), producer_namespace VARCHAR(500)
) STORED AS PARQUET
LOCATION 's3://<your-bucket>/governance/tb_datashares_hist/';

CREATE EXTERNAL TABLE ext_governance.tb_datashare_objects_hist (
    reg_dt INTEGER, cluster VARCHAR(50), share_type VARCHAR(100), share_name VARCHAR(200),
    object_type VARCHAR(100), object_name VARCHAR(1000), producer_account VARCHAR(50),
    producer_namespace VARCHAR(100), include_new VARCHAR(30)
) STORED AS PARQUET
LOCATION 's3://<your-bucket>/governance/tb_datashare_objects_hist/';

CREATE EXTERNAL TABLE ext_governance.tb_datashare_consumers_hist (
    reg_dt INTEGER, cluster VARCHAR(50), share_name VARCHAR(200),
    consumer_account VARCHAR(50), consumer_namespace VARCHAR(100), share_date VARCHAR(50)
) STORED AS PARQUET
LOCATION 's3://<your-bucket>/governance/tb_datashare_consumers_hist/';

CREATE EXTERNAL TABLE ext_governance.tb_ds_database_privs_list (
    reg_dt INTEGER, cluster VARCHAR(50), grant_type VARCHAR(50), grant_list VARCHAR(200),
    database_name VARCHAR(100), grant_target VARCHAR(200), grant_text VARCHAR(300)
) STORED AS PARQUET
LOCATION 's3://<your-bucket>/governance/tb_ds_database_privs_list/';

Step 2: Capture Data Sharing Configuration (Run Periodically or Before DR)

Run the following on each cluster to snapshot the current data sharing state into the dictionary tables, then unload to S3.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Capture datashare metadata
INSERT INTO governance.tb_datashares_hist
SELECT <capture_date>::INTEGER, '<cluster_name>',
       COALESCE(share_name, ' '), COALESCE(share_id, 0),
       COALESCE(source_database, ' '), COALESCE(consumer_database, ' '),
       COALESCE(share_type, ' '), COALESCE(createdate::VARCHAR, ' '),
       COALESCE(producer_account, ' '), COALESCE(producer_namespace, ' ')
FROM svv_datashares;

-- Capture shared objects
INSERT INTO governance.tb_datashare_objects_hist
SELECT <capture_date>::INTEGER, '<cluster_name>',
       COALESCE(share_type, ' '), COALESCE(share_name, ' '),
       COALESCE(object_type, ' '), COALESCE(object_name, ' '),
       COALESCE(producer_account, ' '), COALESCE(producer_namespace, ' '),
       COALESCE(DECODE(include_new, TRUE, 'true', FALSE, 'false'), ' ')
FROM svv_datashare_objects;

-- Capture consumer grants
INSERT INTO governance.tb_datashare_consumers_hist
SELECT <capture_date>::INTEGER, '<cluster_name>',
       COALESCE(share_name, ' '), COALESCE(consumer_account, ' '),
       COALESCE(consumer_namespace, ' '), COALESCE(share_date::VARCHAR, ' ')
FROM svv_datashare_consumers;

-- Capture database-level privileges on consumer databases
INSERT INTO governance.tb_ds_database_privs_list
SELECT <capture_date>::INTEGER, '<cluster_name>',
       CASE WHEN t2.grant_target ILIKE 'group %' THEN 'Group' ELSE 'Account' END,
       SUBSTRING(
           CASE WHEN CHARINDEX('r', t2.grant_value) > 0 THEN ',SELECT'     ELSE '' END
        || CASE WHEN CHARINDEX('w', t2.grant_value) > 0 THEN ',UPDATE'     ELSE '' END
        || CASE WHEN CHARINDEX('a', t2.grant_value) > 0 THEN ',INSERT'     ELSE '' END
        || CASE WHEN CHARINDEX('d', t2.grant_value) > 0 THEN ',DELETE'     ELSE '' END
        || CASE WHEN CHARINDEX('U', t2.grant_value) > 0 THEN ',USAGE'      ELSE '' END
        || CASE WHEN CHARINDEX('C', t2.grant_value) > 0 THEN ',CREATE'     ELSE '' END
        || CASE WHEN CHARINDEX('T', t2.grant_value) > 0 THEN ',TEMPORARY'  ELSE '' END
       , 2),
       t2.database_name,
       REPLACE(t2.grant_target, 'group ', ''),
       t2.grant_text
FROM (
    SELECT SPLIT_PART(t1.grant_contents, '|', t1.div) AS grant_text,
           SPLIT_PART(SPLIT_PART(t1.grant_contents, '|', t1.div), '=', 1) AS grant_target,
           SPLIT_PART(SPLIT_PART(SPLIT_PART(t1.grant_contents, '|', t1.div), '/', 1), '=', 2) AS grant_value,
           t1.datname AS database_name
    FROM (
        SELECT GENERATE_SERIES(1,
                 LENGTH(ARRAY_TO_STRING(c1.datacl, '|'))
               - LENGTH(REPLACE(ARRAY_TO_STRING(c1.datacl, '|'), '|', '')) + 1) AS div,
               ARRAY_TO_STRING(c1.datacl, '|') AS grant_contents,
               c1.datname
        FROM pg_database c1
        INNER JOIN svv_datashares sd ON c1.datname = sd.consumer_database
    ) t1
) t2;

Then unload each table to S3:

1
2
3
4
5
UNLOAD ('SELECT * FROM governance.tb_datashares_hist WHERE reg_dt = <capture_date>')
TO 's3://<your-bucket>/governance/tb_datashares_hist/<capture_date>_<cluster_name>_'
IAM_ROLE '<your-iam-role-arn>' FORMAT PARQUET ALLOWOVERWRITE PARALLEL OFF;

-- Repeat for tb_datashare_objects_hist, tb_datashare_consumers_hist, tb_ds_database_privs_list

Step 3: Create the Namespace Mapping Table

After the new clusters are provisioned in the DR region, collect the new namespace UUIDs and create a mapping table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- Run on each new cluster to get its namespace
SELECT CURRENT_NAMESPACE;

-- 1) Build the mapping LOCALLY first. You cannot INSERT ... VALUES into a Spectrum
--    external table, so create a local table, populate it, then unload it to S3 (step 2
--    below) where the Step 4 & 5 generators read it from.
CREATE TABLE governance.tb_rs_namespace_info (
    reg_dt        INTEGER ENCODE az64,
    cluster       VARCHAR(50) ENCODE zstd,
    aws_account   BIGINT ENCODE az64,
    old_cluster_namespace VARCHAR(100) ENCODE zstd,
    new_cluster_namespace VARCHAR(100) ENCODE zstd
) DISTSTYLE EVEN;

-- Insert mappings for each cluster (old namespace UUID -> new namespace UUID)
INSERT INTO governance.tb_rs_namespace_info VALUES
(<capture_date>, '<cluster_name>', <aws_account_id>,
 '<old-namespace-uuid>', '<new-namespace-uuid>');
-- Repeat for each cluster...

-- 2) Unload the mapping to S3 so the Spectrum generators can read it (same pattern as
--    the dictionary tables in Step 2).
UNLOAD ('SELECT * FROM governance.tb_rs_namespace_info')
TO 's3://<your-bucket>/governance/tb_rs_namespace_info/mapping_'
IAM_ROLE '<your-iam-role-arn>' FORMAT PARQUET ALLOWOVERWRITE PARALLEL OFF;

-- 3) Create the external table the generators query (points at the S3 prefix above).
CREATE EXTERNAL TABLE ext_governance.tb_rs_namespace_info (
    reg_dt INTEGER, cluster VARCHAR(50), aws_account BIGINT,
    old_cluster_namespace VARCHAR(100), new_cluster_namespace VARCHAR(100)
) STORED AS PARQUET
LOCATION 's3://<your-bucket>/governance/tb_rs_namespace_info/';

-- 4) Coverage check: list any captured namespaces that have NO mapping row. The
--    generators in Steps 4 & 5 use INNER JOIN and silently skip unmapped namespaces,
--    so resolve anything returned here before generating the rebuild SQL.
SELECT DISTINCT 'consumer' AS side, a.consumer_namespace AS unmapped_namespace
FROM ext_governance.tb_datashare_consumers_hist a
LEFT JOIN ext_governance.tb_rs_namespace_info b
    ON a.consumer_namespace = b.old_cluster_namespace
WHERE b.old_cluster_namespace IS NULL AND a.consumer_namespace <> ' '
UNION
SELECT DISTINCT 'producer', a.producer_namespace
FROM ext_governance.tb_datashares_hist a
LEFT JOIN ext_governance.tb_rs_namespace_info b
    ON a.producer_namespace = b.old_cluster_namespace
WHERE a.share_type = 'INBOUND' AND b.old_cluster_namespace IS NULL
  AND a.producer_namespace <> ' ';

Step 4: Generate Rebuild SQL (Producer Side)

Run the following generators on the new producer cluster. Most read the captured dictionary and namespace mapping from the Spectrum external tables; the clean-slate DROP (Step 4.1) reads the cluster's live catalog so it only drops datashares that actually exist. The output is ready-to-execute SQL for the new producer cluster.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- Placeholders: substitute these before running (e.g., via your SQL client's parameter
-- feature or a simple find/replace). They are NOT Redshift session variables.
--   @BASE_DATE    -> the capture date, e.g. 20260210
--   @CLUSTER_NAME -> the cluster name, e.g. 'MyCluster'

-- Step 4.1: DROP existing datashares (clean slate).
-- NOTE: Redshift does NOT support IF EXISTS on DROP DATASHARE. To stay rerun-safe, generate
-- the DROPs from what currently exists on THIS (new) producer cluster — its live
-- svv_datashares — rather than from the dictionary. On a freshly provisioned cluster this
-- returns nothing; on a rerun it clears datashares left by a previous attempt.
SELECT 'DROP DATASHARE ' || share_name || ';' AS sql_command
FROM svv_datashares
WHERE share_type = 'OUTBOUND'
ORDER BY share_name;

-- Step 4.2: CREATE datashares
SELECT 'CREATE DATASHARE ' || share_name || ';' AS sql_command
FROM ext_governance.tb_datashares_hist
WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME AND share_type = 'OUTBOUND'
ORDER BY share_name;

-- Step 4.3: ADD schemas (with INCLUDENEW flag if originally set)
SELECT 'ALTER DATASHARE ' || share_name || ' ADD SCHEMA ' || object_name
       || ';' || CASE WHEN include_new = 'true'
           THEN CHR(10) || 'ALTER DATASHARE ' || share_name
                || ' SET INCLUDENEW = TRUE FOR SCHEMA ' || object_name || ';'
           ELSE '' END AS sql_command
FROM ext_governance.tb_datashare_objects_hist
WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME AND object_type = 'schema'
  AND share_name IN (
      SELECT share_name FROM ext_governance.tb_datashares_hist
      WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME AND share_type = 'OUTBOUND')
ORDER BY share_name, object_name;

-- Step 4.4: ADD tables, views, late-binding views, and materialized views
-- NOTE: Redshift adds ALL relation types (tables, regular & late-binding views, and
-- materialized views) to a datashare with ADD TABLE. There is no ADD VIEW clause.
SELECT 'ALTER DATASHARE ' || share_name || ' ADD TABLE ' || object_name || ';' AS sql_command
FROM ext_governance.tb_datashare_objects_hist
WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME
  AND object_type IN ('table', 'view', 'late binding view', 'materialized view')
  AND share_name IN (
      SELECT share_name FROM ext_governance.tb_datashares_hist
      WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME AND share_type = 'OUTBOUND')
ORDER BY share_name, object_name;

-- Step 4.5: ADD functions
SELECT 'ALTER DATASHARE ' || share_name || ' ADD FUNCTION ' || object_name || ';' AS sql_command
FROM ext_governance.tb_datashare_objects_hist
WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME AND object_type = 'function'
  AND share_name IN (
      SELECT share_name FROM ext_governance.tb_datashares_hist
      WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME AND share_type = 'OUTBOUND')
ORDER BY share_name, object_name;

-- Step 4.6: GRANT USAGE to consumer namespaces (remapped to new namespace)
-- NOTE: INNER JOIN is intentional — a consumer namespace with no mapping row is skipped
-- rather than producing a broken statement. Run the Step 3 coverage check first.
SELECT 'GRANT USAGE ON DATASHARE ' || a.share_name
       || ' TO NAMESPACE ''' || b.new_cluster_namespace || ''';' AS sql_command
FROM ext_governance.tb_datashare_consumers_hist a
INNER JOIN ext_governance.tb_rs_namespace_info b
    ON a.consumer_namespace = b.old_cluster_namespace
WHERE a.reg_dt = @BASE_DATE AND a.cluster = @CLUSTER_NAME
  AND a.share_name IN (
      SELECT share_name FROM ext_governance.tb_datashares_hist
      WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME AND share_type = 'OUTBOUND')
ORDER BY a.share_name;

Step 5: Generate Rebuild SQL (Consumer Side)

Run these generators on the new consumer cluster. As in Step 4, the clean-slate DROP (Step 5.1) reads the live catalog, while the CREATE and GRANT steps read the dictionary and namespace mapping from the Spectrum external tables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Step 5.1: DROP existing consumer databases (clean slate).
-- NOTE: Redshift does NOT support IF EXISTS on DROP DATABASE either. Generate the DROPs from
-- the consumer databases that currently exist on THIS (new) consumer cluster — its live
-- svv_datashares. On a freshly provisioned cluster this returns nothing; on a rerun it
-- clears databases left by a previous attempt.
SELECT 'DROP DATABASE ' || consumer_database || ';' AS sql_command
FROM svv_datashares
WHERE share_type = 'INBOUND' AND consumer_database IS NOT NULL
ORDER BY consumer_database;

-- Step 5.2: CREATE databases from datashares (remapped to new producer namespace)
-- NOTE: INNER JOIN is intentional — an inbound datashare whose producer namespace has no
-- mapping row is skipped rather than producing a broken statement. Run the Step 3
-- coverage check first.
SELECT 'CREATE DATABASE ' || a.consumer_database
       || ' FROM DATASHARE ' || a.share_name
       || ' OF NAMESPACE ''' || b.new_cluster_namespace || ''';' AS sql_command
FROM ext_governance.tb_datashares_hist a
INNER JOIN ext_governance.tb_rs_namespace_info b
    ON a.producer_namespace = b.old_cluster_namespace
WHERE a.reg_dt = @BASE_DATE AND a.cluster = @CLUSTER_NAME AND a.share_type = 'INBOUND'
  AND a.consumer_database IS NOT NULL AND a.consumer_database <> ' '
ORDER BY a.consumer_database;

-- Step 5.3: Re-grant database privileges
SELECT 'GRANT ' || grant_list || ' ON DATABASE ' || database_name || ' TO '
       || CASE WHEN grant_type = 'Group' THEN 'GROUP ' || grant_target
               WHEN grant_type = 'Account' THEN grant_target
          END || ';' AS sql_command
FROM ext_governance.tb_ds_database_privs_list
WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME
  AND database_name IN (
      SELECT consumer_database FROM ext_governance.tb_datashares_hist
      WHERE reg_dt = @BASE_DATE AND cluster = @CLUSTER_NAME AND share_type = 'INBOUND'
        AND consumer_database IS NOT NULL AND consumer_database <> ' ')
ORDER BY database_name, grant_target;

Step 6: Automation Script

The following shell script automates the capture and unload stages (Stages 1–2) using the Redshift Data API, then prints guidance for generating and executing the rebuild SQL (Steps 4–5).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
#!/usr/bin/env bash
#
# datashare_dr_migrate.sh
# Automates data sharing configuration capture and rebuild for DR.
#
# Usage: ./datashare_dr_migrate.sh <cluster-id> <database> [try_no] [base_dt]
#   cluster-id : Redshift cluster identifier
#   database   : Redshift database name
#   try_no     : Retry number (default: 1). If >1, deletes existing data first.
#   base_dt    : Base date for sync script generation (YYYYMMDD, default: today)
#
set -euo pipefail

CLUSTER_ID="${1:?Usage: $0 <cluster-id> <database> [try_no] [base_dt]}"
DATABASE="${2:?Usage: $0 <cluster-id> <database> [try_no] [base_dt]}"
TRY_NO="${3:-1}"
BASE_DT="${4:-$(date +%Y%m%d)}"

REG_DT="$(date +%Y%m%d)"
CLUSTER_NAME="<your-cluster-name>"
IAM_ROLE="<your-iam-role-arn>"
S3_BUCKET="s3://<your-bucket>/governance"
REGION="<your-region>"
# Provisioned clusters: the Redshift Data API requires an auth method — a database user
# (temporary credentials, used below) or --secret-arn. For Redshift Serverless, replace
# --cluster-identifier/--db-user with --workgroup-name "<workgroup>" (auth uses your IAM identity).
DB_USER="<your-db-user>"

# ── Helper: execute SQL via Redshift Data API and wait ──
run_sql() {
    local sql="$1"
    local desc="${2:-SQL}"
    echo "[$(date '+%H:%M:%S')] Running: ${desc}"

    local stmt_id
    stmt_id=$(aws redshift-data execute-statement \
        --cluster-identifier "$CLUSTER_ID" \
        --database "$DATABASE" \
        --db-user "$DB_USER" \
        --region "$REGION" \
        --sql "$sql" \
        --query 'Id' --output text)

    local status="SUBMITTED"
    while [[ "$status" == "SUBMITTED" || "$status" == "PICKED" || "$status" == "STARTED" ]]; do
        sleep 2
        status=$(aws redshift-data describe-statement \
            --id "$stmt_id" --region "$REGION" --query 'Status' --output text)
    done

    if [[ "$status" != "FINISHED" ]]; then
        local err
        err=$(aws redshift-data describe-statement \
            --id "$stmt_id" --region "$REGION" --query 'Error' --output text)
        echo "  ERROR ($status): $err" >&2
        return 1
    fi
    echo "  Done ($status)"
}

# ── Stage 1: Capture dictionary (delete first if retry) ──
echo "=== Stage 1: Capture Data Sharing Dictionary ==="

if (( TRY_NO > 1 )); then
    echo "-- Retry mode: deleting existing data for reg_dt=${REG_DT} --"
    for tbl in tb_datashares_hist tb_datashare_objects_hist \
               tb_datashare_consumers_hist tb_ds_database_privs_list; do
        run_sql "DELETE FROM governance.${tbl} WHERE reg_dt = ${REG_DT};" \
                "Delete ${tbl}"
    done
fi

run_sql "
INSERT INTO governance.tb_datashares_hist
SELECT ${REG_DT}::INTEGER, '${CLUSTER_NAME}',
       COALESCE(share_name,' '), COALESCE(share_id,0),
       COALESCE(source_database,' '), COALESCE(consumer_database,' '),
       COALESCE(share_type,' '), COALESCE(createdate::VARCHAR,' '),
       COALESCE(producer_account,' '), COALESCE(producer_namespace,' ')
FROM svv_datashares;
" "Capture svv_datashares"

run_sql "
INSERT INTO governance.tb_datashare_objects_hist
SELECT ${REG_DT}::INTEGER, '${CLUSTER_NAME}',
       COALESCE(share_type,' '), COALESCE(share_name,' '),
       COALESCE(object_type,' '), COALESCE(object_name,' '),
       COALESCE(producer_account,' '), COALESCE(producer_namespace,' '),
       COALESCE(DECODE(include_new, TRUE, 'true', FALSE, 'false'), ' ')
FROM svv_datashare_objects;
" "Capture svv_datashare_objects"

run_sql "
INSERT INTO governance.tb_datashare_consumers_hist
SELECT ${REG_DT}::INTEGER, '${CLUSTER_NAME}',
       COALESCE(share_name,' '), COALESCE(consumer_account,' '),
       COALESCE(consumer_namespace,' '), COALESCE(share_date::VARCHAR,' ')
FROM svv_datashare_consumers;
" "Capture svv_datashare_consumers"

run_sql "
INSERT INTO governance.tb_ds_database_privs_list
SELECT ${REG_DT}::INTEGER, '${CLUSTER_NAME}',
       CASE WHEN t2.grant_target ILIKE 'group %' THEN 'Group' ELSE 'Account' END,
       SUBSTRING(
           CASE WHEN CHARINDEX('r', t2.grant_value) > 0 THEN ',SELECT'    ELSE '' END
        || CASE WHEN CHARINDEX('w', t2.grant_value) > 0 THEN ',UPDATE'    ELSE '' END
        || CASE WHEN CHARINDEX('a', t2.grant_value) > 0 THEN ',INSERT'    ELSE '' END
        || CASE WHEN CHARINDEX('d', t2.grant_value) > 0 THEN ',DELETE'    ELSE '' END
        || CASE WHEN CHARINDEX('U', t2.grant_value) > 0 THEN ',USAGE'     ELSE '' END
        || CASE WHEN CHARINDEX('C', t2.grant_value) > 0 THEN ',CREATE'    ELSE '' END
        || CASE WHEN CHARINDEX('T', t2.grant_value) > 0 THEN ',TEMPORARY' ELSE '' END
       , 2),
       t2.database_name,
       REPLACE(t2.grant_target, 'group ', ''),
       t2.grant_text
FROM (
    SELECT SPLIT_PART(t1.grant_contents, '|', t1.div) AS grant_text,
           SPLIT_PART(SPLIT_PART(t1.grant_contents, '|', t1.div), '=', 1) AS grant_target,
           SPLIT_PART(SPLIT_PART(SPLIT_PART(t1.grant_contents, '|', t1.div), '/', 1), '=', 2) AS grant_value,
           t1.datname AS database_name
    FROM (
        SELECT GENERATE_SERIES(1,
                 LENGTH(ARRAY_TO_STRING(c1.datacl, '|'))
               - LENGTH(REPLACE(ARRAY_TO_STRING(c1.datacl, '|'), '|', '')) + 1) AS div,
               ARRAY_TO_STRING(c1.datacl, '|') AS grant_contents,
               c1.datname
        FROM pg_database c1
        INNER JOIN svv_datashares sd ON c1.datname = sd.consumer_database
    ) t1
) t2;
" "Capture database privileges"

# ── Stage 2: Unload to S3 ──
echo ""
echo "=== Stage 2: Unload Dictionary to S3 ==="

for tbl in tb_datashares_hist tb_datashare_objects_hist \
           tb_datashare_consumers_hist tb_ds_database_privs_list; do
    run_sql "
    UNLOAD ('SELECT * FROM governance.${tbl} WHERE reg_dt = ${REG_DT}')
    TO '${S3_BUCKET}/${tbl}/${REG_DT}_${CLUSTER_NAME}_'
    IAM_ROLE '${IAM_ROLE}' FORMAT PARQUET ALLOWOVERWRITE PARALLEL OFF;
    " "Unload ${tbl} → S3"
done

# ── Stage 3: Generate sync SQL and optionally execute ──
echo ""
echo "=== Stage 3: Generate Rebuild SQL ==="
echo "Run the SQL generator queries from Steps 4 & 5 against the Spectrum"
echo "external tables on your new cluster, then review and execute the output."

echo ""
echo "=== Complete ==="

Step 7: Validation

After executing the generated SQL on the new clusters, validate the migration:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- On the new PRODUCER cluster:
SELECT 'Outbound datashares' AS metric, COUNT(*) AS count
FROM svv_datashares WHERE share_type = 'OUTBOUND'
UNION ALL
SELECT 'Shared objects', COUNT(*)
FROM svv_datashare_objects
WHERE share_name IN (SELECT share_name FROM svv_datashares WHERE share_type = 'OUTBOUND')
UNION ALL
SELECT 'Consumer grants', COUNT(*)
FROM svv_datashare_consumers
WHERE share_name IN (SELECT share_name FROM svv_datashares WHERE share_type = 'OUTBOUND');

-- On the new CONSUMER cluster:
SELECT 'Inbound datashares' AS metric, COUNT(*) AS count
FROM svv_datashares WHERE share_type = 'INBOUND'
UNION ALL
SELECT 'Consumer databases', COUNT(*)
FROM svv_datashares
WHERE share_type = 'INBOUND' AND consumer_database IS NOT NULL;

Compare these counts against the dictionary to confirm completeness.

Quick Reference: Execution Order

Order	Cluster Role	Action
1	Producer	DROP existing datashares
2	Producer	CREATE datashares
3	Producer	ALTER DATASHARE ADD SCHEMA (with INCLUDENEW)
4	Producer	ALTER DATASHARE ADD TABLE (tables, views, materialized views)
5	Producer	ALTER DATASHARE ADD FUNCTION
6	Producer	GRANT USAGE ON DATASHARE TO NAMESPACE
7	Consumer	DROP existing consumer databases
8	Consumer	CREATE DATABASE FROM DATASHARE OF NAMESPACE
9	Consumer	GRANT privileges on consumer databases

Key Considerations

Run the capture periodically (e.g., daily via a scheduled job) so you always have a recent snapshot. This minimizes data loss if DR occurs unexpectedly.
Namespace UUIDs change when clusters are recreated. The namespace mapping table (tb_rs_namespace_info) is the critical bridge between old and new configurations.
Database-level privileges on consumer databases (USAGE, CREATE, etc.) are often overlooked. The privilege capture query parses pg_database.datacl to extract and reconstruct GRANT statements.
INCLUDENEW flag on schemas ensures that new tables added to a shared schema are automatically included. The generator preserves this setting.
Cross-account data sharing requires additional steps (authorizing the producer account on the consumer side). This guide focuses on same-account, cross-region scenarios.
Test in a non-production environment before relying on this for actual DR. Validate that the generated SQL produces identical data sharing topology.

Conclusion

By maintaining a versioned dictionary of your data sharing configuration in S3 and using SQL generators with namespace remapping, you can reliably rebuild complex Redshift data sharing topologies after a cross-region DR failover. This approach scales to environments with many clusters and hundreds of datashares, and can be integrated into your existing DR runbooks.

Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.

Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article

Builder Center

Migrating Amazon Redshift Data Sharing to New Clusters for Cross-Region DR

Dan Jin

Overview

Prerequisites

Architecture

Step 1: Create Dictionary Tables

Local Dictionary Tables

External (Spectrum) Tables

Step 2: Capture Data Sharing Configuration (Run Periodically or Before DR)

Step 3: Create the Namespace Mapping Table

Step 4: Generate Rebuild SQL (Producer Side)

Step 5: Generate Rebuild SQL (Consumer Side)

Step 6: Automation Script

Step 7: Validation

Quick Reference: Execution Order

Key Considerations

Conclusion