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
Performant on large DB size > 500 GB
Should have re-start logic
Should have error logging capabilities
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:
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:
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.
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.
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.
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.
