As we're running a highly available database, we have at least one standby copy available at all times, right? Of course we do. However, after promoting a standby copy to act as a primary, we need to redirect traffic to the new server. How can we do this easily?
One common method is to use a database connection pool. The pool acts as a connection proxy and simply needs each known node to be registered so that it can redirect connections to the proper primary database server. We will eventually discuss this approach, but there's actually a simpler tool available to us that requires no additional software.
Another method is to change DNS to redirect network connections to the new server. The beauty of this technique is that it masquerades the entire access path to the server so that services other than PostgreSQL can access the new server as well. Unfortunately, subdomains are tied to a single IP address. As DBAs, we probably don't have access to most of the network hardware; that means relying on an external infrastructure department.
Instead, we can tie the subdomain to an IP address that isn't associated with any particular server. Then, it's simply a matter of changing the server that claims it owns that IP address. Luckily, this is something we can control directly.
To perform this process, we need both the ifconfig and the arping commands. The arping command may not be present by default, so install it before continuing. If you are on a Debian or Ubuntu system, issue this command:
sudo apt-get install arping
For these steps, assume eth0 is the primary interface and 127.0.0.10 is the IP we are trying to claim. Follow these steps to move or create a virtual IP:
- First, connect to the PostgreSQL node that had the IP address earlier. This is often the primary server.
- Release the IP address with the following command:
sudo ifconfig eth0:pgvip down - Ping the desired IP address with this command:
ping -c 3 127.0.0.10 - If the preceding command reaches any PostgreSQL server, restart from the beginning with this system instead.
- Next, connect to the new server that should own the IP address.
- Claim the IP address with the following command:
sudo ifconfig eth0:pgvip 127.0.0.10 - Tell the network about the location of the new IP address with this command:
sudo arping -c 3 -A -I eth0 127.0.0.10
If we haven't created a virtual IP yet, we can skip the first three steps. Otherwise, in order to use an IP address, it must be available. Setting up an IP address on multiple servers can wreak havoc on network traffic routing.
Next, we ping the desired address to ensure there are no replies. This should prove that our IP address is free for use. It should end with something like this:
--- 127.0.0.10 ping statistics --- 3 packets transmitted, 0 received, +3 errors, 100% packet loss, time 2015ms
We want to see 100 percent packet loss. This means that the IP address is currently unclaimed. If this results in an active server, we need to repeat the command that we used to shut down the existing virtual IPs there as well.
Provided the address is available, we simply connect to the desired server and use ifconfig to create a new virtual IP. We named the virtual IP pgvip, and attached it to the eth0 interface, and used 127.0.0.10 as the target address to claim.
After this step, the IP address is only visible on the local server, so we need to tell the upstream switches and routers that the IP is in use. The arping command does precisely this when passed the -A parameter. We use the -c setting to send three gratuitous broadcasts to help ensure that at least one was accepted. Like ifconfig, we need to tell arping to use eth0 with the -I parameter; otherwise, traffic may be misrouted.
This is really only a demonstration of virtual IP functionality. In the case of a server reboot, network assignments created through ifconfig will disappear. For our purposes, this is actually the desired result. If a PostgreSQL server tried claiming a virtual IP address upon reboot and we had already assigned it to a different system, traffic could go to either system and result in severe consequences. Would either database handle the requests? Would the misrouted network packets cause invalid data or some other result? We don't know; network routing can affect any level of the communication process. The end result is that the database is unusable in this state.
That said, the process of maintaining virtual IP addresses is easily automated. Later in this book, we will discuss at least one tool that automatically assigns the virtual IP to the current primary PostgreSQL server. Until then, this is still a very powerful tool to add to our arsenal.