search

docker-library / postgres

Docker Official Image packaging for Postgres
http://www.postgresql.org
MIT License
2.14k stars 1.11k forks source link

Executing postgres init.sql (create table + copy) script in docker #1140

Closed PiotrBB closed 8 months ago

PiotrBB commented 8 months ago

I'm trying to initialize Postgres database using docker with table specified in init.sql with the following command:

docker volume rm dt
docker volume create dt
docker run --name postgres -e POSTGRES_PASSWORD=pwd -e POSTGRES_USER=usr -p 5432:5432 -v dt:/var/lib/postgresql/data -v $(pwd)/postgres/spotify-2023.csv:/mnt/data/spotify-2023.csv -v $(pwd)/postgres/init.sql:/docker-entrypoint-initdb.d/init.sql -d postgres

The SQL file I'm using:

-- init.sql
-- Create Table
CREATE TABLE IF NOT EXISTS spotify (
  track_name VARCHAR,
  ...);

  -- Insert data into Table
COPY spotify (
    track_name, 
    ...)
FROM '/mnt/data/spotify-2023.csv'
DELIMITER ','
CSV HEADER;

Below is the container log. It seems like Create table and COPY command are executed, but when I log in into psql, there are no tables created. File is mounted properly. There is also an information, that right after CREATE and COPY are executed, server shuts down for some reason.

2023-11-01 22:36:44 The files belonging to this database system will be owned by user "postgres".
2023-11-01 22:36:44 This user must also own the server process.
2023-11-01 22:36:44 
2023-11-01 22:36:44 The database cluster will be initialized with locale "en_US.utf8".
2023-11-01 22:36:44 The default database encoding has accordingly been set to "UTF8".
2023-11-01 22:36:44 The default text search configuration will be set to "english".
2023-11-01 22:36:44 
2023-11-01 22:36:44 Data page checksums are disabled.
2023-11-01 22:36:44 
2023-11-01 22:36:44 fixing permissions on existing directory /var/lib/postgresql/data ... ok
2023-11-01 22:36:44 creating subdirectories ... ok
2023-11-01 22:36:44 selecting dynamic shared memory implementation ... posix
2023-11-01 22:36:44 selecting default max_connections ... 100
2023-11-01 22:36:44 selecting default shared_buffers ... 128MB
2023-11-01 22:36:44 selecting default time zone ... Etc/UTC
2023-11-01 22:36:44 creating configuration files ... ok
2023-11-01 22:36:44 running bootstrap script ... ok
2023-11-01 22:36:44 performing post-bootstrap initialization ... ok
2023-11-01 22:36:44 syncing data to disk ... ok
2023-11-01 22:36:44 
2023-11-01 22:36:44 
2023-11-01 22:36:44 Success. You can now start the database server using:
2023-11-01 22:36:44 
2023-11-01 22:36:44     pg_ctl -D /var/lib/postgresql/data -l logfile start
2023-11-01 22:36:44 
2023-11-01 22:36:44 waiting for server to start....2023-11-01 21:36:44.587 UTC [48] LOG:  starting PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-11-01 22:36:44 2023-11-01 21:36:44.590 UTC [48] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-01 22:36:44 2023-11-01 21:36:44.599 UTC [51] LOG:  database system was shut down at 2023-11-01 21:36:44 UTC
2023-11-01 22:36:44 2023-11-01 21:36:44.602 UTC [48] LOG:  database system is ready to accept connections
2023-11-01 22:36:44  done
2023-11-01 22:36:44 server started
2023-11-01 22:36:44 CREATE DATABASE
2023-11-01 22:36:44 
2023-11-01 22:36:44 
2023-11-01 22:36:44 /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/init.sql
2023-11-01 22:36:44 CREATE TABLE
2023-11-01 22:36:44 COPY 898
2023-11-01 22:36:44 
2023-11-01 22:36:44 
2023-11-01 22:36:44 waiting for server to shut down....2023-11-01 21:36:44.790 UTC [48] LOG:  received fast shutdown request
2023-11-01 22:36:44 2023-11-01 21:36:44.795 UTC [48] LOG:  aborting any active transactions
2023-11-01 22:36:44 2023-11-01 21:36:44.797 UTC [48] LOG:  background worker "logical replication launcher" (PID 54) exited with exit code 1
2023-11-01 22:36:44 2023-11-01 21:36:44.799 UTC [49] LOG:  shutting down
2023-11-01 22:36:44 2023-11-01 21:36:44.805 UTC [49] LOG:  checkpoint starting: shutdown immediate
2023-11-01 22:36:44 2023-11-01 21:36:44.880 UTC [49] LOG:  checkpoint complete: wrote 945 buffers (5.8%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.011 s, sync=0.054 s, total=0.082 s; sync files=305, longest=0.002 s, average=0.001 s; distance=4396 kB, estimate=4396 kB; lsn=0/1935B18, redo lsn=0/1935B18
2023-11-01 22:36:44 2023-11-01 21:36:44.883 UTC [48] LOG:  database system is shut down
2023-11-01 22:36:44  done
2023-11-01 22:36:44 server stopped
2023-11-01 22:36:44 
2023-11-01 22:36:44 PostgreSQL init process complete; ready for start up.
2023-11-01 22:36:44 
2023-11-01 22:36:44 initdb: warning: enabling "trust" authentication for local connections
2023-11-01 22:36:44 initdb: hint: 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.
2023-11-01 22:36:44 2023-11-01 21:36:44.902 UTC [1] LOG:  starting PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-11-01 22:36:44 2023-11-01 21:36:44.902 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-11-01 22:36:44 2023-11-01 21:36:44.902 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-11-01 22:36:44 2023-11-01 21:36:44.904 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-11-01 22:36:44 2023-11-01 21:36:44.908 UTC [66] LOG:  database system was shut down at 2023-11-01 21:36:44 UTC
2023-11-01 22:36:44 2023-11-01 21:36:44.911 UTC [1] LOG:  database system is ready to accept connections

Any tips on how to fix it?