One of Amazon’s latest cloud technologies is the Relational Database Service (RDS) instance. While they provide compute instances via EC2 where you can run your databases on several different operating systems, managing failovers, backups and tuning wasn’t any different for those as it would be on any other server. Yet RDS provides more specialized instances that can be better managed and tuned for the needs of a database system.
PostgreSQL, an advanced open source database, is known for its rich set of features, durability and reliability. The latest releases of PostgreSQL provide many advanced features without compromising ACID compliance. In recent years, PostgreSQL has become the default backend choice of developers for web and enterprise applications. With PostgreSQL now available as one of the managed database offerings provided by Amazon RDS alongside MySQL, Oracle and SQL Server, here's what you need to know.
Basic Setup
Getting an RDS instance up and running is extremely easy, and if you’ve set up an EC2 instance before, it will seem very familiar.
For developers new to PostgreSQL, tuning the parameters to some sane values for even a minimal production system is a major challenge.Amazon RDS simplifies the process by choosing settings for the more important options (such as shared_buffers, work_mem) that are matched fairly well with the instance size chosen. It is very easy to change those settings and the parameters that require a restart to take effect are clearly laid out. You can then choose a maintenance window, for when the database can be restarted so those changes will go into effect.
Scaling
When you reach the point where your current instance isn’t cutting it for performance (or you realized you over-provisioned), RDS makes solving this problem particularly easy. Simply go to the instance list and choose “modify” which brings up a screen of options that you saw when setting up the original instance--and all of them are changeable. Simply choose your new instance parameters and during the next maintenance window, Amazon RDS will take care of these changes for you. So, while this does require an outage window, it’s no worse than if you were performing this process on your own hardware--and the process is much easier. Storage Scaling however requires no downtime.
Database Upgrades and Automated Backups
The maintenance window is also part of another advantage of RDS: automated minor version upgrades. In the past, minor releases have only required a brief restart, so the impact here is very minimal. There have been no new major version releases of Postgres RDS yet, so the major version upgrade path hasn’t been laid out. If it’s anything like the MySQL one, though, it’s pretty straightforward and the Snapshot feature makes testing and rollbacks easy.
Backups are automatically done to support point-in-time recovery. You can designate a retention period for the transaction logs which will then allow you to be able to restore your database to any time in that period. You can also take a Snapshot of the database at any time and keep it as long as necessary. The restoration for both automated and Snapshot backups is done to a second instance, so you can do this without interfering with your current production system or losing anything while it’s done.
Built-in Automated Failover
Perhaps the most important feature for production databases is one of the things RDS has made easiest to handle: failover. With the Multi-AZ (availability zone) feature, Amazon RDS automatically keeps a synchronous slave running in another zone within your region that can be failed over in case the master goes unresponsive. Multi-AZ failover is all managed by Amazon RDS, and because it’s using synchronous replication, it should work rather seamlessly when a switch happens. You can set up an Event monitor to let you know if/when this ever happens.
Security
Amazon RDS Security groups control access to the DB instance. Basically, two types of security groups are used with Amazon RDS:
* DB security groups (controls access to a DB instance that is not in a VPC)
* VPC security groups (controls access to a DB instance inside a VPC)
In most cases, a Postgres RDS instance is accessed by an application server running on an EC2 instance in the same VPC, and the application is, in turn, accessed over the Internet. For this scenario, you would create a separate VPC security group and add rules in the DB server VPC security group to allow access only from the application VPC security group.
Amazon RDS supports SSL encryption for PostgreSQL RDS instances as well. This allows you to encrypt connections between applications and the Postgres RDS instance. For this, you are required to install the sslinfo extension.
postgres=> create extension sslinfo;
CREATE EXTENSION
postgres=> select ssl_is_used();
ssl_is_used
-------------
t
(1 row)
With these options, Amazon provides several ways to secure communication between the application and database. It is recommended to use Postgres RDS with SSL.
Monitoring
The log files are available within the Amazon web interface, and can be viewed and searched as needed. You can either view them as a static file or in a “watch” mode, where you can view in near-real-time what is getting logged. For a high-traffic database, though, watching may not be very useful. Also, the log_line_prefix option cannot be changed, which is a big shortcoming for customized log parsing.