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.96) (envelope-from ) id 1x4H3z-0072jy-1o for pgsql-admin@arkaria.postgresql.org; Wed, 09 Sep 2026 12:06:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1x4H3y-00EUfP-1t for pgsql-admin@arkaria.postgresql.org; Wed, 09 Sep 2026 12:06:02 +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.96) (envelope-from ) id 1x4H3y-00EUfG-0B for pgsql-admin@lists.postgresql.org; Wed, 09 Sep 2026 12:06:02 +0000 Received: from mail2.pscs.co.uk ([178.159.10.131] helo=mail.pscs.co.uk) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1x4H3u-00000004lYh-44w1 for pgsql-admin@lists.postgresql.org; Wed, 09 Sep 2026 12:06:01 +0000 Authentication-Results: mail.pscs.co.uk; spf=none; auth=pass (cram-md5) smtp.auth=pscs Received: from lmail.pscs.co.uk ([192.168.120.1]) by mail.pscs.co.uk ([192.168.120.185] running VPOP3) with ESMTPA for ; Wed, 9 Sep 2026 13:05:55 +0100 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pscs.co.uk; q=dns/txt; s=lmail; h=Content-Type:Message-ID:Date:MIME-Version:Subject:To:References:From :In-Reply-To:Cc:Content-Transfer-Encoding:Reply-to:Sender; t=1788954919; x=1789559719; bh=kumfUHteVNTeM2v4Jo8P/wqa//0mK7QJ0OjPGTxOo88=; b=nvpp6qzxota3P3C7mc+79+tPElhUfQKD+CfqG4NrEpA3SvwsGEZvOQAwjbyZm2iCXbkouZga 43IYgXi9kNwOWybyH+kopOHcoLd6/kk/GPiova2UVvpdKVfjiGUUlvpGRrwRah40p6OiMooX/d YERLLEzpHMKb7qpnHv7caFwpE= Authentication-Results: lmail.pscs.co.uk; spf=none; auth=pass (cram-md5) smtp.auth=paul Received: from [192.168.57.32] ([217.155.111.120] (217-155-111-120.dsl.in-addr.zen.co.uk)) by lmail.pscs.co.uk ([192.168.120.70] running VPOP3) with ESMTPSA (TLSv1.3 TLS_AES_256_GCM_SHA384) for ; Wed, 9 Sep 2026 12:55:19 +0100 Content-Type: multipart/alternative; boundary="------------7dbcVTw7WNxuMYN9jngZaZpA" Message-ID: Date: Wed, 9 Sep 2026 12:55:17 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy To: pgsql-admin@lists.postgresql.org References: Content-Language: en-GB From: Paul Smith In-Reply-To: X-Authenticated-Sender: paul X-Server: VPOP3 Enterprise V9.0 - Registered X-Organisation: Paul Smith Computer Services X-VPOP3Tester: 12 345 X-Authenticated-Sender: pscs List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------7dbcVTw7WNxuMYN9jngZaZpA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 08/09/2026 11:20, Cipriani, Ivan wrote: > > Dear Postgres Community, > > We started to use PostgreSQL database for our project, and we are > happy so far 😊 But we are facing one dilemma and would like to have > your recommendation about it. > > To keep things up to date, we are going to regularly upgrade the > version of PostgreSQL we are using. Sometimes customers do restore > from older versions, and we will need to support restoring database > from multiple older versions. > - upgrading within a major version (eg 18.1 to 18.6) does not require anything to be done to the data files - simply stop, replace the bin/lib/share directories with the new ones and restart. - unless you are doing 'weird' stuff, you can restore a pg_dump backup from any older version into a newer version. You don't need to do anything fancy ('weird stuff' = things that are broken with backwards compatibility - in my experience, if you're doing 'normal' things, then these are few and far between). So the 'sometimes customers do restore from older versions' is probably a non-issue. > Also, in our product, we have functionality for backing up and > restoring databases How are you doing this? pg_dump & pg_restore? Something else? > * *pg_basebackup* + *pg_upgrade* > > This works fast enough and gives us a physical cluster backup. > However, *pg_upgrade* requires not only new binaries to work, but also > the older binaries matching the version database backup was created > with. It brings us a bit of confusion as it’s problematic to ship all > the previous versions of PostgreSQL binaries to support database restore. > You already have the previously-used version installed. In our upgrade process, we rename those directories, install the new binaries, and then can run pg_upgrade with the new and previous binaries there. We don't need to ship the old binaries. (ps - why are you doing pg_basebackup before doing pg_upgrade - we've never needed to do that) Paul --------------7dbcVTw7WNxuMYN9jngZaZpA Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 08/09/2026 11:20, Cipriani, Ivan wrote:

Dear Postgres Community,

We started to use PostgreSQL database for our project, and we are happy so far 😊 But we are facing one dilemma and would like to have your recommendation about it. 

To keep things up to date, we are going to regularly upgrade the version of PostgreSQL we are using.  Sometimes customers do restore from older versions, and we will need to support restoring database from multiple older versions. 

- upgrading within a major version (eg 18.1 to 18.6) does not require anything to be done to the data files - simply stop, replace the bin/lib/share directories with the new ones and restart.

- unless you are doing 'weird' stuff, you can restore a pg_dump backup from any older version into a newer version. You don't need to do anything fancy ('weird stuff' = things that are broken with backwards compatibility - in my experience, if you're doing 'normal' things, then these are few and far between). So the 'sometimes customers do restore from older versions' is probably a non-issue.

Also, in our product, we have functionality for backing up and restoring databases
How are you doing this? pg_dump & pg_restore? Something else?


  • pg_basebackup + pg_upgrade 

This works fast enough and gives us a physical cluster backup. However, pg_upgrade requires not only new binaries to work, but also the older binaries matching the version database backup was created with. It brings us a bit of confusion as it’s problematic to ship all the previous versions of PostgreSQL binaries to support database restore. 

You already have the previously-used version installed. In our upgrade process, we rename those directories, install the new binaries, and then can run pg_upgrade with the new and previous binaries there. We don't need to ship the old binaries.

(ps - why are you doing pg_basebackup before doing pg_upgrade - we've never needed to do that)

Paul

--------------7dbcVTw7WNxuMYN9jngZaZpA--