# Deploy Redis as a cache for Postgres on an AWS Arm based Instance ## In this learning path - [Introduction](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/) - [Deploy Redis as a cache for MySQL on an AWS Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_mysql_aws/) - [Deploy Redis as a cache for MySQL on an Azure Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_mysql_azure/) - [Deploy Redis as a cache for MySQL on a GCP Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_mysql_gcp/) - [Deploy Redis as a cache for Postgres on an AWS Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_psql_aws/) - [Deploy Redis as a cache for Postgres on an Azure Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_psql_azure/) - [Deploy Redis as a cache for Postgres on a Google Cloud Arm based Instance](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/redis_psql_gcp/) - [Next Steps](https://learn.arm.com/learning-paths/servers-and-cloud-computing/redis_cache/_next-steps/) You can deploy Redis as a cache for Postgres on an AWS Arm based Instance using Terraform and Ansible. In this section, you will deploy Redis as a cache for Postgres on an AWS Instance. If you are new to Terraform, you should look at [Automate AWS EC2 instance creation using Terraform](https://learn.arm.com/learning-paths/servers-and-cloud-computing/aws-terraform/terraform/) before starting this Learning Path. ## Before you begin You should have the prerequisite tools installed before starting the Learning Path. Any computer which has the required tools installed can be used for this section. The computer can be your desktop or laptop computer or a virtual machine with the required tools. You will need an [AWS account](https://portal.aws.amazon.com/billing/signup?nc2=h_ct&src=default&redirect_url=https%3A%2F%2Faws.amazon.com%2Fregistration-confirmation#/start) to complete this Learning Path. Create an account if you don’t have one. Before you begin, you will also need: - An AWS access key ID and secret access key - An SSH key pair ### Generate an SSH key-pair Generate an SSH key-pair (public key, private key) using `ssh-keygen` to use for AWS EC2 access. To generate the key-pair, follow this [guide](https://learn.arm.com/install-guides/ssh/#ssh-keys). > If you already have an SSH key-pair present in the `~/.ssh` directory, you can skip this step. ### Acquire AWS Access Credentials The installation of Terraform on your desktop or laptop needs to communicate with AWS. Thus, Terraform needs to be able to authenticate with AWS. To generate and configure the Access key ID and Secret access key, follow this [guide](https://learn.arm.com/install-guides/aws_access_keys/). ## Create an AWS EC2 instance using Terraform Using a text editor, save the code below in a file called `main.tf`: ```hcl provider "aws" { region = "us-east-2" } resource "aws_instance" "PSQL_TEST" { count = "2" ami = "ami-0ca2eafa23bc3dd01" instance_type = "t4g.small" security_groups = [aws_security_group.Terraformsecurity.name] key_name = aws_key_pair.deployer.key_name tags = { Name = "PSQL_TEST" } } resource "aws_default_vpc" "main" { tags = { Name = "main" } } resource "aws_security_group" "Terraformsecurity" { name = "Terraformsecurity" description = "Allow TLS inbound traffic" vpc_id = aws_default_vpc.main.id ingress { description = "TLS from VPC" from_port = 5432 to_port = 5432 protocol = "tcp" cidr_blocks = ["0.0.0.0/0"] } ingress { description = "TLS from VPC" from_port = 22 to_port = 22 protocol = "tcp" cidr_blocks = ["0.0.0.0/0"] } egress { from_port = 0 to_port = 0 protocol = "-1" cidr_blocks = ["0.0.0.0/0"] } tags = { Name = "Terraformsecurity" } } output "Master_public_IP" { value = [aws_instance.PSQL_TEST[0].public_ip, aws_instance.PSQL_TEST[1].public_ip] } resource "aws_key_pair" "deployer" { key_name = "id_rsa" public_key = file("~/.ssh/id_rsa.pub") } // Generate inventory file resource "local_file" "inventory" { depends_on = [aws_instance.PSQL_TEST] filename = "/tmp/inventory" content = < The instance type is t4g.small. This is an Arm-based instance and requires an Arm Linux distribution. The inventory file is automatically generated and does not need to be changed. ## Terraform Commands Use Terraform to deploy the `main.tf` file. ### Initialize Terraform Run `terraform init` to initialize the Terraform deployment. This command downloads the dependencies required for AWS. ```bash terraform init ``` The output should be similar to: ```plaintext __output__Initializing the backend... __output__Initializing provider plugins... __output__- Reusing previous version of hashicorp/local from the dependency lock file __output__- Reusing previous version of hashicorp/aws from the dependency lock file __output__- Using previously-installed hashicorp/local v2.3.0 __output__- Using previously-installed hashicorp/aws v4.52.0 __output__Terraform has been successfully initialized! __output__You may now begin working with Terraform. Try running "terraform plan" to see __output__any changes that are required for your infrastructure. All Terraform commands __output__should now work. __output__If you ever set or change modules or backend configuration for Terraform, __output__rerun this command to reinitialize your working directory. If you forget, other __output__commands will detect it and remind you to do so if necessary. ``` ### Create a Terraform execution plan Run `terraform plan` to create an execution plan. ```bash terraform plan ``` A long output of resources to be created will be printed. ### Apply a Terraform execution plan Run `terraform apply` to apply the execution plan and create all AWS resources. ```bash terraform apply ``` Answer `yes` to the prompt to confirm you want to create AWS resources. The public IP address will be different, but the output should be similar to: ```plaintext __output__Apply complete! Resources: 6 added, 0 changed, 0 destroyed. __output__Outputs: __output__Master_public_IP = [ __output__ "3.144.105.166", __output__ "3.139.240.124", __output__] ``` ## Configure Postgres through Ansible Install Postgres and the required dependencies. Using a text editor, save the code below in a file called `playbook.yaml`. This Playbook installs & enables Postgres in the instances. ```yaml --- - hosts: all become: yes tasks: - name: Update the Machine & Install PostgreSQL shell: | sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list' sudo wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add - sudo apt-get update sudo apt-get install postgresql -y sudo systemctl start postgresql become: true - name: Update apt repo and cache on all Debian/Ubuntu boxes apt: upgrade=yes update_cache=yes force_apt_get=yes cache_valid_time=3600 become: true - name: Install Python pip and PostgreSQL package apt: name={{ item }} update_cache=true state=present force_apt_get=yes with_items: - python3-pip - acl become: true - name: Start and enable services service: "name={{ item }} state=started enabled=yes" with_items: - postgresql - name: Utility present ansible.builtin.package: name: python3-psycopg2 state: present - name: Replace postgresql configuration file to allow remote connection ansible.builtin.lineinfile: path: "/etc/postgresql/15/main/postgresql.conf" line: '{{ item }}' owner: postgres group: postgres mode: '0777' with_items: - "listen_addresses = '*'" become: yes become_user: postgres - name: Allow trust connection for the db user postgresql_pg_hba: dest: "/etc/postgresql/15/main/pg_hba.conf" contype: host databases: all method: trust address: 0.0.0.0/0 users: "all" create: true become: yes become_user: postgres notify: restart postgres handlers: - name: restart postgres service: name=postgresql state=restarted ``` ### Ansible Commands Run the playbook using the `ansible-playbook` command: ```bash ansible-playbook playbook.yaml -i /tmp/inventory ``` Answer `yes` when prompted for the SSH connection. Deployment may take a few minutes. The output should be similar to: ```plaintext __output__PLAY [all] ****************************************************************************************************************************************** __output__TASK [Gathering Facts] ****************************************************************************************************************************** __output__ok: [3.144.105.166] __output__ok: [3.139.240.124] __output__TASK [Update the Machine & Install PostgreSQL] ****************************************************************************************************** __output__changed: [3.139.240.124] __output__changed: [3.144.105.166] __output__TASK [Update apt repo and cache on all Debian/Ubuntu boxes] ***************************************************************************************** __output__ok: [3.139.240.124] __output__ok: [3.144.105.166] __output__TASK [Install Python pip and PostgreSQL package] **************************************************************************************************** __output__ok: [3.139.240.124] => (item=python3-pip) __output__ok: [3.144.105.166] => (item=python3-pip) __output__ok: [3.139.240.124] => (item=acl) __output__ok: [3.144.105.166] => (item=acl) __output__TASK [Start and enable services] ********************************************************************************************************************* __output__ok: [3.139.240.124] => (item=postgresql) __output__ok: [3.144.105.166] => (item=postgresql) __output__TASK [Utility present] ****************************************************************************************************************************** __output__ok: [3.144.105.166] __output__ok: [3.139.240.124] __output__TASK [Replace postgresql configuration file to allow remote connection] ********************************************************************************* __output__ok: [3.139.240.124] => (item=listen_addresses = '*') __output__ok: [3.144.105.166] => (item=listen_addresses = '*') __output__TASK [Allow trust connection for the db user] ******************************************************************************************************* __output__ok: [3.139.240.124] __output__ok: [3.144.105.166] __output__PLAY RECAP ****************************************************************************************************************************************** __output__3.139.240.124 : ok=8 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 __output__3.144.105.166 : ok=8 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 ``` ## Connect to Database from local machine To connect to the database, you need the `public-ip` of the instance where Postgres is deployed. You also need to use the Postgres Client to interact with the PostgreSQL database. ```bash apt install -y postgresql-client ``` ```bash psql -h {public_ip of instance where Postgres deployed} -U postgres ``` Replace `{public_ip of instance where Postgres deployed}` with your value. The output will be: ```plaintext __output__ubuntu@ip-172-31-38-39:~/redis_psql$ psql -h 3.144.105.166 -U postgres __output__psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1), server 15.2 (Ubuntu 15.2-1.pgdg22.04+1)) __output__WARNING: psql major version 14, server major version 15. __output__ Some psql features might not work. __output__SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) __output__Type "help" for help. __output__postgres=# ``` ### Access Database and Create Table 1. You can create and access your database by using the commands below: ```sql create database {your_database}; ``` ```sql \c {your_database}; ``` The output will be: ```plaintext __output__postgres=# create database arm_test1; __output__CREATE DATABASE __output__postgres=# \c arm_test1 __output__psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1), server 15.2 (Ubuntu 15.2-1.pgdg22.04+1)) __output__WARNING: psql major version 14, server major version 15. __output__ Some psql features might not work. __output__SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) __output__You are now connected to database "arm_test1" as user "postgres". ``` 2. Use the commands below to create a table and insert values into it: ```sql create table book(name char(10),id varchar(10)); ``` ```sql insert into book(name,id) values ('Abook','10'),('Bbook','20'),('Cbook','20'),('Dbook','30'),('Ebook','45'),('Fbook','40'),('Gbook','69'); ``` The output will be: ```plaintext __output__arm_test1=# create table book(name char(10),id varchar(10)); __output__CREATE TABLE __output__arm_test1=# insert into book(name,id) values ('Abook','10'),('Bbook','20'),('Cbook','20'),('Dbook','30'),('Ebook','45'),('Fbook','40'),('Gbook','69'); __output__INSERT 0 7 ``` 3. Use the command below to access the content of the table. ```sql select * from {{your_table_name}}; ``` The output will be: ```plaintext __output__arm_test1=# select * from book; __output__ name | id __output__------------+---- __output__ Abook | 10 __output__ Bbook | 20 __output__ Cbook | 20 __output__ Dbook | 30 __output__ Ebook | 45 __output__ Fbook | 40 __output__ Gbook +| 69 __output__ | __output__ (7 rows) ``` 4. Now connect to the second instance and repeat the above steps with a different database as shown below. The output will be: ```plaintext __output__postgres=# create database arm_test2; __output__CREATE DATABASE __output__postgres=# \c arm_test2 __output__psql (14.7 (Ubuntu 14.7-0ubuntu0.22.04.1), server 15.2 (Ubuntu 15.2-1.pgdg22.04+1)) __output__WARNING: psql major version 14, server major version 15. __output__ Some psql features might not work. __output__SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) __output__You are now connected to database "arm_test2" as user "postgres". __output__arm_test2=# create table movie(name char(10),id varchar(10)); __output__CREATE TABLE __output__arm_test2=# insert into movie(name,id) values ('Amovie','1'), ('Bmovie','2'), ('Cmovie','3'), ('Dmovie','4'), ('Emovie','5'), ('Fmovie','6'), ('Gmovie','7'); __output__INSERT 0 7 __output__arm_test2=# select * from movie; __output__ name | id __output__------------+---- __output__ Amovie | 1 __output__ Bmovie | 2 __output__ Cmovie | 3 __output__ Dmovie | 4 __output__ Emovie | 5 __output__ Fmovie | 6 __output__ Gmovie | 7 __output__ (7 rows) ``` ## Deploy Redis as a cache for Postgres using Python You will create two `.py` files on the host machine to deploy Redis as a Postgres cache using Python: `values.py` and `redis_cache.py`. Create `values.py` with the content below to store the IP addresses of the instances and the databases created in them. ```python PSQL_TEST=[["{{public_ip of PSQL_TEST[0]}}", "arm_test1"], ["{{public_ip of PSQL_TEST[1]}}", "arm_test2"]] ``` Replace `{{public_ip of PSQL_TEST[0]}}` & `{{public_ip of PSQL_TEST[1]}}` with the public IPs generated in the `/tmp/inventory` file after running the Terraform commands. Create `redis_cache.py` with content below to access data from Redis Cache and, if not present, store it in the Redis Cache. ```python import sys import psycopg2 import redis from values import * import argparse parser = argparse.ArgumentParser() parser.add_argument("-db", "--database", help="Database") parser.add_argument("-k", "--key", help="Key") parser.add_argument("-q", "--query", help="Query") args = parser.parse_args() R_SERVER = redis.Redis( host='localhost', port='6379') for i in range(0,2): if (PSQL_TEST[i][1]==args.database): try: conn = psycopg2.connect (host = PSQL_TEST[i][0], dbname = PSQL_TEST[i][1], user="postgres") except psycopg2.OperationalError as e: print('Unable to connect!\n{0}').format(e) sys.exit (1) psqldata = R_SERVER.get(args.key) if not psqldata: cursor = conn.cursor() cursor.execute(args.query) rows = cursor.fetchall() mystr = ' '.join(map(str,rows)) R_SERVER.set(args.key,mystr,120) print ("Updated redis with PSQL data") print (rows) else: print ("Loaded data from redis") print (psqldata) break else: print("this database doesn't exist") ``` Change the `range` in `for loop` according to the number of instances created. Install the required Python modules using `pip` and other required dependency: ```bash apt-get install redis ``` ```bash pip install redis ``` To execute the `redis_cache.py` script, run the following command: ```bash python3 redis_cache.py -db {database_name} -k {key} -q {query} ``` Replace `{database_name}` with the database you want to access, `{query}` with the query you want to run in the database, and `{key}` with a variable to store the result of the query in Redis cache. When the script is executed for the first time, the data is loaded from the Postgres database and stored in the Redis cache. The output will be: ```plaintext __output__ubuntu@ip-172-31-38-39:~/redis_psql$ python3 redis_cache.py -db arm_test1 -k AA -q "select * from book limit 3" __output__Updated redis with PSQL data __output__[('Abook ', '10'), ('Bbook ', '20'), ('Cbook ', '20')] ``` ```plaintext __output__ubuntu@ip-172-31-38-39:~/redis_psql$ python3 redis_cache.py -db arm_test2 -k BB -q "select * from movie limit 3" __output__Updated redis with PSQL data __output__[('Amovie ', '1'), ('Bmovie ', '2'), ('Cmovie ', '3')] ``` When executed after that, it loads the data from Redis cache. In the example above, the information stored in Redis cache is in the form of string. When accessing the information (within the 120-second expiry time), the data is loaded from Redis cache and dumped. The output will be: ```plaintext __output__ubuntu@ip-172-31-38-39:~/redis_psql$ python3 redis_cache.py -db arm_test1 -k AA -q "select * from book limit 3" __output__Loaded data from redis __output__b"('Abook ', '10') ('Bbook ', '20') ('Cbook ', '20')" ``` ```plaintext __output__ubuntu@ip-172-31-38-39:~/redis_psql$ python3 redis_cache.py -db arm_test2 -k BB -q "select * from movie limit 3" __output__Loaded data from redis __output__b"('Amovie ', '1') ('Bmovie ', '2') ('Cmovie ', '3')" ``` ### Redis-cli Commands Execute the steps below to verify that the PostgreSQL query is getting stored in Redis cache. 1. Install redis-tools to interact with redis-server. ```bash apt install redis-tools ``` 2. Connect to redis-server through redis-cli. ```bash redis-cli -p 6379 ``` 3. Retrieve data from Redis cache. ```bash get ``` > Key is the variable in which you store the data. In the above command, you are storing the data from the tables `book` and `movie` in `AA` and `BB` respectively. The output will be: ```plaintext __output__ubuntu@ip-172-31-38-39:~/redis_psql$ redis-cli -p 6379 __output__127.0.0.1:6379> get AA __output__"('Abook ', '10') ('Bbook ', '20') ('Cbook ', '20')" __output__127.0.0.1:6379> get BB __output__"('Amovie ', '1') ('Bmovie ', '2') ('Cmovie ', '3')" ``` You have successfully deployed Redis as a cache for Postgres on an AWS Arm based Instance. ### Clean up resources Run `terraform destroy` to delete all resources created. ```bash terraform destroy ``` Continue the Learning Path to deploy Redis as a cache for Postgres on an Azure Arm based Instance.