Error on docker image startup.

[PhillipsOwen]

I am seeing this at startup of the image.

Docker build command (built with no issues):

• docker build --no-cache -f Dockerfile -t pgstac .

Docker run command:

• docker run -p localhost:5432:5432 -v D:/dvols/apsviz/pgstac-pgdata:/var/lib/postgresql/data --env POSTGRES_PASSWORD= --env POSTGRES_USER=postgres --env POSTGRES_DB=postgis --env PGUSER=postgres --env PGPASSWORD= --env PGDATABASE=postgis --name pgstac

---

The files belonging to this database system will be owned by user "postgres". 2024-04-16T17:01:05.441016712Z This user must also own the server process. 2024-04-16T17:01:05.441020812Z 2024-04-16T17:01:05.441023412Z The database cluster will be initialized with locale "en_US.utf8". 2024-04-16T17:01:05.441026113Z The default database encoding has accordingly been set to "UTF8". 2024-04-16T17:01:05.441028713Z The default text search configuration will be set to "english". 2024-04-16T17:01:05.441031213Z 2024-04-16T17:01:05.441033513Z Data page checksums are disabled. 2024-04-16T17:01:05.441035913Z 2024-04-16T17:01:05.442223615Z fixing permissions on existing directory /var/lib/postgresql/data ... ok 2024-04-16T17:01:05.457202849Z creating subdirectories ... ok 2024-04-16T17:01:05.459068453Z selecting dynamic shared memory implementation ... posix 2024-04-16T17:01:05.480821602Z selecting default max_connections ... 100 2024-04-16T17:01:05.506090459Z selecting default shared_buffers ... 128MB 2024-04-16T17:01:05.529712012Z selecting default time zone ... Etc/UTC 2024-04-16T17:01:05.554383267Z creating configuration files ... ok 2024-04-16T17:01:07.600874461Z running bootstrap script ... ok 2024-04-16T17:01:11.441659682Z performing post-bootstrap initialization ... ok 2024-04-16T17:01:13.874030728Z syncing data to disk ... ok 2024-04-16T17:01:13.874059628Z 2024-04-16T17:01:13.874067928Z initdb: warning: enabling "trust" authentication for local connections 2024-04-16T17:01:13.874072928Z initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb. 2024-04-16T17:01:13.874080528Z 2024-04-16T17:01:13.874085728Z Success. You can now start the database server using: 2024-04-16T17:01:13.874089928Z 2024-04-16T17:01:13.874094228Z pg_ctl -D /var/lib/postgresql/data -l logfile start 2024-04-16T17:01:13.874098528Z 2024-04-16T17:01:13.948244386Z waiting for server to start....2024-04-16 17:01:13.947 UTC [49] LOG: starting PostgreSQL 15.6 (Debian 15.6-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit 2024-04-16T17:01:13.952361689Z 2024-04-16 17:01:13.951 UTC [49] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2024-04-16T17:01:13.979781510Z 2024-04-16 17:01:13.979 UTC [52] LOG: database system was shut down at 2024-04-16 17:01:11 UTC 2024-04-16T17:01:14.015124937Z 2024-04-16 17:01:14.014 UTC [49] LOG: database system is ready to accept connections 2024-04-16T17:01:14.113527713Z done 2024-04-16T17:01:14.113561913Z server started 2024-04-16T17:01:15.160859122Z CREATE DATABASE 2024-04-16T17:01:15.161233222Z 2024-04-16T17:01:15.161249822Z 2024-04-16T17:01:15.161253322Z /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-preinitdb.d/* 2024-04-16T17:01:15.161256022Z 2024-04-16T17:01:15.168736928Z 2024-04-16 17:01:15.168 UTC [49] LOG: received fast shutdown request 2024-04-16T17:01:15.173423031Z waiting for server to shut down....2024-04-16 17:01:15.173 UTC [49] LOG: aborting any active transactions 2024-04-16T17:01:15.175922033Z 2024-04-16 17:01:15.175 UTC [49] LOG: background worker "logical replication launcher" (PID 55) exited with exit code 1 2024-04-16T17:01:15.179288636Z 2024-04-16 17:01:15.178 UTC [50] LOG: shutting down 2024-04-16T17:01:15.183563839Z 2024-04-16 17:01:15.183 UTC [50] LOG: checkpoint starting: shutdown immediate 2024-04-16T17:01:15.917290405Z 2024-04-16 17:01:15.916 UTC [50] LOG: checkpoint complete: wrote 918 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.302 s, sync=0.401 s, total=0.738 s; sync files=301, longest=0.005 s, average=0.002 s; distance=4217 kB, estimate=4217 kB 2024-04-16T17:01:15.999259369Z 2024-04-16 17:01:15.998 UTC [49] LOG: database system is shut down 2024-04-16T17:01:16.095598943Z done 2024-04-16T17:01:16.095650443Z server stopped 2024-04-16T17:01:16.164099696Z waiting for server to start....2024-04-16 17:01:16.163 UTC [66] LOG: starting PostgreSQL 15.6 (Debian 15.6-1.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit 2024-04-16T17:01:16.168183099Z 2024-04-16 17:01:16.167 UTC [66] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2024-04-16T17:01:16.204350827Z 2024-04-16 17:01:16.204 UTC [69] LOG: database system was shut down at 2024-04-16 17:01:15 UTC 2024-04-16T17:01:16.234861451Z 2024-04-16 17:01:16.234 UTC [66] LOG: database system is ready to accept connections 2024-04-16T17:01:16.318307515Z done 2024-04-16T17:01:16.318353215Z server started 2024-04-16T17:01:16.318490215Z 2024-04-16T17:01:16.318629915Z /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/990_pgstac.sh 2024-04-16T17:01:16.542862088Z 2024-04-16T17:01:16.542886188Z /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/999_pgstac.sql 2024-04-16T17:01:16.603156835Z 2024-04-16 17:01:16.602 UTC [81] ERROR: syntax error at or near "migrations" at character 1 2024-04-16T17:01:16.603175835Z 2024-04-16 17:01:16.602 UTC [81] STATEMENT: migrations/pgstac.0.8.5.sql 2024-04-16T17:01:16.603179435Z psql:/docker-entrypoint-initdb.d/999_pgstac.sql:1: ERROR: syntax error at or near "migrations" 2024-04-16T17:01:16.603182235Z LINE 1: migrations/pgstac.0.8.5.sql 2024-04-16T17:01:16.603184835Z ^

i am also seeing similar issues with the docker compose: ... pgstac | server started pgstac | pgstac | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/990_pgstac.sh pgstac | pgstac | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/999_pgstac.sql pgstac | psql:/docker-entrypoint-initdb.d/999_pgstac.sql:1: ERROR: syntax error at or near "migrations" pgstac | LINE 1: migrations/pgstac.0.8.5.sql pgstac | ^ pgstac | 2024-04-16 17:13:24.979 UTC [80] ERROR: syntax error at or near "migrations" at character 1 pgstac | 2024-04-16 17:13:24.979 UTC [80] STATEMENT: migrations/pgstac.0.8.5.sql pgstac exited with code 3

i suspect something painfully obvious to you folks that is amiss with my attempts. I appreciate your help!

[bitner]

It looks like it has something to do with src/pgstac/pgstac.sql being a symlink that is set to link to the latest version in the migrations file.

ls -ltrh src/pgstac
total 4.0K
drwxrwxr-x 1 bitner bitner   54 Apr 17 15:54 tests
drwxrwxr-x 1 bitner bitner  568 Apr 17 15:54 sql
lrwxrwxrwx 1 bitner bitner   27 Apr 17 15:54 pgstac.sql -> migrations/pgstac.0.8.5.sql
drwxrwxr-x 1 bitner bitner 4.1K Apr 17 15:54 migrations

What OS are you running this on? I'm wondering if docker behaves differently between Mac and Linux. Everything runs fine for me on Ubuntu.

[PhillipsOwen]

thanks so much for the reply!

even worse, i am doing this on windows. i will re-evaluate the situation with that in mind, thanks!

[PhillipsOwen]

for the record...

my windows environment did not pull the src/pgstac/pgstac.sql file down as a symlink.

the solution in my case was to alter the dockerfile and substitute the two copy commands to use the intended file rather than the symlink. e.g: "COPY src/pgstac/pgstac.sql 999_pgstac.sql" to "COPY src/pgstac/migrations/pgstac.0.8.5.sql 999_pgstac.sql"

now i am facing another issue where the 999_pgstac.sql hangs on or around the first function creation. ... /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/990_pgstac.sh 2024-04-18T13:28:02.058564170Z 2024-04-18T13:28:02.058617272Z /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/991_plrust.sh 2024-04-18T13:28:02.210257544Z 2024-04-18T13:28:02.210281145Z /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/999_pgstac.sql 2024-04-18T13:28:02.485605555Z RESET 2024-04-18T13:28:03.997656633Z DO 2024-04-18T13:28:04.047973016Z DO 2024-04-18T13:28:04.049889280Z DO 2024-04-18T13:28:04.051902047Z DO 2024-04-18T13:28:04.059781111Z GRANT ROLE 2024-04-18T13:28:04.065092589Z CREATE FUNCTION pgstac_admin_owns 2024-04-18T13:28:04.082391167Z ------------------- 2024-04-18T13:28:04.082400267Z
2024-04-18T13:28:04.082405068Z (1 row) 2024-04-18T13:28:04.082409368Z 2024-04-18T13:33:01.746994974Z (END)2024-04-18 13:33:01.746 UTC [78] LOG: checkpoint starting: time 2024-04-18 13:35:17.486 UTC [78] LOG: checkpoint complete: wrote 1351 buffers (8.2%); 0 WAL file(s) added, 0 removed, 1 recycled; write=135.493 s, sync=0.191 s, total=135.740 s; sync files=137, longest=0.005 s, average=0.002 s; distance=11403 kB, estimate=11403 kB

[PhillipsOwen]

FWIW: i got the PgSTAC DB running. here are my notes:

• Windows line endings had to be replaced with linux line endings for all text files. this is an artifact of my GitHub settings.
• the Symbolic link at src/pgstac/pgstac.sql didnt translate to the windows environment. I replaced the symbolic links with their intended equivalent in the DockerFile.
• A DELETE statement had no where clause, replaced it with TRUNCATE
  An UPDATE statement had no where clause so added a WHERE id=id

  there is still one issue that i havnt been able to figure out is that the last configuration script hangs at the first stored function. the only way i was able to continue was to restart the DB and rerun the script manually.

  zacdezgeo commented 7 months ago

  @bitner, should we plan any changes to support Windows users or provide a docs page?

  PhillipsOwen commented 7 months ago

  here are the 2 statements in pgstac.0.8.5.sql that are hanging the db startup configuration when it is being executed as 999_pgstac.sql. commenting these out allow the script to complete fully.

  (line 93) SELECT pgstac_admin_owns(); (line 4392) SELECT update_partition_stats_q(partition) FROM partitions_view;

  bitner commented 7 months ago

  Right now, tracking down Windows issues isn't something I have time for, but I am very happy to take any PRs to increase support for Windows.

  Windows should have absolutely no bearing on any SQL statements, however.

  If you are having issues with DELETE OR UPDATE with no WHERE clause, you must be using some extension that prevents that as that is normal very correct SQL.

  capricorn-one commented 4 months ago

  I'm running into this same issue trying to deploy pgstac on a kubernetes pod. Anyone else have success doing this? I'm building the docker container on a windows machine and pushing the container to a harbor repository, would that mean I would have the same "windows" issue seen here? Running 0.9.0.sql btw.:

  psql:/docker-entrypoint-initdb.d/999_pgstac.sql:1: ERROR: syntax error at or near "migrations" 2024-07-22T22:00:12.645443188Z LINE 1: migrations/pgstac.0.9.0.sql

  For what it's worth.. running the docker container on my local windows machine works fine, only when I push the container to the repo and try to run it on a pod do I get the error above.

  bitner commented 4 months ago

  I don't use our have access to windows. I'm happy to review any PRs that fix this issue, but I can't help track it down.

  On Mon, Jul 22, 2024, 5:09 PM capricorn-one @.***> wrote:

  I'm running into this same issue trying to deploy pgstac on a kubernetes pod. Anyone else have success doing this? I'm building the docker container on a windows machine and pushing the container to a harbor repository, would that mean I would have the same "windows" issue seen here?

  — Reply to this email directly, view it on GitHub https://github.com/stac-utils/pgstac/issues/255#issuecomment-2243895837, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABIHXBJI2RYR77I3S3I7RLZNV7JPAVCNFSM6AAAAABGJYPUQKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENBTHA4TKOBTG4 . You are receiving this because you were mentioned.Message ID: @.***>

  PhillipsOwen commented 4 months ago

  IIRC, it came down to a couple of things:

  • windows line endings in the install scripts (probably introduced by me). that corrected a lot of the issues i was seeing.
  • a few other things that were related to the version of PG i was using (latest at the time).

  in the end i did get the DB working in k8s. however, i have since deleted my efforts as i lost interest in pursuing this further after bitner's comments.

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.

[zacdezgeo]

@bitner, should we plan any changes to support Windows users or provide a docs page?

[PhillipsOwen]

here are the 2 statements in pgstac.0.8.5.sql that are hanging the db startup configuration when it is being executed as 999_pgstac.sql. commenting these out allow the script to complete fully.

(line 93) SELECT pgstac_admin_owns(); (line 4392) SELECT update_partition_stats_q(partition) FROM partitions_view;

[bitner]

Right now, tracking down Windows issues isn't something I have time for, but I am very happy to take any PRs to increase support for Windows.

Windows should have absolutely no bearing on any SQL statements, however.

If you are having issues with DELETE OR UPDATE with no WHERE clause, you must be using some extension that prevents that as that is normal very correct SQL.

[capricorn-one]

I'm running into this same issue trying to deploy pgstac on a kubernetes pod. Anyone else have success doing this? I'm building the docker container on a windows machine and pushing the container to a harbor repository, would that mean I would have the same "windows" issue seen here? Running 0.9.0.sql btw.:

psql:/docker-entrypoint-initdb.d/999_pgstac.sql:1: ERROR: syntax error at or near "migrations" 2024-07-22T22:00:12.645443188Z LINE 1: migrations/pgstac.0.9.0.sql

For what it's worth.. running the docker container on my local windows machine works fine, only when I push the container to the repo and try to run it on a pod do I get the error above.

[bitner]

I don't use our have access to windows. I'm happy to review any PRs that fix this issue, but I can't help track it down.

On Mon, Jul 22, 2024, 5:09 PM capricorn-one @.***> wrote:

I'm running into this same issue trying to deploy pgstac on a kubernetes pod. Anyone else have success doing this? I'm building the docker container on a windows machine and pushing the container to a harbor repository, would that mean I would have the same "windows" issue seen here?

— Reply to this email directly, view it on GitHub https://github.com/stac-utils/pgstac/issues/255#issuecomment-2243895837, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABIHXBJI2RYR77I3S3I7RLZNV7JPAVCNFSM6AAAAABGJYPUQKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENBTHA4TKOBTG4 . You are receiving this because you were mentioned.Message ID: @.***>

[PhillipsOwen]

IIRC, it came down to a couple of things:

• windows line endings in the install scripts (probably introduced by me). that corrected a lot of the issues i was seeing.
• a few other things that were related to the version of PG i was using (latest at the time).

in the end i did get the DB working in k8s. however, i have since deleted my efforts as i lost interest in pursuing this further after bitner's comments.