Failure Recovery

Our failure recovery strategy depends on the kind of failure. We can make a quick classification of the failures we can experience, different failure scenarios implying different recovery processes.

Natural disaster

Bad things happen and some may not be prevented: arsons, earthquakes, floods, you name it. Therefore, we should make sure our critical data is held at different geographical places simultaneously.

Human disaster

Our team may introduce a bug. Even PostgreSQL developers can write bugs. Our OS distribution can ship one, or some hackers can exploit a weakness: you never know. Such a bug can corrupt or delete our master data and all their hot copies. We need at least a week (a month is better) of inert archives (disconnected from our system) so we can restore our system to a previous state. If need be, this dreadful story should convince anyone of the necessity of such a procedure.

Server crash

Sometimes a server crashes. There are byzantine softwares interactions, memory leaks or resource outage. Its motherboard burns, or a disk gets corrupted. Hot data replication can allow us to get up again very fast in case of a server failure.

Update

This is not really a failure, but we must make sure that we will not lose any data when we need to update our hardware, our OS or our PostgreSQL version. There is no excuse, and we want to be up-to-date to mitigate the probabilities of very undesirable situations (like a crash, a hacker finding its way in or an old bug awakening). Our update procedure must be as seamless as possible.

Data Typology

We do not need the same recovery guarantees on all our datasets, so we made a simple classification.

Transient data

Many datasets have a very limited lifetime, and we can compute or fetch them again if need be. The state and telemetry of cars, charge points and batteries, user sessions on the web or in front of a kiosk, etc. This kind of data can be held in a cache. If they are in a database, we can accept to lose them on any failure, and even during a simple update.

Logs

Activity logs are not essential, but they are used for statistics, reports and verifications. Their business value is minor. We can accept to lose all of them in case of a disaster, and lose some of them in case of a failure. We should not lose them during an update, though.

Core data

The core of our business data do not change so much over time: client accounts, cars and stations list and parameters. We can accept to lose some of it: as it does not change so much, losing for example 24 hours of modifications just means the clients who registered this day will have to register again, the new cars and new stations of the day will have to be inserted and configured again: a small price to pay in face of a disaster. Of course we want to make this time range as limited as possible.

Critical data

Finally, a limited set of data is critical and we can not afford to just lose them in any case. Invoices, accounting and payments for example. We have to handle this data carefully as to make sure we do not lose them in any failure scenario, so we check the data life cycle step-by-step. For example, the invoice process goes as follows:

1. We generate invoice data and wait a reasonable time for replication. If some invoices are lost because of a disaster at this point, it has no impact (they were not official invoices yet).
2. We send invoice data for replication in a independent third-party system. The invoices are now replicated on two sites. Data loss is very unlikely at this point. Still, if some invoices are lost, it has no impact.
3. We get a list of all invoices that were properly saved by our partner. These invoices are now official: we will not lose them even if a disaster occurs.

PostgreSQL backup and replication

PostgreSQL comes with an extended documentation about replication and recovery. We will only cover the essential parts here and get straight to the point.

pg_dump -j4 -Fc -T excluded_table -f dump_file.sql db_name

# -j4: Run on 4 threads. Add the --no-synchronized-snapshots option for PostgreSQL < 9.2.
# -Fc: Generate compressed binary dumps, more lightweight than default text dumps.
# -T: Exclude non-critical data you will accept to lose in case of failure.

pg_restore -j4 -d db_name dump_file.sql

Multi-tier replication

Considering the different techniques described above, we came up with a combination to set up a solid multi-tier replication scheme.

wal_level = hot_standby     # this is compatible with the "archive" level
archive_mode = on
# how to save wal records (%p is the source, %f the filename)
archive_command = 'gzip < %p > /mnt/nfs1/wal/%f'
# in case we need to plug another hot standby or do a wal-streamed pg_basebackup
max_wal_senders = 2

pg_basebackup -Ft -z -D backup_dir_path

# -Ft: Output as a tar so we can compress
# -z: Compress using gzip

# how to restore wal records (%p is the source, %f the filename)
restore_command = 'gunzip < /mnt/nfs1/wal/%f > %p'
# once restored, register where we stopped (%r is the first record to keep, the next start point)
archive_cleanup_command = 'echo %r > /tmp/wal_start_point'
# point in time at which we want to recover
recovery_target_time = '2015-04-01'

pg_archivecleanup -x .gz /mnt/nfs1/wal `cat /tmp/wal_start_point`

# -x .gz: wal files have the .gz extension

# Allow the user "foo" from standby (192.168.1.100) to connect to the master for replication (with password)
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     foo             192.168.1.100/32        md5

# The standby connects to the master (192.168.1.50:5432) as "foo" with password "foopass"
primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
# it is a hot standby
hot_standby = true
# same restore as a PITR, but no cleanup
restore_command = 'gunzip < /mnt/nfs1/wal/%f > %p'

pg_ctl promote

Configuration fine-tuning

Once we knew our recovery strategy, we configured our PostgreSQL accordingly. In the rare case of a server failure, we do not hope to get the storage and the file system back: we just switch immediately to the hot standby. This means we have no interest in keeping our file system consistent in failure scenarios. This allows us to change our settings from default quite a bit. There is a PostgreSQL wiki with extended details about configuration.

Default settings:

# core config
max_connections = 100         # max connections
full_page_writes = on         # copy full pages to WAL
work_mem = 1MB                # warning: multiple buffers of such size by query
synchronous_commit = on       # wait for WAL to be flushed
maintenance_work_mem = 64MB   # maintenance memory (for vacuum, index creation)
shared_buffers = 128MB        # cross-queries memory (cache)

# hints for planner estimates
random_page_cost = 4.0        # Disk random/contiguous access cost
effective_cache_size = 4GB    # OS disk-caching capability for planner estimations

# WAL checkpoints and shipping
checkpoint_segments = 3       # max segments before checkpoint (3*16MB)
checkpoint_timeout = 5min     # timeout before checkpoint
checkpoint_completion_target = 0.5  # checkpoint write spread

Our performance settings:

# core config
max_connections = 500         # workers + apis + www
full_page_writes = off        # never rebuild ourselves (use standby)
work_mem = 30MB               # mean 2 buffer allocations by query so it is OK
synchronous_commit = off      # current checkpoint lost in some crash cases (psql, kernel)
maintenance_work_mem = 4GB    # 1/16 memory for vacuum
shared_buffers = 20GB         # 1/4 memory shared between psql processes

# hints for planner estimates
random_page_cost = 1.0        # using SSD drives
effective_cache_size = 40GB   # 1/2 memory

# WAL checkpoints and shipping
checkpoint_segments = 128     # max segments before checkpoint for WAL (2GB)
checkpoint_timeout = 5min     # timeout before checkpoint (should be hit before size limit)
checkpoint_completion_target = 0.9  # spread writes fully

This stresses the importance of having a clear recovery strategy: in short, our PostgreSQL instance operates in memory, quite like a cache software. It does not need to wait for a disk flush to answer a query: this is an enormous performance gain. At the same time, not worrying about our file system state lets us use shorter WAL records (full_page_writes = off) and the streamed replica gets more efficient