Setting Up a PostgreSQL RDS for ATMs

Amazon Web Services (AWS) is a famous global cloud computing platform. AWS started delivering web services for IT infrastructures in 2006. Now we call these IT infrastructure services cloud computing. The main benefit of cloud computing is the replacement of upfront infrastructure expenses with low variable costs depending on your own usage. With the cloud, there is no need for us to plan for servers and other IT infrastructures in advance because AWS can spin up and deliver many servers within seconds according to your needs.

There are over 175 IT infrastructure services of AWS, including Elastic Compute Cloud (EC2), Elastic Container Service (ECS), Relational Database Service (RDS), Simple Storage Service (S3), and CloudFront. Now, AWS supplies reliable and low-cost infrastructure services for thousands of businesses in 190 countries around the world.

In this chapter, we will describe the Database as a service (DBaaS) options for PostgreSQL through Amazon RDS for PostgreSQL. With Amazon RDS, you can deploy scalable PostgreSQL deployments in just a few minutes with cost-efficient and resizable hardware capacity. Amazon RDS manages complex and time-consuming administrative tasks, such as PostgreSQL software installations and upgrades, storage management, replications for high availability and read throughputs, and backups for disaster recovery. Amazon RDS supports PostgreSQL's major version 12, which includes a number of enhancements to performance, robustness, transaction management, query parallelism, and more.

In this chapter, we will cover the following main topics:

  • An overview of the project
  • Creating a PostgreSQL RDS with AWS
  • Connecting to a PostgreSQL database instance
  • Creating a PostgreSQL database snapshot
  • Deleting a PostgreSQL database instance
  • Restoring data from a PostgreSQL database snapshot
  • Point-in-time recovery for PostgreSQL

Technical requirements

The code files for this chapter can be found in the GitHub repository of this book: https://github.com/PacktPublishing/Developing-Modern-Database-Applications-with-PostgreSQL/tree/master/Chapter02.

An overview of the project

In this step-by-step project, you will learn how to use PostgreSQL version 12 on the AWS cloud. You will start by creating an empty PostgreSQL database on AWS. Then, you will use the standard pgAdmin software to connect to your AWS database so that you can create a data table and insert all of the ATM data of New York City, which has been provided in our GitHub link. After that, you will discover how to work with this PostgreSQL database by backing up its data into snapshots; hence, you can delete the database and restore it back from your snapshot. Finally, supposing there are some challenging database disasters, you will learn how to perform a point-in-time recovery using AWS.

The project should only take you between 3 and 4 hours to complete. However, if this is the first time you are working with AWS, you might prefer to repeat the steps a few times so that you can carefully study DBaaS in the AWS cloud. 

We are going to set up a PostgreSQL 12 RDS for the storage of bank ATM machine locations inside the city of New York. There are around 654 bank-owned ATM locations in New York City – part of the data can be seen in Table 2.1The source of our ATM data is granted by the Open Data NY program, which gives access to government data and information. We have already received permission from the Open Data NY public service to use part of their data for this book. From the Open Data NY public data, we use the following web link to filter all of the ATM locations of New York City: https://data.ny.gov/Government-Finance/Bank-Owned-ATM-Locations-in-New-York-State/ndex-ad5r/data.

We will need to import the following table contents into our PostgreSQL database:

ID Name of the institution Street address County City State ZIP code
1 Wells Fargo ATM 500 W 30 STREET New York New York NY 1000
2 JPMorgan Chase Bank, National Association 1260 Broadway New York New York NY 10001
3 Sterling National Bank of New York 1261 Fifth Avenue New York New York NY 10001
4 Bank of America N.A. GA1-006-15-40 1293 Broadway New York New York NY 10001
5 Bank of Hope 16 West 32nd Street New York New York NY 10001
6 TD Bank N.A. 200 West 26th Street New York New York NY 10001
7 Citibank N. A. 201 West 34th Street New York New York NY 10001
8 Capital One, N.A. 215 West 34th Street New York New York NY 10001
9 Citibank N. A. 22 West 32nd Street New York New York NY 10001
10 Sterling National Bank of New York 227 West 27th Street New York New York NY 10001
11 JPMorgan Chase Bank, National Association 245 Seventh Avenue New York New York NY 10001
12 Amalgamated Bank 275 Seventh Avenue New York New York NY 10001
13 JPMorgan Chase Bank, National Association 305 Seventh Avenue New York New York NY 10001
14 Woori America Bank  330 Fifth Avenue New York New York NY 10001
15 Commerce Bank, N.A. 341 Ninth Avenue New York New York NY 10001
16 TD Bank N.A. 350 West 31st Street New York New York NY 10001
17 USAlliance Financial 350 West 31st Street New York New York NY 10001
18 Bank of America N.A. GA1-006-15-40 358 Fifth Avenue New York New York NY 10001
19 Sterling National Bank of New York 406 West 31st Street New York New York NY 10001
20 Sterling National Bank of New York 7 Penn Plaza New York New York NY 10001
... ... ... ... ... ... ...
635 TD Bank N.A. 1709 Third Avenue New York New York NY 10128
636 Citibank N. A. 1781 First Avenue New York New York NY 10128
637 JPMorgan Chase Bank, National Association 1801 Second Avenue New York New York NY 10128
638 JPMorgan Chase Bank, National Association 181 East 90th Street New York New York NY 10128
639 Citibank N. A. 340 East 93rd Street New York New York NY 10128
640 HSBC Bank USA, National Association 45 East 89th Street New York New York NY 10128
641 Bank of America N.A. GA1-006-15-40 345 Park Avenue New York New York NY 10154
642 HSBC Bank USA, National Association 617 Third Avenue New York New York NY 10158
643 Citibank N. A. 200 Park Avenue New York New York NY 10166
644 Bank of America N.A. GA1-006-15-40 200 Park Avenue New York New York NY 10166
645 Apple Bank For Savings 122 East 42nd Street New York New York NY 10168
646 JPMorgan Chase Bank, National Association 405 Lexington Avenue New York New York NY 10174
647 Bank of America N.A. GA1-006-15-40 425 Lexington Avenue New York New York NY 10174
648 HSBC Bank USA, National Association 101 Park Avenue New York New York NY 10178
649 HSBC Bank USA, National Association 117 Broadway New York New York NY 10271
650 Valley National Bank 120 Broadway New York New York NY 10271
651 Citibank N. A. 120 Broadway New York New York NY 10271
652 JPMorgan Chase Bank, National Association 331-337 South End Avenue New York New York NY 10280
653 USAlliance Financial 200 Vesey Street New York New York NY 10281
654 TD Bank N.A. 90 Fifth Avenue New York New York NY

11011

Table 2.1: Bank ATM locations in New York City

We will now move on to the step-by-step project of PostgreSQL creation with AWS.

Creating a PostgreSQL RDS with AWS

Once you register yourself as a user with AWS, sign in and navigate to the AWS Management Console using the following link: https://console.aws.amazon.com/console.

Amazon RDS stands for Amazon Relational Database Service. RDS provides an easy way to set up, operate and/or scale a relational database in the cloud.

Creating a PostgreSQL database

By going through the following steps in detail, you will be able to create a new PostgreSQL RDS and grant access to your PostgreSQL RDS:

  1. Click on the Services drop-down menu option and navigate to the Database section, as shown in the following screenshot:

Figure 2.1  AWS Services
  1. Then, click on the first entry of the Database section named RDS.
  1. We are proceeding to create a PostgreSQL version 12 RDS; you can scroll through the different zones of AWS to select any cloud zones, as shown in the following screenshot. Here, we will keep the zone as it is, that is, N. Virginia

Figure 2.2 – AWS cloud zones
  1. You can either click on the Databases tab on the left-hand panel or you can scroll down the page and click on the Create database button, as shown in the following screenshot:

Figure 2.3 – RDS Services
  1. If you clicked on the Create database button in step 4, skip this step. Otherwise, if you selected the Databases tab on the left-hand side, you will now reach the following Databases page:
Figure 2.4 – The Databases tab

Click on the Create database button on the right-hand side.

  1. The next step is to select the PostgreSQL database engine, as shown in the following screenshot:

Figure 2.5 – The PostgreSQL version 12 database engine
  1. Keeping the Version as PostgreSQL 12.5 and select Free tier template for your database as shown in the following screenshot:

Figure 2.6 – PostgreSQL database details
  1. Now, scroll down and continue to set the following values:
  • DB instance identifieratm
  • Master usernamedba
  • Master passwordbookdemo
  • Confirm passwordbookdemo

You can view these options in Figure 2.7:

Figure 2.7 – PostgreSQL database details (cont.)
  1. After you scroll down, in the DB instance class, select Burstable classes and enter the following values:
  • DB instance classdb.t2.micro
  • Storage typeGeneral Purpose (SSD)
  • Allocated storage20 (GiB)

This is shown in Figure 2.8:

Figure 2.8 – PostgreSQL database details (cont.)
  1. Expand the Additional connectivity configuration section and set the value to Yes for Publicly accessible.
  1. Expand the Additional configuration section and enter atm for Initial database name. For Backup retention periodthe default value of 7 days means that Amazon RDS should retain automatic snapshot backups of this PostgreSQL database for 7 days. You can simply leave it to 7 days, or you can change the value to a longer or shorter period. If you select 0 days, this means that you will not be utilizing the automatic RDS backups:

Figure 2.9 – PostgreSQL additional configuration
  1. For the remaining options, you can use the default values of AWS. Afterward, scroll down and click on the Create database button:

Figure 2.10 – Creating a PostgreSQL database instance
  1. Wait until the status of the database instance changes from Creating to Availableas shown in the following screenshot (this may take a few minutes):

Figure 2.11 – Creating a PostgreSQL RDS
  1. When the database status changes to Available, click on the atm database to note down the RDS endpoint, as shown in the following screenshot:

Figure 2.12 – The PostgreSQL RDS endpoint

As with our example, the endpoint is atm.ck5074bwbilj.us-east-1.rds.amazonaws.com.

After the RDS has been created, we will not be able to use the PostgreSQL database right away because we have not had the security permissions to access the RDS yet. In the next section, we will edit the security group of this new RDS to add our access permit.

Editing the RDS security group

In order to allow all of your work locations to access your new RDS, you have to add all of their IP addresses to the RDS security group:

  1. Recheck the database's security group to make sure that you are able to connect to the RDS from your working locations. Click on VPC security groups default (sg-d6499d86), as shown in the preceding screenshot. This will redirect you to the security group page where you can click on the Inbound tab, as shown in the following screenshot:

Figure 2.13  The PostgreSQL RDS security group
  1. Now click on the Edit inbound rules button, as shown in the preceding screenshot. Add a rule to add your current IPs by entering the following values and clicking on Save rules:
  • TypePostgreSQL
  • ProtocolTCP
  • Port Range5432
  • SourceMyIP or Custom
  • IP: your other IP location

This is shown in the following screenshot:

Figure 2.14 – The RDS security group's rules

By default, AWS allows your current IP address to access your RDS endpoint; in our case, our current IP is 70.77.107.46. If you have more than one work location, you can add more IP addresses from all of your work locations, as shown in the preceding screenshot.

In this section, we have demonstrated how to, step by step, create a standalone PostgreSQL 12 database on the well-known AWS cloud and then grant access to the RDS security group. Now that you have the correct access to the RDS security group, in the next section, we will show you how to use other PostgreSQL tools such as pgAdmin to connect to your new PostgreSQL RDS. 

Connecting to a PostgreSQL database instance

Here, we are using a PostgreSQL tool called pgAdmin 4, version 4.5, which was released in April 2019, to insert data for our ATM database. Download and install the pgAdmin 4 tool from https://www.pgadmin.org/download/pgadmin-4-windows/, and you should be able to view the main browser of pgAdmin 4.5, as follows:

Figure 2.15 – The pgAdmin 4.5 main browser

Next, we need to create a connection to the RDS before we can read and write data to the created ATM database.

The pgAdmin server

We can connect the RDS to pgAdmin by performing the following steps: 

  1. From the pgAdmin 4.5 main browser, right-click on Servers. Then, select Create and click on Server...; the Create - Server popup will display, as follows:

Figure 2.16 – The Create - Server popup
  1. Type the name ATM RDS into the Create - Server popup. Then, select the Connection tab, as shown in Figure 2.17:

Figure 2.17 – PostgreSQL Connection
  1. Within the Connection tab shown in the preceding screenshot, enter your PostgreSQL RDS details:
  • Host name/address (your RDS endpoint): atm.ck5074bwbilj.us-east-1.rds.amazonaws.com
  • Username (your RDS master username): dba
  • Password (your RDS password): bookdemo

Once these details have been entered, click on Save.

  1. Expand the servers in your pgAdmin by clicking on the > button on the left-hand side, and then you can connect to the ATM RDS by simply double-clicking on the ATM RDS link. Alternatively, you can right-click on it and then click on Connect Server:

Figure 2.18 – Connecting from pgAdmin to the PostgreSQL RDS
  1. Expand the Databases drop-down menu inside your pgAdmin, and then select the atm database. After that, continue to navigate to atm > Schemas > public, as shown in the following screenshot:

Figure 2.19 – The PostgreSQL schemas on pgAdmin

Now that we have used pgAdmin to connect to the RDS from AWS, we will move on to the next section, where we will create the ATM locations table and populate it with ATM data.

The ATM locations table

The pgAdmin tool offers a graphical user interface to create a table, so we are going to use that user interface instead of DDL statements: 

  1. We will create the ATM locations table inside the ATM RDS. For this, we need to right-click on the Tables entry on the left-hand side. Then, click on Create and select Table..., as shown in the following screenshot:

Figure 2.20 – Creating a table with pgAdmin
  1. Within the following Create - Table dialog box, enter ATM locations inside the Name section:

Figure 2.21  The Create - Table dialog box
  1. Select the Columns tab, and then use the add new row (Shift + Ctrl + A) button (+) on the right-hand side to add all the table columns, as shown in the following screenshot. Then, click on Save:

Figure 2.22 – Creating columns for the ATM locations table
  1. After the ATM locations table has been created, select the Tools menu and then select Query Tool, as shown in the following screenshot:
Figure 2.23 – Using Query Tool
  1. Now, copy the content of the atmlocations.sql SQL script from the GitHub link at https://github.com/lequanha/PostgreSQL-12-Development-and-Administration-Projects/blob/master/Chapter 2/atmlocations.sql.

Next we paste the atmlocations.sql file content into the Query Tool and click the Execute/Refresh (F5) icon to insert all the data in as shown in the following screenshot:

Figure 2.24 – Inserting data into the ATM locations table
  1. Click on the data view icon,  , to display the whole table, as shown in the following screenshot:

Figure 2.25 – The data of the ATM locations table

In this section, we have guided you through how to populate the AWS PostgreSQL 12 RDS by using pgAdmin; this step-by-step guide is simple and the SQL script is supplied by GitHub. In order to keep your newly added data safe from incidents, you will need to back up your database. The AWS cloud offers a backup facility called a snapshot so that you can back up your RDS. In the next section, we will show you how to create a PostgreSQL snapshot.

Creating a PostgreSQL database snapshot

Backing up a database is a daily task that protects you from possible data disasters. AWS supplies a snapshot method so that you can back up your data not only every day but also every hour or whenever you need to implement a database backup. Perform the following steps to back up your RDS:

  1. From the Amazon RDS Databases environment, select the atm database and then select the Actions option. Then, click on Take snapshot, as shown in the following screenshot:

Figure 2.26 – Taking an RDS snapshot
  1. Enter a valid name for the intended snapshot and then click on the Take Snapshot button:
Figure 2.27 – Adding a snapshot name
  1. Wait for a short while until the snapshot status on the next web page changes from Creating to availableas shown in the following screenshot:
Figure 2.28 – Snapshot status

The preceding snapshot outcome can be used to restore the whole RDS, including all of the current structures and data that we have created and populated so far. However, before we can practice snapshot restoration, we need to delete our RDS. Then, we can illustrate how snapshot restoration recovers the same database with your same data. Therefore, in the next section, we will demonstrate the next step of how to delete a PostgreSQL RDS.

Deleting a PostgreSQL database instance

Sometimes, you might want to remove a piece of software and, in doing so, also remove the unused database from your AWS account. You can delete a PostgreSQL RDS that does not have deletion protection enabled. When we created or modified our PostgreSQL RDS in the previous sections, there was an option to enable deletion protection so that the RDS cannot be deleted. Perform the following steps:

  1. Click on Databases from the navigation tabs on the left-hand side. Then, select the atm database to enable the Actions menu, as shown in the following screenshot:

Figure 2.29 – Enabling the Actions menu on the atm database
  1. Check the drop-down menu for Actions and select the Delete option, as shown in the following screenshot:
Figure 2.30 – The Actions > Delete option to remove the atm database
  1. Enter delete me to confirm the deletion, as shown in the following screenshot. Then, click on Delete:
Figure 2.31 – Delete confirmation

You can choose to create one more final snapshot before deletion, or if you already have a good snapshot just as we created manually in the Creating a PostgreSQL database snapshot section, you will be able to uncheck the final snapshot creation. 

  1. Click on Snapshots from the left-hand side navigation tabs when the atm database has been deleted in order to view all the snapshots that you have so far, as shown in the following screenshot:
Figure 2.32 – A list of snapshots

In this section, we showed you how to safely delete the PostgreSQL 12 RDS because we have stored all of the previous databases within good snapshots already. In practice, when your database loses data or it crashes, you will rely on your snapshots to recover your good RDS PostgreSQL database again. In the next section, we will show you how to restore your RDS from a PostgreSQL snapshot.

Restoring data from a PostgreSQL database snapshot

In the case of database incidents leading to data loss, you can use your RDS snapshot to restore a new PostgreSQL RDS. The recovered RDS will contain the same ATM database with the exact same tables and data rows that your former RDS had when you created the snapshot. The username and password of your RDS automatically remain the same. However, the default security group is applied to your restored RDS; therefore, you will have to modify or correct the security group to grant access to your connections. Perform the following steps for snapshot restoration: 

  1. Starting from Figure 2.32, select the snapshot that you would like to use to restore the deleted atm database, for example, the atm-2019-04-28 snapshot.
  2. Click on the drop-down menu named Actions and select Restore Snapshot:

Figure 2.33  Database restoration
  1. Under the Settings and DB instance size sections of the Restore DB Instance page that is shown, enter the following database details:
  • DB instance identifier: atm
  • DB instance class: db.t3.micro

This is shown in the following screenshot:

Figure 2.34 – The DB instance size section of the database restoration page 

Now, scroll down to the bottom of the page and click on the Restore DB Instance button, as shown in the following screenshot:

Figure 2.35 – Restoring the database instance
  1. Wait for a short while until the ATM database is fully available on the next screen.
  1. Select the ATM database and click on the Modify button to correct the security group for the ATM database by using the sg-d6499d86 security group that we set up last time for the ATM RDS.
  1. Additionally, click on X to remove the incorrect default security group and then click on Continue:
Figure 2.36 – Security group restoration
  1. On the next summary page, select the Apply immediately option and then click on Modify DB Instance, as shown in the following screenshot:
Figure 2.37 – The summary page of RDS modification
  1. Wait for a short while until the ATM database's status changes from Modifying to Available on the next screen.

The username and the password of the RDS that we set up last time are automatically restored:

  • Username (your RDS master username): dba
  • Password (your RDS password): bookdemo

Now you can try opening pgAdmin to reconnect to the atm database as usual.

In this section, we demonstrated quite an important task inside the daily activities of a PostgreSQL database administrator; hence, this step-by-step guideline is useful for us.

Point-in-time recovery for PostgreSQL

A PostgreSQL RDS can be restored to a specific point in time. The default security group is applied to the newly restored database instance. You will have to modify or correct the security group to grant access to your connections. We can do this by performing the following steps:

  1. From the Amazon RDS console in the left-hand navigation pane, choose Databases.
  2. Select the atm database.
  3. If you have not done so yet, you can enable automated backups by clicking on the Modify button and then changing the Backup retention period option from 0 days to another value from 1 day to 35 days, as shown in the following screenshot:

Figure 2.38 – Enabling backup retention

After that, apply the change immediately as you get used to this RDS modification.

  1. Once the atm database is available, from Actions, select Restore to point in time, as shown in the following screenshot:

Figure 2.39 – Restore to point in time
  1. From the Launch DB Instance page, select either of the following options:
  • Select Latest restorable time to restore to the latest possible time.
  • Select Custom to choose a specific time, as shown in the following screenshot:

Figure 2.40 – Launch DB Instance

Now, scroll down to the Instance specifications section and the Settings section on the same page to enter the other options, as follows:

  • DB instance class: db.t3.micro
  • DB instance identifier: atmPITR

This is shown in the following screenshot:

Figure 2.41 – Recovering the database instance settings

After that, navigate to the very end of the page to click on the Launch DB Instance button.

  1. Wait for a couple of minutes until the status of the newly launched atmpitr RDS changes to Available, as shown in the following screenshot: 

Figure 2.42 – The new point-in-time RDS

In order to grant connection access to the new point-in-time RDS, correct the security group in the same way that you did in the previous section from steps 5 to 8.

Point-in-time recovery is especially useful when the database schema structures are modified from time to time. It is not recommended that database schema structures are changed after the applications have already been deployed because the change can cause conflicts with other integrated modules. However, changes in schema structure are sometimes unavoidable. Therefore, after you have changed the schema structures and other application modules are still coded on the ex-structures, the related incompatibilities can pop up improperly. The real difficulty arises if the pop-up issues do not appear right away and you only detect them a few weeks later. In this case, you will be able to restore your RDS back to specific timing points to troubleshoot the related incompatibilities.

Summary

In this chapter, you learned about DBaaS options through Amazon RDS for PostgreSQL. Through a step-by-step project for the storage of banking ATM machine locations inside New York City, you researched how to use Amazon RDS for PostgreSQL DBaaS. Now you are well versed regarding DBaaS within the common tasks of the AWS cloud. You practiced how to create a PostgreSQL RDS and how to connect to that RDS from pgAdmin. Then, you also acquired skills to delete, back up, restore, and maintain a PostgreSQL RDS. You also learned how to restore a database and recover PostgreSQL to a specific point in time. 

In the next chapter, we will create a step-by-step NodeJS project and use the PostgreSQL RDS created in this chapter to implement a RESTful API.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.204.35.30