Infrastructure at your Service

Daniel Westermann

PostgreSQL on Amazon RDS – Adding a replica to the beast

In the last post I looked on how you can change the PostgreSQL parameters when the instance is running in the Amazon cloud. In this post we’ll look at how to add a replica to the PostgreSQL instance that is already running. Even if you push things to the cloud you want to make sure that you have a standby database in case the master crashes. At best the replica is running in another data center. Lets see what Amazon offers in this direction.

As usual we start with the instance page of the Amazon console:
az60

One of the available options in the “Instance Actions” is “Create Read Replicate”:
az61

So, lets do this and see what happens:
az62
az63

Not many parameters we have to specify here. Lets go on and create the replica:
az64

Cool, that’s all we need to do? The replica is now in the process of being created and the master gets modified:
az64

Lets wait a few minutes and then check what is there:
az65

Finally the new replica is replicating from the master. Multi AZ is not available with the free version of Amazon RDS so the replica runs in the same availability zone. Automated backups are disabled and if you check the instance settings there is at least a bug in the description:
az67

I was not aware I am doing MySQL here 🙂

There is even information about the current lag:
az70

The interesting point is if we can connect to our replica for reads, so lets try:
az68

Cool, works. We should not be able to write to the replica:
az69

Perfect. The interesting point is what happens if we switch (promote) to replica and make it active. Will we lose the master or will it be rebuild as standby automatically? Lets try:
az71

We are offered to specify the backup details for the new master:
az91

And here it is: We can not restart the replication process with the new master. Lets see what happens:
az92
az93
az94
az95

And now we have two masters:
az96
az97

Hm, this is not what I expected. For production use the automated rebuild of the old master to a new standby should be done automatically which is not the case here. Maybe there are more options in the non-free version of Amazon RDS. At least for production this might be a killer. Additionally the promotion of the standby took several minutes which is not as fast as I expected. Promoting a PostgreSQL standby database is usually a matter of seconds. Sure, this was a manual promote but ending up with two masters is at least questionable.

In the next and last post in this series I’ll look at how you can load data to your cloud instance.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure