Zipkin原理学习--Zipkin支持PostgreSQL存储追踪日志

目前Zipkin官方支持Memory、Mysql、Elasticsearch和Cassandra作为追踪日志的存储方式，由于公司业务需要目前使用的PostgreSQL数据库，因此基于MySQL方式实现了Zipkin支持PostgreSQL作为存储媒介。

1、GitHub地址

https://github.com/IAMTJW/zipkin/tree/master/zipkin-storage

2、Zipkin Server启动方式

类似zipkin server启动时支持mysql一样，在zipkin server启动时指定postgresql即可。

在zipkin-server-shared.yml添加如下配置，并制定storage类型为postgresql即可。

postgresql:
      host: ${PG_HOST:localhost}
      port: ${PG_TCP_PORT:3306}
      username: ${PG_USER:}
      password: ${PG_PASS:}
      db: ${PG_DB:zipkin}
      max-active: ${PG_MAX_CONNECTIONS:10}
      use-ssl: ${PG_USE_SSL:false}

3、PostgreSQl初始化脚本

支持PG 9.5版本

CREATE TABLE IF NOT EXISTS zipkin_spans (
  "trace_id_high" BIGINT NOT NULL DEFAULT 0,"trace_id" BIGINT NOT NULL,"id" BIGINT NOT NULL,"name" VARCHAR(255) NOT NULL,"parent_id" BIGINT,"debug" BOOLEAN,"start_ts" BIGINT,"duration" BIGINT,constraint KEY_zipkin_spans_trace_id_high unique("trace_id_high","trace_id","id")
) WITH (OIDS=FALSE);

comment on column zipkin_spans.trace_id_high is 'If non zero,this means the trace uses 128 bit traceIds instead of 64 bit';
comment on column zipkin_spans.start_ts is 'Span.timestamp(): epoch micros used for endTs query and to implement TTL';
comment on column zipkin_spans.duration is 'Span.duration(): micros used for minDuration and maxDuration query';

CREATE INDEX zipkin_spans_index_trace_id_high_trace_id_id ON zipkin_spans ("trace_id_high","id");
CREATE INDEX zipkin_spans_index_trace_id_high_trace_id ON zipkin_spans ("trace_id_high","trace_id");
CREATE INDEX zipkin_spans_index_name ON zipkin_spans ("name");
CREATE INDEX zipkin_spans_index_start_ts ON zipkin_spans ("start_ts");



CREATE TABLE IF NOT EXISTS zipkin_annotations (
  "trace_id_high" BIGINT NOT NULL DEFAULT 0,"span_id" BIGINT NOT NULL,"a_key" VARCHAR(255) NOT NULL,"a_value" bytea,"a_type" INT NOT NULL,"a_timestamp" BIGINT,"endpoint_ipv4" INT,"endpoint_ipv6" bytea,"endpoint_port" SMALLINT,"endpoint_service_name" VARCHAR(255),constraint KEY_zipkin_annotations_trace_id_high unique("trace_id_high","span_id","a_key","a_timestamp")
) WITH (OIDS=FALSE);

comment on column zipkin_annotations.trace_id_high is 'If non zero,this means the trace uses 128 bit traceIds instead of 64 bit';
comment on column zipkin_annotations.trace_id is 'coincides with zipkin_spans.trace_id';
comment on column zipkin_annotations.span_id is 'coincides with zipkin_spans.id';
comment on column zipkin_annotations.a_key is 'BinaryAnnotation.key or Annotation.value if type == -1';
comment on column zipkin_annotations.a_value is 'BinaryAnnotation.value(),which must be smaller than 64KB';
comment on column zipkin_annotations.a_type is 'BinaryAnnotation.type() or -1 if Annotation';
comment on column zipkin_annotations.a_timestamp is 'Used to implement TTL; Annotation.timestamp or zipkin_spans.timestamp';
comment on column zipkin_annotations.endpoint_ipv4 is 'Null when Binary/Annotation.endpoint is null';
comment on column zipkin_annotations.endpoint_ipv6 is 'Null when Binary/Annotation.endpoint is null,or no IPv6 address';
comment on column zipkin_annotations.endpoint_port is 'Null when Binary/Annotation.endpoint is null';
comment on column zipkin_annotations.endpoint_service_name is 'Null when Binary/Annotation.endpoint is null';

CREATE INDEX zipkin_annotations_index_trace_id_high_trace_id_id1 ON zipkin_annotations ("trace_id_high","a_timestamp");
CREATE INDEX zipkin_annotations_index_trace_id_high_trace_id2 ON zipkin_annotations ("trace_id_high","span_id");
CREATE INDEX zipkin_annotations_index_trace_id_high_trace_id ON zipkin_annotations ("trace_id_high","trace_id");
CREATE INDEX zipkin_annotations_index_endpoint_service_name ON zipkin_annotations ("endpoint_service_name");
CREATE INDEX zipkin_annotations_index_a_type ON zipkin_annotations ("a_type");
CREATE INDEX zipkin_annotations_index_a_key ON zipkin_annotations ("a_key");
CREATE INDEX zipkin_annotations_index_trace_id_span_id_a_key ON zipkin_annotations ("trace_id","a_key");


CREATE TABLE IF NOT EXISTS zipkin_dependencies (
  "day" DATE NOT NULL,"parent" VARCHAR(255) NOT NULL,"child" VARCHAR(255) NOT NULL,"call_count" BIGINT,"error_count" BIGINT,constraint KEY_zipkin_dependencies_day unique("day","parent","child")
) WITH (OIDS=FALSE);