PostgreSQL – Replica and High Availability Setup

high-availabilitypostgresqlreplication

I've postgresql 9.4.

I'm aware that there are many answer on DBA and SO, I'm aware of the wiki and the documentation.

But anyway, I can't understand in a simple way what's the right way to have a master postgresql server, and a slave postgresql server that is updated from the master (as soon as possible, to reduce data loss).

Also I would need a way to do automatic promotion of the slave to master in case of failing master.

Should I use Repmgr? Or I should just use Postgresql's functions?

Thanks

Best Answer

The right way is setting hot-standby.

Using Repmgr is just a question of your convenience. You can use it, or you can setup trigger file creating, or promoting with pg_ctl, for example. It's your choice. Repmgr is not involved in process of streaming data from master to slave, it is just management tool.

Also,

  1. Be careful with 'automatic promotion of slave in case of failing master'. You should definitely know what you are doing and you should define 'failing' very strictly. If you don't have a super-large cluster, then it is the best not to have failover fully automated.

  2. Be careful about 'data loss'. You should know, that streaming replication will save you only in case there is some failure on server side, or something wrong with network/disk/etc. It won't help you if someone will come to your db and type DROP DATABASE. Changes from statements will be immediately streamed to slave and the DB will be lost everywhere. The only way to avoid data loss from SQL statements is a valid backup.

Related Question