Don’t Use AWS AMI To Backup Your EC2 Database Server
I have started my career to handle the databases which all are in the Cloud. Most of the database servers are in AWS EC2. AMI is one the simplest and great feature to take a VM level backup and restore it when a disaster happens. But DBAs never depend on the VM or SAN backups, so I have configured native backups for all of my database servers. But I used to choose to restore the AMI whenever we need a Dev/Test server. Because restoring a huge database will take more time and its pretty expensive operation. So I just drop the existing Dev DB server and launch it from the AMI.
But a few weeks before, I was working with a PostgreSQL migration process from EC2 to RDS using DMS(AWS Database Migration Service). But the challenge was the EC2 PostgreSQL is 9.2. DMS supports the source as PostgreSQL 9.3+. So I decided to upgrade the PostgreSQL version to 9.6 on a Dev Server. And this is what happened.
- I have a Master-Slave setup, so I took the AMI of the Master instance.
- Launched a new instance from the AMI.
- Upgrade the PostgreSQL version to 9.6.
- Then started the DMS to migrate the data to RDS.
But it didn’t give the expected the Results. I got the below error in few tables.
ERROR: duplicate key value violates unique constraint "table_Primary_key", DETAIL: Key (id, newid)=(136, 1134) already exists., CONTEXT: COPY table.
Read The Full Article Here: https://www.sqlgossip.com/dont-use-aws-ami-backup-ec2-database-server/