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

Hello, there seems to have been a mistake; I’ve just realized that I was
included in the CC list for numerous emails. I am removing myself from the
mailing list. I wish you all success with the project you are working on.

Ron Johnson <ronljohnsonjr(at)gmail(dot)com>, 9 Eyl 2026 Çar, 14:13 tarihinde şunu
yazdı:

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