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 1x4NIc-0076RN-2Y for pgsql-admin@arkaria.postgresql.org; Wed, 09 Sep 2026 18:45:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1x4NIb-00HKSk-2V for pgsql-admin@arkaria.postgresql.org; Wed, 09 Sep 2026 18:45:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1x4NIb-00HKSU-0z for pgsql-admin@lists.postgresql.org; Wed, 09 Sep 2026 18:45:33 +0000 Received: from cc-smtpout1.netcologne.de ([89.1.8.211]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1x4NIY-00000003p4T-1lSf for pgsql-admin@lists.postgresql.org; Wed, 09 Sep 2026 18:45:32 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=netcologne.de; s=nc1116a; t=1788979529; bh=JPF+0IT0lOCAC4AyK+pTPSApnuRjTuoKv5WpGgsTD0c=; h=Date:From:To:Subject:In-Reply-To:References:Message-ID:From; b=DcfK4VZ22t9uHZXEmg/upe6afipg0v8zD4SWsiEtKwc9RhVhiD//nEBC5gAMPksUO rEz1a1Ge02ogLyDUE+l5c7oCeS4UWpqVl2FmOAtR2OS2EDfAh9yky5mqEpgNoqhvH9 1atPs5AN9MtBYGPsoFo3SClIl3TRUGDyV3d5T+Zd39RVNliJ+fc6V8NxgVVZZXP9tn gUZdYQXVUIlx++7izFvVpM1W5VJr515C2eXo8dQ8ViCdfjTM+IO9qeijuAL373Ksjq 6r+2kFqY+hTQpYiKbkC0wjk8R0N/Q3UmI8yKfU72lW1uuy6e/FdeceBzCvKEpzEyY2 UCafs8a7Du5Ww== Received: from cc-rc2.netcologne.de (cc-rc2.netcologne.de [89.1.9.222]) by cc-smtpout1.netcologne.de (Postfix) with ESMTP id 398ED12550 for ; Wed, 9 Sep 2026 20:45:29 +0200 (CEST) Received: from cc-rc2.netcologne.de (localhost [127.0.0.1]) by cc-rc2.netcologne.de (Postfix) with ESMTPA id 05B0820241 for ; Wed, 9 Sep 2026 20:45:28 +0200 (CEST) Received: from 2a03:b580:af79:ce01:f46c:f250:1cf9:8c91 via cc-webproxy1.netcologne.de ([89.1.8.191]) by cc-rc2.netcologne.de with HTTP (HTTP/1.1 POST); Wed, 09 Sep 2026 18:45:28 +0000 MIME-Version: 1.0 X-Originating-IP: 2a03:b580:af79:ce01:f46c:f250:1cf9:8c91 Date: Wed, 09 Sep 2026 20:45:28 +0200 From: gunnar wagner To: pgsql-admin@lists.postgresql.org Subject: Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy In-Reply-To: References: Message-ID: X-Sender: vrms@netcologne.de Content-Type: multipart/alternative; boundary="=_8a682ccf1b11d0327fb36eba62a15188" X-NetCologne-Spam: L List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_8a682ccf1b11d0327fb36eba62a15188 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=UTF-8; format=flowed I think the premium method for major upgrades would be to use logical=20 replication [1], which I think is unique to postgres. This allows to prepare the new version on a different server or on the=20 same and, once logical replication is in sync just switch to the new=20 postgres instance. Due to not having much practical experience with this so I can not=20 provide more detail and potential caveats. I heard sequences might need=20 some manual adjustments, but I can not tell you more on this. https://www.ecosia.org/search?tt=3Dmzl&q=3Dpostgres+AND+logical+replication= =20 [2] might have some more detailed insights all best Gunnar On 2026-09-08 12:20, Cipriani, Ivan wrote: > Dear Postgres Community, >=20 > We started to use PostgreSQL database for our project, and we are happy=20 > so far =F0=9F=98=8A But we are facing one dilemma and would like to have = your=20 > recommendation about it. >=20 > To keep things up to date, we are going to regularly upgrade the=20 > version of PostgreSQL we are using. Also, in our product, we have=20 > functionality for backing up and restoring database. Sometimes=20 > customers do restore from older versions, and we will need to support=20 > restoring database from multiple older versions. >=20 > We tried to use the following approaches: >=20 > * pg_basebackup + pg_upgrade >=20 > This works fast enough and gives us a physical cluster backup. However,=20 > pg_upgrade requires not only new binaries to work, but also the older=20 > binaries matching the version database backup was created with. It=20 > brings us a bit of confusion as it's problematic to ship all the=20 > previous versions of PostgreSQL binaries to support database restore. >=20 > * pg_dump + pg_restore >=20 > This is version-independent and works well across PostgreSQL major=20 > versions. However, restore time is much slower because PostgreSQL must=20 > reload all data and rebuild indexes, constraints, and metadata. With=20 > large databases it can become an issue. Also, requires additional steps=20 > to protect data. >=20 > So, we would like to ask these questions: >=20 > 1. Is there some other intended way of doing backup/restore that should=20 > be used with PostgreSQL? Have we probably missed some proper way of=20 > doing it? >=20 > 2. If we will use pg_upgrade, does it require all the binaries or=20 > probably only just certain DLLs/tools from bin folder that we can keep=20 > with database backup? >=20 > 3. Also, is it intended that pg_upgrade will work with any minor=20 > versions across the major version provided? For example, if we have old=20 > database created with version 18.1, will it work with binaries version=20 > 18.9, or can it depend on actual version changes? >=20 > Please let us know if there is a better approach or if our=20 > understanding is incorrect. >=20 > Thanks for your support, > Ivan Cipriani Links: ------ [1] https://www.postgresql.org/docs/current/logical-replication.html [2]=20 https://www.ecosia.org/search?tt=3Dmzl&q=3Dpostgres+AND+logical+replication --=_8a682ccf1b11d0327fb36eba62a15188 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

I think the premium method for major upgrades would be to use logical = replication, which I think is unique to postgres.

This allows to prepare the new version on a different server or on the s= ame and, once logical replication is in sync just switch to the new postgre= s instance.

Due to not having much practical experience with this so I can not provi= de more detail and potential caveats. I heard sequences might need som= e manual adjustments, but I can not tell you more on this.

https://www.ecosia.org/search?tt=3Dmzl&q=3Dpostgres= +AND+logical+replication might have some more detailed insights


all best Gunnar


On 2026-09-08 12:20, Cipriani, Ivan wrote:

Dear Postgres Community,

We started to use PostgreSQL database for our proj= ect, and we are happy so far =F0=9F=98=8A But we are facing one dilemma and would lik= e to have your recommendation about it. 

To keep things up to date, we are going to regular= ly upgrade the version of PostgreSQL we are using. Also, in our product, we= have functionality for backing up and restoring database. Sometimes custom= ers do restore from older versions, and we will need to support restoring d= atabase from multiple older versions. 

We tried to use the following approaches: 

  • pg_ba= sebackup + pg_upgrade 

This works fast enough and gives us a physical clu= ster backup. However, pg_upgrade requires not only new bin= aries to work, but also the older binaries matching the version database ba= ckup 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 databas= e restore. 

  • pg_du= mp + pg_restore 

This is version-independent and works well across = PostgreSQL major versions. However, restore time is much slower because Pos= tgreSQL must reload all data and rebuild indexes, constraints, and metadata= =2E With large databases it can become an issue. Also, requires additional = steps to protect data. 

So, we would like to ask these questions: 

1. Is there some other intended way of doing backu= p/restore that should be used with PostgreSQL? Have we probably missed some= proper way of doing it? 

2. If we will use pg_upgrade, doe= s it require all the binaries or probably only just certain DLLs/tools from= bin folder that we can keep with database backup? 

3. Also, is it intended that pg_upgrade will work with any minor versions across the major version provided?= For example, if we have old database created with version 18.1, will it wo= rk with binaries version 18.9, or can it depend on actual version changes?&= nbsp;

Please let us know if there is a better approach o= r if our understanding is incorrect. 

Thanks for your support, 
Ivan Cipriani<= /p>

 


--=_8a682ccf1b11d0327fb36eba62a15188--