RE: How to handle application connectivity change when moving from Oracle to Postgres in GCP

From: kunwar singh <krishsingh(dot)111(at)gmail(dot)com> Sent: Sunday, September 10, 2023 10:43 AM
To: pgsql-sql(at)lists(dot)postgresql(dot)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’s 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) – 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’re migrating to their platform).

Mike