In this chapter, we talk about the purpose of replicating data or databases and why we need high availability. Some cloud vendors provide replication for the disks of virtual machines or databases and some do not. Similarly, some vendors provide high availability of servers and database instances by default and some do not. We talk about replication and high availability for every cloud vendor in detail.
The Purpose of Replication and High Availability
High availability is represented by the letter “D” in the ACID properties (for durability). Database servers are prone to single points of failure. In order to avoid such single point of failures, we have a feature called replication in the database world. If the master DB server goes down due to environmental or hardware level damages, we have a slave that can take the role of its master.
Achieving high availability in PostgreSQL has been easy since streaming replication was introduced. We can build slaves (aka, replicas or DRs) to a master (primary) PostgreSQL database server, and these slaves are in a continuous replication with their master at any given time. Replication in the database language is a process in which a DB server can ship its changes to another DB server. It can be achievable using several solutions in the RDBMS world.
Streaming replication
Logical replication
Streaming replication deals with the blocks that have been modified by the processes writing to the master. These blocks are shipped over the network to the slave and replayed on the slave continuously. This replication can be both synchronous and asynchronous.
Logical replication helps users configure replication between multiple versions of PostgreSQL and have a selected list of tables or databases replicated to slaves and cascaded slaves. However, this may not serve the purpose of a true high-availability cluster unless every database object is in the replication set, which may not be possible unless every object has a primary or a unique key and a NOT NULL column.
Thus, to achieve high availability, you may want to have at least one slave server that is in replication (preferably streaming replication) with its master. PostgreSQL allows us to use cascaded replication. This way, a slave can ship its changes to another slave. In this case, the first slave is treated as a master by the second slave.
Ensure you have the same server configurations on both the master and the slave DB servers. In the event of failure, the slave should be able to take the load that would usually hit the master .
Ensure you have a common mount point, such as a NAS mount point, accessible by the master and slave. This mount point can be used to archive the WALs (write-ahead logs or transaction logs). If you subscribed to a DBaaS such as an Amazon RDS, you may not have to worry about archiving WALs. However, if you selected an EC2 Instance (IaaS) or a virtual machine on Azure or GCP or Rackspace, you may subscribe to the vendor-specific cloud storage service for archiving. Vendors provide several APIs that use WALs that can either be sent to or pulled from storage as needed.
Load-balance your reads. When the wal_level has been set to hot_standby, an application can send its read queries to the slave. Slaves are open for read-only queries. Thus, you can have your application logic rewritten in such a way that your writes go to one connection string that connects to the master and the reads go to another connection string that redirects the connections to the slave. You may also have a load balancer service that can redirect your reads to multiple slaves or to the master and the slave in an even manner. This way, you let the idle computing resources on the slave become busy with reporting or read queries.
Consider high availability a key to avoiding single point of failures and an enabler for developing always-on database systems.
Replication and High Availability in AWS
AWS allows you to build replication for RDS and EC2 services. AWS console allow users to achieve replication in RDS instances in very few clicks.
Read Replicas for RDS
Automatically create a read replica
- 1.Search for RDS in the AWS Console and click on it to proceed, as shown in Figure 9-2.
Figure 9-2Choose RDS from the AWS Console
- 2.Click on Instances to view the list of your RDS instances. You may find one or more depending on the number of RDS instances you have already created. See Figure 9-3.
Figure 9-3View the list of RDS instances
- 3.Select the instance for which you need to create the read replica. Once it’s selected, click on the dropdown for Instance Actions. You see an option that says Create Read Replica, as shown in Figure 9-4. Click on it.
Figure 9-4The Create Read Replica option
- 4.Now you should see the options to choose the region and availability zone in which you want to create your read replica. You may choose any region and availability zone depending on your business requirements. See Figure 9-5.
Figure 9-5Read replica options
- 5.On the same page, scroll down to choose the instance type and storage. You may choose any configuration for the read replica that supports your business needs. Once you choose the instance type, choose the DB instance identifier that will help you uniquely identify this read replica, as shown in Figure 9-6.
Figure 9-6Choose a DB instance identifier
- 6.Click on Create Replica to proceed further. Now you should see the read replica listed under the Instances, as shown in Figure 9-7.
Figure 9-7The new read replica is listed under the Instances
You’ve now seen the steps involved in creating a read replica or slave for an RDS instance. It’s now time to go through the steps involved in creating a read replica for an EC2 instance.
You need to remember that you cannot have an EC2 instance with a replica created using RDS instance. Similarly, you cannot have an RDS instance with a slave created on an EC2 instance.
High Availability for EC2
- 1.
An EC2 instance master needs a slave to be created on an EC2 instance or any cloud virtual machine or a physical server in your data center. The server being created as a replica for the EC2 Instance should be using the same OS and PostgreSQL version as the master. The master and slave server should be able to connect between each other over the network. Create a new EC2 instance or a server that can be used as a slave and proceed to the next step.
- 2.
Connect to the new server that has been created as a slave and install PostgreSQL as a root user. Install the latest pgdg repo that suits the PostgreSQL version. See Figure 9-8.
$ yum install https://yum.postgresql.org/9.6/redhat/rhel-7.3-x86_64/pgdg-centos96-9.6-3.noarch.rpm -y
Figure 9-8Install the pgdg repo that suits the PostgreSQL version
- 3.
Install PostgreSQL using the following code. The following example in Figure 9-9 installs PostgreSQL 9.6.
$ yum install postgresql96* -y
Figure 9-9Installing PostgreSQL 9.6
- 4.
Be sure to set the appropriate parameters on the master EC2 instance if this has not been done already.
The parameters shown in Figure 9-10 are set as an example. However, you may need to set different parameters as appropriate for your environment on your master EC2 instance.
Figure 9-10Parameters for the master EC2 instance
- 5.Create a replication user on the master server that can be used by the slave EC2 instance. Add the slave server IP to pg_hba.conf to allow replication connections from the new slave, as shown in Figure 9-11.
Figure 9-11Create a replication user on the master server
- 6.
Take a backup of the master using pg_basebackup and start the slave. Run the following command on the slave to directly copy the data directory to the network. You may choose a different method to copy the data directory to the network.
$ pg_basebackup -h 172.31.20.216 -p 5432 -U replicator -D $PGDATA -Fp -P -Xs -R - 7.
Start the slave using hot_standby = ON.
$ echo "hot_standby = 'ON'" >> $PGDATA/postgresql.conf$ pg_ctl -D $PGDATA start - 8.
Setting up a high availability replica on an EC2 instance uses exactly same approach as setting up the slave on any other virtual machine or dedicated server. As hot_standby is ON, this slave is now open for read connections, making it a read replica .
Replication and High Availability of Rackspace Cloud
The Backups menu
And for PostgreSQL installed on Rackspace cloud servers, you can follow the same HA solution that you follow for PostgreSQL with on-premises servers by following the steps mentioned in the “High Availability for EC2” section.
Replication and High Availability of Google Cloud Instances
Like other cloud providers, Google Cloud also provides read replicas and high availability options for PostgreSQL instances. If a PostgreSQL instance is configured for high availability, we call it a “regional instance”. A regional instance is located in two zones in the configured region, so if it cannot serve data from its primary zone, it fails over and continues to serve data from its secondary zone.
This high availability configuration provides an auto-failover to a secondary zone if an instance experiences outage or is unresponsive. Highly-available PostgreSQL instances do not have a separate failover instance the way MySQL instances do.
Configure an Instance for High Availability
- 1.Select the instance that you want to configure for high availability after logging in to the console and selecting Cloud SQL from the left panel and instance from the right panel, as shown in Figure 9-13.
Figure 9-13Select an instance to configure for high availability
- 2.
Once you click on the Edit instance option, scroll down to the Enable Auto Backups And High Availability section. Then you’ll find the High Availability option under the Availability sub-section. See Figure 9-14.
Single zone: By selecting this, there will not be any failover in case of outage. So it is not recommended for production instances.
High availability (regional): This will enable auto-failover to another zone within the selected region. So it is recommended for production instances.
Figure 9-14The high availability option
Configuring for high availability does not affect your backups and does not change the backup/restore procedures. You can follow the same procedures for backup/restore that you followed before configuring the high availability.
How Failover Works
The zone where the regional instance is located experiences an outage.
The regional instance is unresponsive for approximately 60 seconds.
There is no concept of a replication lag; as as long as the secondary zone is healthy, failover can occur. When failover occurs, all the connections to the primary will be dropped and any new connections will be connected to the promoted instance. There is no need to change anything in the application end for connectivity.
Click the Failover button on the Instance Details tab
Read Replicas
A read replica is just a copy of the master. It is used to offload read requests or analytics traffic from the master. It does not provide failover capability.
- 1.
Go to the console and select Cloud SQL from the left panel.
- 2.Choose the instance that you want to create a replica for and open its More Actions menu at the far right of its listing, as shown in Figure 9-16. Select a zone in which to create the replica.
Figure 9-16Choose Create Read Replica from the more actions menu
- 3.
Click on Create Read Replica .
- 4.From the Replicas tab, as shown in Figure 9-17, you can see the replica.
Figure 9-17The new read replica on the Replicas tab
- 5.If the replica is created, the master instance will have the details of its replica. You can check the pg_stat_replication view for the replica details. It will show the ID of the process responsible for sending data to the replica from the master and the user through which replication was set up. To check the replica from the psql terminal, click on Connect Using Cloud Shell from the Overview tab of the primary instance . You will be connected to the primary instance, as shown in Figure 9-18.
Figure 9-18The primary instance
- 6.Connect to the replica using the Connect Using Cloud Shell option from the Overview tab of the replica instance. Now you will be connected to the replica, as shown in Figure 9-19.
Figure 9-19The replica
Replication and High Availability of Azure Services
This section covers the replication and high availability options for Azure virtual machines and the Azure Database for the PostgreSQL service.
Azure Database for PostgreSQL
Azure Database for PostgreSQL offers built-in high availability out-of-the-box. There are no additional replication setup, configuration changes, or extra costs. As a developer, you do not need to set up any additional VMS or manually configure any replication to achieve high availability. While in preview, the service is not backed up with a specific SLA. However, all databases will have an SLA of 99.99% availability.
This high availability mechanism is based on a built-in fail-over mechanism. It fails over only when node-level interruption occurs, which means if any hardware fails on any node, the total node is shut down.
Every time changes are made to an Azure database for PostgreSQL database server, they will be recorded synchronously in Azure storage. This works on a transaction basis. When a transaction is committed, changes will be recorded to the storage. During the time of failover, it creates a new node and attaches data storage where changes being recorded are attached to the node. However, any active connections during the failover will be dropped.
This service is fairly new, so they are still trying to implement lots of features that other vendors in the market have. Azure continuously allows customers to provide feedback about the service and improve according to the customer requests. There are a lot of feature requests being taken care by the Azure development team to improve the service and make it more reliable when compared to other venders.
As an example, many customers want to create a streaming replica in an Azure service database for many unsupported use cases. However, this has been raised as feature request for the Azure development team. See https://feedback.azure.com/forums/597976-azure-database-for-postgresql/suggestions/19418071-replication-support-or-is-that-built-in for more information.
Replication for read-only replicas is not supported yet in the Azure database for PostgreSQL service. Read replicas are currently in the Azure plan to announce early next year. They are also going to add replication from on-premises to Azure Postgres services. High availability is built into the service using Azure Service Fabric.
You can find feature requests for Azure service at https://feedback.azure.com/forums/597976-azure-database-for-postgresql .
Virtual Machines
Choose high availability
Group virtual machines in an availability set
Summary
The chapter explored how replication and high availability help you when something goes wrong or in disaster situations. We covered details about which cloud vendor provides what kind of replication and which types of high availability solutions. We hope this helps readers proceed with setting up replication and high availability for their cloud instances. The next chapter covers the need of encryption and how to implement it. It also discusses what kind of encryption each cloud vendor provides for its servers and instances by default.