Re: Substitute for table variable and data migration approach

On Mon, Jul 5, 2021 at 4:05 AM Avadhut Narayan Joshi <
AJoshi7(at)sensiaglobal(dot)com> wrote:

> Hello Geeks ,
>
>
>
> As in SQL Server we have table variable and it can be passed as a
> parameter to a stored procedure . Which is the best alternative for it in
> PostgreSQL ?
>
>
>
> Also any advice / suggestions on tool which can be used to migrate data
> from SQL Server / Oracle to PostgreSQL ?
>
> Solution should be
>
> 1. Performant on large DB size > 500 GB
> 2. Should have re-start logic
> 3. Should have error logging capabilities
> 4. Easy to deploy/use in Prod environment
>
>
>
> Please give Suggestions / recommendations .
>
>
>
Hello,

You may have noticed Bruce Momjian from EnterpriseDB posting regularly to
this list. That organization seems to have particular specializations to
help with migrations from Oracle to PG. Here's a starting place:

https://www.enterprisedb.com/blog/the-complete-oracle-to-postgresql-migration-guide-tutorial-move-convert-database-oracle-alternative

If I needed additional professional help with such a migration, I'd
consider hiring them (I don't know exactly what the company does, but they
seem to have long standing roots in the PG open community, which is a good
start, IMO).

Your four criteria seem to imply a few things:

1. Performant: assuming this is read performance (write performance
requires different optimizations), you can presumably scale your PG read
replicas onto multiple servers until you achieve the horizontal scaling
performance you need. Of course, query and index optimization is always
smart, to reduce costs and single query latency.
2. By re-start logic, I think you are talking about detecting server
failures and restarting servers automatically. Depending on your
sophistication in managing servers, you might look into either AWS RDS or
AWS Aurora to give you uptime and backup support. You could consider AWS
RDS until your team gains the expertise to take over management (and
possibly reduce costs). My company uses RDS and we just don't hire the
staff who would do that stuff, and it seems to create lower TCO.
3. Logging is trivial in AWS RDS, but logs aren't too tough in any
environment for Postgres. You should consider a log aggregation service if
you have a large or complex setup. I've had good experiences with DataDog.
4. AWS RDS is about as easy to deploy into production as possible.
Otherwise, consider a container approach. Docker+Kubernetes seems to be
pretty popular these days, though the setup and learning curves are a bit
steep for those without devops background. Both Docker and RDS give you a
"same as development" experience when dealing with databases throughout
your development/deployment lifecycle.

Good luck with your migration and hopefully others will share advice.

Steve