Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy

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

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

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

https://www.ecosia.org/search?tt=mzl&q=postgres+AND+logical+replication
[2] 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 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. Also, in our product, we have
> functionality for backing up and restoring database. Sometimes
> customers do restore from older versions, and we will need to support
> restoring database from multiple older versions.
>
> We tried to use the following approaches:
>
> * 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.
>
> * pg_dump + pg_restore
>
> This is version-independent and works well across PostgreSQL major
> versions. However, restore time is much slower because PostgreSQL must
> reload all data and rebuild indexes, constraints, and metadata. 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 backup/restore that should
> be used with PostgreSQL? Have we probably missed some proper way of
> doing it?
>
> 2. If we will use pg_upgrade, does 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 work with binaries version
> 18.9, or can it depend on actual version changes?
>
> Please let us know if there is a better approach or if our
> understanding is incorrect.
>
> Thanks for your support,
> Ivan Cipriani

Links:
------
[1] https://www.postgresql.org/docs/current/logical-replication.html
[2]
https://www.ecosia.org/search?tt=mzl&q=postgres+AND+logical+replication