# PostgreSQL + pgAdmin

> PostgreSQL database with pgAdmin web interface for easy database management and visualization.

- Category: Database Stacks
- Difficulty: beginner
- Components: postgresql, pgadmin4
- Tags: sql, postgres, admin-ui, database
- Source: https://docker.recipes/databases/postgres-pgadmin

## Overview

PostgreSQL is a powerful, open-source object-relational database system with over 35 years of active development, known for its reliability, ACID compliance, and advanced features like JSON/JSONB support, full-text search, and extensible type system. Unlike simpler databases, PostgreSQL excels at complex queries, data integrity, and mixed relational-document workloads, making it the preferred choice for enterprise applications and data-intensive systems. pgAdmin4 complements PostgreSQL by providing a comprehensive web-based administration platform specifically designed for PostgreSQL management. This combination eliminates the complexity of command-line database administration while maintaining full access to PostgreSQL's advanced features through an intuitive graphical interface that includes visual query building, performance monitoring, and automated backup tools. This stack is ideal for development teams, database administrators, and organizations that need robust database capabilities with user-friendly management tools. The pairing is particularly valuable for development environments where multiple team members need database access, production systems requiring regular monitoring and maintenance, and educational settings where visual database exploration enhances learning.

## Features

- ACID compliance with advanced transaction support and multi-version concurrency control
- JSON and JSONB data types for hybrid relational-document data models
- Web-based SQL editor with syntax highlighting and intelligent autocomplete
- Visual query builder for constructing complex queries without writing SQL
- Real-time database performance monitoring with query statistics and execution plans
- Built-in backup and restore functionality with scheduling capabilities
- Full-text search with ranking, highlighting, and multiple language support
- PostGIS compatibility for geospatial data operations and geographic queries

## Use cases

- Development teams building web applications requiring complex relational data with occasional document-style storage
- Small to medium businesses needing enterprise-grade database features with intuitive management tools
- Educational institutions teaching database concepts with hands-on PostgreSQL experience
- Startups migrating from SQLite or MySQL seeking better JSON support and query capabilities
- Data analysts requiring visual query building and result export functionality
- Organizations implementing microservices that need shared database infrastructure with centralized administration
- GIS applications leveraging PostGIS extensions for location-based services and mapping

## Prerequisites

- Minimum 1.5GB RAM for both services (PostgreSQL: 1GB, pgAdmin: 512MB)
- Ports 5432 and 5050 available on the host system
- Basic understanding of SQL concepts and database relationships
- Familiarity with environment variables for database credentials configuration
- Docker Engine 20.10+ with Docker Compose V2 support

## docker-compose.yml

```yaml
services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB: ${POSTGRES_DB}
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    networks:
      - postgres-network

  pgadmin:
    image: dpage/pgadmin4:latest
    container_name: pgadmin
    restart: unless-stopped
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_EMAIL}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
    volumes:
      - pgadmin_data:/var/lib/pgadmin
    ports:
      - "5050:80"
    depends_on:
      - postgres
    networks:
      - postgres-network

volumes:
  postgres_data:
  pgadmin_data:

networks:
  postgres-network:
    driver: bridge
```

## .env

```bash
# PostgreSQL Configuration
POSTGRES_USER=admin
POSTGRES_PASSWORD=changeme
POSTGRES_DB=myapp

# pgAdmin Configuration
PGADMIN_EMAIL=admin@example.com
PGADMIN_PASSWORD=changeme
```

## Usage notes

- Docs: https://www.postgresql.org/docs/ | pgAdmin: https://www.pgadmin.org/docs/
- Access pgAdmin at http://localhost:5050 with your PGADMIN_EMAIL/PASSWORD
- In pgAdmin, add server with hostname 'postgres', port 5432, and your POSTGRES_USER credentials
- Enable pg_stat_statements for query performance monitoring
- Backup: docker exec postgres pg_dumpall -U admin > backup.sql
- Data persisted in postgres_data volume - backup regularly

## Troubleshooting

- Connection refused on port 5432: Ensure PostgreSQL container is fully started before pgAdmin attempts connection, check docker logs postgres
- pgAdmin login fails with 'Invalid credentials': Verify PGADMIN_DEFAULT_EMAIL and PGADMIN_DEFAULT_PASSWORD environment variables are set correctly
- Cannot connect to server 'postgres' in pgAdmin: Use hostname 'postgres' (container name) not 'localhost' when adding server in pgAdmin interface
- PostgreSQL data lost after container restart: Confirm postgres_data volume is properly mounted and check volume permissions
- High memory usage warnings: Adjust PostgreSQL shared_buffers and work_mem settings via POSTGRES_INITDB_ARGS environment variable
- pgAdmin interface loads slowly: Increase pgAdmin container memory allocation or disable unnecessary pgAdmin features in configuration

