Introduction
When upgrading the database engine, irrespective of whether it is Postgres or any other database, it is necessary to fix existing bugs and to get the new features of the new releases or versions. In this article we will see how to upgrade Postgres from 9.4 to 9.5 when the database is hosted on Amazon RDS, and compare traditional upgrade with performing upgrades through Amazon RDS.
Amazon RDS – Postgres
Amazon Relational Data Services offers direct launch of databases such as Postgres, Oracle, MySQL and so on. For Postgres, RDS lets us launch the instances starting from 9.3.1 until 9.6.1 and it gives the user a lot of control to scale the service as required. Of course, apart from RDS, we can launch in EC2 as well; but in EC2 we have to handle the installation. With RDS everything is installed, including database creation. In this article we are using RDS with Postgres 9.4.9.
Version Upgrades
RDS allows us great flexibility in automating minor version upgrades. Let us understand the difference between version upgrades.
1. Minor version upgrade
– If the database version is 9.4.4 upgrading to 9.4.5 then this kind of upgrade is minor version upgrade; this upgrade we can automate from the Amazon console without user intervention and of course we can disable this option if we prefer that the upgrades be done manually.
2. Major version upgrade
– Let’s suppose Postgres is running on 9.4.4 and we are upgrading to 9.5. This kind of version upgrade is considered a major upgrade. The source database can be either 9.4.4 or 9.4.x.
– In a major version upgrade there will definitely be changes in the datafiles structure.
Upgrade Methods
A Postgres database can be upgraded either in-place or out-of-place. Let’s look at a few examples showing the difference between the in-place and out-of-place upgrades.
Out-of-place/Dump & Load: In this method we are going to install the new Postgres binaries and then we will move the data from the old version to the new version. The image below shows how an out-of-place upgrade actually works.
– The benefit of this approach: no risk, as we always have our database in the older version.
High-level steps of DUMP & LOAD method
1. Perform logical backup using pg_dump
2. Stop Postgres
a. Verify the configuration files ex: /opt/PostgreSQL/9.4.x
4. Back up the datafile directory
5. Install the new software 9.5.x
Create an empty database using “create database”, psql,or pgadmin
a. Restore logical backup
6. Test the database
In-Place/PG_UPGRADE: In this upgrade method, the changes and upgrades happen on the existing binaries and the same data structures; there is the risk of failure in the upgrade process; hence, we should handle this carefully, using best practices.
– In-place upgrade – no need to copy user data to upgrade between software versions.
– Very complex project
– Can be split into several sub-projects
– Amazon RDS supports in-place upgrades starting from 9.3.x to 9.6.x.
Upgrading Postgres through RDS
Before getting into the actual upgrade process through RDS, planning is key, as always in upgrades or migrations. For instance, taking cold backups prior to upgrade, performing complete prerequisites, checking the known issues, so on. In this article we have used the pgAdmin utility to manage the database instead of PSQL. PSQL is a traditional utility to access the database, while pgAdmin supports a GUI interface. We will proceed with the prerequisites of the upgrade and then we will continue with the actual upgrade of the Postgres database engine.
1. Take the backup/snapshot in AWS version
Backup of a Postgres database is generally performed by SQL Dump or file system level backup or continuous archiving. In the case of AWS, we call the backup method that creates a complete backup of the service “Take Snapshot”. We can access this from the instance as shown below.
There will be a dedicated name for each snapshot, so we can accordingly uses names such as “final snapshot” or “before upgrade” as applicable.
Click on “Take Snapshot”; RDS will create the snapshot and this process is very quick. With our snapshot we can launch the instance at any time with the same configuration, which allows us great flexibility and quick availability.
Below we can see the snapshot details.
Now we have completed our RDS backup/snapshot from the cloud console.
2. Prerequisites through pgAdmin
Initially we will check what version we are using and what prerequisites should be performed. In this exercise, as said, we are upgrading Postgres from 9.4.9 to 9.5.4 and we can see the current version, where the database is running. This piece of information we can gather from versionI(), please note that the same commands works from PSQL and also from the pgAdmin console.
a. Version check
b. Check whether there are any active transactions (or) if any transactions pending with commit or rollback. Ensure there are no active transactions running.
c. Check for any Reg* data existing in the database, because it will not be upgraded as part of the database upgrade. Hence, we should remove any Reg* data found prior to upgrading.
d. Perform a VACUUM operation; if this is not done then the upgrade process may take longer than expected. This step is usually initiated through pg_upgrade but it is recommended to do prior to upgrade.
pgAdmin> vacuum full verbose
INFO: vacuuming "pg_catalog.pg_statistic"
INFO: "pg_statistic": found 0 removable, 399 nonremovable row versions in 16 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: vacuuming "pg_catalog.pg_type"
INFO: "pg_type": found 4 removable, 341 nonremovable row versions in 8 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
WARNING: skipping "pg_authid" --- only superuser can vacuum it
INFO: vacuuming "pg_catalog.pg_attribute"
INFO: "pg_attribute": found 11 removable, 2336 nonremovable row versions in 44 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_catalog.pg_proc"
INFO: "pg_proc": found 0 removable, 2685 nonremovable row versions in 67 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: vacuuming "pg_catalog.pg_user_mapping"
INFO: "pg_user_mapping": found 0 removable, 0 nonremovable row versions in 0 pages
. . .
. .
.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "information_schema.sql_parts"
INFO: "sql_parts": found 0 removable, 9 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Query returned successfully in 1 secs.
3. Ensure the database engine is in active status; we can review this from the RDS Dashboard –> Instances –> PGDW
4. Initiate upgrade by clicking on Instance Actions –> Modify.
The modify option opens a new console which presents upgrade version information and we can scale up or down, for instance changes in storage capacity or type.
5. Perform changes in specifications
The actual database engine is on 9.4.9, and we are upgrading our database engine to the version 9.5.4. Apart from that you can change the configuration settings such as subnet group, security groups, passwords, and so on.
There is a section called Backup & Monitoring where we can schedule the backups and also we can enable enhanced monitoring for real-time statistics on service performance.
After confirming the updated details, we can see a summary of changes in a new window; if there are any additional changes to be made, for instance, to security group or backup windows, then we should take care of them. If we continue here, the actual upgrade will be started. Therefore, we need to double check the configuration in summary first.
After crosschecking values, we can click on “Modify DB instance” for the upgrade to start. We can see the status as “Upgrading”.
While upgrade is in progress, we can monitor upgrade events with the database such as internal backups/snapshots, instance stop/start status, and so on.
The log file is great source of information about operations such as installation or upgrade of the database. RDS creates the log file with all the information starting from upgrade to completion.
We can see the logging of each event as it happens, and finally we see that the database is available with the new database engine.
By accessing the logs of the upgrade, we can see all the information starting prior to the upgrade, where RDS has taken a backup of the database we can keep as a master copy for restore in the event the upgrade fails. The logs show the status of each task with timestamp. If we track the start and end time, we can see the upgrade process completed in 13 minutes.
6. Post Upgrade – We will test the database status from the instance console and from the database level.
If we look in the configuration details we can see the updated Database engine, version 9.5.4 from 9.4. We can connect to the database using pgAdmin to check the database version from the database repository and we can view the version as 9.5.4 using view version().
Summary
We have demonstrated the entire process of upgrading Postgres through Amazon RDS, starting from the basics, noted that RDS can support only in-place upgrades, and covered how the traditional out-of-place upgrade works. Finally, we saw how to upgrade the Postgres database engine and how to monitor the upgrade process interactively.
Start the discussion at forums.toadworld.com