Thread: Seeking help extricating data from Amazon RDS Aurora/Postgres

This particular database is currently about 3.5TB.

We are sensitive to this database being down for an extended period of time as it will adversely impact some subsystems within our application.  We're hoping to find a means to migrate within a 4 hour window. We've tried multiple approaches:

• Using Amazon DMS to copy the data
  • despite doing parallelization, the total time would be around 89 hours
    
• Using FiveTran (great tool!) to copy data
  • using this tool was that it would take about 86 hours to complete.
    
• Using a database dump and restore
  • Doing a backup from RDS's replica fails, due to holding a database lock for too long.
    

The ideal situation would be to have a streaming replica from Amazon RDS, but you can only create a replica that is hosted by AWS, and have no access to the WAL logs...

Wondering if any of the other members of this LISTSERV have tried migrating their data off of Amazon RDS Aurora Postgres with success.

Thanks in advance for any pointers,

Bill

> On Jan 29, 2024, at 11:22, Bill Mitchell <bill@publicrelay.com> wrote:
>
> Wondering if any of the other members of this LISTSERV have tried migrating their data off of Amazon RDS Aurora
Postgreswith success. 

Any logical-replication based solution (DMS, fivetran, in-core logical replication) will handle the problem.  Although
theoverall migration time is high, the source database is in production for the entire time, so the actual downtime is
small.

DMS is not a great solution for going PostgreSQL-to-PostgreSQL, as it has limited data type support.  In-core logical
replicationis probably the best solution. 

One thing to be aware of is when replication starts, it first needs to copy over the existing data in the tables *and*
capturechanges that occur during that copy.  Because of that, it will retain WAL created during that copy.  That can be
quitea bit of disk space, so planning for that is important. 

We are attempting to extract one of our database from Amazon RDS Aurora/Postgres to another PostgreSQL cluster that is running directly on EC2 instances.