I am trying to create an init.sql file to automatically create an user, database, schema, and table. Currently, the user and database are created, but I am having troubles with my schema and table. Please see below for my code:
docker-compose:
postgres_db:
image: postgres:14.1
restart: unless-stopped
environment:
POSTGRES_USER: ${SERVER_DB_USER}
POSTGRES_PASSWORD: ${SERVER_DB_PASS}
volumes:
- ./data/postgres:/var/lib/postgresql/data
- ./admin/config:/docker-entrypoint-initdb.d
ports:
- "5432:5432"
networks:
- bypass
init.sql file inside /admin/config:
CREATE USER bypass;
CREATE DATABASE bypass;
GRANT ALL PRIVILEGES ON DATABASE bypass TO bypass;
c bypass;
CREATE SCHEMA bypass_project;
CREATE TABLE bypass_project.Bypass_Data (
id serial PRIMARY KEY,
Date_Time TIMESTAMP,
Module VARCHAR(25),
Area VARCHAR(25),
);
After running docker-compose up, I am not able to see the schema and the table. I’m pretty sure c bypass
is used incorrectly. How do I connect to the bypass database and create everything there?
2
Answers
Your definitely have errors in your SQL syntax in the
CREATE TABLE
statement. You should remove trailing comma:Also you can try (second
bypass
is your DB user):instead of
Also
c
is a command specific to psql. If you try to execute an SQL script containing such command in another client, it will not work.In this case you can try to use
init.sh
script instead ofinit.sql
and usepsql
in your bash-script. You can see full example here.If it does not help you, could you provide more information? I think docker-compose log would be helpful.
You should leave just this in your
init.sql
:Then you should expose the DB port and connect to it from your application container. If you are using Python, as you mentioned in comment, you should use some DB migrations mechanism, like Django ORM or SQLAlchemy. On deployment of python application migration mechanism should be called, it would actualise the state of your database.
Here is an example of configuration of Django + PostgreSQL setup and here is a guide about Django migrations.