PostgreSQL + pgAdmin Docker Application

Complete Docker Compose application with PostgreSQL and pgAdmin, includes scripts for backup, restoration and management.

Note: This README has been cleaned of duplications. For detailed script documentation, see scripts/README.md.

🚀 Quick Start

1. Prepare configuration

# Copy environment variables
cp .env.example .env

# Update values if necessary (optional)
# nano .env

2. Start the application

# Option 1: With script
bash scripts/start.sh

# Option 2: With make
make start

# Option 3: With Docker Compose directly
docker-compose up -d

3. Access the services

• PostgreSQL: localhost:5432

  • User: dbuser (configurable in .env)
  • Database: app_db

• pgAdmin: http://localhost:5050/pgadmin

  • Email: admin@example.com
  • Password: configurable in .env

---

📋 Project Structure

.
├── docker-compose.yml      # Services configuration
├── .env                    # Environment variables (DO NOT commit)
├── .env.example            # Variables template (COMMIT this)
├── Makefile               # Useful commands
├── README.md              # This file
│
└── scripts/
    ├── README.md          # Scripts documentation
    ├── start.sh           # Start application
    ├── stop.sh            # Stop application
    ├── backup.sh          # Create backup
    ├── restore.sh         # Restore backup
    ├── status.sh          # View status and logs
    ├── init.sql           # Initial SQL
    └── backups/           # Backup directory
        └── backup_*.sql.gz

---

🛠️ Main Commands

Using Make (Recommended)

make help          # View all commands
make start         # Start
make stop          # Stop
make restart       # Restart
make status        # View status
make logs          # View logs in real time
make backup        # Create backup
make restore F=backups/backup_*.sql.gz  # Restore backup
make clean         # Clean everything

Using Scripts Directly

bash scripts/start.sh
bash scripts/stop.sh
bash scripts/status.sh -f
bash scripts/backup.sh
bash scripts/restore.sh backups/backup_*.sql.gz

Using Docker Compose

docker-compose up -d        # Start
docker-compose down         # Stop
docker-compose ps           # View status
docker-compose logs -f      # View logs

---

📦 Services

PostgreSQL 17.5 Alpine

• Image: postgres:17.5-alpine
• Container: postgres
• Port: 5432 (configurable)
• Volume: postgres-data
• Health Check: Enabled (pg_isready every 10s)

pgAdmin 4

• Image: dpage/pgadmin4:latest
• Container: pgadmin
• Port: 5050 (configurable)
• Volume: pgadmin-data
• Network: Connected to app-network

Shared Network

• Name: app-network
• Driver: bridge
• Allows communication between PostgreSQL and pgAdmin

---

🗄️ Backup Management

Create a Backup

make backup
# or
bash scripts/backup.sh

Select the database and backup type interactively:

Available databases:

  1) sales_db
  2) users_db
  3) analytics_db

Select database number (1-3): 2

Backup type:

  1) Full backup (with data)
  2) Schema only (without data)

Select backup type (1-2): 1

Generates a compressed file: backups/backup_users_db_full_YYYYMMDD_HHMMSS.sql.gz or backups/backup_users_db_schema_only_YYYYMMDD_HHMMSS.sql.gz

Restore a Backup

make restore F=backups/backup_users_db_full_20251103_120000.sql.gz
# or
bash scripts/restore.sh backups/backup_users_db_full_20251103_120000.sql.gz

Select the destination database interactively:

Available destination databases:

  1) sales_db
  2) analytics_db
  3) Create new database

Select destination database number (1-3): 3
Enter new database name: restored_users

⚠️ Warning: Overwrites the destination database. Confirmation is required.

View Available Backups

ls -lh scripts/backups/

# Show only full backups
ls -lh scripts/backups/*full*

# Show only schema only
ls -lh scripts/backups/*schema_only*

---

⚙️ Configuration

Environment File

# Database
DB_PORT=5432                    # Internal port
DB_EXTERNAL_PORT=5432           # Port on your machine
DB_NAME=app_db                  # Database name
DB_USERNAME=dbuser              # PostgreSQL user
DB_PASSWORD=secure_password_here # PostgreSQL password
TIMEZONE=UTC                    # Timezone

# pgAdmin
PGADMIN_EMAIL=admin@example.com         # Email
PGADMIN_PASSWORD=pgadmin_password_here  # Password
PGADMIN_PORT=5050                       # Access port

Important:

• Change passwords in production
• DO NOT commit .env to repository
• Use .env.example as reference

---

🔐 Security

Recommendations

1. ✅ Change default passwords
2. ✅ Do NOT expose ports unnecessarily
3. ✅ Use private networks in production
4. ✅ Perform backups regularly
5. ✅ Keep images updated

In Production

• Use Docker/Kubernetes secrets
• Implement SSL/TLS
• Configure firewall
• Monitor logs regularly
• Automate backups

---

🐛 Troubleshooting

Services don't start

# See detailed logs
docker-compose logs

# Check container status
docker ps -a

Error: "bind: address already in use"

# Ports are already in use. Options:
# 1. Change ports in .env
# 2. Stop the process using the port
# 3. Wait for it to release

# See what processes use the ports
lsof -i :5432
lsof -i :5050

Cannot connect to PostgreSQL

# Check if it's running
docker exec postgres pg_isready -U dbuser

# Check credentials in .env
# Check volumes are correct
docker inspect postgres

Backup/Restore fails

# Make sure PostgreSQL container is running
docker ps

# Check logs
docker-compose logs postgres

---

📖 Additional Documentation

See scripts/README.md for detailed script documentation.

---

🤝 Contributions

1. Update .env.example if you add new variables