Thread: Managing major PostgreSQL upgrades
Hi,
Every year we spent a lot of time planning and manually performing major PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering if there is a better way of managing these upgrades through automation. Can anyone share their experiences?
Thanks.
Tiff
Hey Tiff,
We're in a similar boat. We currently lean on (mostly custom) ansible scripting to automate all the repeatable tasks we can, but automation of major PG version upgrades is something we are yet to tackle -- although we plan to start this effort in the short term.
Would you mind sharing a bit more about your own current upgrade process? What's your standard, a dump+restore, a pg_upgrade, or replication-based? Also if you are able to share any lessons learned (e.g. common pitfalls you've run into) will all be useful information to identify ahead of time when drafting an automation strategy.
On Mon., Nov. 15, 2021, 6:45 a.m. Tiffany Thang, <tiffanythang@gmail.com> wrote:
Hi,Every year we spent a lot of time planning and manually performing major PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering if there is a better way of managing these upgrades through automation. Can anyone share their experiences?Thanks.Tiff
On Mon, Nov 15, 2021 at 8:48 PM Saul Perdomo <saul.perdomo@gmail.com> wrote:
Hey Tiff,We're in a similar boat. We currently lean on (mostly custom) ansible scripting to automate all the repeatable tasks we can, but automation of major PG version upgrades is something we are yet to tackle -- although we plan to start this effort in the short term.Would you mind sharing a bit more about your own current upgrade process? What's your standard, a dump+restore, a pg_upgrade, or replication-based? Also if you are able to share any lessons learned (e.g. common pitfalls you've run into) will all be useful information to identify ahead of time when drafting an automation strategy.
Our upgrades (pg_upgrade) have been pretty smooth so far. We have not encountered any issues other than the occasional missing OS packages. The issue is I do not know what problems to expect during a major upgrade which makes automating the process difficult.
On Mon., Nov. 15, 2021, 6:45 a.m. Tiffany Thang, <tiffanythang@gmail.com> wrote:Hi,Every year we spent a lot of time planning and manually performing major PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering if there is a better way of managing these upgrades through automation. Can anyone share their experiences?Thanks.Tiff
Thanks. My first instinct might be to simply maintain your own list of missing packages, and ensure they all get installed prior to the pg_upgrade run (something which ansible can handily do).
Now, depending on the level of homogeneity of the server farm under your purview, this might pose its own challenges - but based on this info, it's where I would probably start..
On Wed., Nov. 17, 2021, 12:28 a.m. Tiffany Thang, <tiffanythang@gmail.com> wrote:
On Mon, Nov 15, 2021 at 8:48 PM Saul Perdomo <saul.perdomo@gmail.com> wrote:Hey Tiff,We're in a similar boat. We currently lean on (mostly custom) ansible scripting to automate all the repeatable tasks we can, but automation of major PG version upgrades is something we are yet to tackle -- although we plan to start this effort in the short term.Would you mind sharing a bit more about your own current upgrade process? What's your standard, a dump+restore, a pg_upgrade, or replication-based? Also if you are able to share any lessons learned (e.g. common pitfalls you've run into) will all be useful information to identify ahead of time when drafting an automation strategy.Our upgrades (pg_upgrade) have been pretty smooth so far. We have not encountered any issues other than the occasional missing OS packages. The issue is I do not know what problems to expect during a major upgrade which makes automating the process difficult.On Mon., Nov. 15, 2021, 6:45 a.m. Tiffany Thang, <tiffanythang@gmail.com> wrote:Hi,Every year we spent a lot of time planning and manually performing major PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering if there is a better way of managing these upgrades through automation. Can anyone share their experiences?Thanks.Tiff