A company is migrating its 200 GB on-premises PostgreSQL database to Amazon Aurora PostgreSQL. The original database columns include NOT NULL and foreign key constraints. A database administrator…

AWS Schema Conversion Tool (SCT) and AWS Database Migration Service (DMS) are primarily designed for heterogeneous database migrations, where schema and code conversion is needed. For a homogeneous PostgreSQL to Aurora PostgreSQL migration, native tools are often more direct and efficient as no schema conversion is required.

Creating an AWS Lambda function to migrate 200 GB of data is an inefficient and overly complex approach for a large database migration and is not a recommended best practice.

For homogeneous migrations (PostgreSQL to PostgreSQL), pg_dump and pg_restore are the native, best-practice tools. They effectively capture and restore schema definitions, including NOT NULL and foreign key constraints, and data, ensuring a faithful migration to a compatible target like Aurora PostgreSQL. This approach is reliable for preserving all PostgreSQL-specific features.

An Aurora PostgreSQL read replica can only be created from an existing Aurora PostgreSQL cluster or an RDS PostgreSQL instance within AWS, not directly from an on-premises PostgreSQL database.