I have a simple Dockerfile that sets up a database and then runs a .sql file. The image builds and container starts with no apparent issue, but PostgreSQL is missing data that should have been included. Here's the setup, logs, and problem---
Dockerfile
FROM postgres
ENV POSTGRES_USER postgres
ENV POSTGRES_PASSWORD postgres
ENV POSTGRES_DB exampledb
COPY db-setup.sql /docker-entrypoint-initdb.d/
I run it with docker build -t example-db ./
This is the copied .sql file
BEGIN;
CREATE TABLE IF NOT EXISTS users (
id integer NOT NULL,
name VARCHAR,
email VARCHAR,
password VARCHAR,
remember_token varchar(100),
company_name VARCHAR,
company_street VARCHAR,
company_city VARCHAR,
company_zipcode integer,
created_at timestamp,
updated_at timestamp
);
CREATE TABLE IF NOT EXISTS quotes (
id integer NOT NULL,
dot_number integer,
nbr_of_power_units integer,
value_of_power_units integer,
premium_amount integer,
premium_tax integer,
premium_total integer,
street VARCHAR,
city VARCHAR,
state VARCHAR,
zipcode integer,
driver1_name VARCHAR,
driver1_age integer,
driver2_name VARCHAR,
driver2_age integer,
driver3_name VARCHAR,
driver3_age integer,
driver4_name VARCHAR,
driver4_age integer,
driver5_name VARCHAR,
driver5_age integer,
created_at timestamp,
updated_at timestamp
);
INSERT INTO users (id, name) VALUES (1, 'test-user');
Lastly I start the container via docker run --name example-container -p 5432:5432 example-db
It appears to function correctly, outputing this log.
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... initdb: 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.
ok
Success. You can now start the database server using:
pg_ctl -D /var/lib/postgresql/data -l logfile start
waiting for server to start....2021-05-07 04:04:45.428 UTC [47] LOG: starting PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-05-07 04:04:45.429 UTC [47] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-05-07 04:04:45.435 UTC [48] LOG: database system was shut down at 2021-05-07 04:04:45 UTC
2021-05-07 04:04:45.439 UTC [47] LOG: database system is ready to accept connections
done
server started
CREATE DATABASE
/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/db-setup.sql
BEGIN
CREATE ROLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
waiting for server to shut down....2021-05-07 04:04:45.772 UTC [47] LOG: received fast shutdown request
2021-05-07 04:04:45.774 UTC [47] LOG: aborting any active transactions
2021-05-07 04:04:45.776 UTC [47] LOG: background worker "logical replication launcher" (PID 54) exited with exit code 1
2021-05-07 04:04:45.777 UTC [49] LOG: shutting down
2021-05-07 04:04:45.810 UTC [47] LOG: database system is shut down
done
server stopped
PostgreSQL init process complete; ready for start up.
2021-05-07 04:04:45.919 UTC [1] LOG: starting PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2021-05-07 04:04:45.919 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2021-05-07 04:04:45.920 UTC [1] LOG: listening on IPv6 address "::", port 5432
2021-05-07 04:04:45.923 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-05-07 04:04:45.929 UTC [84] LOG: database system was shut down at 2021-05-07 04:04:45 UTC
2021-05-07 04:04:45.935 UTC [1] LOG: database system is ready to accept connections
Pulling from that log there's an indication that the tables in the .sql file were created, and the test row inserted.
/usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/db-setup.sql
BEGIN
CREATE ROLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
However, when I bash into the Docker container docker exec -it example-container /bin/bash and then log into psql psql -U postgres
I can see the newly created database
------------+----------+----------+------------+------------+-----------------------
exampledb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
But, connecting to the db and running \dt yields- "Did not find any relations."
I am very confused and any help would be appreciated.