Amazon RDS for PostgreSQL Update – Read Replicas, 9.3.5 Support, Migration, Three New Extensions

My colleague Srikanth Deshpande of the Amazon Relational Database Service (RDS) team sent along a guest post in order to bring you up to date on the latest and greatest features added to Amazon RDS for PostgreSQL including Read Replications, data migration enhancements, and three new extensions.

— Jeff;

Read Replicas
Amazon RDS for PostgreSQL now supports Read Replicas, a popular RDS feature making it even easier for PostgreSQL customers to scale out databases to meet the needs of today’s read-heavy, high traffic web and mobile applications.

You can create multiple copies or replica of a given PostgreSQL database instance and scale your application by distributing read traffic across them. After you create a Read Replica, it is kept in sync using PostgreSQL’s asynchronous streaming replication. You can create or delete replicas in minutes using the point-and-click interface of the AWS Management Console, the AWS Command Line Interface (AWS CLI), or the RDS APIs. Once you create a replica, it’s kept in sync with future updates using asynchronous PostgreSQL streaming replication (under certain conditions, Read Replicas can lag behind your write master). Amazon RDS gives you the ability to monitor replica lag so you can adjust your application as needed. Read Replicas can also be used with Multi-AZ, giving you the benefit of read scalability along with the durability and availability benefits of Multi-AZ deployments.

To create a Read Replica, select the write master in the Console and choose Create Read Replica from the Instance Actions menu:

Fill in the details and click on the Create Read Replica button:

You’ll need to upgrade your existing instances to version 9.3.5 of PostgreSQL in order to take advantage of Read Replicas. Fortunately, you can upgrade within minutes using the AWS Management Console or the APIs and then start scaling out your read deployments. PostgreSQL 9.3.5 comes with PostGIS, which is now at version 2.1.3.

A Word From our Customers
I spoke to a couple of AWS customers in order to better understand how they are using Amazon RDS for PostgreSQL and to get a feel for how they will make use of Read Replicas.

Flipagram is a top 100 mobile app in the iOS Appstore and Google Play that helps users create and share video stories using their pictures. I spoke with Founder & CTO Brian Dilley; this is what he told me:

We started our journey on RDS PostgreSQL and have grown to support a large user base on our app with a very small team by leveraging management capabilities, and compute, storage and I/O scalability of RDS PostgreSQL. We use Multi-AZ for high availability and love the ability to get HA for our production workloads using a check box on the management console. We run all of our production, integration and test/dev environments on RDS PostgreSQL. When we ranked as number one app in the Appstore in over 80 countries in December of 2013, RDS helped us scale our infrastructure to meet the huge spike in traffic with the push of a button. As we continue to grow, we look to forward to using Read Replicas to scale our read traffic while we focus on creating beautiful experiences for our users.

E la Carte has been using Amazon RDS for PostgreSQL since it launched. Their guest-facing Presto tablets are designed for the coming era of Smart Dining. Bill Healey (CTO of E la Carte) told me:

Before RDS Postgres, managing our own PostgreSQL instances required significant engineering resources whereas RDS makes it quick and easy to add replication and scale up both database size and IOPS. We’ve definitely been looking forward to the addition of read replicas for RDS Postgres. At E la Carte, we are very data-driven and are constantly analyzing data from both our production DB directly and our data warehouse aggregations. We’re looking forward to using RDS Read Replicas to drive our data warehouse ETL and our analytics tools resulting in increased performance and decreased load on our RDS production instances.

Trigger-Based Replication
RDS PostgreSQL now supports the session replication role. You can use this role in conjunction with open source, trigger-based tools such as Londiste and Bucardo to migrate existing data in to and out of RDS for PostgreSQL with minimal downtime.

Data Import (Londiste)
In order import data using Londiste, you would need to install it on the external PostgreSQL instance (not on RDS), set up RDS PostgreSQL instance as a replica, and enable replication. Londiste would initially do a dump and load of data into RDS PostgreSQL instance while the external instance is still taking write traffic. Over time, the RDS instance will catch up with the updates that are taking place on the external instance. Once the RDS instance is current, you can then point your applications to the RDS PostgreSQL instance, choosing the timing so as to minimize application downtime.

Data Export (Bucardo)
You can also use the session role to export data from an RDS for PostgreSQL instance to a remote target on-premises or on EC2. There are many ways to do this. For example, you can install Bucardo, an open source trigger-based lazy replication solution on the remote instance and set it up as a replication slave to the master RDS PostgreSQL instance. Bucardo replicates the data on the RDS instance to the remote instance as long as the remote instance is online. With lazy replication, when the remote instance goes offline and comes back online, Bucardo ensures that the remote instance will eventually catch up.

New Extensions
In addition to Read Replicas, support for version 9.3.5 of PostgreSQL, and trigger-based replication, we have also added support for three popular PostgreSQL extensions:

• pg_stat_statements – This extension provides lets you track execution statistics such as userid, exact query, and total time, for all SQL statements executed on the instance.
• postgres_fdw – This extension provides you with the ability to access and modify data stored in other PostgreSQL servers as if they are tables within the RDS PostgreSQL instance.
• PL/V8 – This is a PostgreSQL procedural language extension powered by the V8 JavaScript Engine. With this extension, you can write JavaScript functions that can be called from SQL.

You will need to create new instances or upgrade your existing instances to version 9.3.5 using the AWS Management Console or APIs in order to take advantage of these extensions.

The Amazon RDS for PostgreSQL page contains all of the technical and pricing information you’ll need to have in order to get started.

— Srikanth Deshpande, Senior Product Manager, Amazon RDS