Postgres + Docker Compose - Initialization scripts Demonstration

README.md

Postgres + Docker Compose - Initialization scripts Demonstration

This demonstration shows both .sql and .sh files being used to initialize a database programatically. The commands used in the .sh are not important, but they demonstrate the type of commands you might add to that file. The .sql file shows some simple commands you might use to stand up your postgres db.

I've attached a pgadmin4 container to the compose stack. This is just for ease of use, it connects to the postgres db and displays a lot of useful information quickly. Feel free to remove if not wanted.

Assumptions

I've gone ahead and declared these important variables:

POSTGRES_USER: webmaster
POSTGRES_DB: docker

Then inside of the .sh init script, there is a command to create a new user and grant privileges.

There's no reason why i've set them to these values, I am only demonstrating the variables available and highly pertinent.

Feel free to customize these variables, just ensure that if you change a db/table/user name, you update all references to that name.

File naming convention

i used a strange naming convention for the files, the docs say that the init scripts are ran in sorted order. I wanted the .sh file to be ran first so i forced it to be sorted first by adding a.* to the name.

Etc

Ensure that the .sh file is executable. You can check with ls -la while in the directory, and add if needed with chmod +x a.init-user-db.sh

a.init-user-db.sh

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
	CREATE USER docker;
	GRANT ALL PRIVILEGES ON DATABASE docker TO docker;
EOSQL

b.create_new_table.sql

CREATE SCHEMA IF NOT EXISTS note_tool AUTHORIZATION docker;

GRANT USAGE ON SCHEMA note_tool TO docker;

GRANT SELECT ON ALL TABLES IN SCHEMA note_tool TO docker;


DROP TABLE IF EXISTS note_tool.note_detail_config;

CREATE TABLE note_tool.note_detail_config (
    id serial,
    process_id int,
    key_name varchar(64),
    key_value varchar(64)
);

INSERT INTO note_tool.note_detail_config
(process_id, key_name, key_value)
VALUES ('56', '44,75,53,74,37', '27,81,25,65,01');

select * from note_tool.note_detail_config;

docker-compose.yml

version: '3.8'

services:

  postgres:
    image: postgres:16.1
    restart: unless-stopped
    environment:
      POSTGRES_USER: webmaster
      POSTGRES_PASSWORD: supersecurepassword
      POSTGRES_DB: docker
    volumes:
      - .:/docker-entrypoint-initdb.d
      - postgres-data:/var/lib/postgresql/data
    networks:
      - postgres_network

  pgadmin:
    image: dpage/pgadmin4:8.1
    restart: unless-stopped
    environment:
      PGADMIN_DEFAULT_EMAIL: webmaster@localhost.com
      PGADMIN_DEFAULT_PASSWORD: supersecurepassword
    volumes:
      - pgadmin-data:/var/lib/pgadmin
    ports:
      - 9080:80
    networks:
      - postgres_network

volumes:
  postgres-data:
  pgadmin-data:

networks:
  postgres_network: