uuhnaut69

postgres-cdc-kafka-connect-es-setup-guide

A tutorial setup ETL Postgres CDC -> Kafka Connect -> ES

Topology

                   +-------------+
                   |             |
                   |   Postgres  |
 | |

uuhnaut69

---
version: '2'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:5.4.1
    hostname: zookeeper
    container_name: zookeeper
    ports:
      - "2181:2181"

uuhnaut69

version: "3.7"

services:
  mysql:
    container_name: mysql
    image: mysql:5.7
    command: ["mysqld", "--log-bin=mysql-bin", "--server-id=223344", "--binlog_format=row", "--expire_logs_days=1"]
    environment:
 MYSQL_ROOT_PASSWORD: root1234

uuhnaut69

I've been working with Apache Kafka for over 7 years. I inevitably find myself doing the same set of activities while I'm developing or working with someone else's system. Here's a set of Kafka productivity hacks for doing a few things way faster than you're probably doing them now. 🔥

• Show me all my Kafka topics and their partitions, replicas, and consumers
• Show me the contents of a topic
• Create a Kafka topic
• Produce messages to a Kafka topic
• Validate the schema of messages before producing to a topic
• Do all of this at a distance

Get the tools

uuhnaut69

postgres-roaring-bitmap-extension-installation-note

A note about installtion roaring bitmap extension for Postgres. This tutorial using for Postgres App

Installation

• Install Postgres App (ver 10, 11, 12)

• Go to directory Postgres App location (The url maybe difference depend on your installation by homebrew or using Postgres App)

uuhnaut69

Get Started for Mac OS

Install Vagrant

Download & install vagrant here.

Start Vagrant

mkdir example && cd example 
vagrant init centos/7 

uuhnaut69

Get Started

Master configuration

Setup Ubuntu Bionic 18.04

vagrant init ubuntu/bionic64

uuhnaut69

numnodes=2
baseip="192.168.10"

#global script
$global = <<SCRIPT

#check for private key for vm-vm comm
[ -f /vagrant/id_rsa ] || {
  ssh-keygen -t rsa -f /vagrant/id_rsa -q -N ''
}

uuhnaut69

CREATE TABLE large_test (num1 bigint, num2 double precision, num3 double precision);

INSERT INTO large_test (num1, num2, num3)
  SELECT round(random()*10), random(), random()*142
  FROM generate_series(1, 20000000) s(i);

EXPLAIN (analyse, buffers) 
SELECT num1, avg(num3) as num3_avg, sum(num2) as num2_sum 
FROM large_test
GROUP BY num1;

uuhnaut69

Optimizing postgresql table for more than 100K inserts per second

• Create UNLOGGED table. This reduces the amount of data written to persistent storage by up to 2x.
• Set WITH (autovacuum_enabled=false) on the table. This saves CPU time and IO bandwidth on useless vacuuming of the table (since we never DELETE or UPDATE the table).
• Insert rows with COPY FROM STDIN. This is the fastest possible approach to insert rows into table.
• Minimize the number of indexes in the table, since they slow down inserts. Usually an index on time timestamp with time zone is enough.
• Add synchronous_commit = off to postgresql.conf.
• Use table inheritance for fast removal of old data: