Playing with AWS RDS (#2)

Ok, let's start from the previous post in this series..
Playing with AWS RDS (#1)
Quick recap of what we have done..

1. Create a RDS instance
2. Connect from pgadmin
3. Change the instance class (resizing)
4. Multi-AZ Failover

In this post, we'll focus on read-replicas.

As per AWS documentations, Amazon RDS Read Replicas provide enhanced performance and durability for Amazon RDS database (DB) instances. They make it easy to elastically scale out beyond the capacity constraints of a single DB instance for read-heavy database workloads.

a) You can create one or more replicas of a given source DB Instance and serve high-volume application read traffic from multiple copies of your data, thereby increasing aggregate read throughput.
b) Read replicas can also be promoted when needed to become standalone DB instances.

So let's first create a read-replica and test out the replication. Click on the primary DB instance identifier in the RDS console. From actions, select 'Create read replica'.

Set Multi-AZ to 'No', Publicly accessible to 'Yes'.
Give a name to the replica DB instance (e.g., mydbinstancerr). Leave other attributes to their default values and click on the 'Create read replica' button.

It would take some time for the new instance (yes, read-replica will be a new DB instance) to be up and running.

While it's still being created, let us quickly see what is being shown in the replication status.
Select the primary DB instance (which is 'mydbinstance' for my case) and go to the 'Replication' card. You can find that the replication state and replication lag information is not yet populated.

Wait until the read-replica instance is available..

Now, let's test out the replication.
For this, we would create a table with random data in primary DB instance and then check in the replica DB instance if the data gets replicated. Here is our sample SQL.

    CREATE TABLE Results (
    rollno NUMERIC NOT NULL,
    marks NUMERIC NOT NULL);

    insert into Results (rollno, marks) 
    SELECT  num.*,ceil (random()*100)
    FROM    generate_series(1000,1500) num;

Let's execute this SQL query in pgadmin.

Query is successful. The 'Results' table is created with 501 records.

Now check in the replica DB instance to find out if this information is replicated or not. First, create a DB connection in pgadmin (follow the post#1 in this series) and then search for the 'Results' table.

This is great, we can find the records have already been replicated in the replica DB instance.

At this point, let's go back to the 'Replication' card in the RDS console to find out the replication state and lag information.

Note that the read-replica can also be promoted to a standalone database instance, but you can't use it as a replication target. So the replication would stop. Let's test this out now.

Promote read-replica

Ignoring backup for time-being.

See both the DB instances are being shown in the same level now..

Go to the 'replication' card.

'mydbinstancerr' instance is not there anymore. You can try out the sample SQL query (change the table name to 'Results1') again and check yourself through pgadmin whether replication is still happening or not.