Analyzing Impact Of Database Switch Over For Aurora Postgres Serverless Blue/Green Deployment

In Blue/Green Deployment For Aurora Postgres Serverless Cluster, we discussed how we can implement Blue/Green deployment for Aurora Postgres Serverless Cluster.

In this post, we’ll discuss the impact on read/write operations while we switch over the database to promote green database to primary database.

Step 1: Follow Step 1–3 given at Blue/Green Deployment For Aurora Postgres Serverless Cluster to create an Aurora Postgres Serverless Cluster.

Step 2: Connect to mylab database and create a table with following definition.

CREATE TABLE accounts (EMPLOYEE VARCHAR ( 50 ) PRIMARY KEY)

Step 3: Create a Python script with following code.Replace values for <PASSWORD> and WRITER_ENDPOINT> acoordingly.

import psycopg2
import random
import string
import time

#Establishing the connection
while True:
    print("------------------")
    time.sleep(1)
    try:
        conn = psycopg2.connect(database="mylab", user='postgres', password='<PASSWORD>', host='<WRITER_ENDPOINT>', port= '5432',connect_timeout=3)
        conn.autocommit = True

        cursor = conn.cursor()
        letters = string.ascii_letters
        name=''.join(random.choice(letters) for i in range(50)) 

        cursor.execute("INSERT INTO accounts(EMPLOYEE) VALUES (%s);", (name,))
        conn.commit()
        print("Records inserted "+name)
    except Exception as e:
        print("Error writing data")
        print(e)
    try:
        conn1 = psycopg2.connect(database="mylab", user='postgres', password='<PASSWORD>', host='<WRITER_ENDPOINT>', port= '5432',connect_timeout=3)
        cursor = conn1.cursor()
        cursor.execute("SELECT count(*) from accounts;")
        result = cursor.fetchone()
        print("Table count is "+str(result[0]))
    except Exception as e:
        print("Error reading data")
        print(e)

Run this script for a couple of minutes to insert some records and then terminate it.

Step 4: Follow Step 4 given at Blue/Green Deployment For Aurora Postgres Serverless Cluster to create Green environment for our Aurora Postgres Serverless Cluster.

Step 5: Once the green environment is available, run the script created in Step 3 and then perform a switch over. After some time, you will start getting timeout errors for few seconds.

and after a few seconds, cluster will be in read-only mode so write operation will fail but read will work fine.

after few more seconds, read/write will start working fine.

Step 6: Now let’s see what happens when we use writer endpoint for inserting records and reader endpoint to read data.

In the python script, change host value for conn1 to reader endpoint. Run the script and perform another switch over after creating a new green environment.

This time also, the results are same.

Note: Once the switch over is completed, replication won’t happen between 2 clusters.