README.md

Postgres-Kafka-Elastic

This is a simple sample to get the data from a Postgres database, read the data into Kafka and finally put in Elastick Elastic Search

This is a simple project using docker swarm mode.

Table of contents

1. Configuration
   1. Play With Docker
   2. Create swarm cluster
   3. Get the docker compose file
   4. Create the services
   5. Landoop Portal tool
   6. Kafka JDBC connector
   7. Kafka ElasticSearch Sink
   8. Insert data in postgres
   9. Take a look and fun
2. Todos
3. Links
4. License

Configuration

1. Open a web browser and go to Play With Docker tool:

https://play-with-docker.com

2. Create one instache, however to avoid performance issues we recommend you to create a swarm cluster using the PWD templates (3 Managers and 2 Workers or 5 Managers and no workers).

3. Download the docker-compose file in the new instance created in the above step:

wget https://raw.githubusercontent.com/guedim/postgres-kafka-elastic/master/docker-compose.yml

4. Start the services (Postgres - Kafka - Elastick Elastic Search) in a Swarm Mode:

docker stack deploy --compose-file docker-compose.yml postgres-kafka-es

5. Go to Landoop portal (clic in 3030 port), for example:

http://pwd10-0-7-3-3030.host2.labs.play-with-docker.com/

6. In the Landoop portal, create and set up the Postgres Kafka using the JDBC connector:

name=source-postgres
connector.class=io.confluent.connect.jdbc.JdbcSourceConnector
tasks.max=1
connection.url=jdbc:postgresql://pwd10-0-7-3-5432.host2.labs.play-with-docker.com:5432/postgres?user=postgres&password=postgres
topic.prefix=postgres_
mode=timestamp+incrementing
incrementing.column.name=id
timestamp.column.name=updated_at
value.converter=org.apache.kafka.connect.json.JsonConverter
key.converter=org.apache.kafka.connect.json.JsonConverter

Dont forget to change the connection.url parameter using the host with the 5432 port

7. In the Landoop portal, create and set up the kafka elastic Sink Confluent:

name=ElasticsearchSinkConnector
connector.class=io.confluent.connect.elasticsearch.ElasticsearchSinkConnector
topics=postgres_users
tasks.max=1
connection.url=http://pwd10-0-7-3-9200.host2.labs.play-with-docker.com:9200
type.name=kafka-connect
topic.key.ignore=true
key.ignore=true
value.converter=org.apache.kafka.connect.json.JsonConverter
key.converter=org.apache.kafka.connect.json.JsonConverter
topic.schema.ignore=true

Dont forget to change the connection.url parameter using the host with the 9200 port

8. With a Postgres client, connect to the database using the next credentials:

- User: postgres
- password: postgres
- database: postgres

You can use the next commands to connect in the PWD terminal: docker service ps postgres-kafka-es docker exec -it POSTGRES_CONTAINER_NAME /bin/bash psql -U postgres -d postgres

And create the table users and insert some sample data:

-- Create the table users
CREATE TABLE users
(
    id          SERIAL NOT NULL,
    name        VARCHAR(100) NOT NULL,
    age         INTEGER,
    updated_at  TIMESTAMP NOT NULL DEFAULT NOW(),
    PRIMARY KEY(id)
);

-- Insert some data:
INSERT INTO users (name, age) VALUES ('john', 26);
INSERT INTO users (name, age) VALUES ('jane', 24);
INSERT INTO users (name, age) VALUES ('julia', 25);
INSERT INTO users (name, age) VALUES ('jamie', 22);
INSERT INTO users (name, age) VALUES ('jenny', 27);

9. Finally, you can query the Postgres data in the kafka topic or in the ElasticSearch Tool, just click in the port 9200 and go to _plugin/dejavu (dont forget to use the postgres_users index):

• In the Kafka Topic

• In the dejavu ElasticSearch plugin:

http://192.168.99.100:9200/_plugin/dejavu

Todos

• How to create the sql data
• Create all the data and configuration automatically

Links

• https://pgxn.org/dist/jsoncdc/0.0.6/
• https://github.com/snaga/xlogdump/tree/master/doc
• https://www.postgresql.org/docs/9.3/static/pgxlogdump.html

License

---

MIT