Restore specific table data from RDS backup

Steps to restore specific table data from RDS backup

First we need to export that table data from RDS snapshot(which is also stored in s3. but we can’t access it from s3) into s3 bucket in .parquet file format.

1. Click on RDS > Snapshots > System

• Select RDS snapshot name (rds:dev-db-date) and go to action > Export to Amazon S3.
• Fill name to identify your snapshot data.
• select export data type (partial) and fill your tablename as database.schema.table like (dev.public.tablenamewhichwewanttoexport).
• Choose your s3 bucket like (rdsbackup) name in which you want to export.
• Select IAM role to write export data file into S3(IAM role should have s3 write access).
• Encryption — generate KMS key ID if not.
• Click on Export to Amazon s3

You can check your export status in RDS > Snapshots > Exports in Amazon s3( it will take time to start).

2. After that go to your bucket (rdsbackup).

• by default, it will create a folder same name as your DB name (dev).
• Click on it, find your .parquet file and download it in your local.

RDS doesn't allow you to import .parquet file. so first we will convert .parquet file into csv then generate sqls from it and run generated sql on our new db table.

3. Convert .parquert file into csv

import pandas as pd
df = pd.read_parquet('filename.parquet')
df.to_csv('newfile.csv')
https://stackoverflow.com/questions/51215166/convert-parquet-to-csv

• install pandas | if after running this script it will throw some error and ask for other dependency install that too.

4. Read that csv file and generate your sql file( this is one method to generate sql file. you can do it in sublime also)

import csv

fn = lambda val : val.replace(r"'", r"''") if len(val) != 0 else "null"

with open('newfile.csv') as fle:
   reader = csv.reader(fle, delimiter=',')
   f = open("backuptable.sql", "w")
   x = 0
   for row in reader:
       if x == 0:
           x = x + 1
           continue
       sqlRow = "insert into backuptable (field1, field2, field3, field4, field5, field6, field7) values ({0},{1},{2},{3},{4},{5},{6});"
       sqlRow = sqlRow.format(fn(row[1]),"'"+fn(row[2])+"'","'"+fn(row[3])+"'",fn(row[4]),fn(row[5]),"null",fn(row[7]))
       sqlRow += "\n"
       f.write(sqlRow)
   f.close()

5. Create dummy table(to populate exported data backuptable) same fields as you current table and import sql file generated from above script on your database.

• psql databasename < backuptable.sql (for psql)

6. update your current table data with backup table data through sql according to your need.

Hope this will helpful.