Some built-in tools deserve special mention. The pg_receivexlog command was introduced with PostgreSQL 9.2. With this new utility, PostgreSQL has the ability to transmit transaction logs to a remote system without the need for a dedicated PostgreSQL server. This also means that we can avoid ad hoc tools such as rsync when maintaining an archive server to save old WAL files.
This allows us to set up any server to pull transaction logs directly from the primary PostgreSQL server. For highly available servers, PostgreSQL no longer needs to fork an external command to safeguard transaction logs into an archive location. In addition, we can monitor the state of the transmission through the pg_stat_replication system view.
In effect, we remove quite a bit of overhead from our PostgreSQL server and offload it to a less sensitive system. This recipe will provide a quick outline for using this utility.
Before starting with this recipe, ensure that you have a good understanding of how PostgreSQL replication works. To do this, follow the Upgrading to asynchronous replication and Bulletproofing with synchronous replication recipes.
For this scenario, the server at 192.168.56.10 is still the primary PostgreSQL server, and 192.168.56.100 will be our archive server. Follow these steps to save WAL data remotely:
- Ensure that the
pg_hba.conffile on the primary server contains this line:host replication rep_user 192.168.56.100/32 md5
- Ensure that the
wal_keep_segmentsandarchive_modesettings inpostgresql.confare set as follows on the primary server:wal_keep_segments = 1000 archive_mode = off
- Restart the configuration files on the primary server with the following command as the
postgresuser:pg_ctl -D /db/pgdata restart - On the archive server, create the
/db/pg_archiveddirectory with these commands as a root-level user:sudo mkdir -p /db/pg_archived sudo chown postgres:postgres /db/pg_archived sudo chmod 0700 /db/pg_archived
- Start the
pg_receivexlogutility on the archive server with the following command:pg_receivexlog -h 192.168.56.10 -U rep_user -D /db/pg_archived -v &> /db/pg_archived/wal_archive.log &
First, we need to ensure that the archive server at 192.168.56.100 can connect to the primary server to receive the transaction log traffic. Next, unlike other recipes that depend on archive_mode to be enabled on the primary server, we want to disable it this time. Instead, we are going to rely on pg_receivexlog itself.
One setting that we change might seem a bit odd at first. The wal_keep_segments parameter defines how many transaction logs PostgreSQL should keep after it no longer needs them. Normally, it would delete old files or call the archive command to process them if archive_mode is on. By setting it to 1000, we are telling it to always have at least 1000 extra files. This helps avoid lost WAL archives if there's a network problem, or we have to restart pg_receivexlog.
Tip
Is 1000 files too many? At 16 MB each, this accounts for 16 GB of space. Providing this much space should be very easy with modern storage devices. This many files should account for several hours of activity on all but the most active databases. It may actually be prudent to increase the limit further, depending on database activity.
Once these settings are in place, we need to restart PostgreSQL to disable WAL archival. At this point, the primary server will no longer save or transmit old WAL files anywhere. To make up for this, we make sure that the archive server has a location to store these files and that the postgres user can write to it. To continue with our examples, we will continue to use the /db/pg_archived directory.
Finally, we start the pg_receivexlog tool itself. We pass the -h parameter to connect to the primary database and use -U to enforce the replication user, rep_user. The -D parameter is required, and we use it to save WAL files to the /db/pg_archived directory we created. Then, we enable verbose output with -v just so that we are always informed about what pg_receivexlog is doing. We direct all output to a file named wal_archive.log and consider our work complete. The final & character launches the command in the background so that it functions even if we disconnect from the server.
If everything goes well, our /db/pg_archived directory should soon have some WAL files and a log inside it, as shown in the following screenshot:
The file that ends in partial is a WAL transfer that is currently in progress.
- The
pg_receivexlogutility has more extensive documentation on PostgreSQL's site. Visit this URL to learn more: http://www.postgresql.org/docs/9.3/static/app-pgreceivexlog.html.