In this article, we will look at the configuration of the AWS RDS PostgreSQL instances.
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
Select engine type as PostgreSQL.
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
DB Instance Identifier
It is a unique name for your database instance. AWS stores the DB identifier in the lower case.
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.
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.
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.
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.
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.
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
AWS RDS PostgreSQL captures the performance insights for 7 days. It helps you to troubleshoot database or query performance issues.
You can export database system logs – PostgreSQL log and Upgrade log to AWS CloudWatch logs.
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.
SELECT * FROM pg_catalog.pg_tables
Let’s create a new table in the [SQLShackDemo] database and query [pg_catalog] again.
Create table TestTable
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.
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.
- SUBSTRING, PATINDEX and CHARINDEX string functions in SQL queries - March 1, 2021
- Deploy Azure Data Lake Analytics database using the U-SQL scripts - February 23, 2021
- Join database tables using U-SQL scripts for Azure Data Lake Analytics - February 17, 2021