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 1m0mvj-0006bb-Ex for pgsql-sql@arkaria.postgresql.org; Tue, 06 Jul 2021 15:24:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m0mvi-0006cw-Bq for pgsql-sql@arkaria.postgresql.org; Tue, 06 Jul 2021 15:24:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m0mvi-0006co-2x for pgsql-sql@lists.postgresql.org; Tue, 06 Jul 2021 15:24:10 +0000 Received: from premium22-4.web-hosting.com ([68.65.122.106]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m0mvd-0007tE-Sk for pgsql-sql@lists.postgresql.org; Tue, 06 Jul 2021 15:24:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=misuse.org; s=default; h=Content-Type:Cc:To:Subject:Message-ID:Date:From:In-Reply-To: References:MIME-Version:Sender:Reply-To:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=ZvInTeHi0GQyCOgAly+5hNO1KX9jE9+quMGjwssFwfE=; b=IBxDOlmQJ6KNGYIiBqkTa4dEvq xPTFq762/pnzG4JeSf44ETz6BRCRaokKYyUioisyejPap3LF9v0w0yUEdWfrIWWbgiaP/jS4HjxDf Bt3I/rB2C6AbdQc8GNnhuhHgGDB4w568v4FsoXOnlpwzkiWhOaDZLud9BWNlNlB1mg9Lt5fyWHkYg l9WPD+jLMFjTaoPJzd6MnBUkzTUwOWDqpw71rLZWVswwi2jLmMhuTCqSBVAC0IuOitdeIz/i2Sr7b h6pk+FzAD6JfBHtRPlVRXJAvvaCdVJee+ubjCt0uWx1ptHOs3kmwsWtKPrUIkPJn3TVckjzctpjCe el/RBxWg==; Received: from mail-qt1-f179.google.com ([209.85.160.179]:47026) by premium22.web-hosting.com with esmtpsa (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1m0mvU-0017EI-VS for pgsql-sql@lists.postgresql.org; Tue, 06 Jul 2021 11:24:02 -0400 Received: by mail-qt1-f179.google.com with SMTP id h2so6489931qtq.13 for ; Tue, 06 Jul 2021 08:23:56 -0700 (PDT) X-Gm-Message-State: AOAM532oBT6cLSUOnWqQUInyGvFBNKznOpLXakdGw3sFcNp8brXvXC9P rXmweQPVc7OmAlPL5Qye6nihEhYgvfvm5jCroes= X-Google-Smtp-Source: ABdhPJwfYzcrpQaK3aFBdK6NghqBBxmPy1+1dnclKSnKX0arx7wxUr+QekKOo35KcFfmSez8Oz5XRIZmjzYk+mZ3m18= X-Received: by 2002:a05:622a:412:: with SMTP id n18mr18214334qtx.77.1625585035173; Tue, 06 Jul 2021 08:23:55 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Tue, 6 Jul 2021 08:23:46 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: Substitute for table variable and data migration approach To: Avadhut Narayan Joshi Cc: "pgsql-sql@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000092e3a905c6760286" X-OutGoing-Spam-Status: No, score=5.0 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - premium22.web-hosting.com X-AntiAbuse: Original Domain - lists.postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - misuse.org X-Get-Message-Sender-Via: premium22.web-hosting.com: authenticated_id: science@misuse.org X-Authenticated-Sender: premium22.web-hosting.com: science@misuse.org X-Source: X-Source-Args: X-Source-Dir: X-From-Rewrite: unmodified, already matched List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000092e3a905c6760286 Content-Type: text/plain; charset="UTF-8" On Mon, Jul 5, 2021 at 4:05 AM Avadhut Narayan Joshi < AJoshi7@sensiaglobal.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 --00000000000092e3a905c6760286 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Mon, Jul 5, 2021 at 4:05 AM Avadhu= t Narayan Joshi <AJoshi7@sen= siaglobal.com> wrote:

Hello Geeks ,

=C2=A0

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 ?

=C2=A0

Also any advice / suggestions on tool which can be used to mi= grate 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

=C2=A0

Please give Suggestions / recommendations .

=C2=A0

Hel= lo,

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


If I needed add= itional professional help with such a migration, I'd consider hiring th= em (I don't know exactly what the company does, but they seem to have l= ong 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 performa= nce requires different optimizations), you can presumably scale your PG rea= d replicas onto multiple servers until you achieve the horizontal scaling p= erformance you need. Of course, query and index optimization is always smar= t, to reduce costs and single query latency.
  2. By re-start logic, I t= hink you are talking about detecting server failures and restarting servers= automatically. Depending on your sophistication in managing servers, you m= ight look into either AWS RDS or AWS Aurora to give you uptime and backup s= upport. You could consider AWS RDS until your team gains the expertise to t= ake 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 cre= ate lower TCO.
  3. Logging is trivial in AWS RDS, but logs aren't t= oo tough in any environment for Postgres. You should consider a log aggrega= tion service if you have a large or complex setup. I've had good experi= ences with DataDog.
  4. AWS RDS is about as easy to deploy into product= ion as possible. Otherwise, consider a container approach. Docker+Kubernete= s seems to be pretty popular these days, though the setup and learning curv= es are a bit steep for those without devops background. Both Docker and RDS= give you a "same as development" experience when dealing with da= tabases throughout your development/deployment lifecycle.
Goo= d luck with your migration and hopefully others will share advice.=C2=A0

Steve

--00000000000092e3a905c6760286--