EVERY - Aggregate Metodu

Örnek

Şöyle yaparız

SELECT author_id, EVERY( title LIKE '%a')
FROM book
GROUP BY author_id


author_id	every
---		---
1		false
2		true

Analytic Functions / Window Functions - ROWS BETWEEN

Örnek

Soru şöyle

For example, if we want to sum up the revenue from all previous years till this year, we can use this window function!

Şöyle yaparız

SELECT year, SUM (revenue) 
OVER ( 
  ROWS BETWEEN 
    UNBOUNDED PRECEEDING 
    AND 
    CURRENT ROW
) AS running_sum
FROM revenue_table

Debezium Kullanımı İçin Hazırlık

Giriş

1. WAL Seviyesi

var/lib/postgresql/data/postgresql.conf dosyasındaki wal_level alanını değeri logical yapılır

2. Grup/Kullanıcı ve Publication + Slot Yaratma

Açıklaması şöyle

1. To stream changes your user needs the Replication attribute. Without it, you'll hit permission errors. 
2. After updating the parameters and granting the right permissions, the next steps are to create a publication, set up a replication slot, and start streaming changes.

Açıklaması şöyle

A publication defines which tables' changes you're publishing. 

Açıklaması şöyle

 A replication slot ensures changes stick around in the WAL until your consumer reads them.

Replication Slot WAL'in Silinmesini Engeller

Açıklaması şöyle

Write-Ahead Log (WAL) files and Replication Slots.

- The WAL (Write-Ahead Log) file is basically where all database transactions live before they are deleted after acknowledgment.

 - The Replication Slot in simple terms, is like a gatekeeper that gets acknowledgement that a transaction stored on the WAL file has been consumed, it then goes ahead and triggers the deletion for that transaction . 

Normally, Postgres constantly deletes old WAL files to free up space. But when you set up CDC, the Replication Slot takes over that work.

If your replication task fails, those acknowledgments stop. Postgres strictly refuses to delete unacknowledged WAL files to prevent data loss. WAL files stack up and your database storage will get full, this will impact the application using that database.

If you are building CDC pipelines, set up these guardrails:

- Set aggressive alerts on the CDC task: Trigger immediate alerts the moment your replication task fails.

- Enforce max_slot_wal_keep_size (Postgres 13+) to cap WAL storage hoarding.

- Monitor database disk space & wal_status, not just consumer logs.

Regardless of the platform you are using, Debezium, DMS, Airbyte, or Databricks all rely on this core concept.

Örnek

Şöyle yaparız. Burada önce Replication özelliğine sahip replication_role grubu yaratılıyor. Daha sonra bu rol bir kullanıcıya atanıyor 

CREATE ROLE replication_role WITH REPLICATION LOGIN;
CREATE USER replicator WITH PASSWORD 'your-secure-password';
GRANT replication_role TO replicator;
-- or: ALTER USER replicator REPLICATION;

Şöyle yaparız. pg_recvlogical komutu hem publication hem de slot yaratabilir

# Let's create s publication for users table
CREATE PUBLICATION users_pub FOR TABLE users;

# Let's create a slot
pg_recvlogical -h $SERVER_NAME -U replicator -d postgres --slot users_slot 
--create-slot -P wal2json

# start streaming changes and see what they look like:
pg_recvlogical -h $SERVER_NAME -U replicator -d postgres --slot users_slot 
--start -o pretty-print=1 -f -

Örnek

Şöyle yaparız. Burada önce Replication özelliğine sahip cdcuser isimli bir kullanıcı yaratılıyor. Daha sonra replication_group isimli bir grup yaratılıyor. Daha sonra replication_group grubuna yetkiler grant ediliyor

CREATE USER cdcuser WITH PASSWORD 'cdcpassword' REPLICATION LOGIN;

CREATE ROLE replication_group WITH USER foouser, cdcuser;
GRANT CREATE ON DATABASE quant_core TO replication_group;
GRANT USAGE ON SCHEMA foo TO replication_group;
GRANT CREATE ON SCHEMA foo TO replication_group;

CREATE TABLE foo.cdc_heartbeat (
    heartbeat bit
);
ALTER TABLE foo.cdc_heartbeat OWNER TO cdcuser;