docker-compose postgres restart after running scripts in docker-entrypoint-initdb.d

Question

I have a simple Docker Compose file to initialize a Postgres DB Instance:

version: '3.8'

services:
  my-database:
    container_name: my-database
    image: library/postgres:13.1
    volumes:
      - ./db/init-my-database.sql:/docker-entrypoint-initdb.d/init-db-01.sql
    environment:
      - POSTGRES_DB=my-database
      - POSTGRES_USER=admin
      - POSTGRES_PASSWORD=password
    ports:
      - 10040:5432
    restart: always
    networks:
      - app-network

And my script init-my-database.sql looks like this:

DROP DATABASE IF EXISTS myschema;

CREATE DATABASE myschema;

-- Make sure we're using our `myschema` database
\c myschema;


CREATE SEQUENCE IF NOT EXISTS recipient_seq
    START WITH 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

CREATE TABLE IF NOT EXISTS recipient
(
    id                      BIGINT                   NOT NULL,
    recipient_id            VARCHAR(255)             NOT NULL,
    first_name              VARCHAR(255)             NOT NULL,
    middle_name             VARCHAR(255)             NOT NULL,
    last_name               VARCHAR(255)             NOT NULL,
    CONSTRAINT recipient_pk PRIMARY KEY (id)
);

When I tail the Docker logs I do see that the initialization script is being called. However, towards the end the database is shutdown and restarted --> "received fast shutdown request"

Why is the database restarted at the end? As the database and table created in the script is not available anymore.

Complete Docker Logs -->

my-database    | The files belonging to this database system will be owned by user "postgres".
my-database    | This user must also own the server process.
my-database    |
my-database    | The database cluster will be initialized with locale "en_US.utf8".
my-database    | The default database encoding has accordingly been set to "UTF8".
my-database    | The default text search configuration will be set to "english".
my-database    |
my-database    | Data page checksums are disabled.
my-database    |
my-database    | fixing permissions on existing directory /var/lib/postgresql/data ... ok
my-database    | creating subdirectories ... ok
my-database    | selecting dynamic shared memory implementation ... posix
my-database    | selecting default max_connections ... 100
my-database    | selecting default shared_buffers ... 128MB
my-database    | selecting default time zone ... Etc/UTC
my-database    | creating configuration files ... ok
my-database    | running bootstrap script ... ok
my-database    | performing post-bootstrap initialization ... ok
my-database    | syncing data to disk ... ok
my-database    |
my-database    | initdb: warning: enabling "trust" authentication for local connections
my-database    | You can change this by editing pg_hba.conf or using the option -A, or
my-database    | --auth-local and --auth-host, the next time you run initdb.
my-database    |
my-database    | Success. You can now start the database server using:
my-database    |
my-database    |     pg_ctl -D /var/lib/postgresql/data -l logfile start
my-database    |
my-database    | waiting for server to start....2020-12-22 23:42:50.083 UTC [45] LOG:  starting PostgreSQL 13.1 (Debian 13.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
my-database    | 2020-12-22 23:42:50.085 UTC [45] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
my-database    | 2020-12-22 23:42:50.091 UTC [46] LOG:  database system was shut down at 2020-12-22 23:42:49 UTC
my-database    | 2020-12-22 23:42:50.096 UTC [45] LOG:  database system is ready to accept connections
my-database    |  done
my-database    | server started
my-database    | CREATE DATABASE
my-database    |
my-database    |
my-database    | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init-db-01.sql
my-database    | psql:/docker-entrypoint-initdb.d/init-db-01.sql:1: NOTICE:  database "mergeletter" does not exist, skipping
my-database    | DROP DATABASE
my-database    | CREATE DATABASE
my-database    | You are now connected to database "mergeletter" as user "admin".
my-database    | CREATE SEQUENCE
my-database    | CREATE TABLE
my-database    |
my-database    |
my-database    | 2020-12-22 23:42:50.515 UTC [45] LOG:  received fast shutdown request
my-database    | waiting for server to shut down....2020-12-22 23:42:50.516 UTC [45] LOG:  aborting any active transactions
my-database    | 2020-12-22 23:42:50.521 UTC [45] LOG:  background worker "logical replication launcher" (PID 52) exited with exit code 1
my-database    | 2020-12-22 23:42:50.521 UTC [47] LOG:  shutting down
my-database    | 2020-12-22 23:42:50.541 UTC [45] LOG:  database system is shut down
my-database    |  done
my-database    | server stopped
my-database    |
my-database    | PostgreSQL init process complete; ready for start up.
my-database    |
my-database    | 2020-12-22 23:42:50.648 UTC [1] LOG:  starting PostgreSQL 13.1 (Debian 13.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
my-database    | 2020-12-22 23:42:50.649 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
my-database    | 2020-12-22 23:42:50.649 UTC [1] LOG:  listening on IPv6 address "::", port 5432
my-database    | 2020-12-22 23:42:50.652 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
my-database    | 2020-12-22 23:42:50.657 UTC [73] LOG:  database system was shut down at 2020-12-22 23:42:50 UTC
my-database    | 2020-12-22 23:42:50.663 UTC [1] LOG:  database system is ready to accept connections

FWIW, looks like this is intentional: github.com/docker-library/postgres/blob/… — hagemt
– hagemt, Commented Dec 23, 2020 at 0:35
Thanks for the response @TorEHagemann. However, that seems to defeat the purpose of offering the ability to run the initialization scripts. So, how do I initialize my database? — Tora Tora Tora
– Tora Tora Tora, Commented Dec 23, 2020 at 0:39
Note: The process does restart, but the container itself remains running; the recipient schema does appear up within a minute, if not immediately. Personally, I'd use one (or a combination) of the following approaches: a dependent container (sometimes based upon postgres for psql, or better: Liquibase or Flyway, for example) to maintain the schema migrations. Alternatively, write one-off containers scripted for dev, etc. — hagemt
– hagemt, Commented Dec 23, 2020 at 5:29
@TorEHagemann I'm using liquibase and actually found that it is failing due to the database server first communicating that it is ready to receive transaction and then failing once the server is restarted as it can not reach the database. — Ray
– Ray, Commented Feb 3, 2021 at 1:26

hagemt · Accepted Answer · 2021-10-19 04:50:55Z

Put simply, this happens on purpose; the author does it as part of the initialization.

It looks like some answers can be found in the image's entrypoint shell script:

_main() {
    # if first arg looks like a flag, assume we want to run postgres server
    if [ "${1:0:1}" = '-' ]; then
        set -- postgres "$@"
    fi

    if [ "$1" = 'postgres' ] && ! _pg_want_help "$@"; then
        docker_setup_env
        # setup data directories and permissions (when run as root)
        docker_create_db_directories
        if [ "$(id -u)" = '0' ]; then
            # then restart script as postgres user
            exec gosu postgres "$BASH_SOURCE" "$@"
        fi

        # only run initialization on an empty data directory
        if [ -z "$DATABASE_ALREADY_EXISTS" ]; then
            docker_verify_minimum_env

            # check dir permissions to reduce likelihood of half-initialized database
            ls /docker-entrypoint-initdb.d/ > /dev/null

            docker_init_database_dir
            pg_setup_hba_conf

            # PGPASSWORD is required for psql when authentication is required for 'local' connections via pg_hba.conf and is otherwise harmless
            # e.g. when '--auth=md5' or '--auth-local=md5' is used in POSTGRES_INITDB_ARGS
            export PGPASSWORD="${PGPASSWORD:-$POSTGRES_PASSWORD}"
            docker_temp_server_start "$@"

            docker_setup_db
            docker_process_init_files /docker-entrypoint-initdb.d/*

            docker_temp_server_stop
            unset PGPASSWORD

            echo
            echo 'PostgreSQL init process complete; ready for start up.'
            echo
        else
            echo
            echo 'PostgreSQL Database directory appears to contain a database; Skipping initialization'
            echo
        fi
    fi

    exec "$@"
}

As for "why?" I think it's because of desire to run as a less-privileged user.

You can "solve" the problem by specifying a volume in the Compose file like so:

    volumes:
       - ./data/pgsql:/var/lib/postgresql/data

Then, it will skip the routine to ensure DATABASE_ALREADY_EXISTS.

Or, if that's not helpful--you can dig into the entrypoint script a bit more.

docker_temp_server_start starts the database without network listeners, so a client can't connect from another container before the init scripts have run.

Tora Tora Tora · Accepted Answer · 2020-12-23 00:56:58Z

2

Thanks for the above tip from @TorEHagermann (https://stackoverflow.com/a/65417566/5631863) I was able to resolve the problem.

The solution was to include the following:

volumes:
  - ./data/pgsql:/var/lib/postgresql/data

Also, in my case, I needed to clear and refresh the data every time I started the container. So, I included the following in my shell script to delete the drive /data/pgsql:

rm -rf data

With that change, my docker-compose file looks like: version: '3.8'

services:
  my-database:
    container_name: my-database
    image: library/postgres:13.1
    volumes:
      - ./db/init-my-database.sql:/docker-entrypoint-initdb.d/init-db-01.sql
      - ./data/pgsql:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=my-database
      - POSTGRES_USER=admin
      - POSTGRES_PASSWORD=password
    ports:
      - 10040:5432
    restart: always
    networks:
      - app-network

answered Dec 23, 2020 at 0:56

Tora Tora Tora

1,0033 gold badges20 silver badges34 bronze badges

4 Comments

hagemt Over a year ago

Ack. I might suggest something specific to manage schema; for example, if the service is Flask or similar, alembic seems to work pretty well. alembic.sqlalchemy.org/en/latest/tutorial.html

Jay Shukla Over a year ago

Is that "data/pgsql" (which you are passing in the volumes) auto generated? If not, then how to create it? PS: I was facing the same problem, thanks for the explaination.

Tora Tora Tora Over a year ago

@JayShukla - ./data/pgsql is my local directory which I create manually

hagemt Over a year ago

My general practice is to put a data folder w/ .gitignore in it, naming the volume paths. Docker will create the folder itself for volumes, but not the data folder itself. That makes starting fresh with git clean -dix simple.

user3097500 · Accepted Answer · 2022-06-01 12:36:14Z

1

As mentioned above, the restart is intentional. The reason is that changes in some of the database server settings need restart to take effect. The entrypoint init script is a convenient place for the queries that change these settings.

e.g.

ALTER SYSTEM SET max_connections = 300;

answered Jun 1, 2022 at 12:36

user3097500

111 bronze badge

Collectives™ on Stack Overflow

docker-compose postgres restart after running scripts in docker-entrypoint-initdb.d

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related