The recent PostgreSQL 9.0 release offers several significant improvements to PostgreSQL’s built-in replication solution. Ready to add replication to your PostgreSQL install? It’s easier than you might think.
Replication is a key technology for any database offering, because databases are typically mission-critical systems. Downtime — or, worse yet, data loss — can result not only in inconvenience to employees and customers but also in lost productivity and lost revenue. By replicating from a primary server, or master, to one or more secondary servers, sometimes called standbys, the probability of data loss or extended downtime can be greatly reduced.
PostgreSQL’s built-in replication solution works by copying the contents of the write-ahead log (WAL) from the master server to each standby. Certain operations, such as bulk data loads into a newly created table, do not need to be WAL-logged when replication is not in use; so replication does have some overhead. However, in order to allow for proper crash recovery, most operations must be logged whether or not replication is being used, so the overhead is typically quite small.
Although PostgreSQL has supported a form of built-in replication for several years, release 9.0 offers a number of major enhancements. First, in releases prior to 9.0, standby servers often lagged the master by several minutes. WAL was transmitted from master to standby in 16MB chunks, either when a chunk became completely filled or when a timeout expired. Due to the large chunk size, it was impractical to set the timeout to less than several minutes. Thus, in case of a total loss of the master, several minutes worth of transactions could be lost. In PostgreSQL 9.0, the contents of the write-ahead log can be streamed to standby servers in near real-time, typically resulting in a delay of a few seconds or less. While it is still asynchronous replication, the window for data loss is much smaller.
Second, with PostgreSQL 9.0, you can run read-only queries to be run on the standby server (with proper configuration). Standby queries can sometimes postpone the application of write-ahead log records received from the master, or (depending on configuration) they may be cancelled if they result in an undue delay in applying write-ahead log records. As a result, this feature may not be suitable for all use cases, but it still represents a major advance over previous PostgreSQL versions, where no queries could be executed on the standby at all.
How to Configure a PostgreSQL Standby Server
Configuring a standby server for PostgreSQL is relatively simple, although it does require editing several configuration files (all of which use simple text formats) as well as various other steps, which are described below. The first step is to edit postgresql.conf on the master and configure the following settings. The variables shown here are likely already present in postgresql.conf, but they will probably be commented out. Uncomment them and set them to the values shown here.
wal_level=hot_standby
max_wal_senders=1
wal_keep_segments=25
Setting wal_level=hot_standby tells PostgreSQL to generate sufficiently detailed write-ahead logs to allow query execution on the slave. The max_wal_senders parameter controls the maximum number of standby servers this master can support. In some cases, you might want 2 or more, but here we’re assuming you only need 1.
If your standby goes down, the value of wal_keep_segments controls the amount of WAL retained by the master in excess of what is needed for crash recovery. In this case, we’ve asked the master to retain 25 extra 16MB WAL segments, or an additional 400MB of WAL. If a standby goes down for long enough that the master generates more than 400MB of write-ahead logs in the interim, it may find on reconnecting that the files it needs to catch up with the master are no longer present. In that case, you’ll need to repeat this setup process. Alternatively, you can turn on archiving using the archive_mode and archive_command settings on the master, which allow arbitrary amounts of WAL to be retained. However, with this method, you must be careful not to fill up the disk.
After making the changes described above, edit the master’s pg_hba.conf file (typically located in the same directory as postgresql.conf) and add a line of the form “host replication all SLAVEIP/32 trust” where SLAVEIP is an IP address. This will allow replication connections, but only from that IP address. (Other authentication methods can also be used, as discussed in the documentation.) Once you have made all of the configuration changes described above, restart PostgreSQL on the master server: the parameters wal_level and max_wal_senders can’t be changed without a server restart.
You are now ready to set up the slave. To do this, take a “hot backup” of the master. Log into the database as the database superuser (often “postgres”) and execute the command “SELECT pg_start_backup('backup').”
Then, with the server still running, copy the contents of the data directory (use “SHOW data_directory” to find it if you’re not sure where it is; it’s normally the same directory that contains postgresql.conf) to the standby server using any file copy tool; “rsync -a” is a good choice. Once this is complete, log back into the master database and execute the command “SELECT pg_stop_backup()“. (This may produce a warning message, which you can disregard.)
Once the hot backup is complete, you can begin configuring the slave. Edit postgresql.conf on the slave and set hot_standby=on. As with the configuration changes on the master, the setting should already exist in the file, but it may be commented out and the default value will probably be off. Remove any leftover postmaster.pid file copied from the master. Create a file called recovery.conf in the same directory as postgresql.conf containing the following lines (substituting the IP of the master server for MASTERIP):
standby_mode='on'
primary_conninfo='host=MASTERIP'
You’re ready to start up the slave. It should connect to the master and begin replicating! You should also be able to execute queries on the slave, although only if they are strictly read-only. There are several methods for promoting the slave to a master. The simplest method is to remove recovery.conf and restart the slave. Alternatively, you can add the following line to recovery.conf:
trigger_file='/path/to/some/file/that/does/not/exist'
If the named file is subsequently created, the recovery.conf file will be renamed to recovery.done, and the server will cease replication and begin allowing read-write transactions. This method can be advantageous because it avoids the need for a server restart.
That’s about it! Give it a try and see how it works for you. If you have questions, the pgsql-general mailing list is a good place to ask.
