Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qfPFI-00AsUF-DV for pgsql-sql@arkaria.postgresql.org; Sun, 10 Sep 2023 18:33:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qfPFG-00BFRa-7T for pgsql-sql@arkaria.postgresql.org; Sun, 10 Sep 2023 18:33:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qfPFF-00BFRM-NN for pgsql-sql@lists.postgresql.org; Sun, 10 Sep 2023 18:33:17 +0000 Received: from mailtransmit05.runbox.com ([2a0c:5a00:149::26]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qfPF7-004J2Q-69 for pgsql-sql@lists.postgresql.org; Sun, 10 Sep 2023 18:33:16 +0000 Received: from mailtransmit02.runbox ([10.9.9.162] helo=aibo.runbox.com) by mailtransmit05.runbox.com with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.93) (envelope-from ) id 1qfPF2-008bt9-HI for pgsql-sql@lists.postgresql.org; Sun, 10 Sep 2023 20:33:04 +0200 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=runbox.com; s=selector1; h=Content-Type:MIME-Version:Message-ID:Date:Subject:In-Reply-To :References:To:From; bh=HiCInzBNnYqgeemzdDAEkbF75kejsBVLhzePkJG57ZE=; b=w/K1O Dj1VdE5ioCKZbGv7KOMVeQb82Bx9a7tE2AoLFGXDiqImz4bDWNJPo2Yf0sN3DeWJarZXfOyUULnGa kszdWLbYPOh2UJimaQE55L1XqcXQO+fgrrf9abGKiE25ybKlvaRZfyaLb4W7QQYunn18jwcbpl6Pf 4R53zxdRwGcRIoxDMODnCWqSb22VHmmz4ucV6DSHk5Keewr6SbgCQF/0VK/srra7vqSsfwO5tyZXu dhCvJZeFEJ++7Fsy4EMNbUBx4n+v0t/eMslqBkTVqg2RRsfDrtUyBtepWKwEIXX43jC3D71FxgAuZ vPT1FSFNPNKPlA4Oc9tuLUMauA1XQ==; Received: from [10.9.9.73] (helo=submission02.runbox) by mailtransmit02.runbox with esmtp (Exim 4.86_2) (envelope-from ) id 1qfPF1-0003Io-NC; Sun, 10 Sep 2023 20:33:03 +0200 Received: by submission02.runbox with esmtpsa [Authenticated ID (300526)] (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) id 1qfPEr-0004XE-3j; Sun, 10 Sep 2023 20:32:53 +0200 From: "Mike Sofen" To: "'kunwar singh'" , References: In-Reply-To: Subject: RE: How to handle application connectivity change when moving from Oracle to Postgres in GCP Date: Sun, 10 Sep 2023 11:32:50 -0700 Message-ID: <09d101d9e415$35b46040$a11d20c0$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_09D2_01D9E3DA.895A1C20" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQH6sz+jaREOAuyLlPUIMttPDhPRrK/Swcyg Content-Language: en-us List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_09D2_01D9E3DA.895A1C20 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable From: kunwar singh Sent: Sunday, September = 10, 2023 10:43 AM To: pgsql-sql@lists.postgresql.org Subject: How to handle application connectivity change when moving from = Oracle to Postgres in GCP =20 Hi Listers, =20 Say we have this App->DB connectivity Model. =20 APP1 Contains synonyms which point to objects under APP1. =20 APPOWN Contains the tables, indexes =20 And it is a Java application that connects to APP1. =20 Now when the database is switched from Oracle 19c onprem to CloudSQL = Postgres say v15. Questions that I have: =20 1. What needs to be done at the database end in terms of schema setup in = order to ensure the application is able to connect to the relevant = tables without any concerns?=20 2. Do I need to create users APP1 and APPOWN in postgres? If so, what = privileges are required to be assigned to these? 3. How to tie them with service accounts? 4. With which username/service account does the application make = connection to the database? =20 I still don't understand user/schema concepts in postgres tbh as I'm = still a noob :)=20 =20 =20 =20 --=20 Cheers, Kunwar =20 =20 Kunwar,=20 Since you mentioned CloudSQL (Google Cloud=E2=80=99s db platform for = postgres, mysql, and sql server), I recommend contacting your rep at = Google Cloud on this topic. They just went GA on their DMS product = (Data Migration Service) for Oracle to CloudSQL-Postgres migrations (and = AlloyDB shortly) =E2=80=93 pretty good timing. DMS converts schema and = code, understands most permissions and security pieces, and most = importantly, has great tech support. Usually there is no charge for = using DMS (Google underwrites the cost since you=E2=80=99re migrating to = their platform). =20 =20 Mike ------=_NextPart_000_09D2_01D9E3DA.895A1C20 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

From: kunwar singh = <krishsingh.111@gmail.com> =C2=A0Sent: Sunday, September = 10, 2023 10:43 AM
To: = pgsql-sql@lists.postgresql.org
Subject: How to handle = application connectivity change when moving from Oracle to Postgres in = GCP

 

Hi = Listers,

 

Say we have = this App->DB connectivity Model.

 

APP1

Contains synonyms which = point to objects under APP1.

 

APPOWN

Contains the tables, = indexes

 

And it is a Java = application that connects to APP1.

 

Now when the database is = switched from Oracle 19c onprem to CloudSQL Postgres say = v15.

Questions that I = have:

 

1. What needs to be done at = the database end in terms of schema setup in order to ensure the = application is able to connect to the relevant tables without any = concerns? 

2. Do I need to create users APP1 and = APPOWN in postgres? If so, what privileges are required to be = assigned to these?

3. How to tie them with service = accounts?

4. With which username/service account does = the application make  connection to the = database?

 

I still don't understand = user/schema concepts in postgres tbh as I'm still a noob = :) 

 

 

 

--

Cheers,
Kunwar

 

 

Kunwar, =

Since you mentioned CloudSQL (Google = Cloud=E2=80=99s db platform for postgres, mysql, and sql server), I = recommend contacting your rep at Google Cloud on this topic.=C2=A0 They = just went GA on their DMS product (Data Migration Service) for Oracle to = CloudSQL-Postgres migrations (and AlloyDB shortly) =E2=80=93 pretty good = timing.=C2=A0 DMS converts schema and code, understands most permissions = and security pieces, and most importantly, has great tech support.=C2=A0 = Usually there is no charge for using DMS (Google underwrites the cost = since you=E2=80=99re migrating to their platform).=C2=A0 =

 

Mike

------=_NextPart_000_09D2_01D9E3DA.895A1C20--