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 1rZTLf-004Xov-La for pgsql-general@arkaria.postgresql.org; Mon, 12 Feb 2024 10:15:40 +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 1rZTLd-008cDu-VN for pgsql-general@arkaria.postgresql.org; Mon, 12 Feb 2024 10:15:37 +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 1rZTLd-008cDm-Dw for pgsql-general@lists.postgresql.org; Mon, 12 Feb 2024 10:15:37 +0000 Received: from meldrar.postgresql.org ([2a02:c0:301:0:ffff::31]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rZTLb-006LaL-96 for pgsql-general@postgresql.org; Mon, 12 Feb 2024 10:15:36 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Content-Transfer-Encoding:Content-Type: Message-ID:References:In-Reply-To:Subject:Cc:To:From:Date:MIME-Version:Sender :Reply-To:Content-ID:Content-Description; bh=S2rbYjrPDp11uXbT+BAm4iUkahUSXXXCrCjyRh4jNDA=; b=VyH+uLpg8G38tzzU+0zYncVMmp W7dTT2N8ne+vGdqa08m7lwwiSTT91aTFGeGTaAybzcgVHLUZ9jcz+zGw9JrnUzvVYz6qpQBKgQULj CywdyjR1Jx5AIttS5tJkaGBcoyT150NFiuKA1omdKuKvOM3tGcy9AFAj5MwPa2v1mg6mV2ImoLRej +Hvpxe7BPe6LCubq/kwf84906gr84iCYlkbgJ+zEIS+P8dMN2FkkdoNa7WWXBdT/Q1NJZUN0/IHXM v5KR1l0OiUJSwYuO2thAjkToc7lPjVDP8PWDKnGhhU4x+MMoGmyDWXJ0LfAduMn/5brezfZdi39aq /FDS/1qw==; Received: from meldrar.postgresql.org ([87.238.57.231] helo=webmail.postgresql.org) by meldrar.postgresql.org with esmtpsa (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rZTLX-004bFV-Qi; Mon, 12 Feb 2024 10:15:33 +0000 MIME-Version: 1.0 Date: Mon, 12 Feb 2024 20:15:31 +1000 From: Justin Clift To: =?UTF-8?Q?D=C3=A1vid_Suchan?= Cc: pgsql-general Subject: Re: Safest pgupgrade jump distance In-Reply-To: References: User-Agent: Roundcube Webmail/1.4.15 Message-ID: <1a37201c2d8fb3c712baa45aaf325ce5@postgresql.org> X-Sender: justin@postgresql.org 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 2024-02-12 20:07, Dávid Suchan wrote: > Hi, I was wondering what is the safest pg_upgrade version upgrade > distance > going from 9.6 version. Do I need to go version by version or I can go > from > 9.6 to 15? We have a very huge database(TBs) with one replication > server, > so we will first run the pgupgrade on the main server and then rsync to > a > standby replica. I'm not sure whether it's safe to do it from 9.6 to 15 > at > once, I have tested the process on 9,6 to 10 yet. Would that be a wise > approach to such an upgrade of the db? > Also, when upgrading a very big database with replication where none of > the > data can be allowed to be lost, is the pgupgrade into rsync approach > the > best one? Thanks. pgupgrade from 9.6 directly to 16 should work fine. That's part of the test suite for one of my side projects (pgautoupgrade in Docker). You'll probably want to use the "--link" option too, so it uses the existing data files rather than recreating them. It's *much* faster. :) I'm not sure how the rsync of things will figure into it though. If you're just rsyncing files when the database is turned off, it should be straight forward. As always though, make sure you have a backup (that's known to work) before you try it all out though. :) + Justin