Could not create schema and table in PostgreSQL

Question

I faced with a weird behavior in the PostgreSQL database. I have the image in the Docker file that creates the database (here is the code of the Docker file):

  in_postgres:
      image: postgres:latest
      restart: always
      container_name: in_postgres
      hostname: in_postgres_hostname
      ports:
        - "5432:5432"
      environment:
        POSTGRES_USER: in_postgres_user
        POSTGRES_PASSWORD: in_postgres_password
        POSTGRES_DB: test_jdbc_sink_connector
      command: ["postgres", "-c", "wal_level=logical"]
      healthcheck:
        test: ["CMD", "psql", "-U", "in_postgres_user", "-d", "test_jdbc_sink_connector", "-c", "SELECT 1"]
        interval: 10s
        timeout: 5s
        retries: 5
      volumes:
        - ./dependencies/in_postgres_startup_insert.sql:/docker-entrypoint-initdb.d/in_postgres_startup_insert.sql

As you see in this Docker file I use in_postgres_startup_insert.sql file.

Here is the code of the in_postgres_startup_insert.sql file:

CREATE DATABASE test_jdbc_sink_connector;
GRANT ALL PRIVILEGES ON DATABASE test_jdbc_sink_connector TO in_postgres_user;

CREATE SCHEMA IF NOT EXISTS kafka;
GRANT ALL PRIVILEGES ON SCHEMA kafka TO in_postgres_user;

CREATE TABLE IF NOT EXISTS test_jdbc_sink_connector.kafka.location (
    id SERIAL PRIMARY KEY,
    location_id VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    city VARCHAR(255),
    insertion_date TIMESTAMP
);

For some reason, I see only database: test_jdbc_sink_connector but not schema and table (they have not been created for some reason). I already tried different combinations inside the in_postgres_startup_insert.sql (with schema/without schema/tried to create a table inside the public schema), but the result is the same: no schema, no table.

Here is a screenshot of the DBeaver: enter image description here

Maybe somebody could help. I don't understand why the table has not been created.

Answer 1

Always first check the docker container logs if there's anything relevant there. docker compose logs.

Based on my test, the first problem in the log is:

ERROR:  database "test_jdbc_sink_connector" already exists

So if you get that, it means that since you already provided the table name in POSTGRES_DB, you can remove the db creation line from your sql.

Secondly, make sure you do full docker compose down before doing docker compose up if you keep doing changes to the init sql. Otherwise it sneakily fail because it will not re-execute the init sql and just go through:

PostgreSQL Database directory appears to contain a database; Skipping initialization