Asynchronous backup is good for most cases. However, for mission-critical data that absolutely must exist on at least two servers before being saved at all, there is another option. Synchronous replication modifies the main database server in such a way that no transactions will commit at all unless at least one backup server also receives the data. To use this, we need to add a setting to the postgresql.conf file on our main server as shown in the following code:

synchronous_standby_names = 'backup_server'

We also need to append an application_name name to the end of the primary_conninfo setting to name our backup server:

primary_conninfo = '... application_name=backup_server'

Once we restart both the main and backup databases, transactions will presumably reflect increased durability.

Though synchronous backup is more durable since all transactions must pass through at least two systems, PostgreSQL is very serious about honoring that data guarantee. If the backup server disconnects from the main server, either through server maintenance or network disruption, the main database will actually stop processing transactions entirely until the backup server returns to normal operation.

The only way to prevent this behavior is to temporarily disable synchronous replication in postgresql.conf:

synchronous_standby_names = ''

And then telling the server to reload:

$> pg_ctl -D /db/pgdata reload

This should be done before maintenance on the backup server, or if a network outage is detected between the two servers.

We also need to consider the effect of network latency. Because transactions must be acknowledged by two servers, locks are held longer on the main server. For non-critical data in a synchronous replication setup, clients should disable synchronous mode. This can be done with a basic PostgreSQL client command. The following sample update would not use synchronous replication:

SET synchronous_commit TO false;
UPDATE my_table
       SET modified_date = NOW()
 WHERE row_val LIKE '%change%';

The synchronous_commit setting can be used anywhere normal SQL is allowed. This is only a temporary change, and only applies to the current client connection. To reactivate synchronous commit in the same connection, it needs to be explicitly re-enabled using the following code:

SET synchronous_commit TO true;

Finally, we should note that synchronous replication only guarantees transactions that have been received by the backup server, not that they have been processed. Synchronous mode is really just a shallow extension of asynchronous, and as a consequence, retains many of the same limitations. One of those limits is that the backup server can only note that it has the transaction, not the progress of applying it.

In practice, backup servers usually apply transactions much faster than the main server because they do not generally carry the same transaction overhead. We just do not want to be unpleasantly surprised by an unexpected race condition if the two servers get out of sync.

Probably the best use for a hot-standby or streaming backup is the ability to use it as the primary backup source. In all likelihood, the primary database is much busier than the backup or disaster recovery server. There are just a few things we need to remember:

This means tar and rsync style backups are good for older installs, with or without filesystem snapshots. pg_dumpall and pg_dump are also perfectly valid, since they only obtain read locks on the database while they operate.

Any of the previously mentioned backup methods that do not involve pg_basebackup can be used with very little modification. The easiest way is to simply stop the backup copy, since it is likely non-critical. Since the backup server is probably under less duress, parallel compression can utilize more available CPUs, and disk bandwidth saturation carries less operational risks.

The ultimate backup method is to have an entirely separate backup server to act in a disaster recovery role. Not only can it fill in for the main database in case of an outage, but low priority work and maintenance procedures such as regular backups can be offloaded without risking the primary system. Streaming replication makes this both easy and convenient.