In this article I will introduce you with a solution for:
- Performing back up that will allow Point-In-Time Recovery using barman.
- Create a hot standby database server that is ready for use in case your main PostgreSQL server fails, using repmgr.
For the purpose of this article, I will assume you have PostgreSQL 10 or higher installed, and basic knowledge of PostgreSQL configuration.
Introduction
Backup
There are several methods for backing a PostgreSQL database up. You can find more information at https://www.postgresql.org/docs/10/static/backup.html .
- Backup using pg_dump or pgdump_all: this solution is the most simple of all but doesn’t perform well for large database. It also would not allow you to perform a fine grained backup and you will not be able to recover data that happened *after the backup*. If you backup using a cronjob every night at 4:00 AM, and an accident happen at 4:00 PM the same day, you would have lost 12 hours of data.
- In our case we are interested in Continuous Archiving and Point-in-Time Recovery (PITR) which would allow us to recover our database to any point in time. This is obviously a better solution than pg_dump but requires much more knowledge of PostgreSQL and more configuration. It also does not allow to fine tune the backup on a per database basis, the backup happens for the whole cluster and so is the recovery.
Continuous Archiving and Point-in-Time Recovery
To understand PITR, we must first understand what is WAL, the replication and backup will be based on WAL files, so it’s important to understand what it is. WAL stands for Write-Ahead-Log. Basically every modification, insert, update and delete are written into a log *before* they are actually applied. This way if the database crashes in the middle of an operation, upon restart, the server will be able to look at the WAL file and tell if the operation was successful or not and restore the database to a usable state. It is those log files that are used for both backup and replication.
Replication
In order to create a hot standby server, ready to take over in case the main server fails, we will setup replication in a typical master-slave configuration. The master server will replicate any data, schema, database or role changes into the slave server. If the master fails, we have a copy ready for use.
Architecture
We chose an architecture with 3 servers. A primary database server, a standby server and a backup server.
Tutorial
We will divide this tutorial into several part, each being an evolution of the previous, meaning you could stop at the end of one part and still have a working architecture. We will also go further than just configuration and will test our setup in some potential real situation. There is no point in setting up a backup if you find out *after* a disaster than you cannot use it for recovery.
For this I suggest you setup 3 (or 2 depending how far you go in the tutorial) development servers (I use vagrant).
Chapters
- Implement backup with barman
- Recover my database with barman
- Implement replication with repmgr & barman
- Failover with repmgr and automatic failover implementation
I will link the chapters here as I write them.
I will assume PostgreSQL 10 on a Debian like distribution. Feel free to adapt to your use case.
