PostgreSQL Upgrading: Noteworthy at ComposePublished
This is your weekly summary of Compose news for those changes and updates which can make your life easier. In this September 17th 2018 edition, we also take a browse through the past week's Compose Articles, and then look at what the PostgreSQL upgrade process needs.
It was a busy week in Compose Articles:
- We started the week with Noteworthy, and a guide to creating MongoDB connection strings.
- For people trying to cut down on their PostgreSQL connections, we had an article on pgBouncer. In How to Pool PostgreSQL Connections with PgBouncer, Abdullah Alger works through how to get pgBouncer configured and working with Docker, ready to easily deploy where you need it.
- Friday saw two articles published. The first was our regular NewsBits which looked at moves that might reboot RethinkDB, speedups in PostgreSQL 11 and much more.
The second article on Friday was about PostgreSQL on Compose and upgrading...
Last week, we started the one month timer on customers to upgrade their PostgreSQL deployments to the latest minor version for their major version. With that in mind, here's an guide to what to do when you want to upgrade your PostgreSQL.
An in-place upgrade lets you update your database to the latest minor version of the major version you are using. In-place upgrades rely on the fact that minor version updates don't make major changes to the database or stored data. That allows us to temporarily stop the database nodes and swap the binary files of the database to the new version. It gives the least possible interruption to an upgrade. So, what does doing an in-place upgrade on Compose involve?
If you go to your Deployments view, you should see "Upgrade available" next to any database with an available upgrade. Click on that database to view it and on the deployment's Overview, there's a few indicators that an upgrade can be done. The most obvious of which is under the database's icon where it'll say "New Version Available". That label is also a button so if you click it, the Compose front end will take you to the Settings view.
You may have more than one version you can select from. Always select the latest version. Once you are ready you can click Change Version.
And this dialog will pop up to confirm your choice to upgrade. A quick reminder - when you click on Yes, each database server will be stopped, updated and brought back online. We don't do rolling upgrades as this process is so quick, it is done before any failover machanism could act. There may be other reconfiguration to be done, so expect there to be momentary interruption of service as the switch over is done. So, click on Yes to start the process. You'll be sent to the Jobs view where you'll be able to watch upgrade's progress.
When it's complete, which is often within 5 to 30 seconds, you can return to normal operations.
Upgrading with Backups
If you want to use the upgrading process to upgrade a major version or two, then Compose's restore from backup has your back. Whenever you restore a backup on Compose, we create a new deployment to restore it into. That's so, first, you can't overwrite data in your existing database and second, so we can ensure you are working with a fresh database when its populated with your backup data.
With Compose, it's easy to make as many database deployments as you need and tear the unwanted ones down when you are done; you are billed by the hour, not the month, so it is very economical. All you have to do select the Restore from Backup button on the Backups view.
We suggest you do this with a recent on-demand backup if you are doing an upgrade. When you kick off an restore from backup you are taken to the New Deployment screen:
The top blue box gives the core details. The dataset will come from the backup of
oldpostgresql from a particular time. Enter your new deployment name...
newpostgresql. Now notice the
Upgrade available line. This says the backup is from a 9.4.18 deployment and the option box below says it will be restored with PostgreSQL 9.6.10, which is currently the most recent version available in bother major and minor versions. That's the upgrade we'll get when we click on Create Deployment, which is the next step in the process.
The database will then deploy and the backup will be restored into it. Once done, you'll have a new database deployment with the latest version of PostgreSQL available on Compose. You can now test it with your applications, ensure that it performs as you expect a Compose database to perform and do any security setup, like configuring whitelists, which you may want to apply. And once your done testing, you can delete the deployment and repeat the process with new backups as many times as you need till you are ready to cut over to your new database. And all the while, your older database can still be running in production.
So there we have it; two upgrade routes, one for the faster possible update and one for a fully validated upgrade of database and applications.
Pick the one that suits your needs, but don't forget, the clock it ticking on PostgreSQL upgrades over the next month as we have a forced upgrade coming to get everyone up to date in the month after that. Happy upgrading!