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:
Show quoted text
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.
Show quoted text
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:
Show quoted text
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