Amazon Aurora PostgreSQL on AWS

Partner Solution Deployment Guide

QS

July 2023
Arabinda Pani, AWS Database
Tony Vattathil, AWS Integration & Automation team

Refer to the GitHub repository to view source files, report bugs, submit feature ideas, and post feedback about this Partner Solution. To comment on the documentation, refer to Feedback.

This Partner Solution was created by Amazon in collaboration with Amazon Web Services (AWS). Partner Solutions are automated reference deployments that help people deploy popular technologies on AWS according to AWS best practices. If you’re unfamiliar with AWS Partner Solutions, refer to the AWS Partner Solution General Information Guide.

Overview

This Quick Start reference deployment guide provides step-by-step instructions for deploying an Amazon Aurora PostgreSQL database on the AWS Cloud with high availability and failover support.

This Quick Start is for users who are looking for a repeatable, customizable reference deployment for Aurora PostgreSQL using AWS CloudFormation. You can also use the Quick Start as a building block in your own automated deployments by using the existing-VPC template that we’ve provided. This template will build only the Aurora database components and is designed to plug into your existing AWS CloudFormation templates as a drop-in PostgreSQL database replacement.

Amazon Aurora

Aurora is a fully managed relational database engine that’s compatible with MySQL and PostgreSQL. MySQL and PostgreSQL combine the speed and reliability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases. The code, tools, and applications you use today with your existing MySQL and PostgreSQL databases can be used with Aurora. With some workloads, Aurora can deliver up to five times the throughput of MySQL and up to three times the throughput of PostgreSQL without requiring changes to most of your existing applications.

Aurora includes a high-performance storage subsystem. Its database engines are customized to take advantage of that fast distributed storage. The underlying storage grows automatically as needed, up to 64 terabytes. Aurora also automates and standardizes database clustering and replication, which are typically among the most challenging aspects of database configuration and administration.

Aurora is part of the managed database service Amazon Relational Database Service (Amazon RDS). Amazon RDS is a web service that makes it easier to set up, operate, and scale a relational database in the cloud. If you are not already familiar with Amazon RDS, see the Amazon Relational Database Service User Guide.

You can choose Aurora as a DB engine option when setting up new database servers through Amazon RDS. Aurora provides the following features:

  • Aurora takes advantage of the Amazon RDS features for management and administration. It uses the Amazon RDS AWS Management Console interface, AWS Command Line Interface (AWS CLI) commands, and API operations to handle routine database tasks such as provisioning, patching, backup, recovery, failure detection, and repair.

  • Aurora management operations typically involve entire clusters of database servers that are synchronized through replication, instead of individual database instances. The automatic clustering, replication, and storage allocation make it simple and cost-effective to set up, operate, and scale your largest PostgreSQL deployments.

  • You can bring data from Amazon RDS for PostgreSQL into Aurora by creating and restoring snapshots, or by setting up one-way replication. You can use push-button migration tools to convert your existing Amazon RDS for PostgreSQL applications to Aurora.

Costs and licenses

There is no cost to use this Partner Solution, but you will be billed for any AWS services or resources that this Partner Solution deploys. For more information, refer to the AWS Partner Solution General Information Guide.

Architecture

Deploying this Partner Solution with default parameters builds the following Aurora PostgreSQL environment in the AWS Cloud.

Architecture
Figure 1. Partner Solution architecture for Aurora PostgreSQL on AWS

As shown in Figure 1, this Partner Solution sets up the following:

  • A highly available architecture that spans two Availability Zones.*

  • A VPC configured with public and private subnets according to AWS best practices, to provide you with your own virtual network on AWS.*

  • In the public subnets, managed network address translation (NAT) gateways to allow outbound internet access for resources in the private subnets.*

  • In the public subnets, an optional Linux bastion host in an Auto Scaling group to allow inbound Secure Shell (SSH) access to Amazon Elastic Compute Cloud (Amazon EC2) instances in the private subnets.*

  • In the private subnets, an Aurora DB cluster that includes two DB readers and one DB writer.

  • An encryption key using AWS Key Management Service (AWS KMS). The Quick Start uses the key to enable encryption at rest for the Aurora DB cluster.

  • An Amazon CloudWatch alarm to monitor the CPU on the bastion host and send an Amazon Simple Notification Service (Amazon SNS) notification when the alarm is triggered.

* The template that deploys this Partner Solution into an existing VPC skips the components marked by asterisks and prompts you for your existing VPC configuration.

Deployment options

This Partner Solution provides the following deployment options:

  • Deploy Aurora PostgreSQL into a new VPC. This option builds a new AWS environment consisting of the VPC, subnets, NAT gateways, security groups, and an optional Linux bastion host, and then deploys Aurora into this new VPC.

  • Deploy Aurora PostgreSQL into an existing VPC. This option provisions Aurora in your existing AWS infrastructure. Use this option to plug Aurora PostgreSQL into your existing AWS CloudFormation templates as a drop-in PostgreSQL database replacement.

This Partner Solution provides separate templates for these options. It also lets you configure Classless Inter-Domain Routing (CIDR) blocks, instance types, and Aurora PostgreSQL settings.

Deployment steps

  1. Sign in to your AWS account, and launch this Partner Solution, as described under Deployment options. The AWS CloudFormation console opens with a prepopulated template.

  2. Choose the correct AWS Region, and then choose Next.

  3. On the Create stack page, keep the default setting for the template URL, and then choose Next.

  4. On the Specify stack details page, change the stack name if needed. Review the parameters for the template. Provide values for the parameters that require input. For all other parameters, review the default settings and customize them as necessary. When you finish reviewing and customizing the parameters, choose Next.

    Unless you’re customizing the Partner Solution templates or are instructed otherwise in this guide’s Predeployment section, don’t change the default settings for the following parameters: QSS3BucketName, QSS3BucketRegion, and QSS3KeyPrefix. Changing the values of these parameters will modify code references that point to the Amazon Simple Storage Service (Amazon S3) bucket name and key prefix. For more information, refer to the AWS Partner Solutions Contributor’s Guide.

  5. On the Configure stack options page, you can specify tags (key-value pairs) for resources in your stack and set advanced options. When you finish, choose Next.

  6. On the Review page, review and confirm the template settings. Under Capabilities, select all of the check boxes to acknowledge that the template creates AWS Identity and Access Management (IAM) resources that might require the ability to automatically expand macros.

  7. Choose Create stack. The stack takes about 25 minutes to deploy.

  8. Monitor the stack’s status, and when the status is CREATE_COMPLETE, the Amazon Aurora PostgreSQL deployment is ready.

  9. To view the created resources, choose the Outputs tab.

Postdeployment steps

Use the URLs displayed on the Resources tab for the stack to view the resources that were created.

Postdeployment
Figure 2. Aurora resources after successful deployment

Test the deployment

Note This optional test procedure assumes that you have the bastion host access with TCPForwarding capabilities.

  1. Download the latest version of pgAdmin 4 and install it on your workstation from where you will be connecting to the Aurora PostgreSQL DB cluster.

  2. From the AWS CloudFormation console, on the Outputs tab for BastionStack, note the value for EIP1, as shown in Figure 3.

image
Figure 3. IP address for Linux bastion host, on the Outputs tab

  1. From the AWS CloudFormation console, on the Outputs tab for AuroraStack, note the values of DBName, DBMasterUsername, RDSEndpointAddress, and RDSEndpointPort, as shown in Figure 4.

image
Figure 4. Aurora stack outputs

  1. Create an SSH tunnel to the bastion host using the following command.

ssh -N -L <RDSEndpointPort>:<RDSEndPointAddress>:<RDSEndpointPort> ec2-user@EIP1 -i <KeyPairName>

Note You will need to enter the path for the EC2 key pair that you specified for Key name (KeyPairName) parameter while creating the CloudFormation stack. When you’re finished testing, terminate the SSH tunnel by pressing Ctrl+C.

With the values added, the command will look like the following.

ssh -N -L 5432:amazonaurorapostgres-aurorastack-auroradbcluster-13ikqqfuz14ut.cluster-cqkx5uin33n9.us-west-2.rds.amazonaws.com:5432 ec2-user@35.166.99.4 -i keypair.pem

  1. Launch PgAdmin 4 on your workstation, enter a new password, and choose OK to continue.

image
Figure 5. Set Master Password for PgAdmin 4
image

In PgAmin 4, create a new server connection by choosing Servers, choosing the context (right-click) menu, choosing Create, and choosing Server.

image
Figure 6. Create a new server connection in PgAdmin 4

  1. To connect to your database, choose the General tab, and provide a descriptive name.

image
Figure 7. General settings for PgAdmin 4 server connection setup

  1. Choose the Connection tab, and provide the following values.

    • In the Host name/address field, enter 127.0.0.1.

    • In the Maintenance database field, enter the <DBName>.

    • In the Username field, enter the <DBMasterUsername>.

These values can be found on the Outputs tab of the Aurora stack. See Figure 4.

  • In the Password field, enter the <DBMasterUserPassword> you entered during stack creation.

    1. Choose Save to store the connection information and to connect.

image
Figure 8. Connection setting for Aurora PostgreSQL DB cluster

Upon successful connection you will see a PgAdmin 4 dashboard, as shown in Figure 9.

image
Figure 9. Confirmation that the Aurora PostgreSQL cluster is accepting connections

  1. Terminate the SSH tunnel by pressing Ctrl+C.

Troubleshooting

For troubleshooting common Partner Solution issues, refer to the AWS Partner Solution General Information Guide and Troubleshooting CloudFormation.

Customer responsibility

After you deploy a Partner Solution, confirm that your resources and services are updated and configured—including any required patches—to meet your security and other needs. For more information, refer to the Shared Responsibility Model.

Feedback

To submit feature ideas and report bugs, use the Issues section of the GitHub repository for this Partner Solution. To submit code, refer to the Partner Solution Contributor’s Guide. To submit feedback on this deployment guide, use the following GitHub links:

Notices

This document is provided for informational purposes only. It represents current AWS product offerings and practices as of the date of issue of this document, which are subject to change without notice. Customers are responsible for making their own independent assessment of the information in this document and any use of AWS products or services, each of which is provided "as is" without warranty of any kind, whether expressed or implied. This document does not create any warranties, representations, contractual commitments, conditions, or assurances from AWS, its affiliates, suppliers, or licensors. The responsibilities and liabilities of AWS to its customers are controlled by AWS agreements, and this document is not part of, nor does it modify, any agreement between AWS and its customers.

The software included with this paper is licensed under the Apache License, version 2.0 (the "License"). You may not use this file except in compliance with the License. A copy of the License is located at https://aws.amazon.com/apache2.0/ or in the accompanying "license" file. This code is distributed on an "as is" basis, without warranties or conditions of any kind, either expressed or implied. Refer to the License for specific language governing permissions and limitations.