AWS News Blog

Amazon RDS for PostgreSQL – New Minor Versions, Logical Replication, DMS, and More

Amazon Relational Database Service (RDS) simplifies the process of setting up, operating, and scaling a relational database in the cloud. With support for six database engines (Amazon Aurora, Oracle, Microsoft SQL Server, PostgreSQL, MySQL, and MariaDB) RDS has become a foundation component for many cloud-based applications.

We launched support for PostgreSQL in late 2013 and followed up that launch with support for more features and additional versions of PostgreSQL:

Today we are launching several enhancements to Amazon RDS for PostgreSQL. Here’s a summary:

  • New Minor Versions – Existing RDS for PostgreSQL database instances can be upgraded to new minor versions.
  • Logical Replication – RDS for PostgreSQL now support logical replication and the associated logical decoding.
  • DMS Support – The new logical replication feature allows an RDS for PostgresSQL database instance to be used as the source for AWS Database Migration Service.
  • Event Triggers – Newer versions of PostgreSQL support event triggers at the database instance level.
  • RAM Disk Size – RDS for PostgreSQL now supports control of the size of the RAM disk.

Let’s take a closer look!

New Minor Versions
We are adding support for versions 9.3.14, 9.4.9, and 9.5.4 of PostgreSQL. Each of these versions includes fixes and enhancements as documented in the linked release notes. You can also upgrade your database instances using the RDS Console or the AWS Command Line Interface (AWS CLI). Here’s how to upgrade from 9.5.2 to 9.5.4 using the console:

Be sure to check Apply immediately if you don’t want to wait until the next maintenance window.

Here’s how you can initiate the upgrade operation from the command line (I decided to give the command line some extra attention in this post in order to make sure that my skills were still current):

$ aws rds modify-db-instance --region us-west-2  \
  --db-instance-identifier "pg95" \
  --engine-version "9.5.4" \
  --apply-immediately

You can check on the progress of the upgrade like this:

$ aws rds describe-events --region us-west-2 \
  --source-type db-instance --source-identifier "pg95" \
  --duration 10 --output table

The following part of the output will let you know that the instance has been upgraded:

||+-------------------------------------------------+||
||                      Events                       ||
|+--------------------+------------------------------+|
||  Date              |  2016-09-13T00:07:54.547Z    ||
||  Message           |  Database instance patched   ||
||  SourceIdentifier  |  pg95                        ||
||  SourceType        |  db-instance                 ||
|+--------------------+------------------------------+|
|||                 EventCategories                 |||
||+-------------------------------------------------+||
|||  maintenance                                    |||
||+-------------------------------------------------+||

If you take a look at the entire series of events for the database instance, you’ll also see that RDS performs backups before and after the patch. You can find these backups in the console or via the command line:

$ aws rds describe-db-snapshots --region us-west-2 \
  --db-instance-identifier "pg95" \
  --snapshot-type automated --output table

The output will look like this:

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                                                                                                                                                               DescribeDBSnapshots
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
||                                                                                                                                                                  DBSnapshots
|+------------------+-------------------+-----------------------+----------------------------+------------+-----------+----------------+---------------------------+-------+---------------------+-----------------+-----------------------+-
|| AllocatedStorage | AvailabilityZone  | DBInstanceIdentifier  |   DBSnapshotIdentifier     | Encrypted  |  Engine   | EngineVersion  |    InstanceCreateTime     | Iops  |    LicenseModel     | MasterUsername  |    OptionGroupName    |
|+------------------+-------------------+-----------------------+----------------------------+------------+-----------+----------------+---------------------------+-------+---------------------+-----------------+-----------------------+-
||  100             |  us-west-2b       |  pg95                 |  rds:pg95-2016-09-12-23-22 |  False     |  postgres |  9.5.2         |  2016-09-12T23:15:07.999Z |  1000 |  postgresql-license |  root           |  default:postgres-9-5 |
||  100             |  us-west-2b       |  pg95                 |  rds:pg95-2016-09-13-00-01 |  False     |  postgres |  9.5.2         |  2016-09-12T23:15:07.999Z |  1000 |  postgresql-license |  root           |  default:postgres-9-5 |
||  100             |  us-west-2b       |  pg95                 |  rds:pg95-2016-09-13-00-07 |  False     |  postgres |  9.5.4         |  2016-09-12T23:15:07.999Z |  1000 |  postgresql-license |  root           |  default:postgres-9-5 |
|+------------------+-------------------+-----------------------+----------------------------+------------+-----------+----------------+---------------------------+-------+---------------------+-----------------+-----------------------+-

Logical Replication
Amazon RDS for PostgreSQL now supports logical replication. You can now efficiently create database replicas by streaming high-level databases changes from an Amazon RDS for PostgreSQL database instance to a non-RDS database that supports the complementary logical decoding feature (PostgreSQL also supports Physical Streaming Replication, an earlier and less efficient byte/block-based mechanism for creating and maintaining replicas). Replication takes place via logical slots; each slot contains a stream of changes that can be replayed exactly once (you can read about Logical Decoding Slots in the PostgreSQL documentation to learn more).

In order to implement logical replication to a non-RDS database, you will need to ensure that the user account for the PostgreSQL database has the rds_superuser and rds_replication roles. You also need to set the rds.logical_replication parameter to 1 in the database options group for your database instance and then reboot the instance. When this parameter is applied, several PostgreSQL parameters will be configured so as to allow for replication.

With the roles in place and the database instance configured, you can create a logical slot and then instruct the non-RDS database (or other client) to read and process records from the slot. For example, the pg_recvlogical command connects to the database instance and streams data from a replication slot into a local file.

To learn more, read Logical Replication for PostgreSQL in the Amazon RDS for PostgreSQL User Guide.

DMS Support
AWS Database Migration Service helps you to migrate databases to AWS.  In conjunction with the new support for Logical Replication, you can now migrate your data from a PostgreSQL database (running on RDS or on a self-managed host) to another open source or commercial database. To do this, you will need to create a logical replication slot as described above.

Event Triggers
Newer versions of PostgreSQL (9.4.9+ and 9.5.4+) support event triggers at the database level. Because these triggers exist outside of any particular database table, they are able to capture a wider range of events, including DDL-level events that create, modify, and delete tables (here’s a full list of events that fire triggers). To learn more and to see a sample implementation of a trigger, take a look at Event Triggers for PostgreSQL in the User Guide.

RAM Disk Size
You can now use the rds.pg_stat_ramdisk_size parameter to control the amount of memory used for PostgreSQL’s stats_temp_directory. This directory is used to temporarily store statistics on run-time performance and behavior; making more memory available can reduce I/O requirements and improve performance.

Available Now
The new versions and features described above are available now and you can start using them today.

— Jeff;