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:

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

So, lets do this and see what happens:

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

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

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

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:

I was not aware I am doing MySQL here 🙂

There is even information about the current lag:

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

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

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:

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

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

And now we have two masters:

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.