PostgreSQL - Replication

Question

PostgreSQL

Hello everyone,
the task was to achieve 99.9% uptime on the server. We can afford the idea to raise a backup server, down for 3-4 minutes, no more. The PostgreSQL database needs to be replicated in real time or close to it, code and user files (pictures, mostly) can be synchronized with delays.
What are the best practices in such cases?

Answer 1

for database replication, use native streaming replication, it is quite easy to learn.
www.thislinux.org/2014/03/postgresql-streaming-rep ...
Repmgr is essentially a set of scripts for streaming replication, so you can achieve the same result with them.
Best practices are as follows
: standby can become a master in case of an accident and it will be sad if he does not pull the load.
2. pay attention to the reliability and bandwidth of the network, it must digest the volumes of traffic that will fly from the master to the standby
3. test and write instructions for switching the standby to the master so that you don’t fuss in case of an accident (you can test it on staging).
4. Set up wal archiving and backup via basebackup. with potential operator / migration curve errors (DROP / TRUNCATE TABLE, DELETE ALL), all changes will fly away to the standby and it, together with the master, will turn into a pumpkin.

Answer 2

Take a look at pgpool, this is a mature production-ready solution, including for replication. There are quite detailed examples.

Answer 3

I recommend using the very good repmgr tool.
Simple, reliable, easily scalable.
https://github.com/2ndQuadrant/repmgr