Postgres-Docker

Dockerfile

# Use the official PostgreSQL image from the Docker Hub
FROM postgres:17.4

# Create the WAL archive directory and set permissions
RUN mkdir -p /mnt/server/archivedir && chown postgres:postgres /mnt/server/archivedir

# Copy the custom PostgreSQL configuration file (optional)
COPY postgres.conf /usr/share/postgresql/postgresql.conf.sample

# Expose the default PostgreSQL port
EXPOSE 5432

# Use the default command to run PostgreSQL
CMD ["postgres"]

Makefile

# Variables
IMAGE_NAME = my-postgres
CONTAINER_NAME = my-postgres-container
PORT = 5432
VOLUME_NAME = postgres-data
CONFIG_FILE = postgres.conf
DB_NAME = mydb
DB_USER = postgres
DB_PASSWORD ?= secret # Default password

# Prompt for the password if not provided
run:
	@echo "Enter PostgreSQL password (default: $(DB_PASSWORD)):"
	@read -p "Password: " input_password; \
	if [ -z "$$input_password" ]; then \
		input_password=$(DB_PASSWORD); \
	fi; \
	docker run -d \
	  --name $(CONTAINER_NAME) \
	  -p $(PORT):$(PORT) \
	  -v $(VOLUME_NAME):/var/lib/postgresql/data \
	  -v $(PWD)/$(CONFIG_FILE):/usr/share/postgresql/postgresql.conf.sample \
	  -e POSTGRES_USER=$(DB_USER) \
	  -e POSTGRES_PASSWORD=$$input_password \
	  -e POSTGRES_DB=$(DB_NAME) \
	  $(IMAGE_NAME)

# Build the Docker image
build:
	docker build -t $(IMAGE_NAME) .

# Connect to the PostgreSQL server using psql
connect:
	docker exec -it $(CONTAINER_NAME) psql -U $(DB_USER) -d $(DB_NAME)

# Start the PostgreSQL container
start:
	docker start $(CONTAINER_NAME)

# Stop the PostgreSQL container
stop:
	docker stop $(CONTAINER_NAME)

# Remove the PostgreSQL container and associated volume
clean:
	docker rm -f $(CONTAINER_NAME)
	docker volume rm $(VOLUME_NAME)

# Show logs from the PostgreSQL container
logs:
	docker logs $(CONTAINER_NAME)

# List all Docker containers
ps:
	docker ps -a

# Create a database
db.create:
	@echo "Enter the database name:"
	@read DB_NAME; \
	if [ -z "$$DB_NAME" ]; then \
		echo "Error: Database name cannot be empty!"; \
		exit 1; \
	else \
		docker exec -it $(CONTAINER_NAME) psql -U $(DB_USER) -d postgres -c "CREATE DATABASE $$DB_NAME;"; \
	fi


# Help command to display available targets
help:
	@echo "Available targets:"
	@echo "  build          - Build the Docker image"
	@echo "  run            - Run the PostgreSQL container"
	@echo "  connect        - Connect to the PostgreSQL server using psql"
	@echo "  start          - Start the PostgreSQL container"
	@echo "  stop           - Stop the PostgreSQL container"
	@echo "  clean          - Remove the PostgreSQL container and associated volume"
	@echo "  logs           - Show logs from the PostgreSQL container"
	@echo "  ps             - List all Docker containers"
	@echo "  db.create      - Create a database"
	@echo "  help           - Display this help message"

postgres.conf

# Set the working directory for PostgreSQL data
data_directory = '/var/lib/postgresql/data'


####  Connection Settings  ####
# Defines which IP addresses PostgreSQL will listen on. Use * to listen on all addresses or specify specific IPs.
listen_addresses = '*'
# The port PostgreSQL will listen on (default is 5432).
port = 5432
# The maximum number of concurrent connections allowed.
max_connections = 100
# The number of connections reserved for superusers.
superuser_reserved_connections = 3



####   Authentication Settings   ####
# Specifies the encryption method used for passwords (scram-sha-256 or md5).
password_encryption = scram-sha-256
# Whether to use SSL for encrypted connections.
ssl = off
# ssl_cert_file, ssl_key_file: Specify the paths to SSL certificate and key files.
# ssl_cert_file = '/etc/ssl/certs/ssl-cert.pem'
# ssl_key_file = '/etc/ssl/private/ssl-cert.key'


####   Memory Settings   #####
# Defines how much memory PostgreSQL should allocate for caching data.
shared_buffers = 128MB
# Memory available for each query operation.
work_mem = 4MB
# Memory used for maintenance tasks like VACUUM, CREATE INDEX, etc.
maintenance_work_mem = 64MB    


####   Write-Ahead Logging (WAL)   ####
# Defines the amount of information written to the WAL. (minimal, replica, logical)
wal_level = replica
# Enables WAL archiving for replication or point-in-time recovery.
archive_mode = on
# Specifies the command to archive WAL files.
archive_command = 'cp %p /mnt/server/archivedir/%f'


####   Logging Settings   ####
# Defines which SQL statements are logged (none, ddl, mod, all).
log_statement = 'all'
# If set to on, logs the duration of each completed SQL statement.
log_duration = on
# log_directory, log_filename: Controls where logs are stored.
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%a.log'
log_file_mode = 0644


####   Autovacuum Settings   ####
# Enables or disables automatic vacuuming of tables.
autovacuum = on
# Specifies how much of a table must change before it is vacuumed.
autovacuum_vacuum_scale_factor = 0.2
# Similar to vacuum, but for analyzing tables.
autovacuum_analyze_scale_factor = 0.1



####   Checkpoint Settings   ####
# The maximum amount of time between automatic WAL checkpoints.
checkpoint_timeout = 5min
# Controls how quickly the checkpoint should complete.
checkpoint_completion_target = 0.9

Postgres Docker Setup

This gist contains a Dockerized setup for running a Postgres server with custom configuration and optional password authentication. The setup includes a Dockerfile, postgres.conf, and a Makefile for easy management of the Postgres container. Save all files to a repository and use the make file to run the commands. run make help to see the list of available commands.