Creating a replicated database


Home Prev	Chapter 21. Database backups	Next

If you have a separate server that you can use to run a replica of your database, consider replicating your database to that server. In the event that your primary database server suffers a hardware failure, having a database replica gives you the ability to fail over to your database replica with very little downtime and little or no data loss. You can also improve the performance of your overall system by directing some read-only operations, such as reporting, to the database replica. In this section, we describe how to replicate your database using PostgreSQL’s streaming replication support.

You need to prepare your master PostgreSQL database server to support streaming replicas with several configuration changes. The PostgreSQL configuration file is typically located on Debian and Ubuntu servers at /etc/postgresql/<version>/postgresql.conf. The PostgreSQL host-based authentication (pg_hba.conf) configuration file is typically located on Debian and Ubuntu servers at /etc/postgresql/<version>/pg_hba.conf. Perform the following steps on your master database server:

Turn on streaming replication support. In postgresql.conf on your master database server, change max_wal_senders from the default value of 0 to the number of streaming replicas that you need to support. Note that these connections count as physical connections for the sake of the max_connections parameter, so you might need to increase that value at the same time.
Enable your streaming replica to endure brief network outages without having to rely on the archived WAL segments to catch up to the master. In postgresql.conf on your production database server, change wal_keep_segments to a value such as 32 or 64.
Increase the maximum number of log file segments between automatic WAL checkpoints. In postgresql.conf on your production database server, change checkpoint_segments from its default of 3 to a value such as 16 or 32. This improves the performance of your database at the cost of additional disk space.
Create a database user for the specific purpose of replication. As the postgres user on the production database server, issue the following commands, where replicant represents the name of the new user:
```
createuser replicant
psql -d <database> ALTER ROLE replicant WITH REPLICATION;
```
Enable your replica database to connect to your master database server as a streaming replica. In pg_hba.conf on your master database server, add a line to enable the database user replicant to connect to the master database server from IP address 192.168.0.164:
```
host    replication   replicant       192.168.0.164/32          md5
```
To enable the changes to take effect, restart your PostgreSQL database server.

To avoid downtime, you can prepare your master database server for streaming replication at any maintenance interval; then weeks or months later, when your replica server environment is available, you can begin streaming replication. Once you are ready to set up the streaming replica, perform the following steps on your replica server:

Ensure that the version of PostgreSQL on your replica server matches the version running on your production server. A difference in the minor version (for example, 9.1.3 versus 9.1.5) will not prevent streaming replication from working, but an exact match is recommended.
Create a physical backup of the master database server.

Add a recovery.conf file to your replica database configuration directory. This file contains the information required to begin recovery once you start the replica database:

# turn on standby mode, disabling writes to the database
standby_mode = 'on'
# assumes WAL segments are available at network share /data/wal
restore_command = 'cp /data/wal/%f %p'
# connect to the master database to being streaming replication
primary_conninfo = 'host=kochab.cs.uoguelph.ca user=replicant password=<password>

Start the PostgreSQL database server on your replica server. It should connect to the master. If the physical backup did not take too long and you had a high enough value for wal_keep_segments set on your master server, the replica should begin streaming replication. Otherwise, it will replay WAL segments until it catches up enough to begin streaming replication.
Ensure that the streaming replication is working. Check the PostgreSQL logs on your replica server and master server for any errors. Connect to the replica database as a regular database user and check for recent changes that have been made to your master server.

Congratulations, you now have a streaming replica database that reflects the latest changes to your Evergreen data! Combined with a routine of regular logical and physical database backups and WAL segment archiving stored on a remote server, you have a significant insurance policy for your system’s data in the event that disaster does strike.

Prev	Up	Next
Creating physical database backups with support for point-in-time recovery	Home	Part IX. UX Administration