AnalyticDB:Manually migrate data from an Amazon Redshift cluster to an AnalyticDB for PostgreSQL instance

Step 1: Export data from Redshift to Amazon S3

Use the Redshift UNLOAD statement to export table data to an Amazon S3 bucket. See the UNLOAD reference for full syntax.

UNLOAD ('select-statement')
TO 's3://object-path/name-prefix'
AUTHORIZATION
[ option [ ... ] ]

WHERE option IS
{ [ FORMAT [ AS ] ] CSV | PARQUET
| PARTITION BY ( column_name [, ... ] ) [ INCLUDE ]
| MANIFEST [ VERBOSE ]
| HEADER
| DELIMITER [ AS ] 'delimiter-char'
| FIXEDWIDTH [ AS ] 'fixedwidth-spec'
| ENCRYPTED [ AUTO ]
| BZIP2
| GZIP
| ZSTD
| ADDQUOTES
| NULL [ AS ] 'null-string'
| ESCAPE
| ALLOWOVERWRITE
| CLEANPATH
| PARALLEL [ { ON | TRUE } | { OFF | FALSE } ]
| MAXFILESIZE [AS] max-size [ MB | GB ]
| REGION [AS] 'Amazon-region' }

Follow these guidelines for best import performance:

• Format: Use FORMAT AS PARQUET or FORMAT AS CSV.

• Parallelism: Set PARALLEL ON to export data across multiple files simultaneously.

• File size: Set MAXFILESIZE to a value that produces a file count equal to an integer multiple of your AnalyticDB for PostgreSQL node count. This lets the parallel OSS import distribute files evenly across nodes.

Step 2: Sync data from Amazon S3 to OSS

Two tools are available: Data Online Migration (recommended for large-scale or ongoing transfers) and ossimport (recommended for standalone, script-based migration).

Step 3: Convert DDL statements and create destination tables

Before creating tables in AnalyticDB for PostgreSQL, convert your Redshift DDL statements. Redshift-specific syntax is not compatible with AnalyticDB for PostgreSQL.

See DDL conversion reference for the full conversion rules and examples.

After converting the DDL, create the destination schema in AnalyticDB for PostgreSQL. See CREATE TABLE for syntax.

Step 4: Import data from OSS into AnalyticDB for PostgreSQL

Two methods are available for loading data from OSS:

• COPY statement: See Use the COPY or UNLOAD statement to import or export data between OSS foreign tables and AnalyticDB for PostgreSQL tables

• OSS foreign table: See Use OSS foreign tables for data lake analysis

Key syntax differences

Function conversions

AnalyticDB for PostgreSQL does not support several Redshift-specific SQL functions. Replace them as follows:

For user-defined functions (UDFs), rewrite in a language supported by AnalyticDB for PostgreSQL. For example, replace this Redshift Python UDF:

CREATE OR REPLACE FUNCTION public.f_jdate(dt timestamp WITHOUT time zone)
RETURNS character varying AS
'      from datetime import timedelta, datetime
       if dt.hour < 4:
              d = timedelta(days=-1)
              dt = dt + d
       return str(dt.date())'
LANGUAGE plpythonu IMMUTABLE;
COMMIT;

With this SQL expression:

to_char(a - interval '4 hour', 'yyyy-mm-dd')

For a complete list of standard PostgreSQL functions, see Functions and Operators.

CREATE SCHEMA

CREATE SCHEMA schema1 AUTHORIZATION xxxpoc;
GRANT ALL ON SCHEMA schema1 TO xxxpoc;
GRANT ALL ON SCHEMA schema1 TO public;
COMMENT ON SCHEMA model IS 'for xxx migration poc test';

CREATE SCHEMA oss_external_table AUTHORIZATION xxxpoc;

CREATE TABLE

Apply all applicable conversions from Key syntax differences when rewriting CREATE TABLE statements.

Example 1: Redshift table with DISTKEY and INTERLEAVED SORTKEY

Redshift source:

CREATE TABLE schema1.table1
(
    filed1 VARCHAR(100) ENCODE lzo,
    filed2 INTEGER DISTKEY,
    filed3 INTEGER,
    filed4 BIGINT ENCODE lzo,
    filed5 INTEGER
)
INTERLEAVED SORTKEY
(
    filed1,
    filed2
);

AnalyticDB for PostgreSQL equivalent:

CREATE TABLE schema1.table1
(
    filed1 VARCHAR(100),
    filed2 INTEGER,
    filed3 INTEGER,
    filed4 BIGINT,
    filed5 INTEGER
)
WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zstd)
DISTRIBUTED BY (filed2)
ORDER BY (filed1, filed2);

-- Sort
SORT schema1.table1;
MULTISORT schema1.table1;

Example 2: Redshift table with DISTSTYLE EVEN and INTERLEAVED SORTKEY

Redshift source:

CREATE TABLE schema2.table2
(
    filed1 VARCHAR(50) ENCODE lzo,
    filed2 VARCHAR(50) ENCODE lzo,
    filed3 VARCHAR(20) ENCODE lzo,
)
DISTSTYLE EVEN
INTERLEAVED SORTKEY
(
    filed1
);

AnalyticDB for PostgreSQL equivalent:

CREATE TABLE schema2.table2
(
    filed1 VARCHAR(50),
    filed2 VARCHAR(50),
    filed3 VARCHAR(20)
)
WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zstd)
DISTRIBUTED RANDOMLY
ORDER BY (filed1);

-- Sort
SORT schema2.table2;
MULTISORT schema2.table2;

For compression algorithm options, see Data compression.

CREATE VIEW

Convert CREATE VIEW statements using the same rules as CREATE TABLE. The WITH NO SCHEMA BINDING clause is not supported — remove it from the converted statement.