Evolution of fault tolerance in PostgreSQL
We are actively preparing for the PG Day'17, expanding the theme of the conference, so soon you will find a large number of interesting posts not only PostgreSQL, but also on other widely used databases. Today I want to offer to your attention translation of the article Gulcin Yildirim, which served as the basis for its report at PG Conf Europe'16.
"As paradoxical as it may sound, the truth is that the more we learn, the more ignorant you become in the absolute sense, for it is only through enlightenment we realize our limitations. More specifically, one of the most gratifying results of intellectual evolution is the continuous opening of new and broader prospects". Nikola Tesla
PostgreSQL is an amazing project, which is developing with amazing speed. In this series of posts we will focus on the evolution of the capabilities of failover in PostgreSQL for all of its versions.
the
PostgreSQL failover in nature. First, it's an advanced database management system open source, this year otprazdnuem its 20-year anniversary [approx. lane: the anniversary comes in 2016, festive greetings from the Russian community was held on the final night PG Day’16 Russia]. Therefore, it is proven technology with an active community through which she is actively developed.
PostgreSQL is SQL-compatible (SQL: 2011) and fully meets the requirements of ACID (atomicity, consistency, isolation, durability).
PostgreSQL allows you to create physical and logical replica and has a built-in physical and logical solutions for this. We will discuss methods of replication for PostgreSQL in the context of fault tolerance in the next post.
PostgreSQL allows you to perform both synchronous and asynchronous transactions, PITR (Point-in-time Recovery point-in-time) and MVCC (Multiversion concurrency control — the Management of competitive access using versioning). All of these concepts to a certain extent relate to the failover and I'll try to describe their impact in the process of explaining the basic concepts and their applications in PostgreSQL.
the
All actions in the database are produced inside the transaction, protected transaction log that will perform automatic disaster recovery in the event of a failure of the software.
Optionally, you can create a database with data blocks with checksums (data block checksums) to diagnose a hardware problem. There are many backup mechanisms with the full and detailed PITR in case requires a restoration. Also available are various tools for diagnosis.
Database replication is natively supported. Synchronous replication, with the right set up and manage, provides more compared with “5 nines” (99.999%), the degree of availability and data protection.
Considering all the above facts, it is easy to argue that PostgreSQL is reliable!
the
WAL — write ahead logging is the main system failover for PostgreSQL.
WAL consists of a series of binary files stored in the pg_xlog subdirectory of the data directory of PostgreSQL. Every change made to the database is first written to the WAL, hence the name "forward" magazine, in tune with the "transaction log". When the transaction completes, the default behavior — and most secure — is to force WAL records to disk.
In the event of a failure of PostgreSQL WAL to be played, which returns the database to the time of completion of the last transaction and thus ensure the safety of any changes to the database.
the
Changes to the database themselves are not written to disk at the time of completion of the transaction. They are written after some time background writer processes & checkpointer on a well configured server. (See description above WAL)
Transactions are a fundamental concept of all database systems. A distinctive feature of transaction is that it combines multiple steps into a single "all-or-nothing" operation.
The intermediate States between the steps are not visible to other transactions that run in parallel, so if there is any failure, prevent the transaction complete, no one step will not affect the database in General. PostgreSQL does not support dirty reads (dirty reads — a transaction reads data written to the parallel unfinished transaction).
the
Disaster recovery plays a WAL, but at what point begins the restore?
Recovery begins with the point in WAL, known as the breakpoints (checkpoints). The duration of a disaster recovery depends on the number of changes in the transaction log since the last checkpoint. A checkpoint is a known safe starting point of the reconstruction, as it ensures that previous database changes were already written to disk.
A control point can be immediate or scheduled. Instant control points appear as a result of any action the privileged user (your administrator password), for example, team CHECKPOINT or other. Scheduled checkpoints are set by Postgres automatically.
the
In this post we have listed important PostgreSQL features related to its resiliency. Was referred to write ahead logging, transaction, isolation levels, checkpoints, and disaster recovery. In the next post we will continue the theme of the story on replication in PostgreSQL.
Article based on information from habrahabr.ru
"As paradoxical as it may sound, the truth is that the more we learn, the more ignorant you become in the absolute sense, for it is only through enlightenment we realize our limitations. More specifically, one of the most gratifying results of intellectual evolution is the continuous opening of new and broader prospects". Nikola Tesla
PostgreSQL is an amazing project, which is developing with amazing speed. In this series of posts we will focus on the evolution of the capabilities of failover in PostgreSQL for all of its versions.
the
PostgreSQL in a nutshell
PostgreSQL failover in nature. First, it's an advanced database management system open source, this year otprazdnuem its 20-year anniversary [approx. lane: the anniversary comes in 2016, festive greetings from the Russian community was held on the final night PG Day’16 Russia]. Therefore, it is proven technology with an active community through which she is actively developed.
PostgreSQL is SQL-compatible (SQL: 2011) and fully meets the requirements of ACID (atomicity, consistency, isolation, durability).
note: A(tomicity) C(onsistency) I(solation) D(urability) in PostgreSQL
Atomicity guarantees that the results of the transaction will be completely visible or not visible inside other transactions, but the transaction must be atomic in respect to myself. PostgreSQL different consistency, and system-defined limits dictated by the principle of coherence are applied to the results of the transaction. That is, after the completion of the transaction, the data must remain in a consistent state. The simultaneous transactions do not affect each other, which tells about the isolation (we will talk about the levels of transaction isolation later in this post). When a transaction is completed, the results will not be lost, regardless of subsequent failures, and this makes PostgreSQL reliable.
PostgreSQL allows you to create physical and logical replica and has a built-in physical and logical solutions for this. We will discuss methods of replication for PostgreSQL in the context of fault tolerance in the next post.
PostgreSQL allows you to perform both synchronous and asynchronous transactions, PITR (Point-in-time Recovery point-in-time) and MVCC (Multiversion concurrency control — the Management of competitive access using versioning). All of these concepts to a certain extent relate to the failover and I'll try to describe their impact in the process of explaining the basic concepts and their applications in PostgreSQL.
the
PostgreSQL reliable!
All actions in the database are produced inside the transaction, protected transaction log that will perform automatic disaster recovery in the event of a failure of the software.
Optionally, you can create a database with data blocks with checksums (data block checksums) to diagnose a hardware problem. There are many backup mechanisms with the full and detailed PITR in case requires a restoration. Also available are various tools for diagnosis.
Database replication is natively supported. Synchronous replication, with the right set up and manage, provides more compared with “5 nines” (99.999%), the degree of availability and data protection.
Considering all the above facts, it is easy to argue that PostgreSQL is reliable!
the
Failover PostgreSQL: WAL
WAL — write ahead logging is the main system failover for PostgreSQL.
WAL consists of a series of binary files stored in the pg_xlog subdirectory of the data directory of PostgreSQL. Every change made to the database is first written to the WAL, hence the name "forward" magazine, in tune with the "transaction log". When the transaction completes, the default behavior — and most secure — is to force WAL records to disk.
In the event of a failure of PostgreSQL WAL to be played, which returns the database to the time of completion of the last transaction and thus ensure the safety of any changes to the database.
the
Transaction? Commit?
Changes to the database themselves are not written to disk at the time of completion of the transaction. They are written after some time background writer processes & checkpointer on a well configured server. (See description above WAL)
Transactions are a fundamental concept of all database systems. A distinctive feature of transaction is that it combines multiple steps into a single "all-or-nothing" operation.
note: Transactions in PostgreSQL
PostgreSQL actually treats every SQL statement as being executed within a transaction. If you are writing a BEGIN command, then each individual request will have a team implicit BEGIN and (if successful) COMMIT at the beginning and the end, respectively. Group of queries that are wrapped in BEGIN and COMMIT is sometimes called a transaction block.
The intermediate States between the steps are not visible to other transactions that run in parallel, so if there is any failure, prevent the transaction complete, no one step will not affect the database in General. PostgreSQL does not support dirty reads (dirty reads — a transaction reads data written to the parallel unfinished transaction).
note: transaction Isolation
The SQL standard defines 4 levels of transaction isolation: Read uncommitted, Read completed, Re-read, Serialization.
Table 1: isolation Levels the standard SQL transactions
the
the isolation Level Dirty read non-repeatable read Phantom read serialization Anomaly the Read uncommitted Permitted, but not PG Maybe Maybe Maybe the Read complete Maybe Maybe Maybe the Re-read Permitted, but not PG Maybe Serialization
The most stringent is the level of Serialization that is defined by the standard in a paragraph that any concurrent execution of serializable transactions is guaranteed to give the same effect as if run sequentially in the same order.
For more information on this topic refer to the documentation of Postgres about transaction isolation.
the
checkpoint
Disaster recovery plays a WAL, but at what point begins the restore?
Recovery begins with the point in WAL, known as the breakpoints (checkpoints). The duration of a disaster recovery depends on the number of changes in the transaction log since the last checkpoint. A checkpoint is a known safe starting point of the reconstruction, as it ensures that previous database changes were already written to disk.
A control point can be immediate or scheduled. Instant control points appear as a result of any action the privileged user (your administrator password), for example, team CHECKPOINT or other. Scheduled checkpoints are set by Postgres automatically.
the
Conclusion
In this post we have listed important PostgreSQL features related to its resiliency. Was referred to write ahead logging, transaction, isolation levels, checkpoints, and disaster recovery. In the next post we will continue the theme of the story on replication in PostgreSQL.
Comments
Post a Comment