Initilizations scripts in docker-entrypoint-initdb.d cannot be executed to existing lock file about postgres HOT 13 CLOSED

Hello ! I'm running into the same troubles after the recent update.

The structure of my project:

$ tree .
.
├── build
├── Dockerfile
├── initialize.sh
├── launch
└── sql
    ├── 01_mail.sql
    ├── 11_get_list_stored_procedures.sql
    ├── 12_get_unseen_messages.sql
    ├── 13_create_email.sql
    ├── 14_update_message_timestamp.sql
    ├── 20_triggers.sql
    └── 90_stubdata.sql

initialize.sh

#!/bin/bash
set -e
set -x

echo "******PostgreSQL initialisation******"

echo "Starting postgres"
gosu postgres pg_ctl -w start

for f in sql/*.sql; do
    echo "Executing $f"
    gosu postgres psql -h localhost -p 5432 -U postgres -d $POSTGRES_DB -a -f $f
done

echo "Stopping Postgres"
gosu postgres pg_ctl stop

echo "Stopped postgres"

echo "******Initialisation finished******"

Log output:

$ docker run -it -p 5432:5432 -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=metabase postgresql-metabase
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
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.

Success. You can now start the database server using:

    postgres -D /var/lib/postgresql/data
or
    pg_ctl -D /var/lib/postgresql/data -l logfile start

PostgreSQL init process in progress...
LOG:  database system was shut down at 2015-07-28 13:00:27 MSK
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
CREATE DATABASE

ALTER ROLE


/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/initialize.sh
++ echo '******PostgreSQL initialisation******'
******PostgreSQL initialisation******
++ echo 'Starting postgres'
Starting postgres
++ gosu postgres pg_ctl -w start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start... done
server started
++ for f in 'sql/*.sql'
++ echo 'Executing sql/01_mail.sql'
Executing sql/01_mail.sql
++ gosu postgres psql -h localhost -p 5432 -U postgres -d metabase -a -f sql/01_mail.sql
FATAL:  lock file "postmaster.pid" already exists
HINT:  Is another postmaster (PID 53) running in data directory "/var/lib/postgresql/data"?
psql: could not connect to server: Connection refused
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

I had to replace FROM postgres to FROM postgres:9.4.3 in order to override this issue.

from postgres.

@vitalyisaev2 You shouldn't need your initialize.sh anymore. The issue is that what it runs now, a Postgres server is already up and running. The normal entrypoint should now source your *.sql files correctly.

from postgres.

@mmaczka See the discussion at #75. Basically, you'll need to replace gosu postgres postgres --single with an equivalent gosu postgres psql or psql --user postgres command.

cf. postgis/docker-postgis@bf8bfec or aidanlister/postgres-hstore@22fa0d1 for a couple projects that have been updated to fix the breaking changes.

from postgres.

What is the best way to create new database and new user, now after this all changes? I'd prefer to use SQL scripts but I need to pass user name / password via env vars to the container but SQL scripts don't understand environment variables.

from postgres.

@mmarzantowicz you don't have to create the user using the passed envs, you are free to use your sql to create them.

If you need the sql run by something than the env supplied user (or the postgres user when not supplied) then you can still use a shell script and use the psql command with the --user flag. We wanted to remove the limitations that people were having when using postgres --single.

from postgres.

from postgres.

Thanks! I've managed to create user/db "the new way" quite easily: I just use psql --username postgres (instead of deprecated postgres cmd with --single) with heredoc to execute SQL lines. There is also possibility to pass SQL script through envsubst so plain environment-aware SQL could be used.

One thing I don't understand is why psql is not asking for postgres password in init scripts. Password is set before scripts are executes (line 79) so it should be required or am I missing something?

from postgres.

from postgres.

I stumbled upon this issue after discovering that --single created the locking issue on the current 9.4 tag / image_id: 730d1d72bda2 on a 2nd machine I tried deploying to, where my dev machine had the --single enabled with no locking but that was because I was on the 9.4 tag / image_id: f33438ff9aef which was from 2 weeks ago and it had not been updated.

I understand that images will be updated in a public repo all the time, and that the best way for me to ensure consistency across deployments is to roll my own image and distribute it to my machines accordingly, but IMO this defeats the purpose of tagging in the first place.

I guess my question boils down to, is this something we should take up with Docker to support something along the lines of image:tag:commit or should postgres (and other projects) be using some other sort of semantic versioning relevant to docker images to reflect the changes and not break things in these scenarios?

from postgres.

@md5 thank you, that fixed my problem

from postgres.

Hello, I have a question. If I want to execute multiple sql files in order in /docker-entrypoint-initdb.d/, how can I do that? even though I add a shell script to run sqls in order, its not running shell script first.

from postgres.

from postgres.

Closing given that the original issue here is resolved. 👍

from postgres.