You are here

Hot Standby Server

Subscribe to Syndicate

One of the features of Postgres is the ability to create a 'hot standby' database server as a way to introduce redundancy to the system. The notion is that if a catastrophic physical failure occurred to the database server (such as the drives died, the machine melted, it got doused in water or burnt in a fire) and was found to be non-recoverable, then it would be best to get to a backup as soon as possible.

The standard backup approach allows you to go back to a snapshot. If you do a number each day, that may mean going back a few hours and reconstructing what happened from the backup to the failure.

Replication means you have one (or more) standby servers that is shadowing the contents of the main server - and may be as close as seconds old. In the case of failure, you would turn off the replication feature and start using the standby database until your primary server gets fixed.

Do not use replication as the only means of backup - always set up snapshot and offsite copies of your database.

We always recommend using the latest version of PostgreSQL for replication. These instructions were created for PostgreSQL version 10

If you do a minor level upgrade of PostgreSQL on the main database server (e.g. from PostgreSQL v9.6.x to v9.6.y), you must also upgrade the version of Postgres on the hot standby server at the same time. There is no need to migrate the database again.

However, if you do a major level revision, you will need to do ALL the steps to re-establish the hot standby. This will change the time it requires to upgrade (but having a hot backup is worth it).

If you update your version of Theatre Manager, any changes to the data in the main database are also updated on the hot standby. You never have to update the backup server as all changes come from the main server

The general steps for making a hot standby are:

Many thanks to this insightful article by Bartek Ciszkowski for helping make the process on Linux which we adapted to OS X. You can also read more detail on the Postgres site regarding high availability servers for any of the technical details.

Replication can be done on windows per this tutorial and it implies that there are few differences, except how to get the WAL logs replicated from one machine to the other - using a file share.