Amazon Relational Database Service User Guide Amazon Relational Database Service User Guide Amazon Relational Database Service: User Guide Copyright © 2023 Amazon Web Services, Inc. and/or its affiliates. All rights reserved. Amazon's trademarks and trade dress may not be used in connection with any product or service that is not Amazon's, in any manner that is likely to cause confusion among customers, or in any manner that disparages or discredits Amazon. All other trademarks not owned by Amazon are the property of their respective owners, who may or may not be affiliated with, connected to, or sponsored by Amazon. Amazon Relational Database Service User Guide Table of Contents What is Amazon RDS? ........................................................................................................................ 1 Overview................................................................................................................................... 1 Amazon EC2 and on-premises databases ............................................................................... 1 Amazon RDS and Amazon EC2 ............................................................................................ 2 Amazon RDS Custom for Oracle and Microsoft SQL Server ...................................................... 3 Amazon RDS on AWS Outposts ............................................................................................ 3 DB instances.............................................................................................................................. 3 DB engines........................................................................................................................ 4 DB instance classes............................................................................................................ 4 DB instance storage............................................................................................................ 4 Amazon Virtual Private Cloud (Amazon VPC) ......................................................................... 5 AWS Regions and Availability Zones ............................................................................................. 5 Security.................................................................................................................................... 5 Monitoring an Amazon RDS DB instance ....................................................................................... 5 How to work with Amazon RDS ................................................................................................... 5 AWS Management Console .................................................................................................. 6 Command line interface ...................................................................................................... 6 Programming with Amazon RDS .......................................................................................... 6 How you are charged for Amazon RDS ......................................................................................... 6 What's next?.............................................................................................................................. 6 Getting started.................................................................................................................. 6 Topics specific to database engines ...................................................................................... 6 DB instances.............................................................................................................................. 8 DB instance classes................................................................................................................... 10 DB instance class types ..................................................................................................... 10 Supported DB engines ...................................................................................................... 13 Determining DB instance class support in AWS Regions ......................................................... 51 Changing your DB instance class ........................................................................................ 54 Configuring the processor ................................................................................................. 54 Hardware specifications..................................................................................................... 68 DB instance storage .................................................................................................................. 77 Storage types.................................................................................................................. 77 General Purpose SSD storage ............................................................................................. 77 Provisioned IOPS storage .................................................................................................. 79 Comparing SSD storage types ............................................................................................ 81 Magnetic storage.............................................................................................................. 82 Monitoring storage performance ........................................................................................ 82 Factors that affect storage performance .............................................................................. 82 Regions, Availability Zones, and Local Zones ................................................................................ 85 AWS Regions................................................................................................................... 86 Availability Zones............................................................................................................. 88 Local Zones..................................................................................................................... 89 Supported Amazon RDS features by Region and engine ................................................................ 90 Table conventions............................................................................................................. 90 Feature quick reference ..................................................................................................... 90 Blue/Green Deployments................................................................................................... 92 Cross-Region automated backups ....................................................................................... 92 Cross-Region read replicas ................................................................................................. 93 Database activity streams .................................................................................................. 94 Dual-stack mode.............................................................................................................. 98 Export snapshots to S3 ................................................................................................... 104 IAM database authentication............................................................................................ 109 Kerberos authentication.................................................................................................. 111 Multi-AZ DB clusters....................................................................................................... 116 iii Amazon Relational Database Service User Guide Performance Insights...................................................................................................... 120 RDS Custom................................................................................................................... 120 Amazon RDS Proxy ......................................................................................................... 123 Secrets Manager integration ............................................................................................ 129 Engine-native features.................................................................................................... 129 Multi-AZ deployments............................................................................................................. 131 Multi-AZ DB instance deployments ................................................................................... 132 Multi-AZ DB cluster deployments ..................................................................................... 137 DB instance billing for Amazon RDS ......................................................................................... 149 On-Demand DB instances ................................................................................................ 150 Reserved DB instances .................................................................................................... 151 Setting up..................................................................................................................................... 159 Sign up for an AWS account .................................................................................................... 159 Create an administrative user ................................................................................................... 159 Create IAM user access keys ..................................................................................................... 160 Determine requirements.......................................................................................................... 161 Provide access to your DB instance ........................................................................................... 162 Getting started ............................................................................................................................... 165 Creating a MariaDB DB instance and connecting to a database ..................................................... 166 Creating a MariaDB DB instance ....................................................................................... 166 Connecting to a database on a DB instance running MariaDB ............................................... 170 Deleting a DB instance .................................................................................................... 173 Creating a SQL Server DB instance and connecting to it .............................................................. 174 Creating a sample SQL Server DB instance ........................................................................ 174 Connecting to your sample DB instance ............................................................................ 178 Exploring your sample DB instance ................................................................................... 181 Deleting your sample DB instance .................................................................................... 182 Creating a MySQL DB instance and connecting to a database ....................................................... 184 Creating a MySQL DB instance ......................................................................................... 184 Connecting to a database on a DB instance running MySQL ................................................. 188 Deleting a DB instance .................................................................................................... 191 Creating an Oracle DB instance and connecting to a database ...................................................... 192 Creating a sample Oracle DB instance ............................................................................... 192 Connecting to your sample DB instance ............................................................................ 196 Deleting your sample DB instance .................................................................................... 198 Creating a PostgreSQL DB instance and connecting to a database ................................................. 199 Creating a PostgreSQL DB instance ................................................................................... 199 Connecting to a PostgreSQL DB instance ........................................................................... 203 Deleting a DB instance .................................................................................................... 209 Tutorial: Create a web server and an Amazon RDS DB instance ..................................................... 210 Launch an EC2 instance ................................................................................................... 211 Create a DB instance ....................................................................................................... 215 Install a web server ........................................................................................................ 219 Tutorials and sample code ............................................................................................................... 226 Tutorials in this guide ............................................................................................................. 226 Tutorials in other AWS guides .................................................................................................. 227 Tutorials and sample code in GitHub ......................................................................................... 227 Best practices for Amazon RDS ........................................................................................................ 228 Amazon RDS basic operational guidelines .................................................................................. 228 DB instance RAM recommendations .......................................................................................... 229 Using Enhanced Monitoring to identify operating system issues .................................................... 229 Using metrics to identify performance issues ............................................................................. 229 Viewing performance metrics ........................................................................................... 229 Evaluating performance metrics ....................................................................................... 232 Tuning queries....................................................................................................................... 233 Best practices for working with MySQL ..................................................................................... 234 Table size...................................................................................................................... 234 iv Amazon Relational Database Service User Guide Number of tables........................................................................................................... 234 Storage engine............................................................................................................... 235 Best practices for working with MariaDB ................................................................................... 235 Table size...................................................................................................................... 235 Number of tables........................................................................................................... 236 Storage engine............................................................................................................... 236 Best practices for working with Oracle ...................................................................................... 236 Best practices for working with PostgreSQL ............................................................................... 236 Loading data into a PostgreSQL DB instance ...................................................................... 237 Working with the PostgreSQL autovacuum feature ............................................................. 237 Amazon RDS for PostgreSQL best practices video ............................................................... 238 Best practices for working with SQL Server ................................................................................ 238 Amazon RDS for SQL Server best practices video ................................................................ 239 Working with DB parameter groups .......................................................................................... 239 Best practices for automating DB instance creation ..................................................................... 239 Amazon RDS new features and best practices presentation video .................................................. 240 Configuring a DB instance ............................................................................................................... 241 Creating a DB instance ............................................................................................................ 242 Prerequisites.................................................................................................................. 242 Creating a DB instance .................................................................................................... 245 Available settings........................................................................................................... 249 Creating a Multi-AZ DB cluster ................................................................................................. 264 Prerequisites.................................................................................................................. 264 Creating a DB cluster ...................................................................................................... 267 Available settings........................................................................................................... 270 Nonapplicable settings.................................................................................................... 277 Creating resources with AWS CloudFormation ............................................................................ 278 RDS and AWS CloudFormation templates .......................................................................... 278 Learn more about AWS CloudFormation ............................................................................ 278 Connecting to a DB instance .................................................................................................... 279 Finding the connection information .................................................................................. 279 Database authentication options...................................................................................... 282 Encrypted connections.................................................................................................... 283 Scenarios for accessing a DB instance ................................................................................ 283 Connecting to a DB instance running a specific DB engine ................................................... 283 Managing connections with RDS Proxy .............................................................................. 284 Working with option groups .................................................................................................... 285 Option groups overview .................................................................................................. 285 Creating an option group ................................................................................................ 286 Copying an option group ................................................................................................. 288 Adding an option to an option group ................................................................................ 289 Listing the options and option settings for an option group ................................................. 293 Modifying an option setting ............................................................................................ 294 Removing an option from an option group ........................................................................ 297 Deleting an option group ................................................................................................ 298 Working with parameter groups ............................................................................................... 301 Working with DB parameter groups .................................................................................. 303 Working with DB cluster parameter groups ........................................................................ 314 Comparing parameter groups ........................................................................................... 322 Specifying DB parameters ................................................................................................ 322 Managing a DB instance .................................................................................................................. 328 Stopping a DB instance ........................................................................................................... 329 Benefits......................................................................................................................... 330 Limitations..................................................................................................................... 330 Option and parameter group considerations ...................................................................... 330 Public IP address ............................................................................................................ 330 Stopping a DB instance ................................................................................................... 330 v Amazon Relational Database Service User Guide Starting a DB instance ............................................................................................................ 332 Connecting an EC2 instance ..................................................................................................... 333 Overview....................................................................................................................... 333 Connecting an EC2 instance ............................................................................................. 336 Viewing connecting compute resources ............................................................................. 338 Modifying a DB instance .......................................................................................................... 340 Apply Immediately setting ............................................................................................... 341 Available settings........................................................................................................... 341 Modifying a Multi-AZ DB cluster ............................................................................................... 355 Apply Immediately setting ............................................................................................... 356 Available settings........................................................................................................... 356 Nonapplicable settings.................................................................................................... 365 Maintaining a DB instance ....................................................................................................... 366 Viewing pending maintenance .......................................................................................... 366 Applying updates........................................................................................................... 368 Maintenance for Multi-AZ deployments ............................................................................. 369 The maintenance window ................................................................................................ 370 Adjusting the maintenance window for a DB instance .......................................................... 371 Working with operating system updates ............................................................................ 372 Upgrading the engine version .................................................................................................. 376 Manually upgrading the engine version ............................................................................. 376 Automatically upgrading the minor engine version ............................................................. 378 Renaming a DB instance .......................................................................................................... 381 Renaming to replace an existing DB instance ..................................................................... 381 Renaming a Multi-AZ DB cluster ............................................................................................... 383 Renaming to replace an existing Multi-AZ DB cluster ........................................................... 383 Rebooting a DB instance ......................................................................................................... 385 Rebooting Multi-AZ DB clusters ................................................................................................ 387 Working with read replicas ...................................................................................................... 389 Overview....................................................................................................................... 391 Creating a read replica .................................................................................................... 394 Promoting a read replica ................................................................................................. 396 Monitoring read replication .............................................................................................. 399 Cross-Region read replicas ............................................................................................... 402 Tagging RDS resources ............................................................................................................ 411 Overview....................................................................................................................... 411 Using tags for access control with IAM .............................................................................. 412 Using tags to produce detailed billing reports .................................................................... 412 Adding, listing, and removing tags .................................................................................... 413 Using the AWS Tag Editor ............................................................................................... 415 Copying tags to DB instance snapshots ............................................................................. 415 Tutorial: Use tags to specify which DB instances to stop ...................................................... 416 Enabling backups........................................................................................................... 418 Working with ARNs ................................................................................................................. 421 Constructing an ARN ....................................................................................................... 421 Getting an existing ARN .................................................................................................. 425 Working with storage .............................................................................................................. 429 Increasing DB instance storage capacity ............................................................................ 429 Managing capacity automatically with storage autoscaling ................................................... 431 Modifying Provisioned IOPS settings ................................................................................. 435 I/O-intensive storage modifications .................................................................................. 437 Modifying General Purpose (gp3) settings .......................................................................... 437 Deleting a DB instance ............................................................................................................ 440 Deletion protection......................................................................................................... 440 Final snapshots and retained backups ............................................................................... 440 Deleting a DB instance .................................................................................................... 441 Deleting a Multi-AZ DB cluster................................................................................................. 443 vi Amazon Relational Database Service User Guide Using Blue/Green Deployments for database updates ......................................................................... 445 Overview of Amazon RDS Blue/Green Deployments .................................................................... 446 Benefits......................................................................................................................... 446 Workflow....................................................................................................................... 447 Authorizing access.......................................................................................................... 451 Considerations................................................................................................................ 451 Best practices................................................................................................................. 452 Region and version availability ......................................................................................... 453 Limitations..................................................................................................................... 453 Creating a blue/green deployment ........................................................................................... 454 Making changes in the green environment ......................................................................... 454 Handling lazy loading ..................................................................................................... 454 Creating the blue/green deployment ................................................................................ 455 Viewing a blue/green deployment ............................................................................................ 457 Switching a blue/green deployment ......................................................................................... 461 Switchover timeout......................................................................................................... 461 Switchover guardrails...................................................................................................... 462 Switchover actions.......................................................................................................... 462 Switchover best practices ................................................................................................ 463 Switching over a blue/green deployment .......................................................................... 463 After switchover............................................................................................................. 465 Deleting a blue/green deployment ........................................................................................... 465 Backing up and restoring a DB instance ............................................................................................ 468 Working with backups ............................................................................................................. 469 Backup storage.............................................................................................................. 469 Backup window.............................................................................................................. 470 Backup retention period .................................................................................................. 471 Enabling automated backups........................................................................................... 471 Retaining automated backups .......................................................................................... 473 Deleting retained automated backups ............................................................................... 475 Disabling automated backups.......................................................................................... 476 Using AWS Backup ......................................................................................................... 477 Unsupported MySQL storage engines ................................................................................ 477 Unsupported MariaDB storage engines .............................................................................. 478 Cross-Region automated backups ............................................................................................. 479 Region and version availability ......................................................................................... 479 Source and destination AWS Region support ...................................................................... 479 Enabling cross-Region automated backups ........................................................................ 481 Finding information about replicated backups .................................................................... 483 Point-in-time recovery from a replicated backup ................................................................ 486 Stopping backup replication ............................................................................................ 487 Deleting replicated backups ............................................................................................. 488 Creating a DB snapshot ........................................................................................................... 490 Creating a Multi-AZ DB cluster snapshot ................................................................................... 492 Restoring from a DB snapshot ................................................................................................. 494 Parameter groups........................................................................................................... 494 Security groups.............................................................................................................. 494 Option groups................................................................................................................ 495 Tagging......................................................................................................................... 495 Microsoft SQL Server ...................................................................................................... 495 Oracle Database............................................................................................................. 496 Restoring from a snapshot ............................................................................................... 496 Restoring from a snapshot to a Multi-AZ DB cluster .................................................................... 498 Restoring from a Multi-AZ DB cluster snapshot to a DB instance ................................................... 500 Copying a DB snapshot ........................................................................................................... 502 Limitations..................................................................................................................... 502 Snapshot retention......................................................................................................... 502 vii Amazon Relational Database Service User Guide Copying shared snapshots ............................................................................................... 502 Handling encryption....................................................................................................... 503 Incremental snapshot copying .......................................................................................... 503 Cross-Region copying...................................................................................................... 504 Option groups................................................................................................................ 507 Parameter groups........................................................................................................... 507 Copying a DB snapshot ................................................................................................... 508 Sharing a DB snapshot............................................................................................................ 516 Sharing public snapshots................................................................................................. 517 Sharing encrypted snapshots ........................................................................................... 518 Sharing a snapshot......................................................................................................... 520 Exporting DB snapshot data to Amazon S3 ................................................................................ 525 Region and version availability ......................................................................................... 525 Limitations..................................................................................................................... 525 Overview of exporting snapshot data ................................................................................ 526 Setting up access to an S3 bucket .................................................................................... 527 Exporting a DB snapshot ................................................................................................. 530 Monitoring snapshot exports ........................................................................................... 533 Canceling a snapshot export ............................................................................................ 534 Failure messages............................................................................................................ 535 Troubleshooting PostgreSQL permissions errors ................................................................. 536 File naming convention ................................................................................................... 536 Data conversion............................................................................................................ 537 Point-in-time recovery............................................................................................................. 543 Restoring a Multi-AZ DB cluster to a specified time ..................................................................... 546 Deleting a DB snapshot........................................................................................................... 549 Deleting a DB snapshot................................................................................................... 549 Tutorial: Restore a DB instance from a DB snapshot .................................................................... 551 Restoring a DB instance from a DB snapshot ...................................................................... 551 Monitoring metrics in a DB instance ................................................................................................. 554 Overview of monitoring .......................................................................................................... 555 Monitoring plan............................................................................................................. 555 Performance baseline...................................................................................................... 555 Performance guidelines................................................................................................... 555 Monitoring tools............................................................................................................. 556 Viewing instance status and recommendations ........................................................................... 559 Viewing Amazon RDS DB instance status ........................................................................... 560 Viewing Amazon RDS recommendations............................................................................ 564 Viewing metrics in the Amazon RDS console .............................................................................. 569 Monitoring RDS with CloudWatch ............................................................................................. 572 Overview of Amazon RDS and Amazon CloudWatch ............................................................ 573 Viewing CloudWatch metrics ............................................................................................ 574 Creating CloudWatch alarms ............................................................................................ 579 Tutorial: Creating a CloudWatch alarm for DB cluster replica lag ........................................... 580 Monitoring DB load with Performance Insights ........................................................................... 587 Overview of Performance Insights .................................................................................... 587 Turning Performance Insights on and off ........................................................................... 593 Turning on the Performance Schema for MariaDB or MySQL ................................................ 597 Performance Insights policies ........................................................................................... 600 Analyzing metrics with the Performance Insights dashboard ................................................ 603 Retrieving metrics with the Performance Insights API .......................................................... 628 Logging Performance Insights calls using AWS CloudTrail .................................................... 642 Monitoring the OS with Enhanced Monitoring ............................................................................ 644 Overview of Enhanced Monitoring .................................................................................... 644 Setting up and enabling Enhanced Monitoring ................................................................... 645 Viewing OS metrics in the RDS console ............................................................................. 649 Viewing OS metrics using CloudWatch Logs ....................................................................... 652 viii Amazon Relational Database Service User Guide RDS metrics reference ............................................................................................................. 653 CloudWatch metrics for RDS ............................................................................................ 653 CloudWatch dimensions for RDS...................................................................................... 660 CloudWatch metrics for Performance Insights .................................................................... 661 Counter metrics for Performance Insights .......................................................................... 662 SQL statistics for Performance Insights ............................................................................. 672 OS metrics in Enhanced Monitoring .................................................................................. 677 Monitoring events, logs, and database activity streams ....................................................................... 686 Viewing logs, events, and streams in the Amazon RDS console ..................................................... 686 Monitoring RDS events............................................................................................................ 690 Overview of events for Amazon RDS ................................................................................. 690 Viewing Amazon RDS events ............................................................................................ 691 Working with Amazon RDS event notification .................................................................... 694 Creating a rule that triggers on an Amazon RDS event ........................................................ 708 Amazon RDS event categories and event messages ............................................................. 712 Monitoring RDS logs............................................................................................................... 725 Viewing and listing database log files............................................................................... 725 Downloading a database log file ...................................................................................... 726 Watching a database log file ............................................................................................ 727 Publishing to CloudWatch Logs ........................................................................................ 728 Reading log file contents using REST ................................................................................ 730 MariaDB database log files.............................................................................................. 732 Microsoft SQL Server database log files ............................................................................ 741 MySQL database log files ................................................................................................ 745 Oracle database log files................................................................................................. 754 PostgreSQL database log files .......................................................................................... 761 Monitoring RDS API calls in CloudTrail ...................................................................................... 770 CloudTrail integration with Amazon RDS ........................................................................... 770 Amazon RDS log file entries............................................................................................ 770 Monitoring Oracle with Database Activity Streams ...................................................................... 774 Overview....................................................................................................................... 774 Configuring Oracle unified auditing.................................................................................. 777 Starting a database activity stream ................................................................................... 777 Modifying a database activity stream ................................................................................ 779 Getting the activity stream status ..................................................................................... 781 Stopping a database activity stream ................................................................................. 782 Monitoring activity streams ............................................................................................. 783 Managing access to activity streams .................................................................................. 797 Working with Amazon RDS Custom .................................................................................................. 800 Database customization challenge............................................................................................ 800 RDS Custom management model and benefits ........................................................................... 801 Shared responsibility model ............................................................................................. 801 Key benefits of RDS Custom ............................................................................................ 803 RDS Custom architecture ......................................................................................................... 803 VPC.............................................................................................................................. 804 Amazon S3.................................................................................................................... 804 AWS CloudTrail.............................................................................................................. 805 RDS Custom automation and monitoring ........................................................................... 805 Security considerations for RDS Custom .................................................................................... 807 Working with RDS Custom for Oracle ........................................................................................ 808 RDS Custom for Oracle workflow ..................................................................................... 808 RDS Custom for Oracle requirements and limitations .......................................................... 811 Setting up your RDS Custom for Oracle environment .......................................................... 813 Working with CEVs for RDS Custom for Oracle ................................................................... 826 Configuring an RDS Custom for Oracle DB instance ............................................................ 852 Managing an RDS Custom for Oracle DB instance ............................................................... 861 Working with RDS Custom for Oracle replicas .................................................................... 872 ix Amazon Relational Database Service User Guide Backing up and restoring an RDS Custom for Oracle DB instance .......................................... 877 Upgrading a DB instance for RDS Custom for Oracle ........................................................... 884 Working with RDS Custom for SQL Server ................................................................................. 889 RDS Custom for SQL Server workflow ............................................................................... 889 RDS Custom for SQL Server requirements and limitations .................................................... 891 Setting up your RDS Custom for SQL Server environment .................................................... 893 Working with CEVs for RDS Custom for SQL Server ............................................................. 906 Creating and connecting to an RDS Custom for SQL Server DB instance ................................. 918 Managing an RDS Custom for SQL Server DB instance ......................................................... 926 Backing up and restoring an RDS Custom for SQL Server DB instance .................................... 935 Migrating an on-premises database to RDS Custom for SQL Server ....................................... 943 Upgrading a DB instance for RDS Custom for SQL Server ..................................................... 946 Troubleshooting RDS Custom ................................................................................................... 947 Viewing RDS Custom events ............................................................................................ 947 Subscribing to event notifications ..................................................................................... 947 Troubleshooting CEV creation .......................................................................................... 948 Troubleshooting CEV errors for RDS Custom for SQL Server ................................................. 949 Support perimeter and unsupported configurations ............................................................ 951 Fixing unsupported configurations .................................................................................... 952 How Amazon RDS Custom replaces an impaired host .......................................................... 957 Troubleshooting RDS Custom for Oracle upgrade issues ...................................................... 959 Troubleshooting RDS Custom for Oracle replica promotion .................................................. 960 Working with RDS on AWS Outposts ................................................................................................. 961 Prerequisites.......................................................................................................................... 962 Support for Amazon RDS features ............................................................................................ 963 Supported DB instance classes ................................................................................................. 966 Customer-owned IP addresses .................................................................................................. 968 Using CoIPs................................................................................................................... 968 Limitations..................................................................................................................... 969 Multi-AZ deployments............................................................................................................. 970 Working with the shared responsibility model .................................................................... 970 Improving availability...................................................................................................... 970 Prerequisites.................................................................................................................. 971 Working with API operations for Amazon EC2 permissions ................................................... 972 Creating DB instances for RDS on Outposts ............................................................................... 973 Creating read replicas for RDS on Outposts ............................................................................... 980 Considerations for restoring DB instances .................................................................................. 982 Using RDS Proxy ............................................................................................................................ 983 Region and version availability ................................................................................................. 983 Quotas and limitations............................................................................................................ 984 RDS for MariaDB limitations ............................................................................................. 984 RDS for SQL Server limitations ......................................................................................... 985 MySQL limitations.......................................................................................................... 985 PostgreSQL limitations.................................................................................................... 986 Planning where to use RDS Proxy ............................................................................................. 986 RDS Proxy concepts and terminology ........................................................................................ 987 Overview of RDS Proxy concepts ...................................................................................... 987 Connection pooling......................................................................................................... 988 Security......................................................................................................................... 989 Failover......................................................................................................................... 990 Transactions................................................................................................................... 991 Getting started with RDS Proxy ................................................................................................ 991 Setting up network prerequisites ...................................................................................... 991 Setting up database credentials in Secrets Manager ............................................................ 993 Setting up IAM policies ................................................................................................... 995 Creating an RDS Proxy .................................................................................................... 997 Viewing an RDS Proxy ................................................................................................... 1000 x
Description: