agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedSeeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy
5+ messages / 5 participants
[nested] [flat]
* Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy
@ 2026-09-08 10:20 Cipriani, Ivan <ivan.cipriani@gehealthcare.com>
0 siblings, 3 replies; 5+ messages in thread
From: Cipriani, Ivan @ 2026-09-08 10:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy
@ 2026-09-09 11:13 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Cipriani, Ivan <ivan.cipriani@gehealthcare.com>
2 siblings, 1 reply; 5+ messages in thread
From: Ron Johnson @ 2026-09-09 11:13 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy
@ 2026-09-09 11:16 Muhammed Ali Demirci <dmrc.muhammedali@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Muhammed Ali Demirci @ 2026-09-09 11:16 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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@gmail.com>, 9 Eyl 2026 Çar, 14:13 tarihinde şunu
yazdı:
> 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!
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy
@ 2026-09-09 11:55 Paul Smith <paul@pscs.co.uk>
parent: Cipriani, Ivan <ivan.cipriani@gehealthcare.com>
2 siblings, 0 replies; 5+ messages in thread
From: Paul Smith @ 2026-09-09 11:55 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy
@ 2026-09-09 18:45 gunnar wagner <vrms@netcologne.de>
parent: Cipriani, Ivan <ivan.cipriani@gehealthcare.com>
2 siblings, 0 replies; 5+ messages in thread
From: gunnar wagner @ 2026-09-09 18:45 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-09-09 18:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-08 10:20 Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy Cipriani, Ivan <ivan.cipriani@gehealthcare.com>
2026-09-09 11:13 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-09 11:16 ` Muhammed Ali Demirci <dmrc.muhammedali@gmail.com>
2026-09-09 11:55 ` Paul Smith <paul@pscs.co.uk>
2026-09-09 18:45 ` gunnar wagner <vrms@netcologne.de>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox