PostgreSQL Logical Replication – Mantıksal Replikasyon

Merhaba, Bugun ki yazımızda PostgreSQL Logical Replication konusuna değineceğiz. Avantajları dezavantajları ve kullanım amaçları nedir başlıklarını işleyeceğiz. Ayrıca konuları örnekler ile destekleyeceğiz.

Logical Replication (Mantıksal Replikasyon)

Mantıksal replikasyon, bir veri tabanından diğerine veri aktarımı sağlayan bir replikasyon yöntemidir. Bu yöntemde, Sorgu sonuçlarına(tablo üzerindeki insert, update, delete) dayalı olarak mantıksal düzeyde aktarım sağlar. Bu aktarımı Wal dosyalarını okuyarak gerçekleştirmektedir. insert, update ve delete ile işlemler gerçekleştiği için primary key ve unique index çok önemlidir. Mantıksal replikasyon, yüksek kullanılabilirlik, veri yedekleme, raporlama, veri analizi ve veri entegrasyonu gibi senaryolarda kullanılabilir.

Avantajları:

1. Esnek Veri Aktarımı: Belirli tabloları veya sütunları 15 versiyonu ile birlikte belirlediğimiz verilerinde aktarımını yapabiliriz.
2. Veri Dönüşümü: Hedef ve kaynak veritabanlarında farklı veri yapıları veya formlarını düzenlemek için uygundur.
3. Yüksek Ölçeklenebilirlik: Mantıksal replikasyon, birden fazla ikincil sunucuya veri aktarımını destekler. Bu, veritabanının ölçeklenebilirliğini artırır ve yük dengelemesine olanak sağlar. Ayrıca kullanıcı Bağımsızlığı olduğundan dolayı role ve yetkilerde esneklik sağlar
4. Version Bağımsızlığı: Mantıksal replikasyon, kaynak ve hedef veritabanları arasında farklı veritabanı versiyonları arasında replikasyon sağlar.

Dezavantajları:

1. Primary-key veya unique index olmayan tablolarda veri ile alakalı düzensizlik görülebilir.
2. Kullanıcı ve roller replikasyon sağlanmaz.
3. MV ve view replikasyon sağlanmaz.
4. Large objeler replikasyon sağlanmaz.

Postgresql Mantıksal(Logical) Replikansyon Kurulum/Yapılandırma

• node1 : pgdb01 Kaynak
• node2 : pgdb02 Hedef

Kaynak DB(pgdb01) yapılandırma ön hazırlık;

wal_level parametresi logical olması gerekiyor. Ayrıca aktarım hızına önemli katkı sağlayan max_wal_senders ve max_replication_slots da elimizde ki kaynağa bağlı olarak değişken göstermek ile birlikte min 1 değeri set edilmelidir. Replication ve tablolarda SELECT yetkili bir kullanıcı oluşturuyoruz.

ALTER SYSTEM SET wal_level TO logical;
ALTER SYSTEM SET max_wal_senders TO 3;
ALTER SYSTEM SET max_replication_slots TO 3;
create user rep_user with password '123' replication in role pg_read_all_data ;

Bu şekilde Mantıksal replikasyon için Kaynak DB de ön hazırlıkları tamamlamış oldu.

Replikasyonun başlatılması;

PGDB01 de publication oluşturulması ve tüm tabloların replikasyon’a dahil edilmesi.

 create publication test_logical for all tables; 
 

PGDB02 de subscription oluşturulması

create subscription test_subcribtion connection 'dbname=postgres host=192.168.232.129 port=5432  user=rep_user password=123' publication test_logical;

NOT: Kaynak tarafta yayın yapılacak tablonun hedef tarafta da olması gerekmektedir.

Yeni tablo oluşturduğumuzda PGDB01 de all tables yaptığımız için herhangi bir şey yapmamıza gerek yoktur. Fakat PGDB02 tarafında subscription ı refresh yapmamız gerekmektedir.

alter subscription test_subcribtion refresh publication ;

Belirli tabloları replikasyon yapmak için;

PGDB01

create publication test_log for table tablo1 ;
alter publication test_log add TABLE tablo2;

PGDB02

create subscription test_subcribtion connection 'dbname=postgres host=192.168.232.129 port=5432  user=rep_user password=123' publication test_log;

alter subscription test_subcribtion refresh publication ;

DROP Subscription/Publication

Replikasyonu durdurmak ve tamamen kaldırmak için

PGDB02

drop subscription test_subcribtion ;

PGDB01

drop publication test_log;

Berlirli bir tablonun replikasyondan çıkartılması

PGDB01

 alter publication test_log drop TABLE tablo1;

Replikasyonun İzlenmesi(Monitoring)

PGDB01 de replikasyonun kontrolu;

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 21701
usesysid         | 16388
usename          | rep_user
application_name | test_subcribtion
client_addr      | 192.168.232.128
client_hostname  |
client_port      | 59690
backend_start    | 2023-05-10 13:19:47.163858+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/15AD250
write_lsn        | 0/15AD250
flush_lsn        | 0/15AD250
replay_lsn       | 0/15AD250
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-05-10 13:31:23.194423+00

PGDB01 de slot kontrolu;

postgres=# select * from pg_replication_slots ;
-[ RECORD 1 ]-------+-----------------
slot_name           | test_subcribtion
plugin              | pgoutput
slot_type           | logical
datoid              | 5
database            | postgres
temporary           | f
active              | t
active_pid          | 21701
xmin                |
catalog_xmin        | 756
restart_lsn         | 0/15AD218
confirmed_flush_lsn | 0/15AD250
wal_status          | reserved
safe_wal_size       |
two_phase           | f

PGDB01 publication ve tabloların kontrolu;

postgres=# select * from pg_publication;
-[ RECORD 1 ]+-------------
oid          | 16406
pubname      | test_logical
pubowner     | 10
puballtables | t
pubinsert    | t
pubupdate    | t
pubdelete    | t
pubtruncate  | t
pubviaroot   | f
-[ RECORD 2 ]+-------------
oid          | 16414
pubname      | test_log
pubowner     | 10
puballtables | f
pubinsert    | t
pubupdate    | t
pubdelete    | t
pubtruncate  | t
pubviaroot   | f

postgres=# select * from pg_publication_tables ;
-[ RECORD 1 ]------------
pubname    | test_logical
schemaname | public
tablename  | test
attnames   | {id,ad}
rowfilter  |
-[ RECORD 2 ]------------
pubname    | test_logical
schemaname | public
tablename  | tablo1
attnames   | {id,soyad}
rowfilter  |
-[ RECORD 3 ]------------
pubname    | test_logical
schemaname | public
tablename  | tablo2
attnames   | {id,adress}
rowfilter  |
-[ RECORD 4 ]------------
pubname    | test_log
schemaname | public
tablename  | tablo1
attnames   | {id,soyad}
rowfilter  |
-[ RECORD 5 ]------------
pubname    | test_log
schemaname | public
tablename  | tablo2
attnames   | {id,adress}
rowfilter  |

PGDB02 Subscription kontrolu

postgres=# select * from pg_subscription;
-[ RECORD 1 ]----+---------------------------------------------------------------------------
oid              | 16407
subdbid          | 5
subskiplsn       | 0/0
subname          | test_subcribtion
subowner         | 10
subenabled       | t
subbinary        | f
substream        | f
subtwophasestate | d
subdisableonerr  | f
subconninfo      | dbname=postgres host=192.168.232.129 port=5432  user=rep_user password=123
subslotname      | test_subcribtion
subsynccommit    | off
subpublications  | {test_log}

NOT; Primary key yada unique index olmayan tablolarda tablo özelinde identity full olarak ayarlanmasında fayda vardır? Primary key olmayan tabloların bulunması için scripti aşağıya bırakıyorum.

select 'alter table ' ||tab.table_schema||'.'||
       tab.table_name || ' replica identity full;'
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name
          and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('pg_catalog', 'information_schema')
      and tco.constraint_name is null

Bu yazımızda logical replikasyon nedir? kurulumu nasıl yapılır ve kullanımı nasıldır konularını işledik. Bundan sonraki yazımızda PostgreSQL işlemini Logical replication ile online olarak nasıl yapılır konusunu işleyeceğiz.

Teşekkürler