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

On Wed, Sep 9, 2026 at 4:47 AM Cipriani, Ivan <
ivan(dot)cipriani(at)gehealthcare(dot)com> 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.
>

How were those backups taken?

> 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.
>

Postgresql binaries from https://ftp.postgresql.org/ are multi-version, so
you can leave those old binaries on disk alongside the "current" binaries.
That won't work, though, when you upgrade the distro version...

>
> - *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.
>

How often do you all do these restores?

> 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?
>

pg_dump + pg_restore are *the* *OS-neutral*, distro version-neutral,
PG-neutral way to do backups and restores.

For example, it's your only choice to restore a PG 10 database which lived
on a RHEL7 server into a PG 18 instance on RHEL9 or Debian or SUSE.

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?
>

PG is not Oracle... 😀 For example, the PG 17 directory tree is IIRC
*20MB.* Thus, you can keep all the versions on disk.

> 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?
>

The database "on-disk structure" does not change across minor versions.
(The "on-disk structure" doesn't really change between major versions. It's
the catalog tables which change.)

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!