Docker Composed PostgreSQL Copy Functioning but No Relations Found in Database

Question

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.

Answer 1

Ah! Man...this one looks sneaky. I think your culprit is the BEGIN; at the start of your SQL init script. That starts a Postgres transaction. Essentially that means your script will create your tables, but since there isn't an END; statement, the Postgres transaction is left open.

When Docker aborts active transactions as a part of its startup process, that init script transaction is aborted. And when an in progress Postgres transaction is aborted, all the work inside that transaction block is reversed. I.e., you go back to the state your database was in when the init script started - empty.

Try removing the BEGIN; and running it from there!