Aurora PostgreSQL 跨账号跨 Region 同步与迁移

Aurora Global Database:
    good for same-account cross-Region architecture
    not a direct Account A -> Account B replication mechanism

RDS/Aurora snapshot export to S3:
    produces analytics files such as Parquet
    cannot be restored directly as an Aurora cluster
    not a replacement for snapshot restore, pg_restore, or DMS

can stop for hours and wants full cluster copy:
    snapshot copy/share/restore

can stop and needs selective/version-changing migration:
    pg_dump/pg_restore

cannot stop for full copy duration:
    DMS full load + CDC

needs permanent table-level replication and accepts PostgreSQL operations:
    native logical replication

source:
    Account ID and Region
    cluster identifier and ARN
    Aurora PostgreSQL engine version
    instance classes and topology
    database names and total size
    schemas, tables, partitions, indexes, constraints
    extensions and extension versions
    roles, grants, row-level security
    functions, procedures, triggers, views, materialized views
    sequences and identity columns
    large objects and large columns
    parameter group and cluster parameter group
    storage encryption KMS key
    Secrets Manager secret and authentication mode
    logical replication / replication slots

target:
    Account ID and Region
    target engine version
    subnet group, VPC, security groups
    parameter groups
    customer managed KMS key
    instance classes and capacity
    backup retention, deletion protection, monitoring
    application cutover endpoint/secret

SELECT version();
SELECT current_database();
SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

SELECT slot_name, plugin, slot_type, active, restart_lsn
FROM pg_replication_slots;

aws rds describe-db-clusters --profile account-a --region ap-east-1 --db-cluster-identifier A1

aws rds describe-db-cluster-parameters --profile account-a --region ap-east-1 --db-cluster-parameter-group-name SOURCE-CLUSTER-PARAMETER-GROUP

Aurora PostgreSQL:
    DB cluster snapshot
    CLI = create-db-cluster-snapshot

standard RDS PostgreSQL:
    DB instance snapshot
    CLI = create-db-snapshot

Take DB cluster snapshot:
    creates a restorable Aurora snapshot
    can be copied/shared/restored as a cluster

Export snapshot to S3:
    writes analytics-oriented files such as Parquet
    cannot be restored directly as Aurora PostgreSQL

1. stop all application writes
2. create manual DB cluster snapshot in Account A
3. wait until snapshot status = available
4. copy snapshot to destination Region when required
5. share manual snapshot with Account B
6. share source customer managed KMS key permissions when encrypted
7. Account B copies shared snapshot using its own KMS key
8. Account B restores a new Aurora cluster from its owned snapshot
9. recreate networking, parameter groups, monitoring, secrets, IAM integration
10. validate and switch application

aws rds create-db-cluster-snapshot --profile account-a --region ap-east-1 --db-cluster-identifier A1 --db-cluster-snapshot-identifier a1-migration-20260824

aws rds describe-db-cluster-snapshots --profile account-a --region ap-east-1 --db-cluster-snapshot-identifier a1-migration-20260824 --query 'DBClusterSnapshots[0].{Status:Status,Engine:Engine,EngineVersion:EngineVersion,Encrypted:StorageEncrypted,KmsKeyId:KmsKeyId}'

do not use:
    Aurora snapshot AllocatedStorage as database size

use instead:
    CloudWatch AWS/RDS VolumeBytesUsed for cluster volume usage
    PostgreSQL pg_database_size() for logical database size

SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datistemplate = false
ORDER BY pg_database_size(datname) DESC;

aws rds modify-db-cluster-snapshot-attribute --profile account-a --region ap-southeast-1 --db-cluster-snapshot-identifier a1-migration-20260824 --attribute-name restore --values-to-add 222222222222

unencrypted manual snapshot:
    can be shared

snapshot encrypted with customer managed KMS key:
    can be shared when Account B is allowed to use the key
    Account B should copy it with an Account B KMS key before restore

snapshot encrypted with AWS managed key aws/rds:
    cannot be shared cross-account directly
    copy/re-encrypt with a customer managed key first

Account A source Region snapshot
    -> Account A copy to destination Region using Account A customer managed KMS key
    -> share destination-Region snapshot and KMS permission with Account B
    -> Account B copy shared snapshot using Account B destination-Region KMS key
    -> Account B restore cluster

recommended:
    Account A / source Region snapshot
    -> Account A copy to target Region
    -> Account A shares target-Region snapshot with Account B
    -> Account B sees it in target Region
    -> Account B copies it with Account B KMS key

possible but usually more complex:
    Account A shares in source Region
    -> Account B sees it in source Region
    -> Account B performs cross-Region copy

DB subnet group
security groups
cluster and instance parameter groups
DB instance classes
Secrets Manager integration
enhanced monitoring / Performance Insights / log exports
backup retention and deletion protection
Route 53 or application configuration

aws rds describe-db-cluster-snapshots --profile account-b --region ap-southeast-1 --snapshot-type manual --query 'DBClusterSnapshots[].{ID:DBClusterSnapshotIdentifier,ARN:DBClusterSnapshotArn,Status:Status,Engine:Engine,Version:EngineVersion,Encrypted:StorageEncrypted,KmsKeyId:KmsKeyId}'

restore-db-cluster-from-snapshot:
    restores Aurora cluster storage and cluster metadata
    does not create a usable writer DB instance by itself in the CLI flow

create-db-instance:
    creates the Aurora compute instance inside B2
    first available instance becomes the writer

B2 DB cluster:
    shared cluster storage restored
    database/schema/table/index/sequence data restored
    cluster endpoint metadata may exist

compute topology:
    DBClusterMembers = []
    no writer instance
    no reader instance
    PostgreSQL endpoint cannot serve normal connections

aws rds describe-db-clusters --profile account-b --region ap-southeast-1 --db-cluster-identifier B2 --query 'DBClusters[0].DBClusterMembers'

DB subnet group in the target VPC
security group allowing approved PostgreSQL clients on TCP 5432
Aurora PostgreSQL cluster parameter group matching the engine family
Aurora PostgreSQL DB parameter group matching the engine family
target DB instance class

aws rds restore-db-cluster-from-snapshot --profile account-b --region ap-southeast-1 --db-cluster-identifier B2 --snapshot-identifier arn:aws:rds:ap-southeast-1:222222222222:cluster-snapshot:a1-migration-account-b-copy --engine aurora-postgresql --db-subnet-group-name account-b-aurora-subnets --vpc-security-group-ids sg-0123456789abcdef0 --db-cluster-parameter-group-name aurora-postgresql-target-cluster --port 5432 --copy-tags-to-snapshot --enable-cloudwatch-logs-exports postgresql --deletion-protection

aws rds wait db-cluster-available --profile account-b --region ap-southeast-1 --db-cluster-identifier B2

aws rds modify-db-cluster --profile account-b --region ap-southeast-1 --db-cluster-identifier B2 --backup-retention-period 7 --apply-immediately

aws rds create-db-instance --profile account-b --region ap-southeast-1 --db-instance-identifier b2-writer-1 --db-cluster-identifier B2 --engine aurora-postgresql --db-instance-class db.r7g.large --db-parameter-group-name aurora-postgresql-target-instance --promotion-tier 0 --no-publicly-accessible --enable-performance-insights

aws rds wait db-instance-available --profile account-b --region ap-southeast-1 --db-instance-identifier b2-writer-1

aws rds create-db-instance --profile account-b --region ap-southeast-1 --db-instance-identifier b2-reader-1 --db-cluster-identifier B2 --engine aurora-postgresql --db-instance-class db.r7g.large --db-parameter-group-name aurora-postgresql-target-instance --promotion-tier 1 --no-publicly-accessible --enable-performance-insights

aws rds describe-db-clusters --profile account-b --region ap-southeast-1 --db-cluster-identifier B2 --query 'DBClusters[0].{Status:Status,WriterEndpoint:Endpoint,ReaderEndpoint:ReaderEndpoint,Port:Port,Engine:Engine,Version:EngineVersion,KmsKeyId:KmsKeyId}'

Account B / target Region
    RDS -> Snapshots -> Manual
    select Account B owned DB cluster snapshot
    Actions -> Restore snapshot
    choose Aurora PostgreSQL cluster identifier B2
    choose VPC / subnet group / security groups
    choose cluster and instance parameter groups
    choose instance class or Serverless v2 capacity
    configure backup, logs, monitoring and deletion protection
    restore and wait until writer is available

only selected databases/schemas/tables are required
target major version differs but PostgreSQL dump compatibility is supported
schema needs intentional redesign
snapshot sharing/KMS constraints are unacceptable
database size fits the available downtime

pg_dump --host SOURCE-WRITER-ENDPOINT --port 5432 --username migration_user --dbname appdb --format directory --jobs 8 --no-owner --no-privileges --file appdb.dump

pg_restore --host TARGET-WRITER-ENDPOINT --port 5432 --username migration_user --dbname appdb --jobs 8 --no-owner --no-privileges --exit-on-error appdb.dump

roles and grants:
    --no-owner/--no-privileges avoids source role failures
    recreate approved target roles and grants separately

extensions:
    install supported target extension versions before restore when required

large database:
    use directory format + parallel jobs
    measure dump, transfer, restore, index build, analyze duration

consistency:
    pg_dump uses a consistent snapshot
    stop writes before dump if there is no CDC to catch later changes

statistics:
    run ANALYZE after restore before production traffic

Account A Aurora PostgreSQL A1
    -> DMS full load
    -> Account B Aurora PostgreSQL B2

Account A WAL changes
    -> DMS CDC
    -> INSERT / UPDATE / DELETE on B2

rds.logical_replication = 1
max_replication_slots = enough for DMS tasks plus safety margin
max_wal_senders = enough for active logical/physical senders

CREATE USER dms_user WITH LOGIN PASSWORD 'USE-SECRETS-MANAGER';
GRANT rds_replication TO dms_user;
GRANT CONNECT ON DATABASE appdb TO dms_user;
GRANT USAGE ON SCHEMA public TO dms_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dms_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO dms_user;

ALTER TABLE public.example REPLICA IDENTITY FULL;

Transit Gateway inter-Region/account attachments
VPC peering, including cross-account/cross-Region where supported
site-to-site VPN
controlled public endpoints as a last resort, with TLS and narrow security groups

database roles and grants
extensions
functions and procedures
views and materialized views
triggers
all indexes and constraints
sequence current values
DDL changes during CDC
large object behavior outside the selected DMS LOB mode

SELECT sequence_schema, sequence_name
FROM information_schema.sequences
ORDER BY sequence_schema, sequence_name;

DMS task status and table statistics
FullLoadThroughputRowsSource / FullLoadThroughputRowsTarget
CDCIncomingChanges
CDCLatencySource
CDCLatencyTarget
task logs and table validation failures
source replication slot retained WAL
Aurora FreeLocalStorage / transaction log disk usage signals
target CPU, connections, locks, IOPS and write latency

source publication in Account A
    -> network/TLS
    -> target subscription in Account B

CREATE PUBLICATION account_b_migration
FOR TABLE
  public.orders,
  public.order_items;

CREATE SUBSCRIPTION account_a_source
CONNECTION 'host=SOURCE-ENDPOINT port=5432 dbname=appdb user=replication_user sslmode=verify-full'
PUBLICATION account_b_migration
WITH (
  copy_data = true,
  create_slot = true,
  enabled = true
);

target tables created first
compatible column definitions
source logical replication enabled
network connectivity and TLS
primary key / replica identity for update/delete
manual DDL and sequence handling
subscription/slot monitoring
conflict policy when target is writable

Aurora MySQL Restore from S3:
    supports specific MySQL backup formats such as XtraBackup

Aurora PostgreSQL snapshot export to S3:
    produces analytics files
    cannot be passed to Restore from S3

Aurora PostgreSQL aws_s3 extension:
    can import/export selected table/query files
    does not restore a complete cluster

PostgreSQL pg_dump stored in S3:
    download near the target
    restore with pg_restore
    not restored by the RDS Restore from S3 wizard

database structure:
    database/schema/table/partition count
    indexes and constraints valid
    extensions, functions, views and triggers present

data:
    row counts for every critical table
    deterministic checksums or sampled key comparisons
    min/max timestamps and business totals
    large objects and NULL/encoding/timezone cases

runtime:
    representative read/write transactions
    connection pool and TLS behavior
    query plans after ANALYZE
    target CPU/IOPS/locks/latency

security:
    roles/grants/RLS
    KMS and Secrets Manager
    security groups and IAM database authentication
    audit/log exports

1. announce maintenance window
2. stop all source writers and background jobs
3. keep reads only if the application tolerates them
4. wait for DMS/logical replication lag to reach zero
5. verify final row/hash/sequence state
6. stop replication at a recorded position
7. switch secrets, endpoint or Route 53
8. run target smoke tests
9. resume writes only on B2
10. monitor errors, latency and replication remnants

export DMS_PROFILE='REPLACE_MIGRATION_PROFILE'
export DMS_REGION='REPLACE_MIGRATION_REGION'
export TASK_ARN='REPLACE_DMS_TASK_ARN'
aws dms describe-replication-tasks --profile "$DMS_PROFILE" --region "$DMS_REGION" --filters "Name=replication-task-arn,Values=$TASK_ARN" --query 'ReplicationTasks[].{Status:Status,Failure:LastFailureMessage,Stats:ReplicationTaskStats,Checkpoint:RecoveryCheckpoint}'
aws dms describe-table-statistics --profile "$DMS_PROFILE" --region "$DMS_REGION" --replication-task-arn "$TASK_ARN" --query 'TableStatistics[].{Schema:SchemaName,Table:TableName,State:TableState,Validation:ValidationState,Pending:ValidationPendingRecords,Failed:ValidationFailedRecords,Suspended:ValidationSuspendedRecords}'

network:
    prefer private connectivity
    restrict TCP 5432 to migration sources
    enforce PostgreSQL TLS

credentials:
    store in Secrets Manager
    separate snapshot/DMS/database roles
    rotate temporary migration users

KMS:
    cross-account encrypted snapshot requires customer managed keys
    key policy and IAM identity policy must both allow required operations
    AWS managed aws/rds key cannot be edited for cross-account sharing

cost:
    cross-Region snapshot/data transfer
    duplicate Aurora cluster runtime
    DMS replication capacity/serverless usage
    WAL retention and source storage growth
    S3 dump/snapshot export storage
    KMS requests

[ ] migration mode selected: snapshot / pg_dump / DMS / logical replication
[ ] same-account Global Database was not mistaken for cross-account replication
[ ] source/target engine versions and extensions are compatible
[ ] source size and migration duration were measured in rehearsal
[ ] network/TLS connectivity reaches both clusters
[ ] customer managed KMS policies support snapshot or S3 workflow
[ ] schema, roles, grants, functions, triggers, views and extensions are planned
[ ] tables without PK have an explicit replica identity decision
[ ] LOB mode and unsupported data types are tested
[ ] sequence values are synchronized before cutover
[ ] DMS/logical replication lag and retained WAL are monitored
[ ] row counts, checksums and application smoke tests pass
[ ] B2 is the only writer after cutover
[ ] rollback after B2 writes has a reverse-migration plan
[ ] old replication slots/tasks and temporary credentials are removed safely