In-place upgrades for PostgreSQL are back on Compose after a brief hiatus while we enhanced the Compose HA solution for PostgreSQL.
You are now free to move around the cabin and upgrade your PostgreSQL 9.4.x to 9.4.15, your 9.5.x to 9.5.10 and your 9.6.x to 9.6.6. It also means you can upgrade to the new Patroni-based HA solution that we've switched to.
With in-place upgrades re-activated it also means you can move up to the most recent security fixes of PostgreSQL which were released earlier this month.
About that upgrade pause...
If you missed what happened, allow us to go back to late September. That was when we announced that Compose PostgreSQL was switching to Patroni as its high-availability solution. We noted at the time that some engineering work would need to be done to bring all our older PostgreSQL deployments up to the level where they could run Patroni though.
As a precaution, we froze the use of in-place upgrades on PostgreSQL while the engineering work took place. It took a little longer than we'd expected, but for a good reason: it is remarkable how popular PostgreSQL is with Compose users. We've made sure no-one was disturbed while we did the work needed to prepare all those deployments for the new Patroni configuration.
That work has involved making room on each deployment for two more etcd capsules. They get used to support the state management of the PostgreSQL database when Patroni is running. We take stability and availability seriously at Compose and this was the best way we could ensure it.
What's in the latest updates?
The release announcement for 9.6.6, 9.5.10 and 9.4.15 picks out the highlights of the fixes. One of the security updates for CVE-2017-12172 doesn't affect Compose users as we take care of your database startup for you. If you are a user of JSON functions, the fixes for CVE-2017-15098 - Memory disclosure in JSON functions - make the server more stable.
Finally, and most likely to be used, the fix for CVE-2017-15099 - INSERT ... ON CONFLICT DO UPDATE fails to enforce SELECT privileges - makes sure that the PostgreSQL version of UPSERT respects table and row level security settings.
Now it's over to you. Upgrade your deployments in-place to the latest PostgreSQL minor version for your major version and your PostgreSQL will also be upgraded to use the new Patroni system. If you've not done an in-place upgrade before, it works like this:
Go to the Compose console Overview for your PostgreSQL deployment. If there's an upgrade available it will let you know:
There's the banner at the top which will tell you what verion you are on and which version you can upgrade to with links to Settings. There's also the New Version Available label which, if you click the arrow on the right, will also take you to Settings. Or you can just click on Settings in the sidebar. Whichever you chose, you'll turn up here:
This is the settings page and more precisely the Change Version panel on that page. If there's more than one version you can upgrade to, it'll be listed in the drop-down menu of Available Versions but it defaults to the newest version. Click Change Version and the upgrade will begin. There'll be should be no downtime on the database as a whole, but connections will be restarted as the new database versions come on line.
So that's all there is to it. Going forward, there's improved HA with Patroni and easier updates for minor versions. You will still have to do a "restore from backup" upgrade for major version upgrades (9.4 to 9.5, 9.5 to 9.6), but that's because it's the most reliable and safest way to move between major versions.
Read more articles about Compose databases - use our Curated Collections Guide for articles on each database type. If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at email@example.com. We're happy to hear from you.
attribution Matthew Spiteri