Managing PostgreSQL with pgAdmin: Complete Guide

pgAdmin is the most popular open-source administration and management tool for PostgreSQL. Available as both a web application and desktop client, pgAdmin provides a visual interface for database management, query execution, server monitoring, and administration tasks. This guide covers installation, configuration, and effective use of pgAdmin for daily PostgreSQL management.

Installation Options

pgAdmin as a Web Application (Recommended for Servers)

# Ubuntu/Debian
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" | sudo tee /etc/apt/sources.list.d/pgadmin4.list
sudo apt update
sudo apt install pgadmin4-web

# Run setup script
sudo /usr/pgadmin4/bin/setup-web.sh
# Enter admin email and password when prompted

pgAdmin with Docker

docker run -d \
    --name pgadmin \
    -p 5050:80 \
    -e PGADMIN_DEFAULT_EMAIL=admin@example.com \
    -e PGADMIN_DEFAULT_PASSWORD=SecureAdminPass \
    -e PGADMIN_CONFIG_ENHANCED_COOKIE_PROTECTION=True \
    -v pgadmin_data:/var/lib/pgadmin \
    dpage/pgadmin4:latest

Desktop Installation

# macOS
brew install --cask pgadmin4

# Windows — download from https://www.pgadmin.org/download/

Connecting to a PostgreSQL Server

After logging into pgAdmin, add your PostgreSQL servers:

1. Right-click "Servers" in the browser tree → "Register" → "Server"
2. On the "General" tab, give it a descriptive name
3. On the "Connection" tab, enter your server details:

Host: your-server-ip (or localhost for local)
Port: 5432
Maintenance database: postgres
Username: postgres (or your admin user)
Password: your-password
Save password: Yes (optional, stored encrypted)

SSH Tunnel Connection

For remote servers without public PostgreSQL access, use SSH tunneling:

1. On the "SSH Tunnel" tab, enable "Use SSH tunneling"
2. Enter your SSH host, port (22), and username
3. Choose authentication: password or identity file (SSH key)

pgAdmin will create an SSH tunnel and connect through it automatically.

Query Tool

The Query Tool is pgAdmin's SQL editor with powerful features:

• Syntax highlighting and auto-completion for SQL
• Explain/Analyze — visual query plan with graphical node display
• Data output grid — sortable, filterable results with export options
• Query history — access previously executed queries
• Macros — save frequently used queries for quick execution

-- Open Query Tool: right-click database → Query Tool
-- Or use keyboard shortcut: Alt+Shift+Q

-- Example: analyze a slow query visually
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT c.name, COUNT(o.id) as order_count, SUM(o.total) as revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '90 days'
GROUP BY c.id, c.name
HAVING SUM(o.total) > 1000
ORDER BY revenue DESC;

Click the "Explain" button (or F7) to see a graphical representation of the query plan, showing table scans, joins, sorts, and estimated costs.

Database Management Tasks

Creating a Database

-- Via pgAdmin GUI: right-click Databases → Create → Database
-- Or via SQL:
CREATE DATABASE myapp
    OWNER = app_user
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    TEMPLATE = template0;

Managing Tables

pgAdmin provides visual editors for creating and modifying tables, including:

• Column definitions with data types, defaults, and constraints
• Primary keys, foreign keys, unique constraints, and check constraints
• Indexes with type selection (B-tree, Hash, GIN, GiST, BRIN)
• Partitioning configuration (range, list, hash)
• Triggers and rules

Backup and Restore

pgAdmin integrates pg_dump and pg_restore through a visual interface:

1. Right-click a database → "Backup"
2. Choose format: Custom (recommended), Tar, Plain SQL, or Directory
3. Select objects to include/exclude
4. Set options: data only, schema only, include privileges
5. Click "Backup" to start

Server Monitoring Dashboard

pgAdmin includes a built-in dashboard showing real-time server metrics:

• Server activity — active connections, transactions per second, tuples in/out
• Database activity — per-database connection and transaction stats
• Session information — active queries, waiting queries, idle connections
• Lock information — current locks and blocked queries

Access the dashboard by clicking on a server or database in the browser tree — the dashboard panel appears automatically.

User and Role Management

-- Create application role via pgAdmin or SQL
CREATE ROLE app_readonly;
GRANT CONNECT ON DATABASE myapp TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;

-- Create user with role
CREATE USER reporting_user WITH PASSWORD 'ReportPass123!';
GRANT app_readonly TO reporting_user;

Useful pgAdmin Features

ERD Tool

Generate Entity-Relationship Diagrams from existing databases: right-click a database → "ERD For Database". This creates a visual diagram of all tables and their relationships.

Import/Export Data

Right-click a table → "Import/Export Data" to bulk load CSV, TSV, or binary files directly into a table with column mapping.

PSQL Tool

pgAdmin 7+ includes an integrated psql terminal for command-line access without leaving the browser.

Security Configuration

# For web deployment, configure HTTPS
# /etc/pgadmin/config_local.py
ENHANCED_COOKIE_PROTECTION = True
SESSION_COOKIE_SECURE = True
SESSION_COOKIE_HTTPONLY = True
LOGIN_BANNER = "Authorized users only"

# Restrict access with a reverse proxy (Nginx)
location /pgadmin/ {
    proxy_pass http://127.0.0.1:5050/;
    proxy_set_header Host $host;
    proxy_set_header X-Real-IP $remote_addr;
    allow 10.0.0.0/8;    # Internal network only
    deny all;
}

Best Practices

• Always use SSH tunnels or VPN when connecting to remote PostgreSQL servers
• Create read-only users for monitoring and reporting — avoid using superuser accounts
• Use the EXPLAIN Analyze feature regularly to understand query performance
• Store pgAdmin connection details in a server group hierarchy for organized management
• Enable two-factor authentication in pgAdmin for web deployments
• Regularly update pgAdmin to get security patches and new features