I've been knocking my head on this one for days and can't seem to debug what is happening.. I've setup the filebeat module to stream postgres-13 csv logs into ES - everything works great except the time is out of sync (currently 10 hours behind).. was hoping to it is a simple UTC/Timezone issue but can't seem to find the issue and all the timezones are correct.
Any suggestions/guidance would be greatly appreciated as i can't seem to crack this problem..
--The filebeat log snippet
{
"@timestamp": "2021-08-01T12:49:20.417Z",
"@metadata": {
"beat": "filebeat",
"type": "_doc",
"version": "7.13.4",
"pipeline": "filebeat-7.13.4-postgresql-log-pipeline"
},
"fileset": {
"name": "log"
},
"service": {
"type": "postgresql"
},
"ecs": {
"version": "1.9.0"
},
"log": {
"offset": 114988517,
"file": {
"path": "/var/log/postgres/postgresql-Sun.csv"
}
},
"message": "2021-08-01 13:49:20.231 BST,\"fraud\",\"fraud\",570299,\"192.168.111.102:44950\",61069426.8b3bb,941,\"INSERT\",2021-08-01 13:31:34 BST,68/174016,3977209,ERROR,23505,\"duplicate key value violates unique constraint \"\"sourcedcompanieshouseofficerappointment_idx\"\"\",\"Key (sourced_companies_house_company_officer_appointment_entity_id)=(10639050) already exists.\",,,,,\"insert into sourced_companies_house_company_officer_appointment_address (version, address_line1, address_line2, care_of, country, date_ceased, date_confirmed, locality, marked_as_undeliverable, po_box, postal_code, premises, region, sourced_companies_house_company_officer_appointment_entity_id, entity_id) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)\",,,\"PostgreSQL JDBC Driver\",\"client backend\"",
"host": {
"mac": [
"00:15:5d:6f:d7:07"
],
"name": "core.server",
"hostname": "core.server",
"architecture": "x86_64",
"os": {
"name": "CentOS Linux",
"kernel": "4.18.0-305.10.2.el8_4.x86_64",
"type": "linux",
"platform": "centos",
"version": "8",
"family": "redhat"
},
"id": "86320bc600094cc8b450cbb158ed6e11",
"containerized": false,
"ip": [
"192.168.111.100",
"fe80::9a6f:eb68:6919:1f25"
]
},
"agent": {
"version": "7.13.4",
"hostname": "core.server",
"ephemeral_id": "9d69e005-02bd-4bf7-bcf4-30bf7e94d4f8",
"id": "1725d785-4c85-4179-8eb2-162e64887342",
"name": "core.server",
"type": "filebeat"
},
"input": {
"type": "log"
},
"event": {
"module": "postgresql",
"dataset": "postgresql.log"
}
}
--- The ES document for this log entry
{
"_index": "filebeat-7.13.4-2021.07.30-000001",
"_type": "_doc",
"_id": "1GvCAXsBaNnAt8IX_nsR",
"_version": 1,
"_score": null,
"fields": {
"event.category": [
"database"
],
"host.os.name.text": [
"CentOS Linux"
],
"host.hostname": [
"core.server"
],
"process.pid": [
570299
],
"host.mac": [
"00:15:5d:6f:d7:07"
],
"service.type": [
"postgresql"
],
"host.os.version": [
"8"
],
"postgresql.log.sql_state_code": [
"23505"
],
"host.os.name": [
"CentOS Linux"
],
"log.level": [
"ERROR"
],
"agent.name": [
"core.server"
],
"host.name": [
"core.server"
],
"event.kind": [
"event"
],
"postgresql.log.query": [
"insert into sourced_companies_house_company_officer_appointment_address (version, address_line1, address_line2, care_of, country, date_ceased, date_confirmed, locality, marked_as_undeliverable, po_box, postal_code, premises, region, sourced_companies_house_company_officer_appointment_entity_id, entity_id) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)"
],
"host.os.type": [
"linux"
],
"fileset.name": [
"log"
],
"input.type": [
"log"
],
"log.offset": [
114988517
],
"agent.hostname": [
"core.server"
],
"related.user": [
"fraud"
],
"postgresql.log.backend_type": [
"client backend"
],
"host.architecture": [
"x86_64"
],
"postgresql.log.detail": [
"Key (sourced_companies_house_company_officer_appointment_entity_id)=(10639050) already exists."
],
"agent.id": [
"1725d785-4c85-4179-8eb2-162e64887342"
],
"host.containerized": [
false
],
"ecs.version": [
"1.9.0"
],
"agent.version": [
"7.13.4"
],
"host.os.family": [
"redhat"
],
"postgresql.log.session_start_time": [
"2021-08-01T02:31:34.000Z"
],
"user.name": [
"fraud"
],
"postgresql.log.virtual_transaction_id": [
"68/174016"
],
"host.ip": [
"192.168.111.100",
"fe80::9a6f:eb68:6919:1f25"
],
"agent.type": [
"filebeat"
],
"postgresql.log.transaction_id": [
3977209
],
"event.module": [
"postgresql"
],
"postgresql.log.timestamp": [
"2021-08-01 13:49:20.231 BST"
],
"postgresql.log.session_id": [
"61069426.8b3bb"
],
"host.os.kernel": [
"4.18.0-305.10.2.el8_4.x86_64"
],
"host.id": [
"86320bc600094cc8b450cbb158ed6e11"
],
"event.timezone": [
"BST"
],
"postgresql.log.client_addr": [
"192.168.111.102"
],
"postgresql.log.database": [
"fraud"
],
"postgresql.log.error.code": [
"23505"
],
"message": [
"duplicate key value violates unique constraint \"sourcedcompanieshouseofficerappointment_idx\""
],
"postgresql.log.command_tag": [
"INSERT"
],
"event.ingested": [
"2021-08-01T12:49:21.424Z"
],
"@timestamp": [
"2021-08-01T02:49:20.231Z"
],
"postgresql.log.application_name": [
"PostgreSQL JDBC Driver"
],
"host.os.platform": [
"centos"
],
"event.type": [
"error"
],
"log.file.path": [
"/var/log/postgres/postgresql-Sun.csv"
],
"agent.ephemeral_id": [
"9d69e005-02bd-4bf7-bcf4-30bf7e94d4f8"
],
"postgresql.log.client_port": [
"44950"
],
"event.dataset": [
"postgresql.log"
],
"user.name.text": [
"fraud"
]
},
"highlight": {
"agent.ephemeral_id": [
"@kibana-highlighted-field@9d69e005-02bd-4bf7-bcf4-30bf7e94d4f8@/kibana-highlighted-field@"
]
},
"sort": [
1627786160231
]
}