Pgsync process never stops without --daemon flag.

[zPabloMorales]

PGSync version: 3.0.0

Postgres version: PostgreSQL 15.3 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit

Elasticsearch/OpenSearch version: ES 8.11.1

Redis version: 7.2

Python version: 3.11

Problem Description: The first time that I ran the bootstrap and pgsync work fine, the db is sync in 0:11:56.014415 (716.01 sec), but then when I ran pgsync again, never finish of run. I am running pgsync without --daemon flag because when I use --daemon flag, redis always has pending documents and also never finish of sync. The size of the database is around 233MB. The schema is very simple.

• public.table_a |- public.table_b | - public.table_c | - public.table_d | - public.table_e | - public.table_f
  • public.table_g

I am running pgsync using Docker & docker-compose.

I sent you a discord invitation, my user is pablomorales88

.env file:

POLL_TIMEOUT=0.05
REPLICATION_SLOT_CLEANUP_INTERVAL=60
ELASTICSEARCH_THREAD_COUNT=1
ELASTICSEARCH_QUEUE_SIZE=1
ELASTICSEARCH_TIMEOUT=1200
ELASTICSEARCH_CHUNK_SIZE=2000
ELASTICSEARCH_MAX_CHUNK_BYTES=104857600
ELASTICSEARCH_STREAMING_BULK=true
ELASTICSEARCH_MAX_RETRIES=0
ELASTICSEARCH_RAISE_ON_EXCEPTION=false
ELASTICSEARCH_RAISE_ON_ERROR=false

Error Message (if any):

pgsync_1  | Waiting for 600 seconds until next execution...

pgsync_1  | Running pgsync with the configuration file...
pgsync_1  |  - public.table_a
pgsync_1  |    |- public.table_b
pgsync_1  |    |   - public.table_c
pgsync_1  |    |      - public.table_d
pgsync_1  |    |         - public.table_e
pgsync_1  |    |            - public.table_f
pgsync_1  |     - public.table_g

pgsync_1  | 2023-12-14 15:13:27.630:WARNING:elastic_transport.node_pool: Node <Urllib3HttpNode(https://152.70.146.239:9200)> has failed for 1 times in a row, putting on 1 second timeout
pgsync_1  | 2023-12-14 15:13:27.630:WARNING:elastic_transport.transport: Retrying request after non-successful status 429 (attempt 0 of 3)
pgsync_1  | 2023-12-14 15:13:45.417:WARNING:elastic_transport.node_pool: Node <Urllib3HttpNode(https://152.70.146.239:9200)> has been marked alive after a successful request

docker-compose file

version: "3.8"

services:
  redis:
    image: redis:7.2
    command: redis-server --requirepass $REDIS_AUTH

  pgsync:
    build:
      context: .
      dockerfile: Dockerfile
    restart: on-failure
    command: ./runserver.sh
    mem_reservation: "40G"
    mem_limit: "40G"
    sysctls:
      - net.ipv4.tcp_keepalive_time=800
      - net.ipv4.tcp_keepalive_intvl=800
      - net.ipv4.tcp_keepalive_probes=10
    labels:
      org.label-schema.name: "pgsync"
      org.label-schema.description: "Postgres to Elasticsearch sync"
    depends_on:
      - redis
    environment:
      - LOG_LEVEL=DEBUG
      - REDIS_HOST
      - REDIS_PORT
      - REDIS_AUTH
      - PG_USER
      - PG_HOST
      - PG_PORT
      - PG_PASSWORD
      - ELASTICSEARCH
      - ELASTICSEARCH_PORT
      - ELASTICSEARCH_SCHEME
      - ELASTICSEARCH_HOST
      - ELASTICSEARCH_USER
      - ELASTICSEARCH_PASSWORD
      - ELASTICSEARCH_VERIFY_CERTS
      - ELASTICSEARCH_USE_SSL
      - ELASTICSEARCH_TIMEOUT
      - ELASTICSEARCH_CHUNK_SIZE
      - ELASTICSEARCH_MAX_CHUNK_BYTES
      - ELASTICSEARCH_MAX_RETRIES
      - ELASTICSEARCH_RAISE_ON_ERROR
      - ELASTICSEARCH_RAISE_ON_EXCEPTION
      - ELASTICSEARCH_STREAMING_BULK
      - INDEX_ENVIERONMENT
      - POLL_TIMEOUT
      - REPLICATION_SLOT_CLEANUP_INTERVAL
      - ELASTICSEARCH_THREAD_COUNT
      - ELASTICSEARCH_QUEUE_SIZE

I would greatly appreciate a response. Thank you very much for your time. Sincerely, Pablo Morales.

[toluaina]

Sorry for the delay. Just accepted your invitation. Can you show more of the logs when this happens? The fact that Redis has pending docs implies something is not setup correctly.

[zPabloMorales]

I am running Redis from Docker, what configuration needs Redis? Now I am running without --daemon flag, but I have this logs below. Running pgsync with next Environment Variables. Finally, maybe I can meet with you to show you more details.

POLL_TIMEOUT=0.05
REPLICATION_SLOT_CLEANUP_INTERVAL=60
ELASTICSEARCH_THREAD_COUNT=1
ELASTICSEARCH_QUEUE_SIZE=1
# increase this if you are getting read request timeouts
ELASTICSEARCH_TIMEOUT=1200
# number of documents to index at a time
ELASTICSEARCH_CHUNK_SIZE=2000
# the maximum size of the request in bytes (default: 100MB)
ELASTICSEARCH_MAX_CHUNK_BYTES=104857600
ELASTICSEARCH_STREAMING_BULK=true
# set to 0 (default) for no retries on ``429``
ELASTICSEARCH_MAX_RETRIES=0
ELASTICSEARCH_RAISE_ON_EXCEPTION=false
ELASTICSEARCH_RAISE_ON_ERROR=false
ELASTICSEARCH=true

2023-12-24 16:34:44.423:WARNING:elastic_transport.node_pool: Node <Urllib3HttpNode(https://152.70.146.239:9200)> has failed for 1 times in a row, putting on 1 second timeout
2023-12-24 16:34:44.423:WARNING:elastic_transport.transport: Retrying request after non-successful status 429 (attempt 0 of 3)
2023-12-24 16:34:47.315:WARNING:elastic_transport.node_pool: Node <Urllib3HttpNode(https://152.70.146.239:9200)> has failed for 2 times in a row, putting on 2 second timeout
2023-12-24 16:34:47.315:WARNING:elastic_transport.transport: Retrying request after non-successful status 429 (attempt 1 of 3)
2023-12-24 16:34:50.185:WARNING:elastic_transport.node_pool: Node <Urllib3HttpNode(https://152.70.146.239:9200)> has failed for 3 times in a row, putting on 4 second timeout
2023-12-24 16:34:50.186:WARNING:elastic_transport.transport: Retrying request after non-successful status 429 (attempt 2 of 3)
2023-12-24 16:34:53.075:WARNING:elastic_transport.node_pool: Node <Urllib3HttpNode(https://152.70.146.239:9200)> has failed for 4 times in a row, putting on 8 second timeout
2023-12-24 16:35:06.766:WARNING:elastic_transport.node_pool: Node <Urllib3HttpNode(https://152.70.146.239:9200)> has been marked alive after a successful request

[zPabloMorales]

This --daemon flag its working. Close thread.