Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy - Mailing list pgsql-admin

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