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

I think the premium method for major upgrades would be to use logical replication, 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 might have some more detailed insights

all best Gunnar

On 2026-09-08 12:20, Cipriani, Ivan wrote:

@font-face { font-family: "Cambria Math"; } @font-face { font-family: Aptos; } @font-face { font-family: "Segoe UI Emoji"; } #replybody1 p.v1MsoNormal, #replybody1 li.v1MsoNormal, #replybody1 div.v1MsoNormal { margin: 0in; font-size: 12.0pt; font-family: "Aptos",sans-serif; mso-ligatures: standardcontextual; } #replybody1 span.v1EmailStyle17 { mso-style-type: personal-compose; font-family: "Aptos",sans-serif; color: windowtext; } #replybody1 .v1MsoChpDefault { mso-style-type: export-only; } @page WordSection1 { size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; } #replybody1 div.v1WordSection1 {} @list l0 { mso-list-id: 20935342; mso-list-template-ids: -1810231612; } @list l0:level1 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: .5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l0:level2 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 1.0in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l0:level3 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 1.5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l0:level4 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 2.0in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l0:level5 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 2.5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l0:level6 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 3.0in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l0:level7 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 3.5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l0:level8 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 4.0in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l0:level9 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 4.5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1 { mso-list-id: 409082821; mso-list-template-ids: -34715662; } @list l1:level1 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: .5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1:level2 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 1.0in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1:level3 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 1.5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1:level4 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 2.0in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1:level5 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 2.5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1:level6 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 3.0in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1:level7 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 3.5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1:level8 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 4.0in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } @list l1:level9 { mso-level-number-format: bullet; mso-level-text: �; mso-level-tab-stop: 4.5in; mso-level-number-position: left; text-indent: -.25in; mso-ansi-font-size: 10.0pt; font-family: Symbol; } #replybody1 ol { margin-bottom: 0in; } #replybody1 ul { margin-bottom: 0in; }

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