postgres:11.10 docker image APT repository is removed

[logstar]

@atlas4213 encountered the following error when building the db docker image from db/db.Dockerfile. This error can be reproduced by running docker-compose build --no-cache db.

Step 2/10 : RUN apt-get update -qq   && apt-get install -y --no-install-recommends     ca-certificates     curl   && rm -rf /var/lib/apt/lists/*
 ---> Running in ...
W: The repository 'http://apt.postgresql.org/pub/repos/apt stretch-pgdg Release' does not have a Release file.
E: Failed to fetch http://apt.postgresql.org/pub/repos/apt/dists/stretch-pgdg/main/binary-amd64/Packages  404  Not Found [IP: 72.32.157.246 80]
E: Some index files failed to download. They have been ignored, or old ones used instead.

The error is caused by the recent removal of Debian stretch (9) from http://apt.postgresql.org/, according to https://wiki.postgresql.org/wiki/Apt.

Note that this error does not affect remote DEV/QA/PRD sites, because they use Amazon Relational Database Service (RDS) rather than the db docker image to host database servers.

To temporarily fix this error:

• If db docker image has been built, there is no need to change anything. The image cache should still work when building new db docker images.
• If db docker image has not been built, change the first line of db/db.Dockerfile,

  OpenPedCan-api/db/db.Dockerfile

  Line 1 in 0a7046b

  FROM postgres:11.10

  , to FROM postgres:12.

The permanent fix of this error is open for discussions. Following are some potential ones:

• Update PostgreSQL version from 11 to 12 in db/db.Dockerfile and db/build_tools/build_db.Dockerfile. This will require checking PostgreSQL changes from version 11 to 12, to see whether it would be incompatible with any part of the OpenPedCan-api.
• Keep using PostgreSQL version 11, and remove the curl installation part in db/db.Dockerfile. This will require extracting the database downloading procedure,

  OpenPedCan-api/db/load_db.sh

  Lines 34 to 57 in 0a7046b

  	if [[ "${DB_LOCATION}" == "local" ]]; then
  	cd "${BUILD_OUTPUT_DIR_PATH}"
  	if [[ ! -f "sha256sum.txt" ]]; then
  	echo "Error: local file ${PWD}/sha256sum.txt does not exist." 1>&2
  	exit 1
  	fi
  	else
  	if [[ "${DB_LOCATION}" != "aws_s3" ]]; then
  	echo "Invalid DB_LOCATION ${DB_LOCATION}" 1>&2
  	exit 1
  	fi
  	fi
  	db_dump_fn="open_ped_can_db_postgres_pg_dump.sql.gz"
  	API_DB_BASE_URL="https://s3.amazonaws.com/d3b-openaccess-us-east-1-prd-pbta/open-targets/api/v0.4.0-beta"
  	if [[ "${DB_LOCATION}" == "aws_s3" ]]; then \
  	mkdir -p "$DB_LOAD_TOOLS_DIR_PATH"
  	cd "$DB_LOAD_TOOLS_DIR_PATH"
  	curl "${API_DB_BASE_URL}/sha256sum.txt" -o "sha256sum.txt"
  	curl "${API_DB_BASE_URL}/${db_dump_fn}" -o "${db_dump_fn}"
  	fi

  , to a standalone script. Also, PostgreSQL version 11 only will be supported to November 2023, so the PostgreSQL version needs to be updated before then.

cc @sangeetashukla @kelseykeith @ewafula @chinwallaa

[kelseykeith]

I assume this will need to be addressed while working on creating the CNV database for #81 so that might be a good time to implement and review the solution we decide on.

[logstar]

I assume this will need to be addressed while working on creating the CNV database for #81 so that might be a good time to implement and review the solution we decide on.

Thank you for the suggestion. I agree that #81 might be a good place to fix this error permanently. We could discuss further on the solutions in the next API meeting. To avoid blocking the development of CNV databases, any temporary fix could be applied before a permanent fix is decided and implemented.

[logstar]

Regarding options to permanently fix this issue, adding another debian 9 APT repository in db/db.Dockerfile could also be feasible.