PostgreSQL not creating tables when running on docker

Question

I am trying to run PostgreSQL on a linux container docker in my Windows server, but when I run it, it creates database, but there is no table and no data in it, while it should create all tables and add datas to it using that Actibook_latest.sql

Here's the code of Dockerfile

# Dockerfile
FROM postgres:9.4

RUN mkdir -p /tmp/psql_data/

COPY Actibook_latest.sql /tmp/psql_data/
COPY init_docker_postgres.sh /docker-entrypoint-initdb.d/

EXPOSE 5432

Here's the code of init_docker_postgres.sh

#!/bin/bash
# this script is run when the docker container is built
# it imports the base database structure and create the database for the tests
DATABASE_NAME="postgres"
DB_DUMP_LOCATION="/tmp/psql_data/Actibook_latest.sql"

echo "*** CREATING DATABASE ***"

psql "$DATABASE_NAME" < "$DB_DUMP_LOCATION";

echo "*** DATABASE CREATED! ***"

And here's the code of docker-compose

version: '2'

services:
 db:
    build: '.\Main Database Backup'
    environment:
      POSTGRES_DB: ${DB_POSTGRES_APP_DATABASE}
      POSTGRES_USER: ${DB_POSTGRES_APP_USER}
      POSTGRES_PASSWORD: ${DB_POSTGRES_APP_PASSW}
      PGDATA: /var/lib/postgresql/data/pgdata
    ports:
      - "5432:5432"
    restart: unless-stopped

Main Database Backup is the folder that contains that Dockerfile and init_docker_postgres.sh. Also that Actibook_latest.sql contains sql to create the tables, data, etc.

And when I run docker-compose up while other serives go up and running, here's what it shows to logs:

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/data/pgdata/base/1 ... ok
initializing pg_authid ... ok
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    postgres -D /var/lib/postgresql/data/pgdata
or
    pg_ctl -D /var/lib/postgresql/data/pgdata -l logfile start

waiting for server to start....LOG:  database system was shut down at 2018-11-14 16:18:07 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
 done
server started

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init_docker_postgres.sh
/usr/local/bin/docker-entrypoint.sh: /docker-entrypoint-initdb.d/init_docker_postgres.sh: /bin/bash^M: bad interpreter: No such file or directory
LOG:  database system was interrupted; last known up at 2018-11-14 16:18:16 UTC
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  record with zero length at 0/16A4780
LOG:  redo is not required
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

I'm thinking that there might be a firewall issue since its running perfectly in other servers that I have, the problem appears only in this one. So is that any chance that postgresql in this machine is preventing it?

Update

I tried to restart docker and then used these commands:

docker rm $(docker ps -a -q)
docker rmi $(docker images -a -q)
docker volume rm $(docker volume ls -q)

now its showing this error:

/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init_docker_postgres.sh
*** CREATING DATABASE ***
: No such file or directory/init_docker_postgres.sh: line 4: /tmp/psql_data/Actibook_latest.sql
/docker-entrypoint-initdb.d/init_docker_postgres.sh: line 4: $'\r': command not found
*** DATABASE CREATED! ***

and when I go to docker to check for file:

docker exec -it db bin
cd /tmp/psql_data 
ls

it shows that there exists Actibook_latest.sql

This isn't relevant to the question, but to anyone else having a similar problem: Check your databases!!! My postgres docker was working perfectly, I just didn't notice (at first) that my script was running in one database and I was querying another. — Noah, Aug 26 '22 at 19:25

score 0 · Accepted Answer · answered Nov 15 '18 at 09:29

0

It seems that your entrypoint init script was not working, make sure data_volume is empty before run your container.

This is because in docker-entrypoint.sh, at line 57, it checks your data volume, if it exists, it won't execute your init scrip.

answered Nov 15 '18 at 09:29

Siyu

11,187
4
43
55

I am runing `docker volume rm $(docker volume ls -q)` to remove all volumes. Should I run another command to make that? – TimeFrame Nov 15 '18 at 09:46
that should be fine – Siyu Nov 15 '18 at 09:50
can you please check the new error that its showing that the sql file does not exists, while there is that sql file inside that container. I updated the topic and in the end of the question you can find new error – TimeFrame Nov 15 '18 at 11:20
the second one seems like a windows line ending issue, maybe change the editor and retype it? the first one not really sure, try a `ls` before the `psql` command – Siyu Nov 15 '18 at 12:52
1

I used this answer https://stackoverflow.com/a/32912867/10612139 to parse that because somewhere that new line or something that was throwing that \r error. Thank you – TimeFrame Nov 15 '18 at 13:00

PostgreSQL not creating tables when running on docker

1 Answers1