| From: | "Cipriani, Ivan" <ivan(dot)cipriani(at)gehealthcare(dot)com> |
|---|---|
| To: | "pgsql-admin(at)lists(dot)postgresql(dot)org" <pgsql-admin(at)lists(dot)postgresql(dot)org> |
| Subject: | Seeking Recommendations for PostgreSQL Backup, Restore, and Upgrade Strategy |
| Date: | 2026-09-08 10:20:20 |
| Message-ID: | DSVPR22MB996927B59FA485091BAA39FE4E87B12@DSVPR22MB996927.namprd22.prod.outlook.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Shaun Thomas | 2026-09-08 14:15:28 | Re: Is there a way to avoid “refresh materialized view” generating WAL? |
| Previous Message | Laurenz Albe | 2026-09-08 05:49:19 | Re: Is there a way to avoid “refresh materialized view” generating WAL? |