Toad World Blog

In-place upgrade of Postgres database using Amazon RDS

Mar 31, 2017 5:00:04 PM by Nassyam Basha

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.

5875.1 - Traditional Upgrade Image

 

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.

8508.nassyam in place 2

There will be a dedicated name for each snapshot, so we can accordingly uses names such as “final snapshot” or “before upgrade” as applicable.

 2313.3- 2017-02-22 08_09_29-RDS · AWS Console= final snap

 

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. 

1665.nassyam in place 4

Below we can see the snapshot details.

4370.nassyam in place 5

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

3731.nassyam in place 6

b. Check whether there are any active transactions (or) if any transactions pending with commit or rollback. Ensure there are no active transactions running.

3731.7- 2017-02-22 08_18_49-pgAdmin 4 - Reg Data

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.

6445.nassyam in place 8

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

0160.nassyam in place 9

4. Initiate upgrade by clicking on Instance Actions --> Modify.

0160.nassyam in place 10

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.

2235.nassyam in place 11

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.

5050.nassyam in place 12

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.

3302.nassyam in place 13

 

7026.nassyam in place 14

After crosschecking values, we can click on “Modify DB instance” for the upgrade to start.  We can see the status as “Upgrading”.

5468.Nassyam in place 15

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.

8182.nassyam in place 16

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.  

3806.nassyam in place 17

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.

1258.nassyam in place 18 (1)

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.

1258.nassyam in place 18 (1)

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().

1258.nassyam in place 20

 

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.

Tags: PostgreSQL

Nassyam Basha

Written by Nassyam Basha

Oracle DBA on 9i/10g/11g/12c with RAC 10g/11g on Linux/UNIX and Windows platforms including exposure on dBase, Foxpro, ORACLE 8i with forms & reports and always in front row to work on challenging tasks. I'm an Oracle 11g Certified Master and Oracle ACE Director.

Specialties:

  • ORACLE 8i/9i/10g/11g/12c
  • RAC 10g/11g/12c,
  • Strong Exposure on Data Guard.
  • Grid Control/EM 11.1/12c/13c
  • Exadata,
  • Oracle Cloud

Co-Author of Oracle DataGuard 11gR2 Beginners Guide[PACKT] Author of OTN articles Member of AIOUG, Speaker @OTN Tour, AIOUG Tech Day and SANGAM14/15/16, TROUG, OUGF Oracle Guru certified by My Oracle Support Community Frequent Contributer in OTN: https://community.oracle.com/people/CKPT , oracle-lists and etc.. Member of Customer advisory board(MOSC) Website: www.oracle-ckpt.com

Member of OraWorld Team: Facebook Page: https://www.facebook.com/oraworldteam Tweet @oraworld_team www.oraworld-team.com

Certifications: Oracle 11g Certified Master. Oracle Exadata Database Machine Certified Implementation Specialist Oracle 11g Certified Professional. Oracle 10g Certified Professional. Oracle Partner Certified Specialist