01. Migrating AWS RDS data encrypted with KMS key from the source account to the target account
Requirement: Copying encrypted Amazon RDS snapshot from source account to target account and restoring it with the existing running Amazon RDS.
Challenges: You can’t share an RDS snapshot that’s encrypted using the default AWS KMS encryption key.
Why Not Amazon Data Migration Services: The main issue with DMS was when creating tables does not copy across primary keys, foreign keys, indexing, etc., and where the tables are created already DMS docs recommend disabling PKs, FKs, indexes, etc. The validation step of DMS was showing that the copied data wasn’t a 100% match, possibly because the row IDs were being changed.
Resolution:
Allowing access to the target account on the AWS KMS key of the source account
1. Log in to the source account, and then open the AWS KMS console in the same AWS Region as the DB snapshot.
2. Choose Customer managed keys from the navigation pane.
3. Choose the name of your customer-managed key, or choose to Create key, if you don’t yet have one. For more information, see Creating keys.
4. From the Key administrator’s section, Add the AWS Identity and Access Management (IAM) users and roles who can administer the AWS KMS key.
5. From the Key user’s section, Add the IAM users and roles who can use the AWS KMS key (KMS key) to encrypt and decrypt data.
6. In the Other AWS accounts section, choose to Add another AWS account and then enter the AWS account number of the target account. For more information, see Allowing users in other accounts to use a KMS key.
Copy and share the snapshot (In the source account)
1. Open the Amazon RDS console, and then choose Snapshots from the navigation pane.
2. Choose the name of the snapshot that you created, choose Actions, and then choose Copy Snapshot.
3. Choose the same AWS Region that your KMS key is in, and then enter a New DB Snapshot Identifier.
4. In the Encryption section, choose the KMS key that you created.
5. Choose Copy Snapshot.
6. Share the copied snapshot with the target account.
Copy the shared DB snapshot (In the target account)
1. Log in to the target account, and then open the Amazon RDS console.
2. Choose Snapshots from the navigation pane.
3. From the Snapshots pane, choose the Shared with Me tab.
4. Select the DB snapshot that was shared.
5. Choose Actions, and then choose Copy Snapshot to copy the snapshot into the same AWS Region
6. Enter the KMS key ARN from the source account (option — Enter a key ARN)
7. Snapshot is now available in the target account.
Challenge 2: Restoring a snapshot to the existing Postgres RDS (In Target account)
1. Create a temporary Postgres RDS instance from the copied snapshot.
a. Go to snapshot from the navigation.
b. From Action, select Restore snapshot and select the required configuration.
c. Restore DB instance.
2. Spin up an EC2 server with psql and pg_dump packages installed and connect to the RDS instance.
a. psql -h <rds-endpoint> -p5432 -U<user-name> -d postgres
Note — test with your existing target RDS connection
3. Creating the backup.sql dump: Use the pg_dump command to create the backup of the temporary RDS instance in the ec2 server (Bastion-server).
Note → Make sure your ec2 server is having enough to backup the data
a. nohup pg_dump postgresql://<temp-username>:<password>@<host>:<port>/<database> > backup.sql &
Note — <temp-username>/<password> → your source username and password
<host> → your temp rds endpoint
<database> → name of the database (project_db_environment)
4. Restoring the backup.sql using psql command: Restore the backup to the existing RDS instance.
a. nohup psql postgresql://<username>:< password>@<host>:<port>/<database> -f backup.sql &
Note — <username>/<password> → your destination db username and password
<database> → name of the database (project_db_environment)
Note:
1. If the existing RDS is having old data in it, then after connecting to RDS
a. DROP DATABASE <data_base_name>;
b. CREATE DATABASE <data_base_name>;
2. Some handy commands to verify:
a. \l — list database
b. \c — switch to a particular database
c. \dt –list database tables
d. \du –list users
e. \dn — list schema
f. \l+ — show the size of the tables