Forem: Thiago

GOCDC and Postgres

Thiago — Tue, 18 Feb 2020 09:25:30 +0000

Change Data Capture (CDC) and Postgres

Recently, I've written this post here, where I show you guys how to setup Change Data Capture (CDC) in Postgres. So, if you don't know how to setup CDC in Postgres, I totally recommend you guys to take a quick look at that tutorial first.

GOCDC

GoCdc it's an Opensource API for data streaming, developed (I mean, in development) in Golang that I would like to share with you guys. In short, the concept behind it is similar to Debezium, but in Golang, which in my opinion, it's easier for anyone to hack and adapt the application according to your needs. The focus of GoCdc is on simplicity. Simplicity to setup, to modify, etc.

Hands-on 🛠️

Required:
Docker -> https://www.docker.com/get-started

1 - Creating the Project

First of all, create a docker-compose.yml file in the directory of your preference.

version: "3"
services:
  gocdc:
    image: "133thiago/gocdc:latest"
    ports:
      - "8000:8000"

  db:
    image: "postgres:11"
    container_name: "my_postgres"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=example_db
    ports:
      - "5432:5432"
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
    volumes:
      - my_dbdata:/var/lib/postgresql/data

  zookeeper:
    image: wurstmeister/zookeeper
    ports:
      - "2181:2181"

  kafka:
    image: wurstmeister/kafka
    ports:
      - "9092:9092"
    environment:
      KAFKA_ADVERTISED_HOST_NAME: localhost
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock

volumes:
  my_dbdata:

2 - Running in Docker

Now you're going to run the docker-compose up -d, and then run docker ps. Then you should see the containers created.

3 - Postgres Setup

As I said at the beginning of this post, this step is going to be this tutorial in here -> (How to use Change Data Capture (CDC) with Postgres)[https://dev.to/thiagosilvaf/how-to-use-change-database-capture-cdc-in-postgres-37b8].

IMPORTANT: Just make sure you create either create your database as example_db, as it is in the value of POSTGRES_DB in our docker-compose.yml, or you change the value in our docker-compose.yml to the database that you created. It is up to you.

4 - Kafka config:

Now, it's time to create our Kafka Topic, which is going to be the topic that our connector is going to send the database changes.
First, run docker ps again and get the CONTAINER ID of your Kafka container, it will be something like this 4bed6164a8e9

CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS

4bed6164a8e9        wurstmeister/kafka             "start-kafka.sh"         5 hours ago         Up About an hour    0.0.0.0:9092->9092/tcp

Then, run the following command:

docker exec -it 4bed6164a8e9 "bash" # Remember to replace the Container ID with yours

Finally, let's create the topic, I'm going to call it test, but it is up to you. just bear in mind, you will need the topic later!

bash-4.4# Kafka-topics.sh --create --zookeeper zookeeper:2181 --replication-factor 1 --partitions 1 --topic test

5 - Tinder match: Postgres ❤️ Kafka

GOCDC provides a REST Interface for creating the connection between the Database and Kafka. If you look again to our docker-compose.yml file, GOCDC is using the Port 8000. So let's do a POST to our localhost:8000/connectors/Postgres

curl --location --request POST 'http://localhost:8000/connectors/postgres' \
--header 'Content-Type: application/JSON' \
--data-raw '{
    "connector_name":"Conn PG Test",
    "db_host": "localhost",
    "db_port": 5432,
    "db_user": "postgres",
    "db_pass": "postgres",
    "db_name": "example_db",
    "db_slot": "slot",
    "kafka_brokers": [ "localhost:9092" ],
    "kafka_topic": "test"
        "lookup_interval": 5000
}
'

Let's dive into the JSON object sent in this request:
connector_name: No big deal here, just an Identifier. It is unique though, so you can use it to Edit via PUT request.

db_host: The IP where your Postgres is running. In our example, it is localhost.

db_port: The Port to access our Postgres database.

db_user AND db_pass: User and Password of our Postgres database.

db_name: The name of the database

db_slot: The name of the Replication Slot.

kafka_brokers: An array with our Kafka brokers. Well, we're running only one in our example, but you can set multiple.

kafka_topic: Here is the Topic that we created in Step 4.

lookup_interval: Here you tell the connector how often it should execute a CDC lookup. In our example, every 5 seconds (yes, the parameter value is in milliseconds).

6 - Kafka Consumer:

So, we now have our Postgres database up and running, our Kafka "cluster" also up and running and the Connector created!
Assuming that you have the Database and a Table created in your Postgres (Step 3), Connect to your Kafka once again (Step 4) and run the following command:

bash-4.4# Kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic test

And now, you will be able to see every Insert, Update and Delete from your Database, being sent to your Kafka consumer.

I hope you enjoyed the Tutorial, if you've got stuck at some step, please leave a comment and do my best to help! 🖖

Computational Offloading in Mobile Cloud Computing

Thiago — Mon, 17 Feb 2020 22:05:27 +0000

Back in 2014, in this paper, we've got to see the definition of Mobile Cloud Computing (MCC). It says that MCC is the combination of Cloud Computing and Mobile Computing 🤯. The concept of Cloud Computing sounds like the most obvious solution to the hardware limitation of Mobile devices. I know what you're looking at your newest iPhone or Xiaomi and thinking that limitation might not be the right word, in fact, Smartphones nowadays are supercomputers in our pockets, but think for a moment:

What if the battery of your phone dies RIGHT NOW? It is a "hardware limitation".
What if you are on a bus, just passing through a tunnel, how flaky is your mobile connection going to be?

I could keep talking about "limitations" of our Smartphones but if you think, those two scenarios that I mentioned, would not be a problem for an old fashion Desktop for example, as it is always plugged in an electricity cable, always in a cable internet or even your house wifi, pretty good and stable.
But mobile devices are not only Smartphones, right? It can be anything, such as your Smartwatch or your Nintendo Switch - That's why I always carry a Power Bank 16000mah with me 🧞‍♂️ -. So as you can see, MCC Computational Offloading is still a very relevant topic in 2020.

Context-Awareness

Context-Awareness is one of the most important topics in MCC. It has a significant impact on the computational offloading due to the uncertainty whether this would be beneficial or not. Context Awareness in MCC can be classified into two approaches: Context-Aware application partitioning and Context-Aware computational offloading.

Context-Aware application partitioning

If you already know what are the resource intensive features of your application and you will run it in the cloud, as simple as that. mCloud proposes an architecture for dynamic offloading, this framework composed of several components for decision making and context-monitor, was developed in Java using reﬂection for selecting the essential piece of code to be offloaded to the cloud, it basically wraps the source code and sends to the cloud, where it will be compiled and executed. The advantage of mCloud is that you will be able to offload smaller "parts" of your application but as you can imagine, just by doing that, sounds like heavy computing itself. Also, this approach can not be the best decision in cases where the mobile application uses hardware features like sensors or GPS.

Context-Aware computation offloading

The Context-Aware methodology prioritises energy efficiency, application execution and performance. Here, you won't have specific functionality that will always be offloaded to the Cloud, the decision to offload or not, will be based in some aspects:

Objective Awareness

The computational offloading has three principles that were already mentioned in this research: performance enhancement, energy efficiency and execution support. Usually, the application will need to focus on one of those principles according to the respective scenario. The mCloud architecture, for example, prioritises performance and energy efficiency.

Performance Awareness

In the majority of the cases, the computational offloading focus on performance enhancement, but sometimes, the computational offloading takes more time to offload the task than running it locally, so in these cases, the offloading is not executed, thus not to compromise the performance of the application.

Energy Awareness

Every time that a mobile device runs an intensive computational task locally, a significant amount of energy is consumed, it is a problem due to the energy limitation of the devices.
Many authors use energy awareness as the main factor to decide when to offload or not the computational task to the cloud, however, to offload the task itself, the mobile also consumes a considerable amount of energy during the request and result of this process. The mCloud framework takes into account the consumption of energy during the decision-making phase; an algorithm creates an estimative of the amount of energy that the particular task will spend. This information affects the final decision to offload or not to the cloud.

Resource Awareness

Resource Awareness requires the application, to be aware of the mobile device, it means that if the device does not have sufficient resources to run a computational process, plus the awareness of the cloud environment, so when the application has an intensive computational task to run. The cloud awareness verifies the conditions to offload; these two factors will be checked during the phase of the offloading decision-making.
The aim of this research is performance enhancement as the major factor to decide
if the offloading will be beneficial or not for each scenario. Also, context awareness is another important element during the decision-making phase. So it is possible to say that the experiment done for this research is a combination of performance awareness and resource awareness.

Conclusion

This is a summary of a topic that I believe that it is still relevant, mainly if we look at the mobile games that became such a big market and so many other examples.

Thanks for reading! 😁

References:
https://ieeexplore.ieee.org/document/6553297
https://ieeexplore.ieee.org/document/7158968
https://www.researchgate.net/publication/287971934_mCloud_A_Context-Aware_Offloading_Framework_for_Heterogeneous_Mobile_Cloud

How to use Change Data Capture (CDC) with Postgres

Thiago — Fri, 07 Feb 2020 13:09:19 +0000

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!!!

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 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 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:

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

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

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:

show max_replication_slots;
show wal_level;

Alright, We're nearly there. Now, lets create the Slot, running the following command:

SELECT * FROM pg_create_logical_replication_slot('slot_test', 'test_decoding');

Now, insert something in our employee table that we created before and run:


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:

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 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/