Create User/Database in Docker Postgres Script

Prerequisites:

• Bash: Bash should be available as a default shell on most Linux systems.
• Docker: Ensure Docker is installed and running on your system.

Create a Bash Script:

• Name the file create_user_db.sh.
• Open a text editor and create a new file.

Write the Script:

#!/bin/bash

# Set environment variables for Docker Postgres image and container name
POSTGRES_IMAGE="postgres:latest"
CONTAINER_NAME="my_postgres_container"

# Create the container if it doesn't exist
docker run --name "$CONTAINER_NAME" -d "$POSTGRES_IMAGE"

# Wait for the container to be ready
docker wait "$CONTAINER_NAME"

# Get the container's IP address
CONTAINER_IP=$(docker inspect --format '{{ .NetworkSettings.IPAddress }}' "$CONTAINER_NAME")

# Execute the psql command to create the user and database
docker exec -it "$CONTAINER_NAME" psql -h "$CONTAINER_IP" -U postgres -c "
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydatabase OWNER myuser;
"

Explanation:

• SQL commands:
  • CREATE USER myuser WITH PASSWORD 'mypassword';: Creates a new user named myuser with the password mypassword.
  • CREATE DATABASE mydatabase OWNER myuser;: Creates a new database named mydatabase and assigns ownership to the myuser user.
• Execute psql command:
  • docker exec: Executes a command inside the running container.
  • -it: Opens an interactive terminal session inside the container.
  • psql: Connects to the Postgres database using the specified IP address, username, and password.
  • -c: Executes the specified SQL command.
• Get container IP address:
• Wait for container readiness:
• Create container:
  • docker run: Creates a new Docker container using the specified image.
  • --name: Assigns the specified name to the container.
  • -d: Runs the container in detached mode (background).
• Environment variables:
  • POSTGRES_IMAGE: Sets the Docker image for Postgres (e.g., postgres:latest).
  • CONTAINER_NAME: Sets the name for the Docker container.
• Shebang line (#!/bin/bash): This line specifies that the script should be executed using Bash.

• Run the script: ./create_user_db.sh
• Make the script executable: chmod +x create_user_db.sh

Method 1: Using a Bash Script

#!/bin/bash

POSTGRES_IMAGE="postgres:latest"
CONTAINER_NAME="my_postgres_container"

# Create the container if it doesn't exist
docker run --name "$CONTAINER_NAME" -d "$POSTGRES_IMAGE"

# Wait for the container to be ready
docker wait "$CONTAINER_NAME"

# Get the container's IP address
CONTAINER_IP=$(docker inspect --format '{{ .NetworkSettings.IPAddress }}' "$CONTAINER_NAME")

# Execute the psql command to create the user and database
docker exec -it "$CONTAINER_NAME" psql -h "$CONTAINER_IP" -U postgres -c "
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydatabase OWNER myuser;
"

1. Set environment variables: Define the Docker image and container name.
2. Create container: Start a Postgres container in detached mode.
3. Wait for readiness: Ensure the container is ready before proceeding.
4. Get IP address: Obtain the container's IP address.
5. Execute psql command: Connect to the Postgres instance and execute SQL commands to create the user and database.

Method 2: Using a Docker Compose File

version: '3.7'

services:
  postgres:
    image: postgres:latest
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydatabase
    volumes:   
      - postgres_data:/var/lib/postgresql/data   

1. Define service: Specify the Postgres service.
2. Set environment variables: Set environment variables for the user, password, and database.
3. Mount volume: Mount a volume to persist data.

Method 3: Using a Docker Entrypoint Script

FROM postgres:latest

COPY entrypoint.sh /docker-entrypoint.sh
RUN chmod +x /docker-entrypoint.sh
ENTRYPOINT ["/docker-entrypoint.sh"]

# entrypoint.sh
#!/bin/bash

# Create the user and database using psql commands
psql -U postgres -c "
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydatabase OWNER myuser;
"

1. Create Dockerfile: Define a Dockerfile to customize the image.
2. Copy entrypoint script: Add an entrypoint script to the image.
3. Set permissions: Make the script executable.
4. Set entrypoint: Set the script as the container's entrypoint.

Key Points:

• Testing: Thoroughly test your scripts to ensure they create the user and database correctly.
• Customization: Tailor the SQL commands to your specific requirements, such as granting privileges or setting database options.
• Security: Use strong passwords and consider using secrets management for sensitive information.
• Choose the method: Select the method that best suits your workflow and preferences.

Alternative Methods for Creating Users and Databases in Docker Postgres

While the methods described in the previous responses provide effective approaches, here are some alternative options you can consider:

Docker Compose with Environment Variables:

Advantages:

• Can be used for multiple services in a single Docker Compose file.
• Simplifies configuration management.

Example:

version: '3.7'

services:
  postgres:
    image: postgres:latest
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydatabase   

Docker Compose with a Custom Entrypoint:

• Can be used for complex initialization tasks.
• Provides more granular control over the container's startup process.

version: '3.7'

services:
  postgres:
    image: postgres:latest
    entrypoint: ["/docker-entrypoint.sh"]
    volumes:
      - postgres_data:/var/lib/postgresql/data

# docker-entrypoint.sh
#!/bin/bash

# Create the user and database using psql commands
psql -U postgres -c "
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydatabase OWNER myuser;
"

• Can be used for other initialization tasks besides user and database creation.
• Provides more flexibility for customizing the image.

FROM postgres:latest

COPY entrypoint.sh /docker-entrypoint.sh
RUN chmod +x /docker-entrypoint.sh
ENTRYPOINT ["/docker-entrypoint.sh"]

# entrypoint.sh
#!/bin/bash

# Create the user and database using psql commands
psql -U postgres -c "
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydatabase OWNER myuser;
"

• Can be used to create multiple Postgres instances with different configurations within a single Docker network.

version: '3.7'

services:
  postgres1:
    image: postgres:latest
    environment:
      POSTGRES_USER: myuser1
      POSTGRES_PASSWORD: mypassword1
      POSTGRES_DB: mydatabase1
    networks:
      - my-network

  postgres2:
    image: postgres:latest
    environment:
      POSTGRES_USER: myuser2
      POSTGRES_PASSWORD: mypassword2
      POSTGRES_DB: mydatabase2
    networks:
      - my-network

Using a Database Migration Tool:

• Can be used to automate database creation and user setup.
• Provides a structured approach for managing database changes.

version: '3.7'

services:
  postgres:
    image: postgres:latest
    command: -c "PGPASSWORD=mypassword psql -U postgres -c 'CREATE DATABASE mydatabase;'"
    volumes:
      - postgres_data:/var/lib/postgresql/data

# migrations/1_create_user.sql
CREATE USER myuser WITH PASSWORD 'mypassword';