315_postgres in amazon rds
TRANSCRIPT
-
7/25/2019 315_Postgres in Amazon RDS
1/37
/
Postgres in Amazon RDS
Denish Patel
Lead Database Architect
-
7/25/2019 315_Postgres in Amazon RDS
2/37
Who am I ?
Database Architect with OmniTIfor last 7+ years
Expertise in PostgreSQL , Oracle, MySQL, NoSQL
Contact : [email protected], Twitter: @DenishPatel
Blog: http://www.pateldenish.com
Providing Solutions for business problems to deliver
Scalability Reliability
High Availability
Consistency
Security1
We are hiring!!
Apply @ l42.org/lg
-
7/25/2019 315_Postgres in Amazon RDS
3/37
Agenda
What is Amazon RDS?Amazon RDS Service Highlights
Setting up Postgres RDS Instance
Postgres RDS FeaturesAdministration and Limitation
Q & A
2
-
7/25/2019 315_Postgres in Amazon RDS
4/37
-
7/25/2019 315_Postgres in Amazon RDS
5/37
Service Highlights
Managed
Compatible
Scalable Database in the Cloud
Designed for use with other Amazon WebServices
Inexpensive
4
-
7/25/2019 315_Postgres in Amazon RDS
6/37
Getting Started with Postgres RDS (Beta)
5
-
7/25/2019 315_Postgres in Amazon RDS
7/37
Production?
6
-
7/25/2019 315_Postgres in Amazon RDS
8/37
Select Instance Type and Space
7
-
7/25/2019 315_Postgres in Amazon RDS
9/37
Choose VPC and Postgres config
8
-
7/25/2019 315_Postgres in Amazon RDS
10/37
Backups & Maintenance Window
9
-
7/25/2019 315_Postgres in Amazon RDS
11/37
Postgres Instance is Ready!
10
-
7/25/2019 315_Postgres in Amazon RDS
12/37
Lets test it out!
11
-
7/25/2019 315_Postgres in Amazon RDS
13/37
Pre-configured Parameters
max_connections= {DBInstanceClassMemory/12582880} ; 604 effective_cache_size = {DBInstanceClassMemory/16384} ;
3.6GB
shared_buffers = {DBInstanceClassMemory/32768} ; 1.8GB
maintenance_work_mem = default ; 16MB (Can be changed)
work_mem = default ; 1MB (Can be changed)
log_line_prefix = %t:%r:%u@%d:[%p]: (cannot changed)
log_min_duration_statement (disabled by default, enable it)
12
-
7/25/2019 315_Postgres in Amazon RDS
14/37
Pre-configured Parameters
Pros:
Easy to create Parameter Groups and apply it to specific
instance
i.e apply to dev instance vs prodAllow dynamic calculation based on
DBInstanceClassMemory
Cons:
Can not change some parameters
13
-
7/25/2019 315_Postgres in Amazon RDS
15/37
Automatic Software Patching
As of now, Postgres 9.3.1, 9.3.2 and 9.3.3 versions areavailable
You can control upgrade time
You have to wait till the new version is available
Postgres RDS doesnt support anything older than 9.3
Postgres 9.3 replication bugs
Delay in upgrade
14
-
7/25/2019 315_Postgres in Amazon RDS
16/37
Point-in-Time Restore
15
-
7/25/2019 315_Postgres in Amazon RDS
17/37
Point-in-Time Restore
16
-
7/25/2019 315_Postgres in Amazon RDS
18/37
DB Snapshots Manual or Automated
17
-
7/25/2019 315_Postgres in Amazon RDS
19/37
DB Events
18
-
7/25/2019 315_Postgres in Amazon RDS
20/37
DB Event Notifications
19
-
7/25/2019 315_Postgres in Amazon RDS
21/37
Multi-Availability Zone (Multi-AZ) Deployments
20
-
7/25/2019 315_Postgres in Amazon RDS
22/37
Multi AZ , not Multi Region
21
-
7/25/2019 315_Postgres in Amazon RDS
23/37
Provisioned IOPS
IOPS Ranges : 1000 - 30,000
Storage Ranges : 100 GB - 3 TB Range of IOPS to Storage (GB) Ratio : 3:1 10:1
For Example,
you could start by provisioning an Postgres DB instancewith 1000 IOPS and 200 GB storage (a ratio of 5:1).
You could then scale up to 2000 IOPS with 200 GB of
storage (a ratio of 10:1), 3000 IOPS with 300 GB of storage
Up to the maximum for Postgres DB instance of 30,000IOPS with 3 TB (3000 GB) of storage.
22
-
7/25/2019 315_Postgres in Amazon RDS
24/37
Push-Button Scaling
23
-
7/25/2019 315_Postgres in Amazon RDS
25/37
Push-Button Scaling
Took about an hour to increase IOPs and Disk space from 1000IOPs (100GB) to 2000 IOPs(200GB)
Multi AZ was quick because it was applied during instance creation
24
-
7/25/2019 315_Postgres in Amazon RDS
26/37
Isolation and Security
Access from specific CIDR/IP
Limited Default Roles
Access from specific EC2 security group
SSL is ON
25
-
7/25/2019 315_Postgres in Amazon RDS
27/37
Monitoring & Metrics
26
-
7/25/2019 315_Postgres in Amazon RDS
28/37
Other Features
Automatic Host replacement in case ofhardware failure
Replication and automated failover
Synchronous replication is used forfailover
27
-
7/25/2019 315_Postgres in Amazon RDS
29/37
Postgres Logs Monitoring
Possible to download postgres logs for analyze with PgBadger
Install Amazon RDS Command Line Toolkit
rds-watch-db-logfile omniti --log-file-name error/postgresql.log.2014-04-16-22
rds-download-db-logfile DBInstanceIdentifier --log-file-name $filename
28
-
7/25/2019 315_Postgres in Amazon RDS
30/37
PostgreSQL Supported Extensions
29
-
7/25/2019 315_Postgres in Amazon RDS
31/37
PgBench results
Single AZ Multi AZ
# of transactionprocessed
117611 112009
tps (including connectionsestablishing)
195.729775 186.482602
tps (excluding connectionsestablishing)
209.247055 199.764921
m1.large
Provisioned iops = 1000Number of clients: 100Number of threads: 1
Duration: 600 s
30
-
7/25/2019 315_Postgres in Amazon RDS
32/37
Limitations
Replica feature is missing
Streaming replication (Coming up?)
Limited migration/upgrade options
pg_dump/restoreWhat about major upgrade?
pgbouncer can not be installed on DB server
Can not install custom extensionsmimeo, pg_partman etc.
31
-
7/25/2019 315_Postgres in Amazon RDS
33/37
Pricing
m1.large2000 IOPs
200GB
On Demand Reserved (3 yr)
Single AZ $400/month $368/month
Multi AZ $800/month $636/month
Reserved instance can be 10-20% cheaperPrice doesnt include bandwidth
32
-
7/25/2019 315_Postgres in Amazon RDS
34/37
References
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.html
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html
Quick Reference CLI Commands: http://awsdocs.s3.amazonaws.com/RDS/latest/rds-qrc.pdf
http://www.postgresql.org/docs/9.2/static/pgbench.html
https://console.aws.amazon.com/rds/home
33
-
7/25/2019 315_Postgres in Amazon RDS
35/37
Further Reading .
Accessing PostgreSQL from Amazon RDS article onDatabase Trends and Applicationshttp://www.dbta.com/Editorial/Trends-and-
Applications/Accessing-PostgreSQL-from-Amazon-RDS-96507.aspx
34
-
7/25/2019 315_Postgres in Amazon RDS
36/37
Surge 2014
35
-
7/25/2019 315_Postgres in Amazon RDS
37/37
36
Questions?
Twitter: DenishPatelEmail: [email protected]