---

mailarchive-search/
├── docker-compose.yml
├── elasticsearch-data/
└── logstash/
    ├── Dockerfile
    ├── postgresql-42.7.13.jar
    ├── pipeline/
    │   └── mailarchive.conf
    ├── config/
    │   └── logstash.yml
    ├── templates/
    │   └── mailarchive.json
    └── data/

services:

  elasticsearch:
    container_name: mailarchive-elasticsearch
    hostname: mailarchive-elasticsearch
    image: docker.elastic.co/elasticsearch/elasticsearch:9.4.4
    restart: unless-stopped

    environment:
      - discovery.type=single-node
      - xpack.security.enabled=false
      - ES_JAVA_OPTS=-Xms16g -Xmx16g

    volumes:
      - ./elasticsearch-data:/usr/share/elasticsearch/data

    expose:
      - "9200"

    networks:
      - MailArchive


  logstash:
    container_name: mailarchive-logstash
    hostname: mailarchive-logstash
    build:
      context: ./logstash

    restart: unless-stopped

    environment:
      - LS_JAVA_OPTS=-Xms2g -Xmx4g
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}

    volumes:
      - ./logstash/pipeline:/usr/share/logstash/pipeline:ro
      - ./logstash/config:/usr/share/logstash/config:ro
      - ./logstash/templates:/usr/share/logstash/templates:ro
      - ./logstash/data:/usr/share/logstash/data

    depends_on:
      - elasticsearch

    networks:
      - MailArchive


networks:
  MailArchive:
    driver: bridge


FROM docker.elastic.co/logstash/logstash:9.4.4
RUN bin/logstash-plugin install logstash-input-jdbc
COPY postgresql-42.7.13.jar /opt/jdbc/postgresql.jar

logstash/config/logstash.yml
http.host: "0.0.0.0"
path.data: /usr/share/logstash/data
pipeline.ordered: true

logstash/pipeline/mailarchive.conf
input {
    jdbc {
        id => "mailarchive_postgresql"

        jdbc_driver_library => "/opt/jdbc/postgresql.jar"
        jdbc_driver_class   => "org.postgresql.Driver"

        jdbc_connection_string => "jdbc:postgresql://YOUR_POSTGRES_HOST:5432/infradev_mail_prod"
        jdbc_user              => "svc_local_mailarchive_prod"
        jdbc_password          => "${POSTGRES_PASSWORD}"

        schedule => "* * * * *"

        use_column_value     => true
        tracking_column      => "updated_at"
        tracking_column_type => "timestamp"

        last_run_metadata_path => "/usr/share/logstash/data/.mailarchive_last_run"

        jdbc_fetch_size => 5000

        statement => "
            SELECT
                id,
                message_id,
                subject,
                sent_timestamp,
                recipients,
                header_from,
                header_to,
                envelope_from,
                updated_at
            FROM public.mail_archive_email
            WHERE updated_at >= :sql_last_value
            ORDER BY updated_at, id
        "
    }
}

output {
    elasticsearch {
        hosts => ["http://elasticsearch:9200"]

        index       => "mailarchive"
        document_id => "%{id}"

        template          => "/usr/share/logstash/templates/mailarchive.json"
        template_name     => "mailarchive"
        template_overwrite => true
    }

    stdout {
        codec => dots
    }
}

logstash/templates/mailarchive.json
{
  "index_patterns": [
    "mailarchive"
  ],
  "priority": 100,
  "template": {
    "settings": {
      "number_of_shards": 3,
      "number_of_replicas": 0
    },
    "mappings": {
      "properties": {
        "id": {
          "type": "long"
        },
        "message_id": {
          "type": "keyword"
        },
        "subject": {
          "type": "text",
          "fields": {
            "keyword": {
              "type": "keyword"
            }
          }
        },
        "recipients": {
          "type": "keyword"
        },
        "header_from": {
          "type": "keyword"
        },
        "header_to": {
          "type": "keyword"
        },
        "envelope_from": {
          "type": "keyword"
        },
        "sent_timestamp": {
          "type": "date"
        },
        "updated_at": {
          "type": "date"
        }
      }
    }
  }
}