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.

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.

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

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.

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.

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.

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.

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.

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.

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 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

  

• 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

  

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.

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.

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.

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

Click on the RDS instance name and 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.

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

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

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

Execute the below DB query to check 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.

You can also view the table in the 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.

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023