Didn't regret but split between RDS and EC2. Those we kept on EC2 were any of the following reasons:
1. As someone else mentioned, extensions that were not supported in RDS
2. Specific tunings/config by admin not possible ( don't know if still true currently )
3. Wanted the latest version of Postgres (e.g. security related fixes)
4. Perception that RDS was slower under our workloads (you want to test your own)
5. Difficulty to move data back out of RDS into an EC2 (i.e. can't replicate back if we change our minds - especially if (4) was confirmed)
6. Suspicion that some features available in an EC2 install would not be available for a while (months) in RDS - especially if meant that we would save money by not having to upsize our RDS instance
Not that we didn't use RDS, we did but we also chose to stay on EC2.
Anyone migrate to RDS and ultimately regret it?
A few things things come to mind...
1. Check what extensions you are using locally and see if they are supported in RDS and implement a work-around if possible.
2. Access control in RDS is a bit different considering how AWS implements the superuser. It is really a subset of a superuser, hence, called rds_superuser. The problem with that is that if you have multiple rds_superusers creating objects then be ready to deal with the situation that rds_superusers cannot control objects created by other rds_superusers. You can get around these things awkwardly with REASSIGN and DROP OWNED BY.
3. Make sure you are not using LOs (Large Objects) for blob data. Use bytea. Restrictions and performance hits in RDS for using LOs.
4. You are restricted with respect to certain system tables:
pg_shadow, pg_user (no password access) , pg_roles, pg_authid and more. Also, you cannot get cluster info (roles, etc.) since you are not allowed to execute "pg_dumpall -g".
Regards,
Michael Vitale
Wells Oliver wrote on 5/27/2021 5:49 PM:Anyone have a handy write-up or useful experience to share in migrating local PG databases to Amazon's RDS?
I was thinking of making a failover in RDS and promoting it after replication or something to that effect, but I thought I would see if there's some more canonical approach.
--
--