Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error on docker image startup. #255

Open
PhillipsOwen opened this issue Apr 16, 2024 · 10 comments
Open

Error on docker image startup. #255

PhillipsOwen opened this issue Apr 16, 2024 · 10 comments

Comments

@PhillipsOwen
Copy link

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
Copy link
Collaborator

bitner commented Apr 17, 2024

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
Copy link
Author

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
Copy link
Author

PhillipsOwen commented Apr 18, 2024

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
Copy link
Author

PhillipsOwen commented Apr 18, 2024

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
Copy link
Collaborator

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

@PhillipsOwen
Copy link
Author

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
Copy link
Collaborator

bitner commented Apr 22, 2024

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
Copy link

capricorn-one commented Jul 22, 2024

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
Copy link
Collaborator

bitner commented Jul 22, 2024 via email

@PhillipsOwen
Copy link
Author

PhillipsOwen commented Jul 23, 2024

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants