pgAdmin Cheatsheet¶

pgAdmin - PostgreSQL Administration and Development Platform

pgAdmin is the most popular and feature-rich Open Source administration and development platform for PostgreSQL. It provides a web-based interface for managing PostgreSQL databases with comprehensive tools for database administration, query development, and monitoring.

Installation¶

Ubuntu/Debian Installation¶

# Install pgAdmin 4 from official repository
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'

# Update package list
sudo apt update

# Install pgAdmin 4 (web mode)
sudo apt install pgadmin4-web

# Install pgAdmin 4 (desktop mode)
sudo apt install pgadmin4-desktop

# Configure web mode
sudo /usr/pgadmin4/bin/setup-web.sh

# The setup script will:
# - Create a virtual environment
# - Configure Apache/Nginx
# - Set up initial user account
# - Configure SSL (optional)

# Access pgAdmin web interface
# http://your-server-ip/pgadmin4
# or
# https://your-server-ip/pgadmin4 (if SSL configured)

CentOS/RHEL Installation¶

# Install PostgreSQL repository
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install pgAdmin 4
sudo yum install pgadmin4-web

# Configure web mode
sudo /usr/pgadmin4/bin/setup-web.sh

# For CentOS 8/RHEL 8, use dnf
sudo dnf install pgadmin4-web

Docker Installation¶

# Run pgAdmin 4 with Docker
docker run -p 80:80 \
    -e 'PGADMIN_DEFAULT_EMAIL=admin@example.com' \
    -e 'PGADMIN_DEFAULT_PASSWORD=password' \
    -d dpage/pgadmin4

# Run with persistent storage
docker run -p 80:80 \
    -e 'PGADMIN_DEFAULT_EMAIL=admin@example.com' \
    -e 'PGADMIN_DEFAULT_PASSWORD=password' \
    -v pgadmin-data:/var/lib/pgadmin \
    -d dpage/pgadmin4

# Docker Compose setup
cat > docker-compose.yml << EOF
version: '3.8'
services:
  postgres:
    image: postgres:13
    container_name: postgres
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: testuser
      POSTGRES_PASSWORD: testpass
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    container_name: pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@example.com
      PGADMIN_DEFAULT_PASSWORD: password
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    ports:
      - "8080:80"
    volumes:
      - pgadmin-data:/var/lib/pgadmin
    depends_on:
      - postgres

volumes:
  postgres-data:
  pgadmin-data:
EOF

docker-compose up -d

# Access pgAdmin at http://localhost:8080

Windows Installation¶

# Download pgAdmin 4 installer from https://www.pgadmin.org/download/
# Run the installer as administrator

# Alternative: Install via Chocolatey
choco install pgadmin4

# Alternative: Install via winget
winget install PostgreSQL.pgAdmin

# pgAdmin will be available in Start Menu
# Default installation path: C:\Program Files\pgAdmin 4\

macOS Installation¶

# Install via Homebrew
brew install --cask pgadmin4

# Alternative: Download from official website
# https://www.pgadmin.org/download/pgadmin-4-macos/

# pgAdmin will be available in Applications folder

Configuration¶

Initial Setup¶

# Web mode initial configuration
sudo /usr/pgadmin4/bin/setup-web.sh

# During setup, you'll be prompted for:
# - Email address (admin user)
# - Password
# - Apache/Nginx configuration
# - SSL certificate setup (optional)

# Configuration files location:
# /usr/pgadmin4/web/config_local.py (local configuration)
# /usr/pgadmin4/web/config.py (default configuration)

Configuration File¶

# config_local.py - Local configuration overrides

# Server mode (True for web, False for desktop)
SERVER_MODE = True

# Debug mode (disable in production)
DEBUG = False

# Data directory
DATA_DIR = '/var/lib/pgadmin'

# Log file location
LOG_FILE = '/var/log/pgadmin4/pgadmin4.log'

# Database configuration
SQLITE_PATH = '/var/lib/pgadmin/pgadmin4.db'

# Session configuration
SESSION_DB_PATH = '/var/lib/pgadmin/sessions'
SESSION_COOKIE_NAME = 'pga4_session'
SESSION_COOKIE_DOMAIN = None
SESSION_COOKIE_SECURE = False
SESSION_COOKIE_HTTPONLY = True
SESSION_EXPIRATION_TIME = 1  # hours

# Security
SECRET_KEY = 'your-secret-key-here'
SECURITY_PASSWORD_SALT = 'your-password-salt-here'

# CSRF protection
WTF_CSRF_ENABLED = True
WTF_CSRF_TIME_LIMIT = None

# Mail server configuration (for password reset)
MAIL_SERVER = 'localhost'
MAIL_PORT = 587
MAIL_USE_SSL = False
MAIL_USE_TLS = True
MAIL_USERNAME = 'your-email@example.com'
MAIL_PASSWORD = 'your-email-password'
MAIL_DEFAULT_SENDER = 'pgadmin@example.com'

# LDAP authentication
AUTHENTICATION_SOURCES = ['internal', 'ldap']
LDAP_SERVER_URI = 'ldap://ldap.example.com:389'
LDAP_BASE_DN = 'dc=example,dc=com'
LDAP_USERNAME_ATTRIBUTE = 'uid'
LDAP_SEARCH_BASE_DN = 'ou=users,dc=example,dc=com'
LDAP_SEARCH_FILTER = '(objectClass=person)'

# Logging configuration
CONSOLE_LOG_LEVEL = 'WARNING'
FILE_LOG_LEVEL = 'INFO'

# Query tool configuration
DEFAULT_QUERY_TOOL_TAB_SIZE = 4
QUERY_HISTORY_MAX_COUNT = 20

# Browser configuration
DEFAULT_BINARY_PATHS = {
    'pg_dump': '/usr/bin/pg_dump',
    'pg_restore': '/usr/bin/pg_restore',
    'psql': '/usr/bin/psql'
}

Apache Configuration¶

# /etc/apache2/sites-available/pgadmin4.conf

<VirtualHost *:80>
    ServerName pgadmin.example.com
    DocumentRoot /usr/pgadmin4/web

    WSGIDaemonProcess pgadmin processes=1 threads=25 python-home=/usr/pgadmin4/venv
    WSGIProcessGroup pgadmin
    WSGIScriptAlias / /usr/pgadmin4/web/pgAdmin4.wsgi

    <Directory /usr/pgadmin4/web>
        WSGIProcessGroup pgadmin
        WSGIApplicationGroup %{GLOBAL}
        Require all granted
    </Directory>

    # Static files
    Alias /static /usr/pgadmin4/web/static
    <Directory /usr/pgadmin4/web/static>
        Require all granted
    </Directory>

    # Logging
    ErrorLog ${APACHE_LOG_DIR}/pgadmin4_error.log
    CustomLog ${APACHE_LOG_DIR}/pgadmin4_access.log combined
</VirtualHost>

# Enable SSL (recommended)
<VirtualHost *:443>
    ServerName pgadmin.example.com
    DocumentRoot /usr/pgadmin4/web

    SSLEngine on
    SSLCertificateFile /path/to/certificate.crt
    SSLCertificateKeyFile /path/to/private.key

    WSGIDaemonProcess pgadmin-ssl processes=1 threads=25 python-home=/usr/pgadmin4/venv
    WSGIProcessGroup pgadmin-ssl
    WSGIScriptAlias / /usr/pgadmin4/web/pgAdmin4.wsgi

    <Directory /usr/pgadmin4/web>
        WSGIProcessGroup pgadmin-ssl
        WSGIApplicationGroup %{GLOBAL}
        Require all granted
    </Directory>

    Alias /static /usr/pgadmin4/web/static
    <Directory /usr/pgadmin4/web/static>
        Require all granted
    </Directory>
</VirtualHost>

Nginx Configuration¶

# /etc/nginx/sites-available/pgadmin4

server {
    listen 80;
    server_name pgadmin.example.com;
    return 301 https://$server_name$request_uri;
}

server {
    listen 443 ssl http2;
    server_name pgadmin.example.com;

    ssl_certificate /path/to/certificate.crt;
    ssl_certificate_key /path/to/private.key;
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers ECDHE-RSA-AES256-GCM-SHA512:DHE-RSA-AES256-GCM-SHA512:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384;
    ssl_prefer_server_ciphers off;

    location / {
        proxy_set_header Host $http_host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_pass http://unix:/tmp/pgadmin4.sock;
    }

    location /static/ {
        alias /usr/pgadmin4/web/static/;
        expires 1y;
        add_header Cache-Control "public, immutable";
    }
}

Server Management¶

Adding PostgreSQL Servers¶

-- Via pgAdmin Interface:
-- 1. Right-click "Servers" in browser tree
-- 2. Select "Create" > "Server..."
-- 3. Fill in connection details:

-- General Tab:
-- Name: My PostgreSQL Server
-- Server group: Servers (default)
-- Comments: Production database server

-- Connection Tab:
-- Host name/address: localhost (or IP address)
-- Port: 5432
-- Maintenance database: postgres
-- Username: postgres
-- Password: your_password
-- Save password: Yes (optional)

-- SSL Tab:
-- SSL mode: Prefer (or Require for production)
-- Client certificate: /path/to/client.crt
-- Client key: /path/to/client.key
-- Root certificate: /path/to/ca.crt

-- Advanced Tab:
-- DB restriction: database1,database2 (optional)
-- Host address: (leave blank usually)
-- Service: (for Windows service connections)

Server Connection Examples¶

# Programmatic server registration (servers.json)
{
    "Servers": {
        "1": {
            "Name": "Local PostgreSQL",
            "Group": "Servers",
            "Host": "localhost",
            "Port": 5432,
            "MaintenanceDB": "postgres",
            "Username": "postgres",
            "SSLMode": "prefer",
            "Comment": "Local development server"
        },
        "2": {
            "Name": "Production Server",
            "Group": "Production",
            "Host": "prod.example.com",
            "Port": 5432,
            "MaintenanceDB": "postgres",
            "Username": "admin",
            "SSLMode": "require",
            "SSLCert": "/path/to/client.crt",
            "SSLKey": "/path/to/client.key",
            "SSLRootCert": "/path/to/ca.crt",
            "Comment": "Production database server"
        }
    }
}

Connection Troubleshooting¶

# Test PostgreSQL connection from command line
psql -h localhost -p 5432 -U postgres -d postgres

# Check PostgreSQL service status
sudo systemctl status postgresql

# Check PostgreSQL configuration
sudo -u postgres psql -c "SHOW config_file;"
sudo -u postgres psql -c "SHOW hba_file;"

# Common connection issues:

# 1. PostgreSQL not running
sudo systemctl start postgresql

# 2. Wrong port
sudo -u postgres psql -c "SHOW port;"

# 3. Authentication failure - check pg_hba.conf
sudo vim /etc/postgresql/13/main/pg_hba.conf

# Example pg_hba.conf entries:
# local   all             postgres                                peer
# local   all             all                                     md5
# host    all             all             127.0.0.1/32            md5
# host    all             all             ::1/128                 md5

# 4. Connection not allowed - check postgresql.conf
sudo vim /etc/postgresql/13/main/postgresql.conf

# Set listen_addresses:
# listen_addresses = 'localhost'  # for local only
# listen_addresses = '*'          # for all addresses

# Restart PostgreSQL after configuration changes
sudo systemctl restart postgresql

Database Operations¶

Creating Databases¶

-- Via SQL in Query Tool:
CREATE DATABASE myapp
    WITH 
    OWNER = myuser
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1
    TEMPLATE = template0;

-- Create database with specific settings
CREATE DATABASE analytics
    WITH 
    OWNER = analyst
    ENCODING = 'UTF8'
    LC_COLLATE = 'C'
    LC_CTYPE = 'C'
    TABLESPACE = pg_default
    CONNECTION LIMIT = 50
    TEMPLATE = template0;

-- Via pgAdmin Interface:
-- 1. Right-click server name
-- 2. Select "Create" > "Database..."
-- 3. Fill in database details:
--    - Database: myapp
--    - Owner: myuser
--    - Encoding: UTF8
--    - Template: template0
--    - Collation: en_US.UTF-8
--    - Character type: en_US.UTF-8
--    - Connection limit: -1 (unlimited)

Database Management¶

-- List all databases
SELECT datname, datowner, encoding, datcollate, datctype 
FROM pg_database;

-- Show database size
SELECT 
    datname as database_name,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Show database connections
SELECT 
    datname,
    numbackends as active_connections,
    datconnlimit as connection_limit
FROM pg_stat_database;

-- Rename database
ALTER DATABASE old_name RENAME TO new_name;

-- Change database owner
ALTER DATABASE myapp OWNER TO new_owner;

-- Set database connection limit
ALTER DATABASE myapp CONNECTION LIMIT 100;

-- Drop database
DROP DATABASE IF EXISTS myapp;

-- Terminate connections to database before dropping
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'myapp'
  AND pid <> pg_backend_pid();

Schema Operations¶

-- Create schema
CREATE SCHEMA sales AUTHORIZATION sales_user;

-- Create schema with specific privileges
CREATE SCHEMA reporting;
GRANT USAGE ON SCHEMA reporting TO analyst;
GRANT CREATE ON SCHEMA reporting TO analyst;

-- List schemas
SELECT schema_name, schema_owner 
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema', 'pg_catalog', 'pg_toast');

-- Show schema privileges
SELECT 
    schemaname,
    schemaowner,
    schemaacl
FROM pg_namespace n
JOIN pg_user u ON n.nspowner = u.usesysid
WHERE nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast');

-- Drop schema
DROP SCHEMA IF EXISTS old_schema CASCADE;

-- Rename schema
ALTER SCHEMA old_name RENAME TO new_name;

-- Change schema owner
ALTER SCHEMA sales OWNER TO new_owner;

Query Tool¶

Basic Query Operations¶

-- Query Tool Features:
-- 1. Syntax highlighting
-- 2. Auto-completion
-- 3. Query execution
-- 4. Result export
-- 5. Query history
-- 6. Explain plans
-- 7. Query profiling

-- Basic SELECT queries
SELECT * FROM users;

SELECT id, username, email, created_at 
FROM users 
WHERE created_at > '2023-01-01'
ORDER BY created_at DESC
LIMIT 10;

-- Complex queries with JOINs
SELECT 
    u.username,
    u.email,
    COUNT(p.id) as post_count,
    MAX(p.created_at) as last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.active = true
GROUP BY u.id, u.username, u.email
HAVING COUNT(p.id) > 0
ORDER BY post_count DESC;

-- Window functions
SELECT 
    username,
    email,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as user_number,
    RANK() OVER (ORDER BY created_at) as user_rank,
    LAG(created_at) OVER (ORDER BY created_at) as prev_user_date
FROM users;

-- Common Table Expressions (CTEs)
WITH active_users AS (
    SELECT user_id, COUNT(*) as activity_count
    FROM user_activities
    WHERE activity_date > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY user_id
    HAVING COUNT(*) > 10
),
user_stats AS (
    SELECT 
        u.id,
        u.username,
        au.activity_count,
        CASE 
            WHEN au.activity_count > 100 THEN 'Very Active'
            WHEN au.activity_count > 50 THEN 'Active'
            ELSE 'Moderate'
        END as activity_level
    FROM users u
    JOIN active_users au ON u.id = au.user_id
)
SELECT * FROM user_stats ORDER BY activity_count DESC;

Query Execution and Analysis¶

-- Explain query execution plan
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- Explain with detailed analysis
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT u.*, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

-- Show query execution time
\timing on
SELECT COUNT(*) FROM large_table;
\timing off

-- Analyze table statistics
ANALYZE users;

-- Show table statistics
SELECT 
    schemaname,
    tablename,
    n_tup_ins as inserts,
    n_tup_upd as updates,
    n_tup_del as deletes,
    n_live_tup as live_tuples,
    n_dead_tup as dead_tuples,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Query Tool Features¶

-- Query Tool Shortcuts:
-- F5 or Ctrl+Enter: Execute query
-- F7: Explain query
-- Shift+F7: Explain analyze
-- Ctrl+Shift+C: Comment/uncomment
-- Ctrl+A: Select all
-- Ctrl+/: Toggle comment
-- Ctrl+Space: Auto-complete
-- Ctrl+Shift+F: Format SQL

-- Query history access:
-- 1. Click "History" tab in Query Tool
-- 2. View previously executed queries
-- 3. Re-execute or edit historical queries

-- Export results:
-- 1. Execute query
-- 2. Click "Download" button in results
-- 3. Choose format: CSV, Excel, JSON, etc.

-- Save queries:
-- 1. Write query in editor
-- 2. Click "Save" button or Ctrl+S
-- 3. Provide filename and location
-- 4. Access saved queries from "Files" tab

-- Query profiling:
-- 1. Enable auto-explain in postgresql.conf:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = 1000
-- auto_explain.log_analyze = on
-- auto_explain.log_buffers = on

-- 2. Check PostgreSQL logs for execution plans

Advanced Query Features¶

-- Parameterized queries (using pgAdmin variables)
-- Define variable: $$user_id$$ = 123
SELECT * FROM users WHERE id = $$user_id$$;

-- Multiple result sets
SELECT 'User Count' as metric, COUNT(*) as value FROM users
UNION ALL
SELECT 'Post Count' as metric, COUNT(*) as value FROM posts
UNION ALL
SELECT 'Active Users' as metric, COUNT(*) as value FROM users WHERE last_login > CURRENT_DATE - INTERVAL '7 days';

-- JSON operations
SELECT 
    id,
    username,
    profile->>'first_name' as first_name,
    profile->>'last_name' as last_name,
    profile->'preferences'->>'theme' as theme
FROM users
WHERE profile ? 'preferences';

-- Array operations
SELECT 
    id,
    username,
    tags,
    array_length(tags, 1) as tag_count,
    'postgresql' = ANY(tags) as has_postgresql_tag
FROM posts
WHERE tags IS NOT NULL;

-- Full-text search
SELECT 
    id,
    title,
    content,
    ts_rank(to_tsvector('english', title || ' ' || content), 
            plainto_tsquery('english', 'postgresql database')) as rank
FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ 
      plainto_tsquery('english', 'postgresql database')
ORDER BY rank DESC;

Schema Management¶

Table Operations¶

-- Create table with various column types
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price > 0),
    category_id INTEGER REFERENCES categories(id),
    tags TEXT[],
    metadata JSONB,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Add constraints
ALTER TABLE products 
ADD CONSTRAINT uk_products_name UNIQUE (name);

ALTER TABLE products 
ADD CONSTRAINT chk_products_price CHECK (price BETWEEN 0.01 AND 999999.99);

-- Create indexes
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_active ON products(is_active) WHERE is_active = true;
CREATE INDEX idx_products_tags ON products USING GIN(tags);
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- Full-text search index
CREATE INDEX idx_products_search ON products USING GIN(to_tsvector('english', name || ' ' || COALESCE(description, '')));

-- Partial index
CREATE INDEX idx_products_expensive ON products(price) WHERE price > 1000;

-- Composite index
CREATE INDEX idx_products_category_price ON products(category_id, price);

View Management¶

-- Create simple view
CREATE VIEW active_products AS
SELECT id, name, price, category_id
FROM products
WHERE is_active = true;

-- Create complex view with JOINs
CREATE VIEW product_summary AS
SELECT 
    p.id,
    p.name,
    p.price,
    c.name as category_name,
    COUNT(r.id) as review_count,
    AVG(r.rating) as avg_rating
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE p.is_active = true
GROUP BY p.id, p.name, p.price, c.name;

-- Create materialized view
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT 
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_sales,
    AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '2 years'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW monthly_sales;

-- Create updatable view
CREATE VIEW user_profiles AS
SELECT id, username, email, first_name, last_name
FROM users
WHERE is_active = true;

-- Make view updatable with rules
CREATE OR REPLACE RULE user_profiles_update AS
ON UPDATE TO user_profiles
DO INSTEAD
UPDATE users SET
    username = NEW.username,
    email = NEW.email,
    first_name = NEW.first_name,
    last_name = NEW.last_name,
    updated_at = CURRENT_TIMESTAMP
WHERE id = OLD.id;

Function and Procedure Management¶

-- Create function
CREATE OR REPLACE FUNCTION calculate_age(birth_date DATE)
RETURNS INTEGER AS $$
BEGIN
    RETURN EXTRACT(YEAR FROM AGE(birth_date));
END;
$$ LANGUAGE plpgsql;

-- Create function with multiple parameters
CREATE OR REPLACE FUNCTION get_user_stats(user_id INTEGER)
RETURNS TABLE(
    total_posts INTEGER,
    total_comments INTEGER,
    avg_post_length NUMERIC,
    last_activity TIMESTAMP
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        (SELECT COUNT(*)::INTEGER FROM posts WHERE posts.user_id = get_user_stats.user_id),
        (SELECT COUNT(*)::INTEGER FROM comments WHERE comments.user_id = get_user_stats.user_id),
        (SELECT AVG(LENGTH(content))::NUMERIC FROM posts WHERE posts.user_id = get_user_stats.user_id),
        (SELECT MAX(GREATEST(
            COALESCE((SELECT MAX(created_at) FROM posts WHERE posts.user_id = get_user_stats.user_id), '1970-01-01'::TIMESTAMP),
            COALESCE((SELECT MAX(created_at) FROM comments WHERE comments.user_id = get_user_stats.user_id), '1970-01-01'::TIMESTAMP)
        )));
END;
$$ LANGUAGE plpgsql;

-- Create stored procedure (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE update_product_prices(
    category_name TEXT,
    price_increase_percent DECIMAL
)
LANGUAGE plpgsql
AS $$
DECLARE
    affected_count INTEGER;
BEGIN
    UPDATE products 
    SET price = price * (1 + price_increase_percent / 100),
        updated_at = CURRENT_TIMESTAMP
    FROM categories c
    WHERE products.category_id = c.id 
    AND c.name = category_name;

    GET DIAGNOSTICS affected_count = ROW_COUNT;

    RAISE NOTICE 'Updated % products in category %', affected_count, category_name;

    COMMIT;
END;
$$;

-- Create trigger function
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER update_products_modtime
    BEFORE UPDATE ON products
    FOR EACH ROW
    EXECUTE FUNCTION update_modified_column();

Sequence Management¶

-- Create sequence
CREATE SEQUENCE order_number_seq
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999999
    CACHE 1;

-- Use sequence in table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number INTEGER DEFAULT nextval('order_number_seq'),
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2)
);

-- Alter sequence
ALTER SEQUENCE order_number_seq RESTART WITH 5000;
ALTER SEQUENCE order_number_seq INCREMENT BY 5;

-- Show sequence information
SELECT 
    schemaname,
    sequencename,
    start_value,
    min_value,
    max_value,
    increment_by,
    cycle,
    cache_size,
    last_value
FROM pg_sequences;

-- Reset sequence to match table data
SELECT setval('order_number_seq', (SELECT MAX(order_number) FROM orders));

User Management¶

Creating Users and Roles¶

-- Create user
CREATE USER john_doe WITH PASSWORD 'secure_password';

-- Create user with specific attributes
CREATE USER admin_user WITH 
    PASSWORD 'admin_password'
    CREATEDB
    CREATEROLE
    LOGIN
    VALID UNTIL '2024-12-31';

-- Create role (group)
CREATE ROLE developers;
CREATE ROLE analysts;
CREATE ROLE managers;

-- Create role with inheritance
CREATE ROLE app_users INHERIT;

-- Grant role to user
GRANT developers TO john_doe;
GRANT analysts TO jane_smith;

-- Create user and assign to role
CREATE USER developer1 WITH PASSWORD 'dev_password';
GRANT developers TO developer1;

Managing Privileges¶

-- Database privileges
GRANT CONNECT ON DATABASE myapp TO developers;
GRANT CREATE ON DATABASE myapp TO developers;

-- Schema privileges
GRANT USAGE ON SCHEMA public TO developers;
GRANT CREATE ON SCHEMA public TO developers;

-- Table privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analysts;

-- Grant privileges on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO developers;

-- Sequence privileges
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO developers;

-- Function privileges
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO developers;

-- Column-level privileges
GRANT SELECT (id, username, email) ON users TO analysts;
GRANT UPDATE (email, last_login) ON users TO app_users;

-- Revoke privileges
REVOKE DELETE ON users FROM developers;
REVOKE ALL PRIVILEGES ON DATABASE myapp FROM old_user;

Row Level Security (RLS)¶

-- Enable RLS on table
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Create policy for users to see only their own posts
CREATE POLICY user_posts_policy ON posts
    FOR ALL
    TO app_users
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

-- Create policy for managers to see all posts
CREATE POLICY manager_posts_policy ON posts
    FOR ALL
    TO managers
    USING (true);

-- Create policy for different operations
CREATE POLICY posts_select_policy ON posts
    FOR SELECT
    TO public
    USING (status = 'published' OR user_id = current_setting('app.current_user_id')::INTEGER);

CREATE POLICY posts_insert_policy ON posts
    FOR INSERT
    TO app_users
    WITH CHECK (user_id = current_setting('app.current_user_id')::INTEGER);

CREATE POLICY posts_update_policy ON posts
    FOR UPDATE
    TO app_users
    USING (user_id = current_setting('app.current_user_id')::INTEGER)
    WITH CHECK (user_id = current_setting('app.current_user_id')::INTEGER);

-- Set application context
SELECT set_config('app.current_user_id', '123', true);

-- Disable RLS for superuser
ALTER TABLE posts FORCE ROW LEVEL SECURITY;

User Management via pgAdmin¶

User Management via pgAdmin Interface:

Creating Users:
1. Expand server in browser tree
2. Right-click "Login/Group Roles"
3. Select "Create" > "Login/Group Role..."
4. General tab:
   - Name: username
   - Comments: User description
5. Definition tab:
   - Password: user password
   - Password expiration: optional date
6. Privileges tab:
   - Can login: Yes
   - Superuser: No (usually)
   - Create roles: No (usually)
   - Create databases: No (usually)
   - Inherit rights from parent roles: Yes
   - Can initiate streaming replication: No (usually)
7. Membership tab:
   - Add user to existing roles
8. Parameters tab:
   - Set user-specific parameters
9. Security tab:
   - Set security labels

Managing Privileges:
1. Right-click database/schema/table
2. Select "Properties"
3. Go to "Security" tab
4. Click "+" to add new privilege
5. Select grantee (user/role)
6. Select privileges to grant
7. Set grant options if needed

Backup and Restore¶

Database Backup¶

-- Via pgAdmin Interface:
-- 1. Right-click database
-- 2. Select "Backup..."
-- 3. Configure backup options:

-- General Tab:
-- Filename: /path/to/backup.sql
-- Format: Custom, Tar, Plain (SQL), Directory
-- Compression: 0-9 (for custom format)
-- Encoding: UTF8

-- Dump Options Tab:
-- Sections:
-- - Pre-data (schema)
-- - Data
-- - Post-data (indexes, triggers, etc.)

-- Type of objects:
-- - Only data
-- - Only schema
-- - Data and schema

-- Don't save:
-- - Owner
-- - Privilege
-- - Tablespace
-- - Unlogged table data

-- Queries:
-- - Use Column Inserts
-- - Use Insert Commands
-- - Include CREATE DATABASE statement

-- Disable:
-- - Trigger (during restore)
-- - $ quoting (use standard SQL strings)

-- Miscellaneous:
-- - Verbose messages
-- - Force double quotes on identifiers
-- - Use SET SESSION AUTHORIZATION

Command Line Backup¶

# Full database backup
pg_dump -h localhost -U postgres -d myapp -f myapp_backup.sql

# Custom format backup (recommended)
pg_dump -h localhost -U postgres -d myapp -Fc -f myapp_backup.dump

# Compressed backup
pg_dump -h localhost -U postgres -d myapp -Fc -Z 9 -f myapp_backup.dump

# Schema only backup
pg_dump -h localhost -U postgres -d myapp -s -f myapp_schema.sql

# Data only backup
pg_dump -h localhost -U postgres -d myapp -a -f myapp_data.sql

# Specific tables backup
pg_dump -h localhost -U postgres -d myapp -t users -t posts -f tables_backup.sql

# Exclude specific tables
pg_dump -h localhost -U postgres -d myapp -T logs -T temp_data -f myapp_no_logs.sql

# Directory format backup (parallel)
pg_dump -h localhost -U postgres -d myapp -Fd -j 4 -f myapp_backup_dir

# All databases backup
pg_dumpall -h localhost -U postgres -f all_databases.sql

# Roles and tablespaces only
pg_dumpall -h localhost -U postgres -r -f roles.sql
pg_dumpall -h localhost -U postgres -t -f tablespaces.sql

Database Restore¶

# Restore from SQL file
psql -h localhost -U postgres -d myapp -f myapp_backup.sql

# Restore from custom format
pg_restore -h localhost -U postgres -d myapp myapp_backup.dump

# Restore with parallel jobs
pg_restore -h localhost -U postgres -d myapp -j 4 myapp_backup.dump

# Restore specific tables
pg_restore -h localhost -U postgres -d myapp -t users -t posts myapp_backup.dump

# Restore schema only
pg_restore -h localhost -U postgres -d myapp -s myapp_backup.dump

# Restore data only
pg_restore -h localhost -U postgres -d myapp -a myapp_backup.dump

# Restore to different database
pg_restore -h localhost -U postgres -d newapp myapp_backup.dump

# Restore with clean (drop existing objects)
pg_restore -h localhost -U postgres -d myapp -c myapp_backup.dump

# Restore with create database
pg_restore -h localhost -U postgres -C -d postgres myapp_backup.dump

# List contents of backup file
pg_restore -l myapp_backup.dump

# Restore specific items using list file
pg_restore -h localhost -U postgres -d myapp -L restore_list.txt myapp_backup.dump

Automated Backup Script¶

#!/bin/bash
# PostgreSQL backup script

# Configuration
DB_HOST="localhost"
DB_PORT="5432"
DB_USER="postgres"
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

# Create backup directory
mkdir -p $BACKUP_DIR

# Function to backup single database
backup_database() {
    local db_name=$1
    local backup_file="$BACKUP_DIR/${db_name}_${DATE}.dump"

    echo "Backing up database: $db_name"
    pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -Fc -Z 9 -d $db_name -f $backup_file

    if [ $? -eq 0 ]; then
        echo "Backup successful: $backup_file"

        # Calculate file size
        size=$(du -h $backup_file | cut -f1)
        echo "Backup size: $size"
    else
        echo "Backup failed for database: $db_name"
        return 1
    fi
}

# Get list of databases (excluding system databases)
databases=$(psql -h $DB_HOST -p $DB_PORT -U $DB_USER -t -c "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname NOT IN ('postgres', 'template0', 'template1');")

# Backup each database
for db in $databases; do
    backup_database $db
done

# Backup global objects (roles, tablespaces)
echo "Backing up global objects..."
pg_dumpall -h $DB_HOST -p $DB_PORT -U $DB_USER -g -f "$BACKUP_DIR/globals_${DATE}.sql"

# Remove old backups
echo "Cleaning up old backups..."
find $BACKUP_DIR -name "*.dump" -mtime +$RETENTION_DAYS -delete
find $BACKUP_DIR -name "*.sql" -mtime +$RETENTION_DAYS -delete

echo "Backup process completed at $(date)"

# Log backup completion
echo "$(date): PostgreSQL backup completed" >> /var/log/postgresql_backup.log

Monitoring¶

Server Activity Monitoring¶

-- Current connections
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    client_port,
    backend_start,
    state,
    query_start,
    LEFT(query, 50) as query_preview
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

-- Long running queries
SELECT 
    pid,
    usename,
    query_start,
    now() - query_start as duration,
    state,
    LEFT(query, 100) as query_preview
FROM pg_stat_activity
WHERE state = 'active'
AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

-- Blocking queries
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

-- Database statistics
SELECT 
    datname,
    numbackends as connections,
    xact_commit as commits,
    xact_rollback as rollbacks,
    blks_read,
    blks_hit,
    round((blks_hit::float / (blks_hit + blks_read)) * 100, 2) as cache_hit_ratio,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1', 'postgres');

Table and Index Statistics¶

-- Table statistics
SELECT 
    schemaname,
    tablename,
    n_live_tup as live_tuples,
    n_dead_tup as dead_tuples,
    n_tup_ins as inserts,
    n_tup_upd as updates,
    n_tup_del as deletes,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count,
    analyze_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- Table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Index usage statistics
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Unused indexes
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Performance Monitoring¶

-- Slow queries (requires pg_stat_statements extension)
-- Enable in postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top queries by total time
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Top queries by average time
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

-- Buffer cache hit ratio
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    round((sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read))) * 100, 2) as ratio
FROM pg_statio_user_tables;

-- Checkpoint statistics
SELECT 
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint,
    buffers_clean,
    maxwritten_clean,
    buffers_backend,
    buffers_backend_fsync,
    buffers_alloc
FROM pg_stat_bgwriter;

pgAdmin Dashboard¶

pgAdmin Dashboard Features:

Server Dashboard:
- Server activity (connections, transactions)
- Database statistics
- Session statistics
- Lock statistics
- Prepared transactions
- Configuration parameters

Database Dashboard:
- Database statistics
- Table statistics
- Index statistics
- Schema statistics

Monitoring Tools:
1. Server Activity:
   - View active sessions
   - Kill sessions
   - Monitor locks and blocking queries

2. System Statistics:
   - CPU usage
   - Memory usage
   - Disk I/O
   - Network activity

3. Query Tool:
   - Execute monitoring queries
   - View execution plans
   - Analyze performance

4. Graphs and Charts:
   - Real-time monitoring
   - Historical data
   - Custom dashboards

Accessing Dashboards:
1. Select server/database in browser tree
2. Click "Dashboard" tab
3. View real-time statistics
4. Refresh automatically or manually
5. Export data for analysis

Security¶

SSL Configuration¶

-- Check SSL status
SELECT name, setting FROM pg_settings WHERE name LIKE '%ssl%';

-- Show current connections with SSL info
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    ssl,
    ssl_version,
    ssl_cipher
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid);

-- Force SSL connections in pg_hba.conf
-- hostssl all all 0.0.0.0/0 md5
-- host all all 0.0.0.0/0 reject

Authentication Configuration¶

# pg_hba.conf configuration examples

# Local connections
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 local connections
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.1.0/24          md5

# IPv6 local connections
host    all             all             ::1/128                 md5

# SSL connections only
hostssl all             all             0.0.0.0/0               md5

# LDAP authentication
host    all             all             0.0.0.0/0               ldap ldapserver=ldap.example.com ldapprefix="uid=" ldapsuffix=",ou=users,dc=example,dc=com"

# Certificate authentication
hostssl all             all             0.0.0.0/0               cert

# GSSAPI authentication
host    all             all             0.0.0.0/0               gss

# Reject specific users
host    all             baduser         0.0.0.0/0               reject

# Database-specific rules
host    production      prod_users      192.168.1.0/24          md5
host    development     dev_users       192.168.2.0/24          md5

Audit Logging¶

-- Enable logging in postgresql.conf
-- log_destination = 'stderr,csvlog'
-- logging_collector = on
-- log_directory = 'pg_log'
-- log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
-- log_rotation_age = 1d
-- log_rotation_size = 100MB

-- Log connections and disconnections
-- log_connections = on
-- log_disconnections = on

-- Log all statements
-- log_statement = 'all'

-- Log slow queries
-- log_min_duration_statement = 1000  # milliseconds

-- Log lock waits
-- log_lock_waits = on

-- Log checkpoints
-- log_checkpoints = on

-- Install pgaudit extension for detailed auditing
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure pgaudit
-- pgaudit.log = 'all'
-- pgaudit.log_catalog = off
-- pgaudit.log_parameter = on
-- pgaudit.log_relation = on
-- pgaudit.log_statement_once = off

-- Session-level auditing
SET pgaudit.log = 'read,write';
SELECT * FROM sensitive_table;
SET pgaudit.log = '';

-- Object-level auditing
SELECT pgaudit.log_catalog = on;

Data Encryption¶

-- Enable data encryption at rest
-- Requires file system level encryption or
-- PostgreSQL with TDE (Transparent Data Encryption)

-- Column-level encryption using pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt sensitive data
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash TEXT NOT NULL,
    ssn BYTEA  -- encrypted column
);

-- Insert encrypted data
INSERT INTO users (username, email, password_hash, ssn)
VALUES (
    'john_doe',
    'john@example.com',
    crypt('password', gen_salt('bf')),
    pgp_sym_encrypt('123-45-6789', 'encryption_key')
);

-- Query encrypted data
SELECT 
    id,
    username,
    email,
    pgp_sym_decrypt(ssn, 'encryption_key') as ssn_decrypted
FROM users
WHERE username = 'john_doe';

-- Verify password
SELECT * FROM users 
WHERE username = 'john_doe' 
AND password_hash = crypt('password', password_hash);

Security Best Practices¶

-- 1. Remove default databases and users
DROP DATABASE IF EXISTS template0;
-- Note: Don't actually drop template0, it's needed

-- 2. Change default passwords
ALTER USER postgres PASSWORD 'strong_password_here';

-- 3. Create application-specific users
CREATE USER app_user WITH PASSWORD 'app_password';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- 4. Limit connection attempts
-- In postgresql.conf:
-- max_connections = 100
-- superuser_reserved_connections = 3

-- 5. Enable SSL
-- ssl = on
-- ssl_cert_file = 'server.crt'
-- ssl_key_file = 'server.key'
-- ssl_ca_file = 'ca.crt'

-- 6. Configure firewall rules
-- Allow only necessary IP addresses
-- Block default PostgreSQL port (5432) from internet

-- 7. Regular security updates
-- Keep PostgreSQL and pgAdmin updated
-- Monitor security advisories

-- 8. Backup encryption
-- pg_dump with encryption:
-- pg_dump mydb | gpg --cipher-algo AES256 --compress-algo 1 --symmetric --output mydb.sql.gpg

-- 9. Monitor failed login attempts
-- log_statement = 'all'
-- Check logs for authentication failures

-- 10. Use connection pooling
-- Configure pgBouncer or similar
-- Limit direct database connections

Customization¶

pgAdmin Themes and Appearance¶

# Custom CSS for pgAdmin
# Create custom.css in pgAdmin web directory

/* Dark theme customization */
.pgadmin-body {
    background-color: #2d3748;
    color: #e2e8f0;
}

.navbar {
    background-color: #1a202c !important;
}

.browser-tree {
    background-color: #2d3748;
    color: #e2e8f0;
}

.sql-editor {
    background-color: #1a202c;
    color: #e2e8f0;
}

/* Query result grid styling */
.slick-header-column {
    background-color: #4a5568;
    color: #e2e8f0;
}

.slick-cell {
    background-color: #2d3748;
    color: #e2e8f0;
    border-color: #4a5568;
}

/* Custom button styling */
.btn-primary {
    background-color: #3182ce;
    border-color: #3182ce;
}

.btn-primary:hover {
    background-color: #2c5aa0;
    border-color: #2c5aa0;
}

Custom Dashboards¶

# config_local.py - Custom dashboard configuration

# Custom dashboard widgets
CUSTOM_DASHBOARDS = {
    'server': [
        {
            'name': 'Custom Server Stats',
            'sql': '''
                SELECT 
                    'Active Connections' as metric,
                    count(*) as value
                FROM pg_stat_activity 
                WHERE state = 'active'
                UNION ALL
                SELECT 
                    'Database Size' as metric,
                    pg_size_pretty(sum(pg_database_size(datname))::bigint) as value
                FROM pg_database
                WHERE datistemplate = false
            ''',
            'refresh': 30  # seconds
        }
    ],
    'database': [
        {
            'name': 'Table Statistics',
            'sql': '''
                SELECT 
                    tablename,
                    n_live_tup as "Live Tuples",
                    n_dead_tup as "Dead Tuples",
                    last_vacuum as "Last Vacuum"
                FROM pg_stat_user_tables
                ORDER BY n_live_tup DESC
                LIMIT 10
            ''',
            'refresh': 60
        }
    ]
}

# Custom menu items
CUSTOM_MENU_ITEMS = [
    {
        'name': 'Performance Monitor',
        'url': '/performance',
        'icon': 'fa-chart-line'
    },
    {
        'name': 'Custom Reports',
        'url': '/reports',
        'icon': 'fa-file-alt'
    }
]

Query Tool Customization¶

// custom.js - Query tool enhancements

$(document).ready(function() {
    // Auto-format SQL on Ctrl+Shift+F
    $(document).keydown(function(e) {
        if (e.ctrlKey && e.shiftKey && e.keyCode === 70) {
            formatSQL();
        }
    });

    // Custom SQL snippets
    var sqlSnippets = {
        'sel': 'SELECT * FROM table_name WHERE condition;',
        'ins': 'INSERT INTO table_name (column1, column2) VALUES (value1, value2);',
        'upd': 'UPDATE table_name SET column1 = value1 WHERE condition;',
        'del': 'DELETE FROM table_name WHERE condition;',
        'cte': 'WITH cte_name AS (\n    SELECT column1, column2\n    FROM table_name\n    WHERE condition\n)\nSELECT * FROM cte_name;'
    };

    // Add snippet functionality
    function insertSnippet(snippet) {
        var editor = $('.sql-editor textarea');
        if (editor.length) {
            var cursorPos = editor[0].selectionStart;
            var textBefore = editor.val().substring(0, cursorPos);
            var textAfter = editor.val().substring(cursorPos);
            editor.val(textBefore + sqlSnippets[snippet] + textAfter);
        }
    }

    // Add custom toolbar buttons
    $('.query-tool-toolbar').append(`
        <div class="btn-group" role="group">
            <button type="button" class="btn btn-secondary dropdown-toggle" data-toggle="dropdown">
                Snippets
            </button>
            <div class="dropdown-menu">
                <a class="dropdown-item" href="#" onclick="insertSnippet('sel')">SELECT</a>
                <a class="dropdown-item" href="#" onclick="insertSnippet('ins')">INSERT</a>
                <a class="dropdown-item" href="#" onclick="insertSnippet('upd')">UPDATE</a>
                <a class="dropdown-item" href="#" onclick="insertSnippet('del')">DELETE</a>
                <a class="dropdown-item" href="#" onclick="insertSnippet('cte')">CTE</a>
            </div>
        </div>
    `);
});

function formatSQL() {
    // SQL formatting logic
    var editor = $('.sql-editor textarea');
    if (editor.length) {
        var sql = editor.val();
        // Basic SQL formatting
        sql = sql.replace(/\bSELECT\b/gi, '\nSELECT');
        sql = sql.replace(/\bFROM\b/gi, '\nFROM');
        sql = sql.replace(/\bWHERE\b/gi, '\nWHERE');
        sql = sql.replace(/\bORDER BY\b/gi, '\nORDER BY');
        sql = sql.replace(/\bGROUP BY\b/gi, '\nGROUP BY');
        sql = sql.replace(/\bHAVING\b/gi, '\nHAVING');
        editor.val(sql.trim());
    }
}

Best Practices¶

Performance Best Practices¶

-- 1. Use connection pooling
-- Configure pgBouncer or similar connection pooler
-- Limit concurrent connections to pgAdmin

-- 2. Optimize queries in Query Tool
-- Use EXPLAIN ANALYZE for query optimization
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM large_table WHERE indexed_column = 'value';

-- 3. Limit result sets
-- Use LIMIT for large queries
SELECT * FROM large_table ORDER BY created_at DESC LIMIT 100;

-- 4. Use appropriate data types
-- Avoid TEXT when VARCHAR(n) is sufficient
-- Use appropriate numeric types (INTEGER vs BIGINT)

-- 5. Index optimization
-- Create indexes for frequently queried columns
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- 6. Regular maintenance
-- Schedule VACUUM and ANALYZE
VACUUM ANALYZE users;

-- 7. Monitor query performance
-- Use pg_stat_statements extension
-- Identify and optimize slow queries

-- 8. Partition large tables
-- Use table partitioning for very large tables
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2023 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Security Best Practices¶

# pgAdmin security configuration

# 1. Use HTTPS only
FORCE_SSL = True

# 2. Strong session configuration
SESSION_COOKIE_SECURE = True
SESSION_COOKIE_HTTPONLY = True
SESSION_COOKIE_SAMESITE = 'Strict'

# 3. CSRF protection
WTF_CSRF_ENABLED = True

# 4. Content Security Policy
CSP_DEFAULT_SRC = "'self'"
CSP_SCRIPT_SRC = "'self' 'unsafe-inline'"
CSP_STYLE_SRC = "'self' 'unsafe-inline'"

# 5. Hide server information
SERVER_MODE = True
DEBUG = False

# 6. Limit login attempts
MAX_LOGIN_ATTEMPTS = 5
LOGIN_ATTEMPT_TIMEOUT = 300  # 5 minutes

# 7. Regular backups
BACKUP_ENCRYPTION = True
BACKUP_RETENTION_DAYS = 30

# 8. Audit logging
AUDIT_LOG_ENABLED = True
AUDIT_LOG_LEVEL = 'INFO'

# 9. User session timeout
SESSION_EXPIRATION_TIME = 8  # hours

# 10. Database connection security
REQUIRE_SSL_DB_CONNECTIONS = True

Maintenance Best Practices¶

#!/bin/bash
# pgAdmin maintenance script

# 1. Regular updates
echo "Checking for pgAdmin updates..."
apt list --upgradable | grep pgadmin

# 2. Log rotation
echo "Rotating pgAdmin logs..."
logrotate /etc/logrotate.d/pgadmin4

# 3. Database cleanup
echo "Cleaning up pgAdmin database..."
sqlite3 /var/lib/pgadmin/pgadmin4.db "DELETE FROM session WHERE expiry < datetime('now');"

# 4. Backup pgAdmin configuration
echo "Backing up pgAdmin configuration..."
cp /usr/pgadmin4/web/config_local.py /backup/pgadmin/config_local_$(date +%Y%m%d).py

# 5. Monitor disk space
echo "Checking disk space..."
df -h /var/lib/pgadmin
df -h /var/log/pgadmin4

# 6. Check service status
echo "Checking pgAdmin service status..."
systemctl status apache2
systemctl status postgresql

# 7. Verify SSL certificates
echo "Checking SSL certificate expiry..."
openssl x509 -in /etc/ssl/certs/pgadmin.crt -noout -dates

# 8. Performance monitoring
echo "Checking pgAdmin performance..."
ps aux | grep pgadmin
netstat -tulpn | grep :80

# 9. Security audit
echo "Running security checks..."
# Check for unauthorized access attempts
grep "authentication failed" /var/log/pgadmin4/pgadmin4.log | tail -10

# 10. Cleanup temporary files
echo "Cleaning up temporary files..."
find /tmp -name "pgadmin*" -mtime +7 -delete

echo "Maintenance completed at $(date)"

Backup and Recovery Best Practices¶

#!/bin/bash
# Comprehensive pgAdmin backup strategy

BACKUP_DIR="/backup/pgadmin"
DATE=$(date +%Y%m%d_%H%M%S)

# 1. Backup pgAdmin configuration
echo "Backing up pgAdmin configuration..."
mkdir -p $BACKUP_DIR/config
cp -r /usr/pgadmin4/web/config* $BACKUP_DIR/config/

# 2. Backup pgAdmin database
echo "Backing up pgAdmin database..."
cp /var/lib/pgadmin/pgadmin4.db $BACKUP_DIR/pgadmin4_$DATE.db

# 3. Backup user preferences and settings
echo "Backing up user data..."
mkdir -p $BACKUP_DIR/userdata
cp -r /var/lib/pgadmin/storage $BACKUP_DIR/userdata/

# 4. Backup SSL certificates
echo "Backing up SSL certificates..."
mkdir -p $BACKUP_DIR/ssl
cp /etc/ssl/certs/pgadmin.* $BACKUP_DIR/ssl/ 2>/dev/null || true

# 5. Export server definitions
echo "Exporting server definitions..."
# This would require custom script to export from pgAdmin database

# 6. Create restore script
cat > $BACKUP_DIR/restore_$DATE.sh << 'EOF'
#!/bin/bash
# pgAdmin restore script

BACKUP_DIR=$(dirname $0)

echo "Restoring pgAdmin configuration..."
cp -r $BACKUP_DIR/config/* /usr/pgadmin4/web/

echo "Restoring pgAdmin database..."
cp $BACKUP_DIR/pgadmin4_*.db /var/lib/pgadmin/pgadmin4.db

echo "Restoring user data..."
cp -r $BACKUP_DIR/userdata/storage /var/lib/pgadmin/

echo "Setting permissions..."
chown -R pgadmin:pgadmin /var/lib/pgadmin
chmod 600 /var/lib/pgadmin/pgadmin4.db

echo "Restarting services..."
systemctl restart apache2

echo "Restore completed!"
EOF

chmod +x $BACKUP_DIR/restore_$DATE.sh

# 7. Compress backup
echo "Compressing backup..."
tar -czf $BACKUP_DIR/pgadmin_backup_$DATE.tar.gz -C $BACKUP_DIR config userdata ssl pgadmin4_$DATE.db restore_$DATE.sh

# 8. Remove old backups
echo "Cleaning up old backups..."
find $BACKUP_DIR -name "pgadmin_backup_*.tar.gz" -mtime +30 -delete

echo "pgAdmin backup completed: $BACKUP_DIR/pgadmin_backup_$DATE.tar.gz"

Summary¶

pgAdmin is a comprehensive PostgreSQL administration and development platform that provides powerful tools for database management, query development, and monitoring. This cheatsheet covers all aspects of pgAdmin from installation to advanced administration.

Key Strengths: - Comprehensive Interface: Complete PostgreSQL administration capabilities - Cross-Platform: Available on Windows, macOS, and Linux - Web-Based: Accessible from any modern browser - Rich Query Tool: Advanced SQL editor with syntax highlighting and auto-completion - Monitoring: Real-time server and database monitoring - Backup/Restore: Integrated backup and restore functionality

Best Use Cases: - PostgreSQL database administration - SQL query development and testing - Database monitoring and performance analysis - User and security management - Backup and restore operations - Development and production database management

Important Considerations: - Security configuration is critical for production deployments - Performance can be impacted with very large databases - Regular updates are essential for security and features - Proper backup procedures should be implemented - SSL/TLS should be configured for production use

By following the practices and techniques outlined in this cheatsheet, you can effectively use pgAdmin to manage PostgreSQL databases while maintaining security, performance, and reliability in your database administration tasks.