Remote control on my postgresql container docker works but can't see tables creates

Question

I am new in docker, I've created a dockerfile to launch postgresql image and insert data in a new database.

When I run docker it works well in its container, I can see database and tables created.

But When I try to connect from a client postgres, I see the database created but not tables and we dont know why me and my team..

My DockerFile:

# Dockerfile
FROM postgres:9.6.5
RUN localedef -i fr_FR -c -f UTF-8 -A /usr/share/locale/locale.alias fr_FR.UTF-8
ENV LANG fr_FR.utf8
COPY postgresql.conf /
COPY sql/structure.sql /docker-entrypoint-initdb.d/

My file SQL structure.sql:

CREATE DATABASE DB_ALLOT_BOUCHON_NEW;
GRANT ALL PRIVILEGES ON DATABASE DB_ALLOT_BOUCHON_NEW TO postgres;
CREATE Table IF NOT EXISTS public.SUBSCR (
    ID              BIGINT       NOT NULL,
    subscriber_name VARCHAR(100) NOT NULL,
    sp_array        VARCHAR(100) NOT NULL,
    PRIMARY KEY (ID)
);

INSERT INTO SUBSCR (ID, subscriber_name, sp_array) VALUES (1, 'client_1', 'sp_array client_1 client_2');
INSERT INTO SUBSCR (ID, subscriber_name, sp_array) VALUES (2, 'client_2', 'sp_array client_2 client_2');

Command to build and run my dockerfile:

$ docker build -t allot_psql .
$ docker run -d --name custom_psql_running -p 5467:5432 allot_psql -c config_file=postgresql.conf
$ docker logs custom_psql_running
$ docker run -it --rm --link custom_psql_running postgres psql -h custom_psql_running -U postgres
# postgres=# SELECT * from subscr;

Result in my container:


postgres=# select * from subscr;
 id | subscriber_name |          sp_array
----+-----------------+----------------------------
  1 | client_1        | sp_array client_1 client_2
  2 | client_2        | sp_array client_2 client_2
(2 rows)

postgres=# \d
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | subscr | table | postgres
(1 row)

postgres=# \d*
Invalid command \d*. Try \? for help.
postgres=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | subscr | table | postgres
(1 row)

postgres=# \d+
                       List of relations
 Schema |  Name  | Type  |  Owner   |    Size    | Description
--------+--------+-------+----------+------------+-------------
 public | subscr | table | postgres | 8192 bytes |
(1 row)

postgres=# \d
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | subscr | table | postgres
(1 row)

postgres=# \
Invalid command \. Try \? for help.
postgres=# \q

Everything seems to work well but when I use postgresql client, I can see only the database created but not tables inside.

jdbc:postgresql://localhost:5467/db_allot_bouchon_new
username:postgres

b0gusb · Answer 1 · 2019-08-29T09:23:29.267

3

The structure.sql script creates the tables in the default database postgres and not in DB_ALLOT_BOUCHON_NEW. When the container is initialized you are connected to postgres.To quickly fix connect to db_allot_bouchon_new right after you create it

CREATE DATABASE DB_ALLOT_BOUCHON_NEW;
\connect db_allot_bouchon_new
 ...

This may help as well.

edited Aug 29 '19 at 09:23

answered Aug 29 '19 at 05:15

b0gusb

4,283
2
14
33

1

Thanks a lot i was totally blind and didnt see my mistake, lost 2 days.. – Anthony Attia Aug 29 '19 at 21:42
You're weIcome, glad I could help! – b0gusb Aug 30 '19 at 05:44

score 1 · Answer 2 · answered Aug 29 '19 at 05:58

I will suggest to set POSTGRES_DB environment variable in your Dockerfile so this will create DB and Container will treat this DB as a default DB.

POSTGRES_DB

This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.

Dockerfile


FROM postgres:latest
RUN localedef -i fr_FR -c -f UTF-8 -A /usr/share/locale/locale.alias fr_FR.UTF-8
ENV LANG fr_FR.utf8
ENV POSTGRES_DB="db_allot_bouchon_new"
# To set user and pass but better to pass at run time
ENV POSTGRES_USER="appdbuser"  
ENV POSTGRES_PASSWORD="123123"

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

In the above Dockerfile, this will create user appdbuser.

docker exec -it custom_psql_running bash -c " psql -U appdbuser"

So you will not need to mention these two lines in SQL script as Docker image will do that for you. So better to remove these line.

CREATE DATABASE DB_ALLOT_BOUCHON_NEW;
GRANT ALL PRIVILEGES ON DATABASE DB_ALLOT_BOUCHON_NEW TO postgres;

Warning:

scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue on with your scripts.

Thanks a lot for your advices and now it works ! Great ! – Anthony Attia Aug 29 '19 at 21:42 — Anthony Attia, Aug 29 '19 at 21:42

Remote control on my postgresql container docker works but can't see tables creates

2 Answers2

Dockerfile