Logical replikasyon, veri objelrini ve değişikliklerini replikasyon kimliklerine(replication identity) (genellikle primary key olur) dayalı olarak replikasyon yöntemidir. Bir önceki yazımda detaylı şekilde anlattığım gibi stream replikasyon,  primary sunucudaki her değişiklik WAL üzerinden stream edilip, standby sunucuya uygulanır. Bununla birlikte, stream replikasyon kullanırken bazı şeyler mümkün değildir;

  • Stream replikasyon cluster seviyesindedir, cluster da sadece istediğimiz veritabanları veya veritabanlarındaki istediğimiz objeleri replikasyon yapamayız. Stream replikasyonla sadece komple cluster seviyesinde replikasyon yapabiliriz, tahmin edeleceği gibi bu kısıtın nedeni de cluster seviyesinde sadece bir tane WAL mekanizması olması.
  • Stream replikasyonla iki majör versiyon arasında replikasyon yapılamaz.
  • Standby sunucu read-write olarak kullanılmaz.
  • Farklı platformlar arasında replikasyon yapılamaz.(Linux,Windows vb.)
  • Birden fazla veritabanını aynı yere replikasyon yapılamaz.

PostgreSQL 10 ile stream replikasyonun yukarıdaki sınırlamalarını ele alan ve replikasyonda yeni kullanım alanları yaratan logical replikasyon hayatımıza girdi. Transactional replikasyon olarak da bilinir.

Logical replikasyon, publish ve subscribe modelini takip eder. Publisher(kaynak) sunucu, bir tablodan veya bir grup tablodan yapılan değişikliklerden oluşan bir publication(yayın) oluşturulur. Subscriber sunucu, bir veya daha fazla publication(yayına) abone olabilen bir abonelik oluşturulur. Publication, triggerların belirli olay türleri tarafından nasıl çalıştırıldığına benzer şekilde, ürettikleri değişiklikleri herhangi INSERT, UPDATE, DELETE ve TRUNCATE kombinasyonuyla sınırlamayı seçebilirler. Varsayılan olarak, tüm işlem türleri çoğaltılır.

postgresql logical replication

Update veya delete görecek satırların abone tarafında tanımlanabilmesi için, yayınlanan bir tablonun GÜNCELLEME ve SİLME işlemlerini replike edebilmek için yapılandırılmış bir replica identity olması gerekir. Varsayılan olarak bu primary key’dir, tabi istenirse unique bir sütunda replica identity olarak seçilebilir. Tablonun primary key veya uniqe alanı yoksa bu defada replica identity olarak full seçebiliriz yani tüm satırı replica identity olarak kullanabiliriz, ancak bu  çok verimsizdir. Publisher tarafında “full” dışında bir replica identity ayarlanmışsa, subscriber tarafında aynı veya daha az sütunu içeren bir replica identity de ayarlanmalıdır.Bu arada her publication’ın birden fazla subscriber’ı olabilir. Kaynak ve hedefteki tablolar farklı sütun sayısına veya sırasına sahip olabilir ancak sütun veri tipleri ve sütun adları aynı olmalıdır.

Her subscription, değişiklikleri bir replication slot aracılığıyla alacaktır. Subscription silindiğinde ve yeniden oluşturulduğunda, senkronizasyon bilgileri kaybolur. Bu, verilerin daha sonra yeniden senkronize edilmesi gerektiği anlamına gelir. Şema tanımları kopyalanmaz ve yayınlanan tablolar abone üzerinde bulunmalıdır. Yalnızca normal tablolar çoğaltmanın hedefi olabilir. Yani aşağıdaki objeleri logical replikasyona dahil edemeyiz;

  • Veritabanı şemaları ve DDL komutları replike edilemez, şema pg_dump –schema-only kullanılarak elle kopyalanabilir. Sonraki şema değişikliklerinin manuel olarak senkronize edilmesi gerekir. Bununla birlikte, şemaların her iki tarafta da kesinlikle aynı olmasına gerek olmadığını unutmayın taki bizim subscriber sunucuya gelen tablolarımızla ilgili değişiklik yapmadığımız sürece.
  • Sequence replikasyonu yapılamaz, subscriber read only bir veritabanı olarak kullanılıyorsa, bu genellikle bir sorun teşkil etmemelidir. Bununla birlikte, subscriber veritabanına bir tür geçiş veya yük devretme amaçlanıyorsa, publisher’dan mevcut verileri kopyalayarak (pg_dump kullanarak) veya yeterince yüksek bir değer belirleyerek sequenclerin en son değerlere güncellenmesi gerekecektir.
  • Large nesneler replike edilemez
  • View ve Materialized view replike edilemez
  • Foreing table replike edilemez
  • Partition tablolardaki root bölümler replike edilemez

Şimdi Örnek üzerinden bir logical replikasyon yapılandıralım;
Minimal bir logical replikasyon kurulumu için, değiştirilmesi gereken tek yapılandırma parametresi wal_level parametresidir. wal_level parametresi logical olarak ayarlanmalıdır. Bu, sunucuya binary değişiklikleri mantıksal olanlara dönüştürmek için WAL’de ek bilgi depolaması talimatını verir.

wal_level=logical

Kaynak taraf da test veritabanı,tablo ve veri oluşturuyorum.

create database source_rep;
\c source_rep
create table test_rep(id int primary key, name varchar);
create table test_rep_other(id int primary key, name varchar);
insert into test_rep values(generate_series(1,100),'data'||generate_series(1,100));
insert into test_rep_other  values(generate_series(1,100),'data'||generate_series(1,100));

DDL verileri replike edilmediği için hedefte create cümlelerini çalıştırıyorum, istersek pg_dump tarzı araç kullanabiliriz.

create database target_rep;
\c target_rep
create table test_rep_other(id int primary key, name varchar);
create table test_rep(id int primary key, name varchar);

Replikasyonu sağlayacak kullanıcıya ihtiyacımız olacak. Bu kullanıcı mutlaka REPLICATION yetkisine sahip olmalıdır yada SUPERUSER olmalıdır. ve bu kullanıcının LOGIN yetkisi mutlaka olmalıdır. Yetkiler sadece replication bağlantısının başladığı anda kontrol edilir. Her değişen row ile bu durum yeniden kontrol edilmez.

CREATE ROLE rep_usr WITH REPLICATION LOGIN PASSWORD 'rep_pwd';
GRANT CONNECT ON DATABASE postgres TO rep_usr;
GRANT USAGE ON SCHEMA public TO rep_usr;
GRANT SELECT ON ALL TABLES IN SCHEMA public to rep_usr;

Erişim yetkisi için pg_hba.conf üzerinde konfigure edilmelidir.

echo "host    all     rep_usr 192.168.10.106/32   md5">>/var/lib/pgsql/12/data/pg_hba.conf
postgres=# select pg_reload_conf();

Kaynak veritabanında publication(yayın) oluştururuz.

CREATE PUBLICATION mypub FOR TABLE test_rep, test_rep_other;

Oluşturduğumuz publication ları kontrol edelim;

source_rep=# select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------
 16402 | mypub   |       10 | f            | t         | t         | t         | t
(1 row)
source_rep=# select * from pg_publication_tables ;
 pubname | schemaname |   tablename    
---------+------------+----------------
 mypub   | public     | test_rep
 mypub   | public     | test_rep_other
(2 rows)

Aşağıdaki gibi söz dizimlerini de kullanabiliriz;

CREATE PUBLICATION test_pub1 FOR TABLE test_table1;
CREATE PUBLICATION test_pub2 FOR TABLE test_table1, test_table2;
CREATE PUBLICATION test_pub3 FOR ALL TABLES;
CREATE PUBLICATION test_pub4 FOR TABLE test_table1 WITH (publish = 'insert');

Örneklerden de görüleceği üzere tüm tabloları yayınlamak yerine, yayınlamak için bir dizi tablo da belirleyebiliriz. Sadece bu da değil, bir yayın, yayınladıkları değişiklikleri INSERT, UPDATE, DELETE ve TRUNCATE işlemlerinin herhangi bir kombinasyonuyla sınırlamayı seçebilir. Ancak, GÜNCELLEME ve SİLME işlemlerini çoğaltabilmek için, “yayınlanan” bir tablonun bir REPLICA IDENTITY’ye sahip olması gerekir, böylece değiştirilen satırlar abonede tanımlanabilir. INSERT işlemleri, herhangi bir replica identity’ye bakılmaksızın devam edebilir.
NOT : Bir tablo için REPLICA IDENTITY 4 farklı değer alabilir; DEFAULT, USING INDEX index, FULL, NOTHING varsayılan olarak bu değer DEFAULT’dur ve bu değer primary key kullanılmasını sağlar eğer tablomuzda primary key yoksa unique bir indexi veya full kelimesini kullanarak bütün satırı baz almasını sağlayabiliriz. Burada unutulmaması gerek eğer full kelimesini kullanırsak wal dosyasına çok fazla veri yazılacağı yani sistemin fazladan io yapmasına neden olacak, bunu örnekle açıklamak gerekirse 50 sütunluk bir tabloda veri silerken wal dosyasına “delete from test_table where id=1” yazması var bir de “delete from test_table where name=.. and surname=…….” yazması var.

ALTER TABLE test_table REPLICA IDENTITY USING INDEX test_index;
ALTER TABLE test_table REPLICA IDENTITY FULL;

NOT : Bir yayının oluşturulması, replikasyona başlamaz. Yalnızca gelecekteki aboneler için bir gruplama ve filtreleme mantığı tanımlar.

Oluşturulan publication(yayına) subscription(abonelik) oluşturulur.

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=source_rep host=192.168.10.105 user=rep_usr password=rep_pwd port=5432' PUBLICATION mypub;

Oluşturduğumuz subscription ları kontrol edelim.

target_rep=# select * from pg_subscription;
-[ RECORD 1 ]---+------------------------------------------------------------------------------
oid             | 16404
subdbid         | 16384
subname         | mysub
subowner        | 10
subenabled      | t
subconninfo     | dbname=source_rep host=192.168.10.105 user=rep_usr password=rep_pwd port=5432
subslotname     | mysub
subsynccommit   | off
subpublications | {mypub}
target_rep=# select * from pg_subscription_rel;
-[ RECORD 1 ]-----
srsubid    | 16404
srrelid    | 16393
srsubstate | d
srsublsn   | 
-[ RECORD 2 ]-----
srsubid    | 16404
srrelid    | 16385
srsubstate | d
srsublsn   | 

Replikasyonun durumunu aşağıdaki gibi yayın yapan sunucudan kontrol edilebiliriz. Replikasyon slot üzerinden gerçekleştiği için slotu kontrol ediyoruz.

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 54321
usesysid         | 16401
usename          | rep_usr
application_name | mysub
client_addr      | 192.168.10.106
client_hostname  | 
client_port      | 49594
backend_start    | 2020-10-14 23:44:31.621954+03
backend_xmin     | 
state            | streaming
sent_lsn         | 0/16F2AD8
write_lsn        | 0/16F2AD8
flush_lsn        | 0/16F2AD8
replay_lsn       | 0/16F2AD8
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2020-10-14 23:52:05.985311+03

Aynı şekilde üye sunucudan kontrol edebiliriz.

target_rep=# select * from pg_stat_subscription ;
-[ RECORD 1 ]---------+------------------------------
subid                 | 16404
subname               | mysub
pid                   | 53727
relid                 | 
received_lsn          | 0/16F6638
last_msg_send_time    | 2020-10-14 23:59:21.054668+03
last_msg_receipt_time | 2020-10-14 23:59:21.055182+03
latest_end_lsn        | 0/16F6638
latest_end_time       | 2020-10-14 23:59:21.054668+03

Mustafa Bektaş Tepe
İyi Çalışmalar

https://www.enterprisedb.com/postgres-tutorials/logical-replication-postgresql-explained
https://blog.gunduzdanismanlik.com/
https://www.digitalocean.com/community/tutorials/how-to-set-up-logical-replication-with-postgresql-10-on-ubuntu-18-04

Loading