AWS doesn’t make it quite simple to restore just one DB on a RDS. I came up with below plans for DR and would like to know your feedback.
- Our current RDS-T has D1, D2, D3 and D4 postgresql databases
- Because of some human error, we want to restore only DB D1 and D2 as T-1 and keep data in other Databases as it is (T).
- We will decide the what snapshot in AWS we want to restore from.
- Create a new RDS-(T-1) instance with old version of all DBs: D1, D2, D3 and D4.
- Our goal here is to restore only D1 and D2 as of T-1.
- For D1, since this is a small DB, I will dumb the DB file from RDS-(T-using pg_dump utility and then restore it back to RDS-T using pg_restore utility. this should be fairly quick
- For big DBs like D2 ( 200 GB), I would use a Data Migration service .
- I will design a DMS beforehand and just change the endpoint . in this case Source : D2 on RDS-(T-1) and destination : D2 on RDS-(T).
- Just run this service to restore data on D2 on RDS-T
- We can always restore only one/two/n tables with this service. Only modification we need to do is change the Jason file that has the mapping defined.
- Once the DB on RDS-T is restored, we can just get rid of RDS-(T-1) .
is there a better way to approach this? Do you see any pitfalls with this approach?