how to migrate very large database (postgresql engine) around 64 tb from EC2 on aurora postgreql

For migrating a very large database (around 64 TB) from EC2 to Aurora PostgreSQL with minimal downtime, you have several options:

1. Migration with continuous replication: This approach is particularly effective for large databases. You would first load baseline data from your EC2 PostgreSQL database to Aurora PostgreSQL, and then enable replication to catch up with ongoing changes. This can be done using:

• PostgreSQL native tools
• AWS Database Migration Service (AWS DMS)
• Third-party tools

2. Hybrid approach: Use native PostgreSQL tools (like pg_dump and pg_restore) for the initial full load, and then use AWS DMS for ongoing replication. This combines the strengths of both methods.

3. Multiphase migration: Break down the migration of thousands of tables into multiple phases. For example, you could move a set of tables with no cross-join queries each weekend until the entire database is migrated. Note that this requires your application to connect to two databases simultaneously during the migration period.

Important factors to consider:

1. Database cleanup: Before migration, identify and remove unused tables and data. Many large databases contain tables that remain unused or backup copies that developers forgot to drop. This is an opportunity to clean up your database by dropping unused tables or archiving them to another database.

2. Static tables: If your database has large static tables with reference data, consider migrating these tables first before moving your active dataset. You can use AWS DMS to copy tables selectively or export and import these tables manually.

3. Size limitations: While Aurora PostgreSQL supports up to 128 TiB of storage, be aware of any size limitations in your migration tools or processes.

4. Performance impact: Consider the performance impact on your source database during migration, especially when using replication methods.

5. Data validation: Ensure data integrity by validating that all data has been correctly migrated.

6. Application changes: Depending on the migration strategy, you might need to modify your application to handle the transition period.

For non-PostgreSQL source databases, AWS DMS would be the primary tool for migration, as it can handle heterogeneous migrations while providing continuous replication capabilities to minimize downtime.
Sources
Planning your database migration process - Migrating Your Databases to Amazon Aurora
Migrating large databases to Amazon Aurora - Amazon Aurora MySQL Migration Handbook
Migrating PostgreSQL Databases to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL - Database Migration Guide
Migrating data to Amazon Aurora with PostgreSQL compatibility - Amazon Aurora

Looking at the size of DB on Ec2 , consider DMS. It will give minimal downtime by keeping teh source up and running at all times. Once all the records are migrated from EC2 to Aurora Postgres DB, you can redirect client connects (after testing first) to connect to Aurora Postgres DB.

Source : https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Prerequisites DMS Best Practices : https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html Target : https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html KC article : https://repost.aws/knowledge-center/dms-migrate-postgresql-to-postgresql