Rajendra Gupta
AWS RDS PostgreSQL

Deploying AWS RDS PostgreSQL instances

January 27, 2021 by

In this article, we will look at the configuration of the AWS RDS PostgreSQL instances.

Introduction

AWS offers the relational database service for MySQL, Oracle, Microsoft SQL Server and PostgreSQL. It is a managed database service, and AWS manages the underlying infrastructure such as Hardware, Operating system, Network, Firewalls, Security and regular patches.

PostgreSQL is an open-source relational database supporting both SQL and JSON queries. It provides several useful features such as Multi-version concurrency control ( MVCC), nested transactions, write-ahead logging, international character support. You can use it without any licensing cost because it has an open-source license.

Previously, we deployed the Microsoft SQL Server and MySQL on the AWS RDS platform. In this article, we will look at configurations for AWS RDS PostgreSQL deployment.

AWS RDS PostgreSQL deployment

Connect to the AWS web console using your credentials. If you do not have an account, I suggest you creating a free-tier account for learning purposes.

AWS RDS PostgreSQL

In the AWS console, navigate to RDS -> Create Database. In the database creation wizard, go through the following configurations.

Choose a database creation method

  • Standard Create: In this option, you can set all RDS PostgreSQL configurations such as security, backups, maintenance window, and availability
  • Easy Create: If you are a beginner and do not know about PostgreSQL configuration, it is a recommended approach to use easy create. It deploys the DB with best-practice configurations

Engine Type

Select engine type as PostgreSQL.

Choose a database creation method

Templates

The template autos fill the default values based on your selection.

  • Production: A production instance requires high availability and consistent performance. If you deploy for RDS PostgreSQL for production workload, this template suits your requirements
  • Dev\Test: It is suitable for development or test instances
  • Free-tier: You can deploy a free-tier PostgreSQL instance to become familiar with the database system, queries, and sample workload

Templates

Settings

DB Instance Identifier

It is a unique name for your database instance. AWS stores the DB identifier in the lower case.

Credential settings

By default, PostgreSQL creates a Postgres login account with the maximum permissions. You can specify the login name as per your requirement as well. Specify a login password or use the Auto-generate password option.

Credential settings

DB instance Size

AWS gives you various instance classes to meet your compute and memory requirement. However, in the free-tier template, you can use only Burstable classes that have minimal resources. As shown below, the Standard and Memory Optimized classes options are disabled.

If you require these standard and Memory-optimized classes, you can choose the production or Dev template.

You can refer to DB instance classes documentation before implementing it.

DB instance Size

Storage

AWS RDS offers three types of storage.

  • General Purpose SSD: It is a cost-effective storage solution and supports up to 3000 IOPS. You can get single-digit millisecond latencies in the general-purpose SSD
  • Provisioned IOPS: In the provisioned IOPS, you get very low latency and a consistent IO throughput
  • Magnetic: It is available for backward compatibility requirements. You should avoid using Magnetic storage

In the free-tier category, AWS gives 20 GB of General Purpose (SSD) database storage. It is sufficient for preparing our lab PostgreSQL environment.

Enabling autoscaling

AWS automatically extends the storage once your storage reaches the threshold. You should use it for the production environment so that your database environment does not get affected due to insufficient free space.

Storage

Availability & Durability

You can deploy a standby PostgreSQL instance in the different availability zone for data redundancy and high availability purpose. In the below screenshot, these options are disabled because we are using the free-tier template.

Availability & Durability

Connectivity

It is a critical part of your RDS deployment. The Virtual private cloud is a networking component for your database instance. By default, AWS RDS creates a default VPC, subnet for your initial deployments. It is recommended to go through VPC documentation and build your VPC, security groups.

Public access: In this article, we will connect with the PostgreSQL from my laptop; therefore, I enable the public access for the AWS RDS PostgreSQL instance.

Connectivity

VPC Security Groups

You can choose an existing VPC for your AWS infrastructure. If you do not have any VPC configured, create a new VPC.

VPC Security Groups

Database authentication

You can do database authentication using a specific database user or using the Kerberos authentication, IAM authentication. In this article, we use database authentication using a Postgres database user.

Database authentication

Database options

On the database options page, you can configure the database, DB parameter, database backups, and performance insights.

  • Initial database name: You can specify a database name in the textbox, and AWS RDS PostgreSQL automatically creates the database during initial setup
  • DB parameter group: By default, RDS creates a default parameter group for each RDS instance. We cannot modify the DB parameter group. In case, we want to configure a specific parameter, create a new DB parameter group and select the DB parameter group on this page

    Database options

  • Backup: By default, AWS enables automatic backups during a specific time window. The default retention period is 7 days. We can set it to a maximum of 35 days retention period.
  • Backup Window: If you have a preferred backup window for your RDS instance, you can configure it here. Else, AWS uses the default windows for automated DB backups

    Backup Window

Performance insights

AWS RDS PostgreSQL captures the performance insights for 7 days. It helps you to troubleshoot database or query performance issues.

Log exports

You can export database system logs – PostgreSQL log and Upgrade log to AWS CloudWatch logs.

Performance insights

IAM Role

AWS creates an IAM role RDS-Service-linked role for publishing logs to AWS CloudWatch.

Enable auto-minor version upgrade

If you put a check on enable auto-minor version upgrade, AWS automatically upgrades your database to a newer minor version during the maintenance window. It is a good option, especially for the development and staging environment.

Enable deletion protection

It protects the database from any accidental deletion. You cannot drop an RDS instance until the deletion protection is enabled.

Enable auto-minor version upgrade

Estimated Monthly Cost

In the bottom part of AWS RDS Postgres configuration, you get an estimated cost of running the instance for a month. However, if you use the free-tier account, you have 750 hrs. of free RDS in a single AZ t2.micro instance in a year. You can stop an instance when it is not in use to save the resource cost.

Estimated Monthly Cost

Click on the Create database button. It configures the RDS instance as per your configurations and becomes available in a few minutes.

configures the RDS instance

Click on the RDS instance name and copy the RDS endpoint.

copy the RDS endpoint

Connect to AWS RDS PostgreSQL using Azure Data Studio (ADS)

We can use native RDS client tools to connect with RDS instance or use the Azure Data Studio for executing DB queries.

Azure Data Studio is a cross-platform application for Microsoft SQL Server. We can use the PostgreSQL extension to connect with AWS RDS PostgreSQL and executing DB queries.

Firstly, browse to Microsoft docs and download the latest version of Azure Data Studio.

Connect to AWS RDS PostgreSQL

Launch ADS and search for PostgreSQL extension in the marketplace. The PostgreSQL extension is currently in the preview phase.

search for PostgreSQL extension

Restart Azure data Studio after PostgreSQL extension installation. To connect with AWS RDS PostgreSQL, change the connection type to PostgreSQL as shown below.

  • Server Name: Enter the RDS PostgreSQL endpoint
  • Credentials: Enter the credentials in the username and password section

PostgreSQL extension installation

It connects AWS RDS PostgreSQL successfully. You can view the default databases rdsadmin, Postgres. We also created an initial database SQLShackDemo during RDS configuration.

view the default databases

Execute the below DB query to check the tables for the [pg_catalog] schema.

the tables for the [pg_catalog] schema

Let’s create a new table in the [SQLShackDemo] database and query [pg_catalog] again.

You can see the table in the public schema because we have not specified any schema in the table definition.

create a new table

You can also view the table in the Azure Data Studio database dashboard.

Azure Data Studio database dashboard

Conclusion

In this article, we deployed an AWS RDS PostgreSQL instance and explored its various configurations. Further, we queried a PostgreSQL database using the Azure Data Studio. You should leverage the AWS free tier account to learn and deploy your AWS instances.

Rajendra Gupta
AWS, AWS RDS

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

1,364 Views