Deep Dive on Amazon RDS
PostgreSQL
Jignesh Shah, Amazon RDS
PostgreSQL
Robust feature sets and
extensions Reliable
High fault tolerance, ACID
Multi-Version Concurrency compliance, and full support for
Control (MVCC), point in time
foreign keys, joins, views, triggers,
recovery, granular access controls,
and stored procedures
tablespaces, asynchronous
replication, nested transactions,
online/hot backups, a refined Standards-compliant
query planner/optimizer, and Includes most SQL:2008 data
write ahead logging types, including INTEGER,
NUMERIC, BOOLEAN, CHAR,
Supports international character VARCHAR, DATE, INTERVAL, and
sets, multi-byte character TIMESTAMP. Supports storage of
encodings, Unicode, and it is binary large objects, including
locale-aware for sorting, case- pictures, sounds, or video
sensitivity, and formatting
PostgreSQL Deployment Options
EC2 DB Services
On-Premises Hosted Managed
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Running PostgreSQL on EC2
Full control over parameters of server, OS, and database
Remote access to host via ssh
Customer can install 3rd party applications and extensions
Self Managed PostgreSQL
• Customer has full responsibility for upgrades and backup
• Customer has major responsibility for security
• High Availability and replication are expensive, complex, and
require a lot of engineering
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Amazon Relational Database Service
Multi-engine support: Amazon Aurora, MySQL,
MariaDB, PostgreSQL, Oracle, SQL Server
Automated provisioning, patching, scaling,
replicas, backup/restore, failover
High availability with RDS Multi-AZ and Amazon
Aurora
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Security and Compliance
Cloud security at AWS is the highest
priority
Compliance is important to meet
industry and local regulations
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Security Groups
Database IP firewall protection
Corporate address admins
Protocol Port Range Source
TCP 3306 172.31.0.0/16
TCP 3306 “Application
security group”
Application tier
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Amazon Virtual Private Cloud (Amazon VPC)
10.1.0.0/16
Private Network
10.1.1.0/24
Security Group for fine grained
control
Availability Zone
AWS Region
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
At Rest Encryption
Leveraging AWS Key Management Service (KMS) Customer master
key(s)
Default key available for encryption
• Cannot share outside account
Recommended to security use separate keys for
each instances Data key 1 Data key 2 Data key 3 Data key 4
Benefits:
• Ability to share encrypted snapshots across
accounts
• Limited risks of a compromised key
Amazon Amazon Amazon
RDS RDS RDS
instance 1 instance 2 instance 3
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Forcing SSL on all connections
Applicatio Snapshot
n DB
Host SSL Instance
ssl_mode=disable
Security Group
VPC Log Backups
rds.force_ssl=1 (default 0) Encryption at Rest
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
IAM Governed Access
Use AWS Identity and Access Management
(IAM) to control who can perform actions on
RDS using web console or “aws” command
line interface Controlled with IAM
Examples:
1. Deploy, Modify, delete instances
2. Create, delete, promote read replicas
DBA and Ops
3. Reboot, Start/Stop instance
RDS
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Database Users Access
Connects to the database instance using
PostgreSQL Clients
Controlled with database
Examples: grants
CREATE USER foobar
ALTER DATABASE testdb RENAME to proddb
Applications Users and DBA
Your database
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
AWS Identity and Access Management (IAM)
authentication
PostgreSQL authentication is managed
externally using IAM
• Available for Amazon RDS PostgreSQL and
Aurora PostgreSQL
Authentication tokens are used to validate
the user
• Tokens have a lifetime of 15 minutes
• Generated using AWS Signature Version 4
New role rds_iam available when IAM
integration is enabled
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Restrict Password changes on PostgreSQL
instances Simplifies integration of home grown or
3rd party password management tools
New database parameter to restrict
password changes
• rds.restrict_password_commands =
on/off
Flexibility to assign a role to allow
certain users to
• GRANT rds_password TO tom;
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Compliance
EC2 RDS Aurora
SOC 1, 2, 3 PostgreSQL
SOC 1, 2, 3
ISO 20001/9001
SOC 1, 2, 3 ISO 20001/9001
ISO 27107/27018 ISO 20001/9001
PCI ISO 27107/27018 ISO 27107/27018
FedRAMP PCI PCI
HIPAA BAA FedRAMP HIPAA BAA
UK Gov. Programs HIPAA BAA
UK Gov. Programs Singapore MTCS
Singapore MTCS
Singapore MTCS
27001/9001
Details: https://aws.amazon.com/compliance/services-in-scope/ 27017/27018
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Database Parameter Groups
Create a standard group based on your
needs (IAM protected)
rds.force_ssl=true
shared_preload_libraries=pgaudit,
auto_explain,pg_stat_statements,pg_re
pack
pgaudit.role = rds_pgaudit
huge_pages = on
Use your standard group or copy to instance
specific parameter group
Avoid typos while editing parameters
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Database server instance types
General purpose (T2/T3) General purpose Memory optimized
(M4/M5) (R4/R5)
• 1 vCPU / 1 GB RAM to
8 vCPU 32 GB RAM • 2 vCPU / 8 GiB RAM to • 2 vCPU / 16 GiB RAM to
• Moderate networking 96 vCPU 384 GiB RAM 96 vCPU 768 GiB RAM
performance • High performance • High performance
• Good for smaller or variable networking networking
workloads • Good for running CPU • Good for query intensive
• T2.micro is eligible for free intensive workloads (e.g. workloads or high
tier WordPress) connection counts
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
High performance database storage
General purpose (GP2) Provisioned IOPS (IO1)
• SSD storage • SSD storage
• Maximum of 32 TB • Maximum of 32 TB
• Latency in milliseconds • Single digit millisecond
• IOPS determined by volume latencies
size • Maximum of 40 K IOPS
• Bursts to 3,000 IOPS • Delivers within 10% of
(applicable below 1.3 TB) the IOPS performance
• Affordable performance 99.9% of the time
• High performance and
consistency
Automated Backup Snapshots
RDS PostgreSQL Snapshots
• Scheduled daily volume backup of entire instance
• Archive database change logs (WAL)
• 35-day retention Every day during your
• Multiple copies in each AZ when running multi-AZ Every five minutes,
backup window, RDS
RDS backs up the
• Taken from standby when running multi-AZ creates a storage
transaction logs of
volume snapshot of
your database
your database
Aurora PostgreSQL Snapshots
• Automatic, continuous, incremental backups
• No impact on database performance
• 35-day retention
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Availability – Read and Write – Multi-AZ
cname update
DNS
Primary Update
AZ1 Physical AZ2
Synchronous
Replication
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Read Replicas
• Relieve pressure on your master node
with additional read capacity
• Bring data close to your applications
in different regions
• Promote a Read Replica to a master
for faster recovery in the event of
disaster
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Read Replicas = Availability
Sync
Replication
Multi-AZ
Async Replication
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Cross Region Replicas – Reduce Latency
Async Replication
AZ1 AZ2 AZ1
US-EAST-1 EU-WEST-1
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Cross Region Replicas – DR & Moves
Async Replication
AZ1 AZ1 AZ2
AZ2
US-EAST-1 EU-WEST-1 EU-WEST-1
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Minor version upgrade
Prod reboot Prod
10.1 10.3
Minor Versions upgrade on managed services
- Shutdown instance
- Replace version binaries
- Start instance
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Major version upgrade
Prod pg_upgrade Prod
9.6 10
No PITR
Backup Backup
Restore to a test instance
Test Test Application
pg_upgrade
9.6 10 Testing
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Upgrade with minimum downtime using DMS
RDS PostgreSQL 9.5 RDS PostgreSQL 10
AWS Database
Migration Service
Create new target instance
Create Schema on Target ( SCT) Let the AWS Database Migration
Start a replication instance Service truncate tables and load
data
Connect to source and target databases Application Users Uses change data capture to keep
Select tables, schemas, or databases them in sync
Switch applications over to the
target at your convenience
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Logical Replication Support
• Set rds.logical_replication parameter to 1
• As user who has rds_replication & rds_superuser role
SELECT * FROM pg_create_logical_replication_slot('test_slot',
'test_decoding');
pg_recvlogical -d postgres --slot test_slot -U master --host $rds_hostname -f - --
start
• Support for Event Triggers
• Now support
• Native Logical replication RDS
On
NoSQL
Postgres
• Pglogical Premise
Logical
DB
Custom Postgres
• wal2json RDS DMS
Logical Replica
Postgres
• decoder_raw Handler
RDS/Aurora
EC2
Redshift
PostgreSQL
Postgres
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Amazon RDS for PostgreSQL
Support for latest minor releases
• 10.6, 9.6.11, 9.5.15, 9.4.20
60+ extensions supported
• pg_similarity, orafce, pageinspect, amcheck
PostgreSQL Version 11 available in preview
https://aws.amazon.com/rds/databasepreview/
PostgreSQL Extensions/Modules Supported
9.3 Original - 32
9.3 Current - 35
9.4 Current - 39
9.5 Current - 46
9.6 Current - 58
10 Current - 60
Future - ???
Email: rds-postgres-extensions-request@amazon.com
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
New PostgreSQL Extensions Supported
Extensions Description
pglogical Support logical replication –for PostgreSQL 9.6 and PostgreSQL 10
pg_similarity Extension for supporting similar text queries
pageinspect Allows to inspect the contents of database pages at a low level
protobuf Enable Map Box Vector Tiles support in PostGIS
amcheck Allows verify the logical consistency of the structure of indexes
orafce Implements commonly used functions to ease migration from Oracle
prefix Makes it easy to match prefix using @> operator
Replication in Amazon RDS PostgreSQL
• Statement based
Logical - SQL • Trigger Based
• Standard PostgreSQL
• Extension “pglogical”
Logical - Engine • AWS DMS
• Third-party
• Read replicas
Physical - Engine • Multi-AZ
Monitoring Amazon RDS
Performance
Insights
• Measures database load to help
you identify bottlenecks
• Top SQL/most intensive queries
• Adjustable timeframe: hour, day, week,
longer
• Compliments other key tools
• query execution plans
• pg_stat_statements
Monitoring Amazon RDS
Enhanced
Monitoring
Enhanced Monitoring for Amazon RDS
Access to over 50 CPU, memory, file
system, and disk I/O metrics
Access to top processes
As low as 1 second intervals
Monitoring Amazon RDS
Amazon
CloudWatch Amazon CloudWatch metrics
Displayed in the Amazon RDS Console or
personalized CloudWatch dashboards
As low as one minute intervals
Amazon CloudWatch alarms
Trigger actions based on a metric value
relative to a threshold you set
Upload PostgreSQL Logs to Amazon Cloudwatch
Amazon
CloudWatch Amazon CloudWatch
Upload PostgreSQL logs to CloudWatch
Export logs to S3 from CloudWatch
AWS Forums
EC2
• https://forums.aws.amazon.com/forum.jspa?forumID=30
Amazon RDS
• https://forums.aws.amazon.com/forum.jspa?forumID=60
Aurora with PostgreSQL compatibility
• https://forums.aws.amazon.com/forum.jspa?forumID=227
© 2019, Amazon Web Services, Inc. or its Affiliates. All rights reserved.
Thank you!
aws.amazon.com/rds/postgresql
