agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: gunnar wagner <vrms@netcologne.de>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy
Date: Wed, 09 Sep 2026 20:45:28 +0200
Message-ID: <b61b86022da179c359958925e85347ee@netcologne.de> (raw)
In-Reply-To: <DSVPR22MB996927B59FA485091BAA39FE4E87B12@DSVPR22MB996927.namprd22.prod.outlook.com>
References: <DSVPR22MB996927B59FA485091BAA39FE4E87B12@DSVPR22MB996927.namprd22.prod.outlook.com>
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
view thread (5+ messages)
Message-ID: <b61b86022da179c359958925e85347ee@netcologne.de>
Permalink: ../b61b86022da179c359958925e85347ee@netcologne.de/
Also on: postgresql.org/message-id/b61b86022da179c359958925e85347ee@netcologne.de
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: vrms@netcologne.de, pgsql-admin@lists.postgresql.org
Subject: Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy
In-Reply-To: <b61b86022da179c359958925e85347ee@netcologne.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox