Gülçin Yıldırım Jelínek
Staff Database Engineer @Xata, Main Organizer @Prague PostgreSQL Meetup, MSc, Computer and Systems Engineering @ Tallinn University of Technology, BSc, Applied Mathematics @Yildiz Technical University
Gülçin Yıldırım PGConf.EU, Tallinn 2016
Postgres DBA & Automation Developer @ 2ndQuadrant
MSc Comp. & Systems Eng. @ Tallinn University of Technology
BSc Maths Eng. @ Yildiz Technical University
Writes blog on 2ndQuadrant blog
Does some childish paintings
Loves independent films
Write ahead logging mechanism is the main fault tolerance system for PostgreSQL which ensures durability of any db changes.
Database changes themselves are not written to disk at transaction commit. Those changes are written to disk sometime later by the background writer or checkpointer on a well-tuned server.
Standard SQL Transaction Isolation Levels
Crash recovery replays the WAL, but from what point does it start to recover?
Database replication is the term we use to describe the technology used to maintain a copy of a set of data on a remote system.
WAL over network from master to standby
Warm
Hot
WAL level | Suitable for |
---|---|
minimal | crash recovery |
replica | physical replication
file-based archiving |
logical | logical replication |
wal_level parameter determines how much information is written to the WAL.
Failover
Switchover
Timelines provide protection from connecting to the wrong upstream after promotion (failover, switchover).
TL1
TL2
Master (Old master)
Standby (New master)
TL1
TL2
Master (Old master)
Standby (New master)
TL1
TL2
Standby (New master)
TL1
TL2
Master (Old master)
TL1
TL2
TL1
TL2
Standby (New master)
Synchronous replication guarantees that data is written to at least two nodes before the user or application is told that a transaction has committed.
Logical replication allows us to stream logical data changes between two nodes.
There are several ways of how to look at fault tolerance. In this sense PostgreSQL provides different means of achieving fault tolerance and dependability either out of the box solutions or using extensions depending on what are the user’s priorities about their system.
General fault tolerance in PostgreSQL is improving over time and I expect this trend to continue. In my opinion, Logical Replication in core of PostgreSQL will be the next big leap of fault tolerance in PostreSQL.
By Gülçin Yıldırım Jelínek
This presentation is prepared for PGConf.EU 2016 in Tallinn.
Staff Database Engineer @Xata, Main Organizer @Prague PostgreSQL Meetup, MSc, Computer and Systems Engineering @ Tallinn University of Technology, BSc, Applied Mathematics @Yildiz Technical University