postgres:10.4-alpine database empty after Init scripts

[nurked]

Per the write-up here I should be able to extend the image to init the database on startup. No matter what I do I can't seem to be able to run this init script. According to the logs script starts and all instructions get executed, but there is nothing in the final database once I connect there.

Here is the example:

docker-compose.yaml:

services:
  nec-postgres:
    image: nec-postgres
    ports:
      - "5432:5432"
    networks:
      - smdr-network
    volumes: 
      - "pgdata:/var/lib/postgresql/data"

volumes:
  pgdata:

Dockerfile:

FROM postgres:10.4-alpine

ENV POSTGRES_USER=user
ENV POSTGRES_PASSWORD=pass
ENV POSTGRES_DB=db

COPY *.sql /docker-entrypoint-initdb.d/

One of the SQL scripts (vetted):

CREATE TABLE users(
    id                bigserial   PRIMARY KEY    NOT NULL,
    name              TEXT                       NOT NULL
);

create table account(
    id                bigserial   PRIMARY KEY    NOT NULL,
    user_id           bigserial                  NOT NULL,
    balance           numeric(15,6)             
);

ALTER TABLE account
   ADD CONSTRAINT user_id_fkey
   FOREIGN KEY (user_id)
   REFERENCES users(id);

Build happens without any problems and once I have it done I can see the startup log:

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
running bootstrap script ... ok
performing post-bootstrap initialization ... 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:

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

waiting for server to start....2018-06-05 16:00:25.543 UTC [37] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-06-05 16:00:25.560 UTC [38] LOG:  database system was shut down at 2018-06-05 16:00:25 UTC
2018-06-05 16:00:25.566 UTC [37] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE

CREATE ROLE

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

2018-06-05 16:00:26.551 UTC [37] LOG:  received fast shutdown request
waiting for server to shut down....2018-06-05 16:00:26.555 UTC [37] LOG:  aborting any active transactions
2018-06-05 16:00:26.558 UTC [37] LOG:  worker process: logical replication launcher (PID 44) exited with exit code 1
2018-06-05 16:00:26.559 UTC [39] LOG:  shutting down
2018-06-05 16:00:26.612 UTC [37] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2018-06-05 16:00:26.671 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-06-05 16:00:26.672 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2018-06-05 16:00:26.679 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2018-06-05 16:00:26.697 UTC [109] LOG:  database system was shut down at 2018-06-05 16:00:26 UTC
2018-06-05 16:00:26.703 UTC [1] LOG:  database system is ready to accept connections

Once you connect to the database you see empty table:

Currently I have to init the database by other means, but I wanted to be able to use docker-entrypoint-initdb.d

[wglambert]

I'm not real experienced in SQL's but the databases in your screenshot appears to be missing the one defined from the ENV POSTGRES_DB=db and also the postgres database from the default user postgres

When I run your dockerfile everything seems correct.

$ docker run --rm -d --name postgres_test postgres_test
$ docker exec -it postgres_test bash
bash-4.4# psql -ddb -Uuser
psql (10.4)
Type "help" for help.

db=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 db        | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

db=# \dt
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | account | table | user
 public | users   | table | user
(2 rows)

db=# SELECT * from account;
 id | user_id | balance 
----+---------+---------
(0 rows)

[nurked]

Ok, thank you very much @wglambert for the answer. This basically gave me a solution. It's not a postgres issue and postgres is working allright. In fact when I logged in to the container I found all of my tables without any problem.

The thing to blame is heidiSQL program which seems to be unable to display posgtres database structure properly. I connected to the container same way you did it and I was able to see the tables. Then I used pgAdmin III and despite numerous errors I was able to find all my tables.

Darn! Wasted entire day debugging things when there is nothing to debug at all.

Well, at least everything is working fine.