Docker compose Postgres database with new database & table creation using docker entry point

Question

I have postgres docker question.

After following this link How to create User/Database in script for Docker Postgres

I have added .sql files to init folder. All queries are executing, but the problem is tables are getting created in default postgres database instead of the database i have created.

SQL Files i have

01-init.sql

CREATE USER test;
CREATE DATABASE orders;
GRANT ALL PRIVILEGES ON DATABASE orders TO test;

02-tables.sql

CREATE TABLE IF NOT EXISTS orders_list
(
  id uuid,
  data jsonb,
  created_date timestamp with time zone DEFAULT now(),
  modified_date timestamp with time zone DEFAULT now(),
  CONSTRAINT orders_pkey PRIMARY KEY (id)  
);

Once i'm done with docker-compose up. As expected all queries executed inside the init folder.

But as i mentioned earlier the tables.sql created the tables under postgres database.

Can anyone explain or explain how to fix this ?

I want the tables to be created under orders database.

Note: I'm asking in the context of postgres docker-compose, I know create database does not mean i have switched to that database, i want to know how to do this by using .sql files itself.

Solutions & Problems i'm foreseeing

In case if i have to write shell script to create database first then i have to use psql to execute the table files.

If i copy the sql files to init folder it will automatically run all the sql files. How can i stop init.d to run all the sql files copied ?

score 0 · Answer 1 · answered Jul 31 '22 at 18:04

version: '3.4'
services:
  db:
    image: postgres:latest
    networks:
      net:
    volumes:
      - db_volume_name:/var/lib/postgresql/data:rw
      - $PWD/db_dumps:/docker-entrypoint-initdb.d:ro
    environment:
      - TIMEZONE=America/Argentina/Buenos_Aires
      - POSTGRES_DB=${DB_DATABASE}
      - POSTGRES_USER=${DB_USERNAME}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
DB_DATABASE=orders
POSTGRES_USER=test
POSTGRES_PASSWORD=test_pwd
and put 02-tables.sql at $PWD/db_dumps directory

score 0 · Accepted Answer · answered Jul 31 '22 at 18:12

The postgres image runs initialization scripts using psql (https://github.com/docker-library/postgres/blob/5c0e796bb660f0ae42ae8bf084470f13417b8d63/13/alpine/docker-entrypoint.sh#L181) so you should be able to add \c orders to the beginning of your tables.sql to get it to connect to the new database. Note that it will create the tables as the postgres user, so you probably want add grant or alter table… owner to statements so the test user will have access to the tables.

Docker compose Postgres database with new database & table creation using docker entry point

2 Answers2