Loading sample items causes database connection to close

[tylere]

Loading collection.json works:

(eoapi-test) tylere@tylers-mbp eoapi-test % pypgstac load collections ~/Documents/GitHub/vincentsarago/MAXAR_opendata_to_pgstac/Maxar/collections.json --dsn postgresql://username:password@0.0.0.0:5439/postgis --method insert_ignore 

But loading items.json fails:

(eoapi-test) tylere@tylers-mbp eoapi-test % pypgstac load items ~/Documents/GitHub/vincentsarago/MAXAR_opendata_to_pgstac/Maxar/items.json --dsn postgresql://username:password@0.0.0.0:5439/postgis --method insert_ignore
Traceback (most recent call last):
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/bin/pypgstac", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 125, in cli
    fire.Fire(PgstacCLI)
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/fire/core.py", line 141, in Fire
    component_trace = _Fire(component, args, parsed_flag_args, context, name)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/fire/core.py", line 466, in _Fire
    component, remaining_args = _CallAndUpdateTrace(
                                ^^^^^^^^^^^^^^^^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
    component = fn(*varargs, **kwargs)
                ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 76, in load
    loader.load_items(file, method, dehydrated, chunksize)
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/pypgstac/load.py", line 613, in load_items
    self.load_partition(self._partition_cache[k], g, insert_mode)
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/tenacity/__init__.py", line 326, in wrapped_f
    return self(f, *args, **kw)
           ^^^^^^^^^^^^^^^^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/tenacity/__init__.py", line 406, in __call__
    do = self.iter(retry_state=retry_state)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/tenacity/__init__.py", line 351, in iter
    return fut.result()
           ^^^^^^^^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/concurrent/futures/_base.py", line 449, in result
    return self.__get_result()
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result
    raise self._exception
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/tenacity/__init__.py", line 409, in __call__
    result = fn(*args, **kwargs)
             ^^^^^^^^^^^^^^^^^^^
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/pypgstac/load.py", line 282, in load_partition
    cur.execute(
  File "/Users/tylere/Documents/GitHub/tylere/eoapi-test/.pixi/envs/default/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.OperationalError: consuming input failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
discarding closed connection: <psycopg.Connection [BAD] at 0x10611a110>

docker-compose.yml

version: '3'

services:

  # change to official image when available https://github.com/radiantearth/stac-browser/pull/386
  stac-browser:
    build:
      context: dockerfiles
      dockerfile: Dockerfile.browser
    ports:
      - "${MY_DOCKER_IP:-127.0.0.1}:8085:8085"
    depends_on:
      - stac
      - raster
      - database

  stac:
    # Note:
    # the official ghcr.io/stac-utils/stac-fastapi-pgstac image uses python 3.8 and uvicorn
    # which is why here we use a custom Dockerfile using python 3.11 and gunicorn
    build:
      context: .
      dockerfile: dockerfiles/Dockerfile.stac
    ports:
      - "${MY_DOCKER_IP:-127.0.0.1}:8081:8081"
    environment:
      # Application
      - HOST=0.0.0.0
      - PORT=8081
      - MODULE_NAME=stac_fastapi.pgstac.app
      - VARIABLE_NAME=app
      # gunicorn
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#web_concurrency
      - WEB_CONCURRENCY=10
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#workers_per_core
      # - WORKERS_PER_CORE=1
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#max_workers
      # - MAX_WORKERS=10
      # Postgres connection
      - POSTGRES_USER=username
      - POSTGRES_PASS=password
      - POSTGRES_DBNAME=postgis
      - POSTGRES_HOST_READER=database
      - POSTGRES_HOST_WRITER=database
      - POSTGRES_PORT=5432
      - DB_MIN_CONN_SIZE=1
      - DB_MAX_CONN_SIZE=10
    depends_on:
      - database
    command:
      bash -c "bash /tmp/scripts/wait-for-it.sh -t 120 -h database -p 5432 && /start.sh"
    volumes:
      - ./dockerfiles/scripts:/tmp/scripts

  raster:
    # At the time of writing, rasterio and psycopg wheels are not available for arm64 arch
    # so we force the image to be built with linux/amd64
    platform: linux/amd64
    image: ghcr.io/stac-utils/titiler-pgstac:0.5.1
    ports:
      - "${MY_DOCKER_IP:-127.0.0.1}:8082:8082"
    environment:
      # Application
      - HOST=0.0.0.0
      - PORT=8082
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#web_concurrency
      - WEB_CONCURRENCY=1
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#workers_per_core
      - WORKERS_PER_CORE=1
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#max_workers
      - MAX_WORKERS=10
      # Postgres connection
      - POSTGRES_USER=username
      - POSTGRES_PASS=password
      - POSTGRES_DBNAME=postgis
      - POSTGRES_HOST=database
      - POSTGRES_PORT=5432
      - DB_MIN_CONN_SIZE=1
      - DB_MAX_CONN_SIZE=10
      # - DB_MAX_QUERIES=10
      # - DB_MAX_IDLE=10
      # GDAL Config
      - CPL_TMPDIR=/tmp
      - GDAL_CACHEMAX=75%
      - GDAL_INGESTED_BYTES_AT_OPEN=32768
      - GDAL_DISABLE_READDIR_ON_OPEN=EMPTY_DIR
      - GDAL_HTTP_MERGE_CONSECUTIVE_RANGES=YES
      - GDAL_HTTP_MULTIPLEX=YES
      - GDAL_HTTP_VERSION=2
      - VSI_CACHE=TRUE
      - VSI_CACHE_SIZE=536870912
      # TiTiler Config
      - MOSAIC_CONCURRENCY=1
      # AWS S3 endpoint config
      - AWS_ACCESS_KEY_ID=${AWS_ACCESS_KEY_ID}
      - AWS_SECRET_ACCESS_KEY=${AWS_SECRET_ACCESS_KEY}
    depends_on:
      - database
    command:
      bash -c "bash /tmp/scripts/wait-for-it.sh -t 120 -h database -p 5432 && /start.sh"
    volumes:
      - ./dockerfiles/scripts:/tmp/scripts

  vector:
    image: ghcr.io/developmentseed/tipg:0.3.1
    ports:
      - "${MY_DOCKER_IP:-127.0.0.1}:8083:8083"
    environment:
      # Application
      - HOST=0.0.0.0
      - PORT=8083
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#web_concurrency
      - WEB_CONCURRENCY=10
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#workers_per_core
      # - WORKERS_PER_CORE=1
      # https://github.com/tiangolo/uvicorn-gunicorn-docker#max_workers
      # - MAX_WORKERS=10
      # Postgres connection
      - POSTGRES_USER=username
      - POSTGRES_PASS=password
      - POSTGRES_DBNAME=postgis
      - POSTGRES_HOST=database
      - POSTGRES_PORT=5432
      - DB_MIN_CONN_SIZE=1
      - DB_MAX_CONN_SIZE=10
    command:
      bash -c "bash /tmp/scripts/wait-for-it.sh -t 120 -h database -p 5432 && /start.sh"
    depends_on:
      - database
    volumes:
      - ./dockerfiles/scripts:/tmp/scripts

  database:
    image: ghcr.io/stac-utils/pgstac:v0.8.4
    environment:
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=postgis
      - PGUSER=username
      - PGPASSWORD=password
      - PGDATABASE=postgis
    ports:
      - "${MY_DOCKER_IP:-127.0.0.1}:5439:5432"
    command: postgres -N 500
    # volumes:
    #   - ./.pgdata:/var/lib/postgresql/data

networks:
  default:
    name: eoapi-network

This docker-compose.yml file from eoapi-template, but with an upgraded database image ghcr.io/stac-utils/pgstac:v0.8.4 because v0.7.10 was producing the following error:

Exception: pypgstac version 0.8.4 is not compatible with the target database version 0.7.10. database version 0.7.10.

Note that the loading issue occurs even when only attempting to load the first row of items.json.

[vincentsarago]

@tylere I should update the documentation, but did you unzip the items.json.zip first? the error message is not really clear about this.

[tylere]

Yes, I did unzip the zip file before ingesting.
At first I tried to ingest the zip file directly (which would be a nice feature), but I think the error I received was pretty clear.

[vincentsarago]

Well I have no idea what's going on then. It seems the database is 💥 but I can't know why 🤷