Running PostgreSQL Using Amazon RDS

Cloud computing is now commonplace in most companies. It allows for on demand availability of compute power, database, storage, applications, and other resources via the internet. 

The main advantages behind the cloud are that you don’t need to spend a lot of money to buy powerful servers or build your own data centers. But this is not the only advantage, when you need to scale you don’t need to buy a new server you can just add resources with a few clicks. In a similar way, we can also decrease the number of resources when they aren’t needed to reduce costs.

A cloud database is a database running on a cloud provider. It allows us to store, manage, retrieve, and manipulate our data via a cloud platform; accessible over the internet. 

In this blog, we’ll look at the different types of cloud offerings and then focus in on running a  PostgreSQL  database using Amazon RDS

Cloud Service Offerings & Options

As we can see in the image above, there are several different kinds of cloud services depending on the level of access needed.

• On-prem: It’s installed and runs on computers on the premises of the person or organization using the system. In fact, this is not a cloud service, but it’s useful to see the difference.
• IaaS: It’s an online service that provides high-level APIs used to access various low-level details of underlying network infrastructure like physical computing resources, location, data partitioning, scaling, security, backup, etc.
• PaaS: It provides a platform allowing customers to develop, run, and manage applications without the complexity of building and maintaining the infrastructure associated with developing and launching an app.
• SaaS: It’s accessed by users over the Internet using a client (browser). It doesn’t require any installation on the client side.

If we’re talking about PostgreSQL, there are cloud providers that offer PostgreSQL in the cloud; in different flavors and using different methods. As we mentioned above, we’re going to focus on Amazon RDS.

What is Amazon RDS (Relational Database Service)?

According to the Amazon web site, they offer over 165 fully featured services, including over 40 services that aren’t available anywhere else. So, AWS is probably the world’s most advanced cloud provider in terms of features and services with millions of customers.

Amazon RDS allows us to create, manage and scale a relational database in the cloud in an easy and fast way, and it’s available on different database types like Amazon Aurora, PostgreSQL, MySQL and more. AWS provides a tool called AWS Database Migration Service to migrate an existing database to Amazon RDS.

Benefits of Amazon RDS

• Easy to use: We can use the Amazon RDS Management Console, the AWS RDS Command-Line Interface, or API calls to access the relational database. We don’t need infrastructure provisioning or installing and maintaining database software.
• Scalable: We can scale our database’s compute and storage resources with only a few clicks. Many Amazon RDS engine types allow us to launch one or more Read Replicas to offload read traffic from our primary database instance.
• Availability: When we provision a Multi-AZ DB Instance, Amazon RDS synchronously replicates the data to a standby instance in a different Availability Zone (AZ). Amazon RDS has many other features that enhance reliability for critical production databases, including automated backups, database snapshots, and automatic host replacement.
• Performance: We can choose between two SSD-backed storage options: one optimized for high-performance OLTP applications, and the other for cost-effective general-purpose use. 
• Secure: Amazon RDS lets us run the database instances in Amazon VPC (Virtual Private Cloud), which allows us to isolate our database instances and to connect to our existing IT infrastructure through a VPN. Also, many Amazon RDS engine types offer encryption at rest and encryption in transit.

While this is not officially mentioned on the AWS web site, but if we consider DBaaS (Database as a Service) as a database service which is managed and deployed in the outside provider’s infrastructure (according to our list in the section above) we can say that Amazon RDS is a “kind-of” DBaaS, somewhere between a PaaS and a SaaS service.

A Guide to PostgreSQL on Amazon RDS

First, we need to login the AWS console. (If you don’t have an AWS account, you can create a free one here.)

Then, go to Services -> Database -> RDS and Create database section.

Now, we must choose if we want to follow the normal or easy creation, the engine, and version that we’ll deploy. 

If we select the easy creation, we only need to add the database instance name, user and password.

In this case, we’ll choose PostgreSQL 10 and the normal creation to be able to see the creation details, so this will require a bit more work than the easy one.

In the normal creation, first, we’ll choose a template, it could be Production, Dev/Test or Free tier option.

In the next step, we’ll add the database instance name, user, and password.

The next step is the database instance size where we have several options in three different categories: Standard classes, Memory Optimized classes, and Burstable classes.

In the storage section, we can select the disk type, size, and storage behavior.

One of the most important AWS features is the Multi-AZ deployment, where we can create a standby instance in a different availability zone to provide redundancy.

About the connectivity, we can choose a Virtual Private Cloud (VPC) to connect the new database. Here, we can select additional options like public access, availability zone, and database port.

Then, we have additional configuration where we can specify the database name, database authentication, backups details, encryption, monitoring, logging, and maintenance service (auto minor upgrades).

Finally, we’ll have the option to check the Estimated Monthly Costs.

We can see more details about the costs here, or even use the AWS Monthly Calculator.

After adding all this information, we must wait until the creation process finishes.

When the Status changes to “Available”, our database instance is ready to use.

If we press on the DB identifier (“pg1” in our example), we’ll access our database section, where we can see a summary with information like CPU usage, connections, status, and type. Here, we can also modify our instance configuration or perform different actions like reboot, delete, create read replica, take snapshots, and even more.

In the same place, we can also see more detailed information in different sections. 

​Connectivity and Security

We can configure the security rules and check the network information.

Monitoring

We have some metrics to check our database status.

Logs and Events 

We have alarms, events, and logs from our database.

Configuration 

We can see our instance configuration, but also a list of recommendations to improve it, like enable enhanced monitoring.

Maintenance and Backups 

We can see information about the maintenance tasks, backups, and snapshot process.

Now, we should be able to access our database by using the Endpoint name assigned by AWS (“pg1.cibqq2gl0qof.us-east-1.rds.amazonaws.com” in our example). For this, make sure you allowed access from the security group section and you enabled the public access from the instance configuration (Public accessibility: Yes). In our example, we’re allowing all the traffic from all the sources, but for security reason, you’ll probably want to limit the access from one or a few sources.

Now, let’s try to connect to our Amazon RDS instance from the command line:

[root@local ~]# psql -U postgres -h pg1.cibqq2gl0qof.us-east-1.rds.amazonaws.com

Password for user postgres:

psql (11.5, server 10.6)

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

Type "help" for help.



postgres=> l

                                  List of databases

   Name    | Owner   | Encoding |   Collate | Ctype    | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin

 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +

           |          | |             | | rdsadmin=CTc/rdsadmin

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          | |             | | postgres=CTc/postgres

(4 rows)



postgres=> select version();

                                                version

--------------------------------------------------------------------------------------------------------

 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

(1 row)

In the same way, we can connect to it from our preferred GUI (if we have one).

A Note on Amazon Aurora

Amazon Aurora is a MySQL and PostgreSQL compatible relational database built for the cloud. According to the AWS web site, Amazon Aurora is three times faster than standard PostgreSQL databases and provides the security, availability, and reliability of commercial databases at 1/10th the cost. Regardless of the claim, this is not a true PostgreSQL instance, just a compatible engine. But, if you are considering running PostgreSQL on Amazon, you should definitely consider this as a possible alternative. You can learn more about Aurora and how it relates to PostgreSQL here.

Conclusion

The cloud is everywhere. We can use it for both small and huge projects alike. In this blog, we looked at the different kinds of clouds and shared how to run PostgreSQL on Amazon RDS. Let us know in the comments below you thoughts.