Initialization script not being executed

[hugo-paredes]

Hello team.

The problem

I'm trying to create a proof of concept and, to do that, I need to create a Postgres DB and execute a small initialization script. I've followed, I think, the documentation, but the script isn't being executed. The message I get is this:

/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

When I login to the instance, the file /usr/local/bin/docker-entrypoint.sh is indeed there and the script I'm passing is in the /docker-entrypoint-initdb.d/ folder.

drwxrwx---    2 1000     1000          4096 Feb 27 10:47 .
drwxr-xr-x    1 root     root          4096 Feb 27 13:17 ..
-rwxrwx---    1 1000     1000           505 Feb 27 11:16 init.sql

Can you guys tell me what am I doing wrong?

What's the code

# docker-compose.yml
version: '3'

services:

  db:
    image: postgres:12.1-alpine
    container_name: pgsql
    restart: always
    volumes:
      - ./data/:/import_data/
      - ./scripts/:/docker-entrypoint-initdb.d/
      - /tmp/pgdata:/var/lib/postgresql/data
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432

-- ./scripts/init.sql
-- Create a new database called 'moviesshows'
CREATE TABLE Movies (
    show_id INTEGER NULL,
    title VARCHAR (1024)  NULL,
    director VARCHAR (1024) NULL,
    actors TEXT NULL,
    country VARCHAR (512) NULL,
    date_added VARCHAR (256) NULL,
    release_year SMALLINT NULL,
    rating VARCHAR (50) NULL,
    duration VARCHAR (128) NULL,
    listed_in VARCHAR (1024) NULL,
    plot TEXT NULL,
    which VARCHAR (50) NULL
);

COPY movies FROM '/import_data/movies-shows.csv' delimiter ',' CSV HEADER;

# This is the file structure
.
├── data
│   └── movies-shows.csv
├── docker-compose.yml
└── scripts
    └── init.sql

OS

I'm running an Ubuntu VM (Linux dev-vm 5.3.0-40-generic #32~18.04.1-Ubuntu SMP Mon Feb 3 14:05:59 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux) in VirtualBox (up-to-date).

Docker info:

Client:
 Debug Mode: false

Server:
 Containers: 2
  Running: 2
  Paused: 0
  Stopped: 0
 Images: 2
 Server Version: 19.03.6
 Storage Driver: overlay2
  Backing Filesystem: extfs
  Supports d_type: true
  Native Overlay Diff: true
 Logging Driver: json-file
 Cgroup Driver: cgroupfs
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local logentries splunk syslog
 Swarm: inactive
 Runtimes: runc
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: b34a5c8af56e510852c35414db4c1f4fa6172339
 runc version: 3e425f80a8c931f88e6d94a8c831b9d5aa481657
 init version: fec3683
 Security Options:
  apparmor
  seccomp
   Profile: default
 Kernel Version: 5.3.0-40-generic
 Operating System: Ubuntu 18.04.4 LTS
 OSType: linux
 Architecture: x86_64
 CPUs: 2
 Total Memory: 7.776GiB
 Name: dev-vm
 ID: FBZ4:NL7C:3MHU:7O2I:3XB7:CIDI:MGEP:IYQB:5R2Y:2E7N:F5E7:KRZF
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 Registry: https://index.docker.io/v1/
 Labels:
 Experimental: false
 Insecure Registries:
  127.0.0.0/8
 Live Restore Enabled: false

Thanks

[wglambert]

Is your /tmp/pgdata directory empty?

[hugo-paredes]

Hi @wglambert

Yes, the /tmp/pgdata directory is empty. The message would also be different.

This is the output when it's being created from scratch:

pgsql      | The files belonging to this database system will be owned by user "postgres".
pgsql      | This user must also own the server process.
pgsql      | 
pgsql      | The database cluster will be initialized with locale "en_US.utf8".
pgsql      | The default database encoding has accordingly been set to "UTF8".
pgsql      | The default text search configuration will be set to "english".
pgsql      | 
pgsql      | Data page checksums are disabled.
pgsql      | 
pgsql      | fixing permissions on existing directory /var/lib/postgresql/data ... ok
pgsql      | creating subdirectories ... ok
pgsql      | selecting dynamic shared memory implementation ... posix
pgsql      | selecting default max_connections ... 100
pgsql      | selecting default shared_buffers ... 128MB
pgsql      | selecting default time zone ... UTC
pgsql      | creating configuration files ... ok
pgsql      | running bootstrap script ... ok
pgsql      | performing post-bootstrap initialization ... sh: locale: not found
pgsql      | 2020-02-27 18:11:19.392 UTC [29] WARNING:  no usable system locales were found
pgsql      | ok
pgsql      | syncing data to disk ... initdb: warning: ok
pgsql      | 
pgsql      | enabling "trust" authentication for local connections
pgsql      | You can change this by editing pg_hba.conf or using the option -A, or
pgsql      | --auth-local and --auth-host, the next time you run initdb.
pgsql      | 
pgsql      | Success. You can now start the database server using:
pgsql      | 
pgsql      |     pg_ctl -D /var/lib/postgresql/data -l logfile start
pgsql      | 
pgsql      | waiting for server to start....2020-02-27 18:11:20.463 UTC [34] LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
pgsql      | 2020-02-27 18:11:20.468 UTC [34] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
pgsql      | 2020-02-27 18:11:20.511 UTC [35] LOG:  database system was shut down at 2020-02-27 18:11:19 UTC
pgsql      | 2020-02-27 18:11:20.520 UTC [34] LOG:  database system is ready to accept connections
pgsql      |  done
pgsql      | server started
pgsql      | 
pgsql      | /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
pgsql      | 
pgsql      | waiting for server to shut down....2020-02-27 18:11:20.536 UTC [34] LOG:  received fast shutdown request
pgsql      | 2020-02-27 18:11:20.540 UTC [34] LOG:  aborting any active transactions
pgsql      | 2020-02-27 18:11:20.543 UTC [34] LOG:  background worker "logical replication launcher" (PID 41) exited with exit code 1
pgsql      | 2020-02-27 18:11:20.543 UTC [36] LOG:  shutting down
pgsql      | 2020-02-27 18:11:20.571 UTC [34] LOG:  database system is shut down
pgsql      |  done
pgsql      | server stopped
pgsql      | 
pgsql      | PostgreSQL init process complete; ready for start up.
pgsql      | 
pgsql      | 2020-02-27 18:11:20.652 UTC [1] LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
pgsql      | 2020-02-27 18:11:20.652 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
pgsql      | 2020-02-27 18:11:20.653 UTC [1] LOG:  listening on IPv6 address "::", port 5432
pgsql      | 2020-02-27 18:11:20.663 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
pgsql      | 2020-02-27 18:11:20.683 UTC [43] LOG:  database system was shut down at 2020-02-27 18:11:20 UTC
pgsql      | 2020-02-27 18:11:20.692 UTC [1] LOG:  database system is ready to accept connections

As you can see, after the script runs, the database is empty but it should have 1 table.

This is the output when the /tmp/pgdata/ directory already exists:

pgsql      | 
pgsql      | PostgreSQL Database directory appears to contain a database; Skipping initialization
pgsql      | 
pgsql      | 2020-02-27 18:16:28.111 UTC [1] LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
pgsql      | 2020-02-27 18:16:28.112 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
pgsql      | 2020-02-27 18:16:28.112 UTC [1] LOG:  listening on IPv6 address "::", port 5432
pgsql      | 2020-02-27 18:16:28.124 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
pgsql      | 2020-02-27 18:16:28.148 UTC [20] LOG:  database system was shut down at 2020-02-27 18:13:31 UTC
pgsql      | 2020-02-27 18:16:28.153 UTC [1] LOG:  database system is ready to accept connections

[wglambert]

I can't reproduce, but that error docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/* is shown when there's nothing in /docker-entrypoint-initdb.d/

$ docker run -d --name postgres -e POSTGRES_PASSWORD=pass postgres:alpine
fd80604e7a2e1d7cadb7ac4490eccc8d5e04a84b5b31a43078918164fc2264ee

$ docker logs -f postgres
[. . .]
/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

docker-compose up

$ cat docker-compose.yml 
version: '3'

services:

  db:
    image: postgres:12.1-alpine
    container_name: pgsql
    restart: always
    volumes:
      - ./scripts/:/docker-entrypoint-initdb.d/
    environment:
      POSTGRES_PASSWORD: example
    ports:
      - 5432:5432

$ cat scripts/script.sql 
-- Create a new database called 'moviesshows'
CREATE TABLE Movies (
    show_id INTEGER NULL,
    title VARCHAR (1024)  NULL,
    director VARCHAR (1024) NULL,
    actors TEXT NULL,
    country VARCHAR (512) NULL,
    date_added VARCHAR (256) NULL,
    release_year SMALLINT NULL,
    rating VARCHAR (50) NULL,
    duration VARCHAR (128) NULL,
    listed_in VARCHAR (1024) NULL,
    plot TEXT NULL,
    which VARCHAR (50) NULL
);

$ docker-compose up -d
Creating pgsql ... done

 docker logs -f pgsql
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 dynamic shared memory implementation ... posix                                                                                                      
selecting default max_connections ... 100                                                                                                                     
selecting default shared_buffers ... 128MB                                                                                                                    
selecting default time zone ... UTC                                                                                                                           
creating configuration files ... ok
running bootstrap script ... ok
sh: locale: not found
2020-02-27 18:56:41.380 UTC [28] WARNING:  no usable system locales were found
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

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

initdb: 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.
waiting for server to start....2020-02-27 18:56:45.372 UTC [33] LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
2020-02-27 18:56:45.431 UTC [33] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-02-27 18:56:45.721 UTC [34] LOG:  database system was shut down at 2020-02-27 18:56:41 UTC
2020-02-27 18:56:45.800 UTC [33] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/script.sql
CREATE TABLE


waiting for server to shut down...2020-02-27 18:56:45.945 UTC [33] LOG:  received fast shutdown request
.2020-02-27 18:56:46.000 UTC [33] LOG:  aborting any active transactions
2020-02-27 18:56:46.001 UTC [33] LOG:  background worker "logical replication launcher" (PID 40) exited with exit code 1
2020-02-27 18:56:46.002 UTC [35] LOG:  shutting down
.2020-02-27 18:56:47.241 UTC [33] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2020-02-27 18:56:47.335 UTC [1] LOG:  starting PostgreSQL 12.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 9.2.0) 9.2.0, 64-bit
2020-02-27 18:56:47.335 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-02-27 18:56:47.335 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-02-27 18:56:47.494 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-02-27 18:56:47.795 UTC [44] LOG:  database system was shut down at 2020-02-27 18:56:47 UTC
2020-02-27 18:56:47.944 UTC [1] LOG:  database system is ready to accept connections
^C

$ docker exec -it pgsql psql -U postgres                                                                                                  
psql (12.1)                                                                                                                                                   
Type "help" for help.                                                                                                                                         
                                                                                                                                                              
postgres=# \d                                                                                                                                                 
         List of relations                                                                                                                                    
 Schema |  Name  | Type  |  Owner                                                                                                                             
--------+--------+-------+----------                                                                                                                          
 public | movies | table | postgres                                                                                                                           
(1 row)

Shows:

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/script.sql
CREATE TABLE

If it were a permission error it would say permission denied, so I'm not sure exactly what the issue with your setup is

[yosifkit]

drwxrwx---    2 1000     1000          4096 Feb 27 10:47 .
drwxr-xr-x    1 root     root          4096 Feb 27 13:17 ..
-rwxrwx---    1 1000     1000           505 Feb 27 11:16 init.sql

The problem is directory and file permissions. The postgres user cannot read the directory and once that is solved, will need access to read the sql file.

Additionally, as of #253, these initialization scripts are run as the postgres user (or as the "semi-arbitrary user" specified with the --user flag to docker run; see the section titled "Arbitrary --user Notes" for more details).

- docker hub docs

[wglambert]

Closing since this is resolved

There's also this PR that will make folder permission errors more obvious

[PraneethGopinathan]

Is your /tmp/pgdata directory empty?

what if it is not empty in my case i have some data and i want to install an extension on top of that is this possible?

[yosifkit]

Is your /tmp/pgdata directory empty?

what if it is not empty in my case i have some data and i want to install an extension on top of that is this possible?

If your pgdata directory is not empty, then the initialization scripts will not run (#173, #821, #191, #929). The docker-entrypoint.sh is extendable as given in the docker-ensure-initdb.sh example (#1150), so something like /always.initdb.d/ scripts is possible in a custom startup script.