Amazon RDS – PostgreSQL Features

PostgreSQL is a powerful, open source object-relational database system which has earned a strong reputation for reliability, feature robustness, and performance. AWS RDS runs various versions of PostgreSQL. It supports point-in-time restore and backups, creation of DB snapshots and running it on a multi-AZ environment.

Supported Versions

The versions 9.3 through 10.4 are the major versions supported in the RDS platform. If no version is mentioned during the DB creation, it defaults to the most recent version at that point in time. Below is an example of how to get all supported DB Engine versions using AWS API in a python SDK program.

 

import boto3
client = boto3.client('rds')
response = client.describe_db_engine_versions(
    DBParameterGroupFamily='',
    DefaultOnly=True,
    Engine='postgres',
    EngineVersion='',
    ListSupportedCharacterSets=False, #True,
)

print(response)

When we run the above program, we get the following output −

 

{
   "ResponseMetadata": {
      "RetryAttempts": 0,
      "HTTPStatusCode": 200,
      "RequestId": "c85cd49f-2c16-44b4-9890-cb233651f962",
      "HTTPHeaders": {
         "x-amzn-requestid": "c85cd49f-2c16-44b4-9890-cb233651f962",
         "date": "Fri, 14 Sep 2018 07:31:34 GMT",
         "content-length": "995",
         "content-type": "text/xml"
      }
   },
   "u'DBEngineVersions'": [
      {
         "u'Engine'": "postgres",
         "u'DBParameterGroupFamily'": "postgres10",
         "u'SupportsLogExportsToCloudwatchLogs'": false,
         "u'SupportsReadReplica'": true,
         "u'DBEngineDescription'": "PostgreSQL",
         "u'EngineVersion'": "10.4",
         "u'DBEngineVersionDescription'": "PostgreSQL 10.4-R1",
         "u'ValidUpgradeTarget'": []
      }
   ]
}

Database Preview Environment

The PostgreSQL community releases new versions and new extensions continuously. You can try out new PostgreSQL versions and extensions before they are fully supported by Aws RDS. To do that, you can create a new DB instance in the Database Preview Environment.

DB instances in the Database Preview Environment are similar to DB instances in a production environment. However, keep in mind several important factors:

  • All DB instances are deleted 60 days after you create them, along with any backups and snapshots.
  • You can only create a DB instance in a virtual private cloud (VPC) based on the Amazon VPC service.
  • You can only create M4, T2, and R4 instance types. For more information about RDS instance classes,
  • You can’t get help from AWS Support with DB instances. You can post your questions in the RDS Database Preview Environment Forum.
  • You can only use General Purpose SSD and Provisioned IOPS SSD storage.
  • You can’t copy a snapshot of a DB instance to a production environment.
  • Some Amazon RDS features aren’t available in the preview environment, as described following.

Logical Replication

Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow cascading replication or more complex configurations. It is used for the below actions.

  • Sending incremental changes in a single database or a subset of a database to subscribers as they occur.
  • Consolidating multiple databases into a single one (for example for analytical purposes).
  • Replicating between different major versions of PostgreSQL.
  • Replicating between PostgreSQL instances on different platforms (for example Linux to Windows)
  • Giving access to replicated data to different groups of users.
  • Sharing a subset of the database between multiple databases.

To enable logical replication for an Amazon RDS for PostgreSQL DB instance

  • The AWS user account requires the rds_superuser role to perform logical replication for the PostgreSQL database on Amazon RDS.
  • Set the rds.logical_replication parameter to 1.
  • Modify the inbound rules of the security group for the publisher instance (production) to allow the subscriber instance (replica) to connect. This is usually done by including the IP address of the subscriber in the security group.