pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMigrating local PG instance to AWS RDS?
9+ messages / 6 participants
[nested] [flat]
* Migrating local PG instance to AWS RDS?
@ 2021-05-27 21:49 Wells Oliver <wells.oliver@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: Wells Oliver @ 2021-05-27 21:49 UTC (permalink / raw)
To: pgsql-admin
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.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrating local PG instance to AWS RDS?
@ 2021-05-27 21:58 Ron <ronljohnsonjr@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Ron @ 2021-05-27 21:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 5/27/21 4:49 PM, Wells Oliver wrote:
> 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.
What version?
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrating local PG instance to AWS RDS?
@ 2021-05-27 21:59 Wells Oliver <wells.oliver@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Wells Oliver @ 2021-05-27 21:59 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
13.2
On Thu, May 27, 2021 at 2:58 PM Ron <ronljohnsonjr@gmail.com> wrote:
> On 5/27/21 4:49 PM, Wells Oliver wrote:
> > 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.
>
> What version?
>
>
> --
> Angular momentum makes the world go 'round.
>
>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrating local PG instance to AWS RDS?
@ 2021-05-27 22:44 Ron Nollen <nollenr@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Ron Nollen @ 2021-05-27 22:44 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Data Migration Service (DMS) is one way to do it. You can do a full with
CDC (change data capture) and then cut over when you are ready.
https://aws.amazon.com/getting-started/hands-on/move-to-managed/migrate-postgresql-to-amazon-rds/
Cheers,
Ron.
On Thu, May 27, 2021 at 2:59 PM Wells Oliver <wells.oliver@gmail.com> wrote:
> 13.2
>
> On Thu, May 27, 2021 at 2:58 PM Ron <ronljohnsonjr@gmail.com> wrote:
>
>> On 5/27/21 4:49 PM, Wells Oliver wrote:
>> > 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.
>>
>> What version?
>>
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>>
>>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrating local PG instance to AWS RDS?
@ 2021-05-27 22:56 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: MichaelDBA @ 2021-05-27 22:56 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
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.oliver@gmail.com <mailto:wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrating local PG instance to AWS RDS?
@ 2021-05-27 23:49 Wells Oliver <wells.oliver@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 1 reply; 9+ messages in thread
From: Wells Oliver @ 2021-05-27 23:49 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: pgsql-admin
Anyone migrate to RDS and ultimately regret it?
On Thu, May 27, 2021 at 3:56 PM MichaelDBA <MichaelDBA@sqlexec.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.oliver@gmail.com <wellsoliver@gmail.com>
>
>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrating local PG instance to AWS RDS?
@ 2021-05-28 00:47 Victor Tan <victortcs@gmail.com>
parent: Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Victor Tan @ 2021-05-28 00:47 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: MichaelDBA <MichaelDBA@sqlexec.com>; pgsql-admin
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.oliver@gmail.com> wrote:
> Anyone migrate to RDS and ultimately regret it?
>
> On Thu, May 27, 2021 at 3:56 PM MichaelDBA <MichaelDBA@sqlexec.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.oliver@gmail.com <wellsoliver@gmail.com>
>>
>>
>>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <wellsoliver@gmail.com>
>
--
- Victor Tan
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrating local PG instance to AWS RDS?
@ 2021-05-28 00:56 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Victor Tan <victortcs@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: MichaelDBA @ 2021-05-28 00:56 UTC (permalink / raw)
To: Victor Tan <victortcs@gmail.com>; +Cc: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
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.oliver@gmail.com
> <mailto:wells.oliver@gmail.com>> wrote:
>
> Anyone migrate to RDS and ultimately regret it?
>
> On Thu, May 27, 2021 at 3:56 PM MichaelDBA <MichaelDBA@sqlexec.com
> <mailto:MichaelDBA@sqlexec.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.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
>
>
> --
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>
>
>
>
> --
> - Victor Tan
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Migrating local PG instance to AWS RDS?
@ 2021-05-28 02:45 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Wells Oliver <wells.oliver@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Laurenz Albe @ 2021-05-28 02:45 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; pgsql-admin
On Thu, 2021-05-27 at 14:49 -0700, Wells Oliver wrote:
> Anyone have a handy write-up or useful experience to share in migrating local PG databases to Amazon's RDS?
Kind of a general remark:
Hosting providers make it easy to move the database to their services.
The real trouble starts once you decide you want to get your database out again.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2021-05-28 02:45 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-27 21:49 Migrating local PG instance to AWS RDS? Wells Oliver <wells.oliver@gmail.com>
2021-05-27 21:58 ` Ron <ronljohnsonjr@gmail.com>
2021-05-27 21:59 ` Wells Oliver <wells.oliver@gmail.com>
2021-05-27 22:44 ` Ron Nollen <nollenr@gmail.com>
2021-05-27 22:56 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-05-27 23:49 ` Wells Oliver <wells.oliver@gmail.com>
2021-05-28 00:47 ` Victor Tan <victortcs@gmail.com>
2021-05-28 00:56 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-05-28 02:45 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox