How to use Change Data Capture (CDC) with Postgres
Tags: database, postgres, sql, tutorial
Published: 2020-02-07 13:09:19


## __The Problem__

I'm always surprised by the number of people who never heard about CDC, seriously. Say, you need to capture every change in a specific table, like an Update, an Insert or a Delete, how do you do that? they say __TRIGGERS!!!__ 

![Alt text of image](https://media.giphy.com/media/LpkBAUDg53FI8xLmg1/giphy.gif)

Ok, let's be honest here, it is not completely wrong, it will do the job of _" capturing a change in a table"_, but be aware that you're going to face some performance issues because using this method, because Triggers are Database Operations that will run before or after a _Data Manipulation Language_ (DML) actions, [here](https://www.essentialsql.com/what-is-a-database-trigger/) you can read more about Triggers. 



## __The Solution - CDC__

Alright, but we want to talk about _Log Based CDC_. Every DML action in a specific table will be saved in a Transactional log file, so we can take advantage of that. [Here](https://www.hvr-software.com/blog/change-data-capture/) a very good article about Log Based CDC. 



## __CDC and Postgres - Hands-on__

Enough talking, I'm also going to show a quick Demo with Postgres. Get your Docker ready!!

Here is my docker-compose.yml file, that's all that you'll need for this tutorial: 
```yaml
version: "3"
services:
  db:
    image: "postgres:11"
    container_name: "my_postgres"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=shop_db
    ports:
      - "5432:5432"
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
    volumes:
      - my_dbdata:/var/lib/postgresql/data
volumes:
  my_dbdata:
```

Copy this file and run __docker-compose up -d__
Then run __docker ps__, you should see something like that
```dos
0c28a37615f4        postgres:11              "docker-entrypoint.s…"   24 hours ago        Up 24 hours         0.0.0.0:5432->5432/tcp                 my_postgres_
```


Great! our Container us up and running, now let's connect to our Postgres database running in our docker. Just run __docker exec -it my_postgres psql -U postgres postgres__

Now, run the following script
```sql
create table employees (id int primary key, name varchar, age int);
```



__Important:__ 
Make sure the __wal_level__ is set to _logical_ and the __max_replication_slots__ is set to at least _1_. To set these values, you will need to be a _superuser_. In our example, it's all good as wal_level was set in our docker-compose.yml, but just in case you're trying to do it in your own Postgres database.
If you want to check the parameters, just run:

```sql
show max_replication_slots;
show wal_level;
```


Alright, We're nearly there. Now, lets create the [Slot](https://www.postgresql.org/docs/9.4/catalog-pg-replication-slots.html), running the following command:
```sql
SELECT * FROM pg_create_logical_replication_slot('slot_test', 'test_decoding');
```

Now, insert something in our _employee_ table that we created before and run:
```sql

Insert into employees (id, name, age) values (1, 'Thiago', '99');

SELECT * FROM pg_logical_slot_peek_changes('slot', NULL, NULL);
--and we should see something like that

0/16CEE88 | 582 | BEGIN 582
0/16CEE88 | 582 | table public.employees: INSERT: id[integer]:1 name[character varying]:'Thiago' age[integer]:99
0/16CEFA0 | 582 | COMMIT 582

--Now lets run it again
SELECT * FROM pg_logical_slot_peek_changes('slot', NULL, NULL);
--We should see the same result... duuhhh!

0/16CEE88 | 582 | BEGIN 582
0/16CEE88 | 582 | table public.employees: INSERT: id[integer]:1 name[character varying]:'Thiago' age[integer]:99
0/16CEFA0 | 582 | COMMIT 582
```



####pg_logical_slot_peek_changes vs pg_logical_slot_get_changes

Not sure if you've noticed but we've run __pg_logical_slot_get_changes__ twice, the reason for that is that you can retrieve the log data also using __pg_logical_slot_get_changes__. Lets take a look at the second one:
```sql
SELECT * FROM pg_logical_slot_get_changes('slot', NULL, NULL);
-- all good, our information is still here
 0/16CEE88 | 582 | BEGIN 582
 0/16CEE88 | 582 | table public.employees: INSERT: id[integer]:1 name[character varying]:'Thiago' age[integer]:99
 0/16CEFA0 | 582 | COMMIT 582

-- Alright, once again
postgres=# SELECT * FROM pg_logical_slot_get_changes('slot', NULL, NULL);
(0 rows)

--Holy shhh#@&.. 
```


Yeah, as you can see, __pg_logical_slot_get_changes__ consumes once the files and then it's gone. It's amazing if you want to keep retrieving the data from the last time since you've checked for changes. It is going to be very useful _<Spoiler Alert!!>_ for the next few posts, not saying much now. 😃


Anyway, I hope it was helpful and thanks for your time! 👋


__references:__
https://www.hvr-software.com/blog/change-data-capture/
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15
https://www.postgresql.org/docs/9.4/catalog-pg-replication-slots.html
https://www.essentialsql.com/what-is-a-database-trigger/