-
-
Save onjin/2dd3cc52ef79069de1faa2dfd456c945 to your computer and use it in GitHub Desktop.
postgres: | |
image: postgres:9.4 | |
volumes: | |
- ./init.sql:/docker-entrypoint-initdb.d/init.sql |
create table sometable(id int); |
I'm running into an issue where my db init process is in fact running in the container, at least batching a bunch of CREATES and INSERTS of my dump data, but then is abruptly shut down and restarted.
strends_psql | CREATE DATABASE strends_psql | You are now connected to database "rivers" as user "usr". strends_psql | CREATE SCHEMA strends_psql | SET strends_psql | SET strends_psql | SET strends_psql | SET strends_psql | SET strends_psql | SET strends_psql | SET strends_psql | CREATE TABLE strends_psql | INSERT 0 4 strends_psql | strends_psql | strends_psql | 2019-04-29 17:43:06.633 UTC [41] LOG: received fast shutdown request strends_psql | waiting for server to shut down....2019-04-29 17:43:06.637 UTC [41] LOG: aborting any active transactions
When I get a shell into my db container, the database
rivers
was created but is empty, with no tables, relations, or data from my batched inserts.I had the same issue but after a few hours of trying, I found a working combination of docker-compose and init script. Btw, I read here that the container shutting down and restarting is (apparently) part of the initialization process. This is part of my initdb.sh:
#!/bin/bash psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$POSTGRES_DB" <<-EOSQL create schema if not exists $SCHEMA; create table $SCHEMA.todos ( id serial primary key, done boolean not null default false, task text not null, due timestamptz ); create role $ANON nologin; create role $AUTHENTICATOR noinherit login password '$POSTGRES_PASSWORD'; grant $ANON to $AUTHENTICATOR; EOSQL
This is part of my docker-compose.yml:
version: '3.7' services: db: image: postgres restart: always volumes: - ./initdb.sh:/docker-entrypoint-initdb.d/initdb.sh - ./pgdata:/var/lib/postgresql/data ports: - 8002:5432 environment: POSTGRES_PASSWORD: docker POSTGRES_DB: mydb SCHEMA: public ANON: web_anon AUTHENTICATOR: authenticator
If you put a CREATE DATABASE statement at the top of your actual SQL script, but don't supply POSTGRES_DB, it will use the POSETGRES_USER as the DB name so the tables don't go where they should.
If you supply both POSTGRES_DB and put a CREATE DATABASE statement at the top of the SQL script, you will get an error about the DB already existing since POSTGRES_DB will create the DB before the script runs. You can see this order of operations in the logs.
I think the real fix here is to:
- set POSTGRES_DB to be the name of the DB you want to create and don't CREATE TABLE in your script
- Add SQL scripts to volume per docs
I was facing the same issue of init scripts getting ignored.
I HAVE FOUND A SOLUTION if it helps anyone !!
version: '2' services: common_db: image: postgres:10.4 restart: always environment: - POSTGRES_USER=postgres - POSTGRES_PASSWORD=postgres123 - POSTGRES_MULTIPLE_DATABASES=db1,db2 volumes: - ./db.sql:/docker-entrypoint-initdb.d/db.sql - ./postgres-data:/var/lib/postgresql/data ports: - 5436:5432 networks: - app-network volumes: postgres-data: networks: app-network: driver: bridge`
Now as you can see I have
postgres-data:
volume mounted. If i remove this directory before starting docker-compose file things work !!.I that directory/volume is present the init scripts are ignored !! enjoy!!
GOD!!!
I struggled with this same issue and thought the only solution was to not use a sql file in the docker-entrypoint-initdb.d directory but actually the only problem was that I wasn't granting pivs to my user i was logging in as so the schemas were getting created just invisible to my user...
Don't have to do this
#!/bin/bash
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$POSTGRES_DB" <<-EOSQL
create schema if not exists $SCHEMA;
create table $SCHEMA.todos (
id serial primary key,
done boolean not null default false,
task text not null,
due timestamptz
);
create role $ANON nologin;
create role $AUTHENTICATOR noinherit login password '$POSTGRES_PASSWORD';
grant $ANON to $AUTHENTICATOR;
EOSQL
You can do this...
create_tables.sql
CREATE DATABASE audit_service;
GRANT ALL PRIVILEGES ON DATABASE audit_service to "postgres";
docker-compose.yml
db:
image: postgres:14.3-alpine
restart: unless-stopped
networks:
- my-appliance
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
ports:
- '5432:5432'
volumes:
- ./postgres/sql/create_tables.sql:/docker-entrypoint-initdb.d/create_tables.sql
- db_data:/var/lib/postgresql/data