Re: Migrating local PG instance to AWS RDS?

I've been supporting clients using AWS RDS and Aurora for a few years
now, and I love it, especially Aurora.  Stuff is so damn fast in Aurora
since they came up with the best hook for databases: eliminate database
logging, ie, no checkpoints, no WAL.  And of course built in solutions
for HA and DR with global databases.

Regards,
Michael Vitale

Victor Tan wrote on 5/27/2021 8:47 PM:
> 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.
>
> On Thu, May 27, 2021 at 7:50 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com
> <mailto:wells(dot)oliver(at)gmail(dot)com>> wrote:
>
> Anyone migrate to RDS and ultimately regret it?
>
> On Thu, May 27, 2021 at 3:56 PM MichaelDBA <MichaelDBA(at)sqlexec(dot)com
> <mailto:MichaelDBA(at)sqlexec(dot)com>> wrote:
>
> 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.
>>
>> Thanks.
>>
>> --
>> Wells Oliver
>> wells(dot)oliver(at)gmail(dot)com <mailto:wellsoliver(at)gmail(dot)com>
>
>
>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <mailto:wellsoliver(at)gmail(dot)com>
>
>
>
> --
> - Victor Tan