Docker Community Forums

Share and learn in the Docker community.

Create docker container with postgres db and tables

Hi,

I am trying to create a Postgres Docker container. I want to connect to this container from Python. I need to create a database and some tables while Docker is being created.
I attached my docker files.
I am running the command below:
docker build -t postgres . && docker run --name postgres-db postgres

However, the docker container is stopped after this command ends. (I see new container while I write docker container ls -a) (I do not see it when I execute docker container ls)

I tried to add pg_ctl -D /usr/local/var/postgres start to entrypoint.sh
In this case, I got the error message:
**pg_ctl: cannot be run as root
Please log in (using, e.g., “su”) as the (unprivileged) user that will
own the server process.
**

My Dockerfile:

FROM postgres:latest

ENV POSTGRES_USER docker

ENV POSTGRES_PASSWORD docker

ENV POSTGRES_DB docker

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

COPY pg-setup.sql /docker-entrypoint-initdb.d/

Create a config directory

RUN mkdir -p /usr/config

WORKDIR /usr/config

Bundle config source

COPY . /usr/config

Grant permissions for to our scripts to be executable

RUN chmod +x /usr/config/entrypoint.sh

RUN chmod +x /usr/config/configure-db.sh

ENTRYPOINT ["./entrypoint.sh"]

Tail the setup logs to trap the process

CMD [“tail -f /dev/null”]

HEALTHCHECK --interval=15s CMD /opt/mssql-tools/bin/sqlcmd -U sa -P $SA_PASSWORD -Q “select 1” && grep -q “MSSQL CONFIG COMPLETE” ./config.log

My docker-compose.yml file:

version: ‘3.2’
services:
db:
build: .
image: postgres
restart: always
environment:
ACCEPT_EULA: Y
POSTGRES_DB: ‘docker’
POSTGRES_USER: ‘docker’
POSTGRES_PASSWORD: ‘docker’
POSTGRES_DATA: /var/lib/postgresql/data/pgdata
ports:
- “1433:1433”
container_name: postgres-db
adminer:
image: adminer
restart: always
ports:
- 8080:8080

Entrypoint.sh:

#!/usr/bin/env bash
echo ‘hello world’
pg_ctl -D /usr/local/var/postgres start

init.sql:

CREATE USER cobiro;
CREATE DATABASE cobiro_postgres_db;
GRANT ALL PRIVILEGES ON DATABASE cobiro_postgres_db TO cobiro;
CREATE TABLE IF NOT EXISTS etl_campaign_performance
(
day_of_camp_performance date NOT NULL,
hour_of_camp_performance smallint NOT NULL,
start_date date,
end_date varchar(10),
campaign_id bigint NOT NULL,
campaign_state text,
click_count smallint,
cost integer,
account_currency_code text NOT NULL,
click_through_rate text,
impression_count integer
);
ALTER TABLE etl_campaign_performance
ADD CONSTRAINT PK_ETL_CAMPAIGN_PERFORMANCE
PRIMARY KEY (campaign_id, day_of_camp_performance, hour_of_camp_performance, account_currency_code);

Hi

Is there a reason why you override the default entrypoint / cmd from postgres image?

1 Like

No, I tried a lot so that may be wrong.
I am just trying to learn docker and connect to a postgres in the container.

Okay, try and remove the CMD and ENTRYPOINT from your dockerfile, then it will use the default one shipped in the postgres image

1 Like

Thanks a lot.
Now I am able to create the docker container. :slightly_smiling_face:
I followed the article in https://docs.docker.com/engine/examples/postgresql_service/
I am running a postgres docker container by using the commands below:
docker build -t eg_postgresql .
docker run --rm -P --name pg_test eg_postgresql

This works but the port number is dynamic. I can connect to the database by giving the port number. (the port I see in docker ps command) I would like to connect to this docker database from Python so I need a static port number.

I tried the parameters below: -p 127.0.0.1:5432:5432 -p 5432:5432

In that case, the docker container’s port number was set as 5432. However, I could not connect to the database. I get docker user does not exist error message.

What is your advice?

Hi again.

Good you got so far :slight_smile:

Try and only use: " -p 5432:5432 "

I tried:
docker run --rm -p 5432:5432 --name pg_test eg_postgresql

But I received the error:
image

My Dockerfile Dockerfile.txt (5.1 KB)

The default postgres user is postgres. Try using that user