Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lmOva-00082S-Kz for pgsql-admin@arkaria.postgresql.org; Thu, 27 May 2021 22:56:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lmOvZ-0001W2-Aj for pgsql-admin@arkaria.postgresql.org; Thu, 27 May 2021 22:56:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lmOvZ-0001Vt-1d for pgsql-admin@lists.postgresql.org; Thu, 27 May 2021 22:56:33 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lmOvW-0006op-RU for pgsql-admin@postgresql.org; Thu, 27 May 2021 22:56:32 +0000 Received: from [192.168.1.165] ([98.169.214.254]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MOxnN-1ljwc71CvX-006P5f; Fri, 28 May 2021 00:56:28 +0200 Subject: Re: Migrating local PG instance to AWS RDS? To: Wells Oliver Cc: pgsql-admin References: From: MichaelDBA Message-ID: Date: Thu, 27 May 2021 18:56:25 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.48 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------508EBE3D7BD47F3A67C4DC06" Content-Language: en-US X-Provags-ID: V03:K1:VE8kiqFEfvUKa8T3234s4yvWM789DByHYaFuj7wT/IoWpFdOIOM z1wD0BtkWVcDW3MqC6BSajtvc4H6EOSQAi4GD2+tZiBJCHUAYgqse0QVMNlvCd8Vjl9zEH3 SQteffspKfm3nYJAu6fHTAp7IIEyvKoHGTe8TKgqPwpTq7bWZlAPN2xMFi8fiH1nzL0LT9j cG/9rVi5QFHQi3IaS6uGQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:A+pHIXZ/Fq8=:TixYImWHBGzI8yzOww+du9 kqiuH40Qo+EpbKdiJm/RhfMAtT8P0WLAlFWqJk2vE4SfuOGiNbwwdJ7EiYXx4DYrHflfuWhzE vUALAHRarpyS6dy0x5l1Lx73u1i/Fzu9WZEWIiuZsFiixvJm/k2xI3GIv1rJ/p2rXtqw/jcsb Df4hsgEZcW/PR6xYVT566vnnSk/cKS7Yx2EH7NCeqpYY6W2+Zfe6ETt4YRykW8Avz0LobHfcS XdPHc7gQnWYH18jOv97hj5ljQW2Euj2iJhU2iqChL+VvAAtCaSCP/vGfEyXvy8RyuFkXHTU/g r0smWXWFOYa9vHGlgh+Q+a+yZGVTtFr2aLRFueFNq0lyKA9ooio9lFDyV0RMsVq5m7o7K1MQf 1IHfK9P6jD5RQLUqc6Tcc5+OoIBJUfsxKckDMTFQRgEtJOmc+gYs8J2oubnHjMPUqgUFdarDb C3LT47K/3SO8Y9u1k4+ARzsPUrfsw4K6FdUZPkU5T0Dh1urpCNWU2ZPUiYouEx3UF4inis0c4 6+8Gt/q09rl5xVQmzDe0Vs= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------508EBE3D7BD47F3A67C4DC06 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------508EBE3D7BD47F3A67C4DC06 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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.

--

--------------508EBE3D7BD47F3A67C4DC06--