When moving from Oracle Flashback operations to Aurora PostgreSQL, the best way to handle point-in-time recovery is by using Aurora snapshots. These snapshots serve as the main method for backing up and restoring your Aurora PostgreSQL databases.
Aurora offers two types of backups. The first is automated backups, which are always on and have no effect on database performance. The second is manual snapshots, which you can create anytime without impacting performance. Unlike Oracle Flashback, which works at the table level, Aurora snapshots are created at the cluster level.
If you want to minimize the downtime for your batch jobs, follow this simple process:
– Before starting your batch job, create a manual snapshot. You can do this using the AWS Command Line Interface or the AWS Management Console.
– If you need to restore the data, create a new Aurora cluster using the snapshot you took.
– Add any additional instances needed to the new cluster.
– Use PostgreSQL tools to copy specific tables from the new cluster back into your original database.
The main difference from Oracle is that Aurora snapshots are very quick and don’t interfere with your database. However, restoring a snapshot doesn’t happen directly in the same database; instead, you create a new cluster and work from there. You can restore to the exact moment when the snapshot was taken or any other time within your backup retention period.
For large tables that are hundreds of gigabytes, keep in mind the time it takes to create the new cluster from the snapshot and to copy the tables back to your original database. While this process isn’t as fast as Oracle Flashback Table, Aurora’s snapshot system is built to be quick and does not require traditional incremental backups.
It’s also wise to set up a suitable backup retention window when you create your database. This ensures you have enough points in time to recover your data when needed for batch jobs.
Remember, using snapshots this way gives you reliable recovery options and helps keep your database operations smooth.