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

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