Giriş
Write-Ahead Log ayarları postgresql.conf dosyasında wal_level alanındadır
Write-Ahead-Log Nedir
Açıklaması şöyle. Yani veri tabanında yapılan değişiklikler önce WAL dosya sistemine yazılır. Daha sonra gerçek veri tabanı dosyasına işlenir
From this log, a database can rebuild its tables, databases, schemas, and everything else if the data files are somehow corrupted. But there’s one very important caveat: Once a transaction is committed or rolled back, it is removed from the log. The purpose of the log is to stage changes until a transaction is completed — not to act as a sort of backup mechanism. Somewhat small and insignificant blips in the database can be recovered from the log, but anything more serious than a blip will call for some kind of external backup to be restored.In PostgreSQL and some other relational databases, this log is called the Write-Ahead-Log (WAL). Managing this WAL and its various features is a big part of performance-tuning these databases and also how PostgreSQL manages replication. Any transaction that is written to the WAL is also broadcast to its replication peers so they can add the transaction to their WAL.Understanding this mechanism is key to understanding how databases work and troubleshooting them when things go bad.
WAL Loglarını Okumak
Normalde WAL loglarını okumak mümkün değil. Bu dosyaları okumak için Logical Decoding yapmak gerekiyor. Açıklaması şöyle
Normalde WAL loglarını okumak mümkün değil. Bu dosyaları okumak için Logical Decoding yapmak gerekiyor. Açıklaması şöyle
WAL records being representative of the internal state of the Database system, are not easy to be fed into or understood by an external system/consumer. Logical Decoding to the rescue! “Logical decoding is the process of extracting all persistent changes to a database's tables into a coherent, easy to understand format which can be interpreted without detailed knowledge of the database's internal state.” Using logical decoding, replication solutions and auditing can be achieved much easily.
wal_level Alanı
Bu alan minimal, logical veya replica değerini alabiliyor. Debezium için logical olması gerekir
Örnek
Bu alanın değerini görmek için SQL ile şöyle yaparız
> show WAL_LEVEL
Örnek
Bu alanın değerini görmek için şöyle yaparız.
$ grep wal_level /var/lib/pgsql/10/data/postgresql.confwal_level = logical
1. replica
Örnek
Değiştirmek için şöyle yaparız. Postgres'i tekrar başlatmak gerekir.
psql> alter system set wal_level to 'replica';
2. logical
Debezium için gerekir
Şöyle yaparız
wal_level = logical # default value is `replica` max_replication_slots = 1 # good enough for a sample project max_wal_senders = 1 # default is 10
Açıklaması şöyle. Yani Wal logları artık bir eklentiye geçilmeye başlanır. Çeşitli eklentiler var.
Once we have the configuration up and running, record changes are passed to the Output Plugin which does the key step of transformation from the WAL format to the format specified in the plugin (eg. JSON). These changes are made available on the replication slot(s) and consumer applications can receive the stream of updates as and when those occur.wal_writer_delay Alanı
Some output plugins and consumer apps out there:
- wal2json Output Plugin that converts WAL output to JSON objects [Open Source]
- pg_recvlogical Postgres app that can consume update stream [Out-of-the-box with Postgres]
- decoderbufs Output Plugin that delivers data as protobuf [Open Source, Used in Debezium]
Açıklaması şöyle
wal_writer_delay: The Write-Ahead Logging (WAL) process in PostgreSQL involves writing changes to a transaction log before committing them to the main database. The `wal_writer_delay` parameter introduces a pause between WAL flushes, affecting the frequency of writes to the transaction log.