Connector with postgresql not connect

Elastic Search
,
1

I'm having a problem with the postgresql connector, it's the first time I've tried to use it. I installed the entire elk suite in a container, I uploaded elastic, kibana, apm-server and enterprise-search, all properly installed and working.

I'm using version 8.10 of ELK all in Docker as shown in the image and the 8.10.0.0-SNAPSHOT connector from connectors-python which I downloaded from the docker.elastic.co repository

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
baffca2fecfe docker.elastic.co/enterprise-search/elastic-connectors:8.10.0.0-SNAPSHOT "/app/bin/elastic-in…" 21 minutes ago Up 21 minutes lucid_merkle
26bcdddffce4 docker-elk-main_metricbeat "/usr/bin/tini -- /u…" 3 days ago Up 3 days docker-elk-main_metricbeat_1
41f7df6c278a docker-elk-main_enterprise-search "tini -- /usr/local/…" 3 days ago Up 3 days 0.0.0.0:3002->3002/tcp, :::3002->3002/tcp docker-elk-main_enterprise-search_1
8a76515d46eb docker-elk-main_logstash "/usr/local/bin/dock…" 3 days ago Up 3 days 0.0.0.0:5044->5044/tcp, :::5044->5044/tcp, 0.0.0.0:9600->9600/tcp, :::9600->9600/tcp, 0.0.0.0:50000->50000/tcp, :::50000->50000/tcp, 0.0.0.0:50000->50000/udp, :::50000->50000/udp docker-elk-main_logstash_1
b1363f57cc84 docker-elk-main_filebeat "/usr/bin/tini -- /u…" 3 days ago Up 3 days docker-elk-main_filebeat_1
84e3b5180f82 docker-elk-main_kibana "/bin/tini -- /usr/l…" 3 days ago Up 3 days 0.0.0.0:5601->5601/tcp, :::5601->5601/tcp docker-elk-main_kibana_1
706c5ef4b6af docker-elk-main_apm-server "/usr/bin/tini -- /u…" 3 days ago Up 3 days 0.0.0.0:8200->8200/tcp, :::8200->8200/tcp docker-elk-main_apm-server_1
d80d69a1ac78 docker-elk-main_elasticsearch "/bin/tini -- /usr/l…" 3 days ago Up 3 days 0.0.0.0:9200->9200/tcp, :::9200->9200/tcp, 0.0.0.0:9300->9300/tcp, :::9300->9300/tcp docker-elk-main_elasticsearch_1

I am using the following config.yml with the data generated in enterprise-search

service.log_level: DEBUG
connectors:
- connector_id: "tUO63ooBEVngNXI1-kjm"
  service_type: "postgresql"
  api_key: "WTF1ZTdJb0JFVm5nTlhJMVdVRFU6TDBoaEVpbzJRb20wS3FUWE9fMVZLZw=="
elasticsearch:
  host: "ip:9200/"
  api_key: "WTF1ZTdJb0JFVm5nTlhJMVdVRFU6TDBoaEVpbzJRb20wS3FUWE9fMVZLZw=="

I run the following docker command to upload the connector container:
docker run -v /home/irianvillalba/estudo_ar2662/connector-config:/config --network "elastic" --tty --rm docker.elastic.co/enterprise-search/elastic-connectors:8.10.0.0-SNAPSHOT /app/bin/elastic-ingest -c /config/config.yml

And with that, the index status appears in kibana, demonstrating that it is configured

And I also inform all the postgresql database access settings that I use, remembering that I do not use SSL

image
image752×785 33 KB

And in the container log of this connector that I uploaded, the following information appears:
[FMWK][19:51:56][DEBUG] Cleaning up orphaned jobs
[FMWK][19:51:56][DEBUG] No orphaned jobs found, skipping cleaning
[FMWK][19:51:56][DEBUG] Start cleaning up idle jobs...
[FMWK][19:51:56][DEBUG] No idle jobs found. Skipping...
[FMWK][19:51:57][DEBUG] Polling every 30 seconds for Job Execution
[FMWK][19:51:57][DEBUG] Polling every 30 seconds for Job Scheduling
[FMWK][19:51:57][DEBUG] [Connector id: tUO63ooBEVngNXI1-kjm, index name: search-testebanco] Sending heartbeat
[FMWK][19:51:57][DEBUG] [Connector id: tUO63ooBEVngNXI1-kjm, index name: search-testebanco] Status is Status.CONFIGURED
[FMWK][19:51:57][DEBUG] [Connector id: tUO63ooBEVngNXI1-kjm, index name: search-testebanco] Filtering is in state valid, skipping...
[FMWK][19:51:57][DEBUG] [Connector id: tUO63ooBEVngNXI1-kjm, index name: search-testebanco] 'full' sync scheduling is disabled

But when clicking the sync > sync full content button, nothing happens, no different information appears, it shows the following message "succesfully scheduled a sync, waiting for a connector to pick it up"

In kibana, this information appears about the syncs I scheduled and asked to run

And nothing happens, there is no connection with postgresql, the container does not execute the scheduled sync, the container just deletes the orphaned sync that appears in the index, so if anyone had the same difficulty or knows where I am going wrong I would be grateful

2

Hi @irianvillalba ,

Sorry you're having issues getting this set up. Can you try tailing the logs for the connector for a full 60 seconds after you click the full sync button? The logs that you shared there look fine, but don't seem to have yet received the signal that you've tried to run a sync. However, it does see that the connector is in the CONFIGURED state, which means that the connector can talk to Elasticsearch.

Without seeing more logs, the one potential issue I see in your screenshot is that you've configured your host to be 10.20.132.17. That looks like a local network IP. However, you're also running with --network "elastic" which I bet does not include that IP.

3

Hi @Sean_Story

This IP 10.20.132.17 is from a VM where the postgres database is running on port 5432, when it enters the connector container that is on the "elastic" network and I telnet to the postgresql IP on port 5432 it stays connected, What's strange to me is that I do the 'full' sync, it should at least show the sync record I did and show that there was some failure in the connection with the bank and nothing appears, I did a test using the elastic cloud and The synchronization error appears when I put some incorrect information in the stack I have installed, it appears like this here

image
image1224×521 35.9 KB

shows "ingest incomplete" and "orphaned sync" what I would like to know is if there is somewhere I can see the record of this sync that I requested because it doesn't even appear when I enter the index to see if there was any connection error with the bank or some error log

4

Below is the image where no sync failure appears even though they are requested, it is as if they were not even registered

image
image1248×628 40.6 KB

5

in the index .elastic-connectors-sync-jobs-v1 this document is created, which is then automatically deleted

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": ".elastic-connectors-sync-jobs-v1",
        "_id": "DWOJ8YoBEVngNXI13MJ7",
        "_score": 1,
        "_source": {
          "cancelation_requested_at": null,
          "canceled_at": null,
          "completed_at": null,
          "connector": {
            "configuration": {
              "password": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Password",
                "sensitive": true,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "qwe123",
                "order": 4,
                "ui_restrictions": []
              },
              "database": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Database",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "comunicacao",
                "order": 5,
                "ui_restrictions": []
              },
              "tables": {
                "depends_on": [],
                "display": "textarea",
                "tooltip": "This configurable field is ignored when Advanced Sync Rules are used.",
                "default_value": null,
                "label": "Comma-separated list of tables",
                "sensitive": false,
                "type": "list",
                "required": true,
                "options": [],
                "validations": [],
                "value": "tb_carater",
                "order": 7,
                "ui_restrictions": []
              },
              "port": {
                "depends_on": [],
                "display": "numeric",
                "tooltip": null,
                "default_value": null,
                "label": "Port",
                "sensitive": false,
                "type": "int",
                "required": true,
                "options": [],
                "validations": [],
                "value": 30000,
                "order": 2,
                "ui_restrictions": []
              },
              "ssl_ca": {
                "depends_on": [
                  {
                    "field": "ssl_enabled",
                    "value": true
                  }
                ],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "SSL certificate",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "",
                "order": 11,
                "ui_restrictions": []
              },
              "ssl_enabled": {
                "depends_on": [],
                "display": "toggle",
                "tooltip": null,
                "default_value": null,
                "label": "Enable SSL verification",
                "sensitive": false,
                "type": "bool",
                "required": true,
                "options": [],
                "validations": [],
                "value": false,
                "order": 10,
                "ui_restrictions": []
              },
              "retry_count": {
                "depends_on": [],
                "display": "numeric",
                "tooltip": null,
                "default_value": 3,
                "label": "Retries per request",
                "sensitive": false,
                "type": "int",
                "required": false,
                "options": [],
                "validations": [],
                "value": 3,
                "order": 9,
                "ui_restrictions": [
                  "advanced"
                ]
              },
              "host": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Host",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "192.168.52.10",
                "order": 1,
                "ui_restrictions": []
              },
              "fetch_size": {
                "depends_on": [],
                "display": "numeric",
                "tooltip": null,
                "default_value": 50,
                "label": "Rows fetched per request",
                "sensitive": false,
                "type": "int",
                "required": false,
                "options": [],
                "validations": [],
                "value": 50,
                "order": 8,
                "ui_restrictions": [
                  "advanced"
                ]
              },
              "username": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Username",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "caravelas",
                "order": 3,
                "ui_restrictions": []
              },
              "direct_connection": {
                "depends_on": [],
                "display": "toggle",
                "tooltip": null,
                "default_value": null,
                "label": "Direct connection",
                "sensitive": false,
                "type": "bool",
                "required": true,
                "options": [],
                "validations": [],
                "value": true,
                "order": 6,
                "ui_restrictions": []
              },
              "collection": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Collection",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "comunicacao",
                "order": 5,
                "ui_restrictions": []
              },
              "user": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Username",
                "sensitive": false,
                "type": "str",
                "required": false,
                "options": [],
                "validations": [],
                "value": "caravelas",
                "order": 2,
                "ui_restrictions": []
              },
              "schema": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Schema",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "comunicacao",
                "order": 6,
                "ui_restrictions": []
              }
            },
            "filtering": {
              "advanced_snippet": {
                "updated_at": "2023-09-29T22:15:53.074Z",
                "created_at": "2023-09-29T22:15:53.074Z",
                "value": {}
              },
              "rules": [
                {
                  "field": "_",
                  "updated_at": "2023-09-29T22:15:53.074Z",
                  "created_at": "2023-09-29T02:18:30.494Z",
                  "rule": "regex",
                  "id": "DEFAULT",
                  "value": ".*",
                  "order": 0,
                  "policy": "include"
                }
              ],
              "validation": {
                "state": "valid",
                "errors": []
              }
            },
            "id": "tUO63ooBEVngNXI1-kjm",
            "index_name": "search-testebanco",
            "language": "pt-br",
            "pipeline": {
              "extract_binary_content": true,
              "name": "ent-search-generic-ingestion",
              "reduce_whitespace": true,
              "run_ml_inference": false
            },
            "service_type": "postgresql"
          },
          "created_at": "2023-10-02T17:57:38.554Z",
          "deleted_document_count": 0,
          "error": null,
          "indexed_document_count": 0,
          "indexed_document_volume": 0,
          "job_type": "full",
          "last_seen": null,
          "metadata": {},
          "started_at": null,
          "status": "pending",
          "total_document_count": null,
          "trigger_method": "on_demand",
          "worker_hostname": null
        }
      }
    ]
  }
}
6

Is it possible that your Kibana docker image and your Connectors docker image are running on hosts where the timezone is different? I'm wondering if Kibana is creating the record in .elastic-connector-sync-jobs and as soon as the connector sees that record, it decides that its hours or days old and should be cleaned up. That's the only process I'm aware of that deletes records from that index.

I recommend ensuring that all your hosts are using the same time, regardless of their physical location.

7

the timezone of all containers are the same, Note that the field "Ingestion methods incomplete" appears with the value 1 on the screen where I see the indexes. Wouldn't that be the problem, and where could I find out what is incomplete?

image
image1235×534 34.5 KB

8

also follow the elastic-connector-sync-jobs index

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": ".elastic-connectors-sync-jobs-v1",
        "_id": "j2bp8ooBEVngNXI10hsJ",
        "_score": 1,
        "_source": {
          "cancelation_requested_at": null,
          "canceled_at": null,
          "completed_at": null,
          "connector": {
            "configuration": {
              "schema": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Schema",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "comunicacao",
                "order": 6,
                "ui_restrictions": []
              },
              "password": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Password",
                "sensitive": true,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "qwe123",
                "order": 4,
                "ui_restrictions": []
              },
              "database": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Database",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "comunicacao",
                "order": 5,
                "ui_restrictions": []
              },
              "tables": {
                "depends_on": [],
                "display": "textarea",
                "tooltip": null,
                "default_value": null,
                "label": "Comma-separated list of tables",
                "sensitive": false,
                "type": "list",
                "required": true,
                "options": [],
                "validations": [],
                "value": "tb_carater",
                "order": 7,
                "ui_restrictions": []
              },
              "port": {
                "depends_on": [],
                "display": "numeric",
                "tooltip": null,
                "default_value": null,
                "label": "Port",
                "sensitive": false,
                "type": "int",
                "required": true,
                "options": [],
                "validations": [],
                "value": 30000,
                "order": 2,
                "ui_restrictions": []
              },
              "ssl_ca": {
                "depends_on": [
                  {
                    "field": "ssl_enabled",
                    "value": true
                  }
                ],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "SSL certificate",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "",
                "order": 11,
                "ui_restrictions": []
              },
              "ssl_enabled": {
                "depends_on": [],
                "display": "toggle",
                "tooltip": null,
                "default_value": null,
                "label": "Enable SSL verification",
                "sensitive": false,
                "type": "bool",
                "required": true,
                "options": [],
                "validations": [],
                "value": false,
                "order": 10,
                "ui_restrictions": []
              },
              "retry_count": {
                "depends_on": [],
                "display": "numeric",
                "tooltip": null,
                "default_value": 3,
                "label": "Retries per request",
                "sensitive": false,
                "type": "int",
                "required": false,
                "options": [],
                "validations": [],
                "value": "",
                "order": 9,
                "ui_restrictions": [
                  "advanced"
                ]
              },
              "host": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Host",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "192.168.52.10",
                "order": 1,
                "ui_restrictions": []
              },
              "fetch_size": {
                "depends_on": [],
                "display": "numeric",
                "tooltip": null,
                "default_value": 50,
                "label": "Rows fetched per request",
                "sensitive": false,
                "type": "int",
                "required": false,
                "options": [],
                "validations": [],
                "value": "",
                "order": 8,
                "ui_restrictions": [
                  "advanced"
                ]
              },
              "username": {
                "depends_on": [],
                "display": "text",
                "tooltip": null,
                "default_value": null,
                "label": "Username",
                "sensitive": false,
                "type": "str",
                "required": true,
                "options": [],
                "validations": [],
                "value": "caravelas",
                "order": 3,
                "ui_restrictions": []
              }
            },
            "filtering": {
              "advanced_snippet": {
                "created_at": "2023-10-03T00:20:03.466Z",
                "updated_at": "2023-10-03T00:20:03.466Z",
                "value": {}
              },
              "rules": [
                {
                  "created_at": "2023-10-03T00:20:03.466Z",
                  "field": "_",
                  "id": "DEFAULT",
                  "order": 0,
                  "policy": "include",
                  "rule": "regex",
                  "updated_at": "2023-10-03T00:20:03.466Z",
                  "value": ".*"
                }
              ],
              "validation": {
                "errors": [],
                "state": "valid"
              }
            },
            "id": "NGbn8ooBEVngNXI1-RgP",
            "index_name": "search-postgre2",
            "language": null,
            "pipeline": {
              "extract_binary_content": true,
              "name": "ent-search-generic-ingestion",
              "reduce_whitespace": true,
              "run_ml_inference": false
            },
            "service_type": "postgresql"
          },
          "created_at": "2023-10-03T00:22:04.552Z",
          "deleted_document_count": 0,
          "error": null,
          "indexed_document_count": 0,
          "indexed_document_volume": 0,
          "job_type": "full",
          "last_seen": null,
          "metadata": {},
          "started_at": null,
          "status": "pending",
          "total_document_count": null,
          "trigger_method": "on_demand",
          "worker_hostname": null
        }
      }
    ]
  }
}
9

The Incomplete ingest methods counter is just acknowledging that the search-testebanco index is in the Configured state, but has not transitioned to the Connected state.

I'll reiterate that the connectors logs are what would be most helpful to look at right now.

10

Hi @Sean_Story

It really was a matter of time, I made the change here and it worked, thanks for the information

1 Like
11

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.