SQL files under docker-entrypoint-initdb.d executed but tables not created

[SimoneGiusso]

According to the documentation running docker compose on this yaml file:

services:
  masterdb:
    image: postgres:15.6-alpine
    container_name: primary-server
    ports:
      - 5432:5432
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: psw
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
  readonlydb:
    image: postgres:15.6-alpine
    container_name: hot-standby
    ports:
      - 5430:5432
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: psw

should initialise:

primary-server with init.sql.

However it does not happen even if the file is correctly mounted:

Ii tried, to delete/re build the images, delete all the volumes but nothing. The init.sql script is not executed.

[tianon]

Compose works really hard to keep and re-use volumes between runs (including and especially "anonymous" volumes that aren't specified in the file, but are specified in the images in use), which is probably what you're running into.

Have you tried docker-compose down --volumes and/or docker-compose up --renew-anon-volumes?

[SimoneGiusso]

I've run:

docker system prune --volumes

In fact all the volumes were deleted:

I've just tried your suggestions, however this really didn't help. I'm just trying to create a simple table:

but when I try to execute:

SELECT *
FROM authors

it says table not found.

[SimoneGiusso]

Actually from the logs the script is executed. However when connecting to the container the tables are not found:

2024-04-19 00:25:57 The files belonging to this database system will be owned by user "postgres".
2024-04-19 00:25:57 This user must also own the server process.
2024-04-19 00:25:57 
2024-04-19 00:25:57 The database cluster will be initialized with locale "en_US.utf8".
2024-04-19 00:25:57 The default database encoding has accordingly been set to "UTF8".
2024-04-19 00:25:57 The default text search configuration will be set to "english".
2024-04-19 00:25:57 
2024-04-19 00:25:57 Data page checksums are disabled.
2024-04-19 00:25:57 
2024-04-19 00:25:57 fixing permissions on existing directory /var/lib/postgresql/data ... ok
2024-04-19 00:25:57 creating subdirectories ... ok
2024-04-19 00:25:57 selecting dynamic shared memory implementation ... posix
2024-04-19 00:25:57 selecting default max_connections ... 100
2024-04-19 00:25:57 selecting default shared_buffers ... 128MB
2024-04-19 00:25:57 selecting default time zone ... UTC
2024-04-19 00:25:57 creating configuration files ... ok
2024-04-19 00:25:57 running bootstrap script ... ok
2024-04-19 00:25:58 performing post-bootstrap initialization ... ok
2024-04-19 00:25:58 syncing data to disk ... ok
2024-04-19 00:25:58 
2024-04-19 00:25:58 
2024-04-19 00:25:58 Success. You can now start the database server using:
2024-04-19 00:25:58 
2024-04-19 00:25:58     pg_ctl -D /var/lib/postgresql/data -l logfile start
2024-04-19 00:25:58 
2024-04-19 00:25:58 waiting for server to start....2024-04-18 22:25:58.415 UTC [36] LOG:  starting PostgreSQL 15.6 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
2024-04-19 00:25:58 2024-04-18 22:25:58.416 UTC [36] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-04-19 00:25:58 2024-04-18 22:25:58.420 UTC [39] LOG:  database system was shut down at 2024-04-18 22:25:58 UTC
2024-04-19 00:25:58 2024-04-18 22:25:58.422 UTC [36] LOG:  database system is ready to accept connections
2024-04-19 00:25:58  done
2024-04-19 00:25:58 server started
2024-04-19 00:25:58 CREATE DATABASE
2024-04-19 00:25:58 
2024-04-19 00:25:58 
2024-04-19 00:25:58 /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
2024-04-19 00:25:58 CREATE TABLE
2024-04-19 00:25:58 CREATE TABLE
2024-04-19 00:25:58 INSERT 0 3
2024-04-19 00:25:58 INSERT 0 3
2024-04-19 00:25:58 
2024-04-19 00:25:58 
2024-04-19 00:25:58 sh: locale: not found
2024-04-19 00:25:58 2024-04-18 22:25:58.068 UTC [30] WARNING:  no usable system locales were found
2024-04-19 00:25:58 initdb: warning: enabling "trust" authentication for local connections
2024-04-19 00:25:58 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-19 00:25:58 2024-04-18 22:25:58.676 UTC [1] LOG:  starting PostgreSQL 15.6 on aarch64-unknown-linux-musl, compiled by gcc (Alpine 13.2.1_git20231014) 13.2.1 20231014, 64-bit
2024-04-19 00:25:58 2024-04-18 22:25:58.676 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-04-19 00:25:58 2024-04-18 22:25:58.677 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2024-04-19 00:25:58 2024-04-18 22:25:58.677 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-04-19 00:25:58 2024-04-18 22:25:58.680 UTC [57] LOG:  database system was shut down at 2024-04-18 22:25:58 UTC
2024-04-19 00:25:58 2024-04-18 22:25:58.681 UTC [1] LOG:  database system is ready to accept connections
2024-04-19 00:25:58 waiting for server to shut down....2024-04-18 22:25:58.567 UTC [36] LOG:  received fast shutdown request
2024-04-19 00:25:58 2024-04-18 22:25:58.569 UTC [36] LOG:  aborting any active transactions
2024-04-19 00:25:58 2024-04-18 22:25:58.570 UTC [36] LOG:  background worker "logical replication launcher" (PID 43) exited with exit code 1
2024-04-19 00:25:58 2024-04-18 22:25:58.570 UTC [37] LOG:  shutting down
2024-04-19 00:25:58 2024-04-18 22:25:58.579 UTC [37] LOG:  checkpoint starting: shutdown immediate
2024-04-19 00:25:58 2024-04-18 22:25:58.640 UTC [37] LOG:  checkpoint complete: wrote 938 buffers (5.7%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.020 s, sync=0.013 s, total=0.065 s; sync files=309, longest=0.003 s, average=0.001 s; distance=11234 kB, estimate=11234 kB
2024-04-19 00:25:58 2024-04-18 22:25:58.643 UTC [36] LOG:  database system is shut down
2024-04-19 00:25:58  done
2024-04-19 00:25:58 server stopped
2024-04-19 00:25:58 
2024-04-19 00:25:58 PostgreSQL init process complete; ready for start up.
2024-04-19 00:25:58 
2024-04-19 00:30:58 2024-04-18 22:30:58.778 UTC [55] LOG:  checkpoint starting: time
2024-04-19 00:31:03 2024-04-18 22:31:03.163 UTC [55] LOG:  checkpoint complete: wrote 45 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.372 s, sync=0.004 s, total=4.386 s; sync files=13, longest=0.002 s, average=0.001 s; distance=252 kB, estimate=252 kB
2024-04-19 00:32:23 2024-04-18 22:32:23.482 UTC [84] ERROR:  relation "authors" does not exist at character 15
2024-04-19 00:32:23 2024-04-18 22:32:23.482 UTC [84] STATEMENT:  SELECT *
2024-04-19 00:32:23     FROM authors

The restarting seems part of the database initialisation. However I can image that when restarting cause the data are lost? meh, no I don't think so. I created the tables manually and restarted the container and the tables are still present. It happens only at db initialisation apparently.

So why the init.sql is executed but the tables are not present @tianon?

[SimoneGiusso]

Ok I got it, I forgot the \c postgres; statement in the init.sql before creating the tables to executed the DDL queries on the default created postgres database, sorry for the noise.

[frontloop]

Can you please show the init.sql now? Which statement is it?