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 1uFbDh-006PTh-8p for pgsql-general@arkaria.postgresql.org; Thu, 15 May 2025 16:14:06 +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 1uFbDf-00FSaD-Iw for pgsql-general@arkaria.postgresql.org; Thu, 15 May 2025 16:14:03 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uFbDe-00FSZy-7u for pgsql-general@lists.postgresql.org; Thu, 15 May 2025 16:14:03 +0000 Received: from fhigh-a2-smtp.messagingengine.com ([103.168.172.153]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uFbDb-001wfw-0i for pgsql-general@lists.postgresql.org; Thu, 15 May 2025 16:14:01 +0000 Received: from phl-compute-04.internal (phl-compute-04.phl.internal [10.202.2.44]) by mailfhigh.phl.internal (Postfix) with ESMTP id ACED511400A1; Thu, 15 May 2025 12:13:58 -0400 (EDT) Received: from phl-mailfrontend-02 ([10.202.2.163]) by phl-compute-04.internal (MEProxy); Thu, 15 May 2025 12:13:58 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm2; t=1747325638; x=1747412038; bh=vmaNX6IWAQPSoHRPTbIYExGHF+Ujmph4FnslwP+b9YU=; b= OC2mW1BDVe55utpsbQHmig+Y4U80YqDu95niaHhLscDADM+FUkW9ulvSrxk9dBpg 0EJeXfoL3a0NsGpvcxuAnv0cwOBfQ/4diS6v8ZUwAEt6hpSS0Gt6MyhEfoKk6n2E COBZc3cNoGoIc71y8bSf4HO96Qxs9el37qO4ySmroFYBLvel/3YKegY/xJgu3kWa fW1+I7oNubX8f42vmMNYCw/yNTQN686+Jnz9c9615Non7wbqjx/Q5DOaSQSjvhlS VYXHX5Engi+e3y8qjnC5i+evT+sv3yZIirNuiQYTRFh76VsLwUpqKh+tiUcuPlJj jNbuv4U4VEPkmeo7X8Ojwg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:subject:subject:to:to:x-me-proxy:x-me-sender :x-me-sender:x-sasl-enc; s=fm3; t=1747325638; x=1747412038; bh=v maNX6IWAQPSoHRPTbIYExGHF+Ujmph4FnslwP+b9YU=; b=L8iDHP6Gb9XuIsSbg jUOcyw+sPxKkCOa1TvO703F73+2wHV3kFNTZmQWyAsnCrM4T2BAOmlkN9fJjcxH4 iI9ZrHOc3n8s0NreMdMnLRlOH66kygk2QhjlB/kIycvZqsE3nDSDiRcaQ2kV58SD Cq+1ZlHVvrSPaXfqYy0pcMmOyAPHyPFbTGqFMCme0xLPdkkvVTt94niIkKGXA9JW /eec7uY+VDz8WqAv/5jen+5HLb9L6g7b9GquDkShrdfZ2ZNJrLKy5Qa//ZFyfHEH eS4SYHOzMurDT3PnWAz8s9E73+aTQzyo22OomGNDSb9q/etI8gFrPDtneQEmKTru oDvTg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefvddrtddtgdefuddtfedvucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdggtfgfnhhsuhgsshgtrhhisggv pdfurfetoffkrfgpnffqhgenuceurghilhhouhhtmecufedttdenucesvcftvggtihhpih gvnhhtshculddquddttddmnecujfgurhepkfffgggfuffvfhfhjggtgfesthekredttddv jeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvg hrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpeeifeekkeeiteeuhfdv keegffegfffhtddvtddtveekheehvdevieejudevteduffenucffohhmrghinhepshhqih httghhrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhf rhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomhdpnhgspghrtg hpthhtohepvddpmhhouggvpehsmhhtphhouhhtpdhrtghpthhtohepvhgvvghmrgdttddt tdesghhmrghilhdrtghomhdprhgtphhtthhopehpghhsqhhlqdhgvghnvghrrghlsehlih hsthhsrdhpohhsthhgrhgvshhqlhdrohhrgh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 15 May 2025 12:13:57 -0400 (EDT) Message-ID: <060c8abd-7448-4af9-8265-0ca8b81ccf86@aklaver.com> Date: Thu, 15 May 2025 09:13:56 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: How to have a smooth migration To: veem v , pgsql-general References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 5/14/25 23:09, veem v wrote: > Hi, > Its postgres database behind the scenes. > > We have a use case in which the customer is planning to migrate data > from an older version (V1) to a newer version (V2). For V2, the tables > will be new, but their structure will be similar to the V1 version with > few changes in relationship might be there. We want to have this > migration approach happen in multiple phases in which each time the > delta data from version V1 will be moved to version- V2 and then final > cutover will  happen to V2 if all looks good or else rollback to V1. The > tables are smaller in size like max ~100K records in tables. > > My question is, is it a good idea to have an approach in which we will > have procedures created to move the delta data in every phase and > schedule those using some tasks for each table. Or any other strategy > should we follow? This is what Sqitch(https://sqitch.org/) was designed for. The biggest issue is that the data will be incrementing while you do the structural changes. How you handle that is going to depend on the question raised by Peter J. Holzer: Is this being done in place on one Postgres instance or between separate Postgres instances? > > Also another thing to note , we have used sequences as primary keys in > some tables and they have FK relationships with other tables, so the > same sequence number in version V2 will cause issues/conflict, so how > should we handle this scenario? Should we just create new sequences with > higher start values? > > Regards > Veem -- Adrian Klaver adrian.klaver@aklaver.com