It's time to address the elephant in the room. When managing a wide PostgreSQL cluster, we will often need to rebuild, reassign, and repair nodes that are replicas of our primary server. If we remember our rule-of-threes, three or more nodes make it difficult and error prone to perform any task related to replication.
While Barman and OmniPITR are useful, neither of them is capable of managing a wide network of PostgreSQL replicas. This is why we would like to thank 2ndQuadrant for repmgr. With it, we can create new clones and add them to an existing cluster of PostgreSQL servers. We can shut down the existing primary server and promote any node in this network. Further, all of the existing replicas automatically consider the promoted node their new source of streaming updates.
This may not be the first tool to perform this task, but it is one of the best available. We'll tackle the process of installing it in this recipe before moving on to usage scenarios.
At the time of writing this book, the most recent version of repmgr is 2.0. As with Barman, repmgr is available within the PostgreSQL package repositories. If you are using a Debian or Ubuntu-based system, follow the instructions at http://wiki.postgresql.org/wiki/Apt to add the PostgreSQL repository to any system that will be running as a repmgr server or client.
Otherwise, Red-Hat-based systems should add the PostgreSQL repository by installing the derivative-appropriate RPM located at http://yum.postgresql.org/repopackages.php.
We recommend that you use repositories only, as the repository-provided packages perform tasks other than software installation, such as user creation.
For the purposes of this recipe, we will need two servers. The primary PostgreSQL node will be named pg-primary, and the replica will be pg-clone. Both servers exist on the 192.168.56.0 subnet. As always, the /db/pgdata path will be our default data directory. Be sure to have the password for the postgres system user ready.
Follow these steps to install repmgr on both servers:
- Red-Hat-based systems should use this command as a root-capable user:
sudo yum install repmgr - Debian-based systems should use this command instead:
sudo apt-get install repmgr postgresql-9.3-repmgr - Optionally, copy the
repmgrscript from the/initdirectory in this chapter to the/etc/init.ddirectory on each server. - If the supplied
initscript was copied, execute these commands as a root-capable user:sudo rm -f /etc/init.d/repmgrd sudo chmod 755 /etc/init.d/repmgr
Next, follow these steps on pg-primary to set it up as a master node. We'll consider pg-clone in the next section:
- As the
postgresuser, generate an RSA key pair and transmit it topg-clonewith these commands:ssh-keygen -t rsa -N '' ssh-copy-id postgres@pg-clone
- Modify the
postgresql.conffile and set the following parameters:wal_level = hot_standby archive_mode = on archive_command = 'exit 0' wal_keep_segments = 5000 hot_standby = on
- Modify the
pg_hba.conffile and add the following lines:host all postgres 192.168.56.0/24 trust host replication postgres 192.168.56.0/24 trust
- Restart the PostgreSQL service as the
postgresuser with this command:pg_ctl -D /db/pgdata restart - Execute this command to find the binary path to PostgreSQL tools:
pg_config --bindir - Create a file named
/etc/repmgr.confwith the following contents:cluster=pgnet node=1 node_name=parent conninfo='host=pg-primary dbname=postgres' pg_bindir=[value from step 5]
- Register the master node with the following command as the
postgresuser:repmgr -f /etc/repmgr.conf master register - Start the
repmgrddaemon with the following command as a root-level user:sudo service repmgr start - Examine the
repmgrlogfile withcat:cat /var/log/repmgr/repmgr.log
These may seem like a lot of instructions, but they're actually very simple, merely numerous. We start the process by actually installing repmgr on both nodes. Depending on our OS, we do this either with yum or apt-get. Afterwards, we have a choice. This chapter supplies an initialization script for repmgr that we know is fully LSB compliant and functional. The script bundled with the Debian-based packages didn't daemonize, log, or stop the service. We suggest that you use ours, but it is not required.
Once we've installed repmgr, we want to focus on pg-primary as it will be the source of all of our data clones. To facilitate secure communication, our first job is to establish an RSA SSH key pair and transmit it to the clone. For repmgr to work best, every server should be able to interact with every other server in this manner.
Then, we need to modify some PostgreSQL configuration files. We start with the postgresql.conf file. Earlier chapters recommend that you set wal_level to hot_standby, but what about the other settings? We've already used archive_mode in this chapter; however, we've set archive_command to exit 0. In Unix, any command that exits with a status of 0 is assumed to be functioning properly. Thus, PostgreSQL will believe that its archive process always succeeds.
Next, we set wal_keep_segments to 5000. Why such a high value? That's almost 80 GB of extra files! For one, it's required by repmgr, so we have no choice. Yet, it's a small price to pay for easy management of multiple PostgreSQL clones. We enable hot_standby for similar reasons; it's ignored on master nodes but ready when the configuration file is copied to a replica.
Next, we add two lines to the pg_hba.conf file to allow the postgres user to connect to any database, including the replication pseudo-database. To follow our example, we allow these connections to originate from anywhere within the 192.168.56.0 subnet.
Tip
Though our example uses trust authorization, we suggest that real production systems utilize .pgpass files and md5 authentication instead. Unless the PostgreSQL servers can communicate directly on a private firewalled network, this setup allows any user on these servers to clone our database. Further, only use the postgres database user when configuring repmgr. There is currently a bug that prevents repmgr from working properly if you are using any other superuser name.
To finish our configuration duties, we create a single file named repmgr.conf in the /etc directory. We named the repmgr cluster pgnet, noted that this is our first node, and named our node parent as it is easy to remember. The connection information needs to match our entry in pg_hba.conf; thus, we use the repmgr user that we added to the database earlier. Finally, we set pg_bindir so that repmgr always knows where to find certain PostgreSQL binaries. This setting is supposed to be optional, but we ran into several problems when we tried to omit this entry; just keep it for now.
Now that everything is prepared, we can finally register the primary node and complete the installation process by creating various database objects. These steps are all performed by the repmgr command, provided we specify the configuration file with -f and use the master register parameter. Our output should look something like this:
We're almost done! The repmgr system comes with a daemon that manages communication and controls behavior between other repmgr nodes. If we start this daemon, repmgr will run in the background and await the arrival of new clones. If we examine the log output in /var/log/repmgr, we'll see the initial startup messages:
- The repmgr system exists mainly as a source repository. Though, like Barman, it is maintained by 2ndQuadrant, its documentation is much more sparse. However, it does provide a very lengthy installation and usage overview at https://github.com/2ndQuadrant/repmgr.