PgAdmin Cheatsheet¶

pgAdmin - PostgreSQL Administration and Development Platform

pgAdmin ist die beliebteste und funktionsreichste Open Source Administrations- und Entwicklungsplattform für PostgreSQL. Es bietet eine webbasierte Schnittstelle zur Verwaltung von PostgreSQL-Datenbanken mit umfassenden Tools für Datenbankverwaltung, Abfrageentwicklung und Überwachung. < p>

generieren

Inhaltsverzeichnis¶

[Installation](#installation
[Konfiguration](LINK_1__
[Server Management](#server-management_
[Datenbankoperationen](LINK_3__
[Query Tool](#query-tool
[Schema Management](#schema-management
(#user-management_)
(#backup-and-restore)
(Monitoring)(LINK_8_)
[Sicherheit](#security
(#customization_)
Beste Praktiken

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
```bash
# 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
```bash
# 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
```powershell
# 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
```bash
# 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
```_

## Konfiguration

### Erster Setup
```bash
# 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)
```_

### Datei konfigurieren
```python
# 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 Konfiguration
```apache
# /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 Konfiguration
```nginx
# /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";
    }
}
```_

## Serververwaltung

### PostgreSQL hinzufügen Server
```sql
-- 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)
```_

### Beispiele für Serververbindungen
```python
# 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"
        }
    }
}
```_

### Anschluss Fehlerbehebung
```bash
# 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
```_

## Datenbanken

### Datenbanken erstellen
```sql
-- 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)
```_

### Datenbankverwaltung
```sql
-- 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();
```_

### Operationen
```sql
-- 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;
```_

## Anfrage-Tool

### Grundlagen der Abfrage
```sql
-- 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;
```_

### Ausführung und Analyse
```sql
-- 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;
```_

### Quer-Tool-Funktionen
```sql
-- 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
```_

### Erweiterte Abfrage-Funktionen
```sql
-- 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;
```_

## Projektmanagement

### Tabelle Operationen
```sql
-- 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);
```_

### Management ansehen
```sql
-- 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;
```_

### Funktion und Verfahrensmanagement
```sql
-- 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
```sql
-- 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));
```_

## Benutzermanagement

### Nutzer und Roles erstellen
```sql
-- 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;
```_

### Vorrechte verwalten
```sql
-- 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)
```sql
-- 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;
```_

### Benutzerverwaltung über 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 und Wiederherstellung

### Datenbank-Backup
```sql
-- 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
```_

### Befehlszeilensicherung
```bash
# 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
```_

### Datenbankwiederherstellung
```bash
# 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
```_

### Automatisiertes Backup-Script
```bash
#!/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
```_

## Überwachung

### Server-Aktivitätsüberwachung
```sql
-- 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');
```_

### Tabelle und Indexstatistik
```sql
-- 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;
```_

### Leistungsüberwachung
```sql
-- 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

System Statistics:
CPU usage
Memory usage
Disk I/O
Network activity
Query Tool:
Execute monitoring queries
View execution plans
Analyze performance
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 ```_

Sicherheit¶

SSL Konfiguration¶

```sql -- 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 Konfiguration¶

```bash

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¶

```sql -- 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; ```_

Datenverschlüsselung¶

```sql -- 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); ```_

Sicherheit Best Practices¶

```sql -- 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 ```_

Anpassung¶

pgAdmin Themen und Aussehen¶

```python

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; } ```_

Individuelles Armaturenbrett¶

```python

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 = [ { 'name': 'Performance Monitor', 'url': '/performance', 'icon': 'fa-chart-line' }, { 'name': 'Custom Reports', 'url': '/reports', 'icon': 'fa-file-alt' } ] ```_

Anfrage Tool Anpassung¶

```javascript // 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¶

```sql -- 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'); ```_

Sicherheit Best Practices¶

```python

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

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 ```_

Best Practices der Wartung¶

```bash

!/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 und Recovery Best Practices¶

```bash

!/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" ```_

--

Zusammenfassung¶

pgAdmin ist eine umfassende PostgreSQL-Administrations- und Entwicklungsplattform, die leistungsstarke Tools für das Datenbankmanagement, die Abfrageentwicklung und die Überwachung bietet. Dieses Cheatsheet umfasst alle Aspekte von pgAdmin von der Installation bis zur erweiterten Administration.

Key Strengths: - Comprehensive Interface*: Vollständige PostgreSQL Administrationsfunktionen - **Cross-Platform*: Verfügbar unter Windows, macOS und Linux - **Web-Based: Erreichbar von jedem modernen Browser - Rich Query Tool*: Advanced SQL Editor mit Syntax-Highlighting und Auto-Komplettierung - **Monitoring: Echtzeit-Server und Datenbanküberwachung - Backup/Restore: Integrierte Backup- und Wiederherstellungsfunktionalität

Best Use Cases: - PostgreSQL Datenbankverwaltung - SQL Abfrage Entwicklung und Test - Datenbanküberwachung und Leistungsanalyse - Benutzer- und Sicherheitsmanagement - Backup und Wiederherstellung von Operationen - Entwicklung und Produktionsdatenbankverwaltung

Importierte Überlegungen: - Sicherheitskonfiguration ist für Produktionseinsätze entscheidend - Leistung kann mit sehr großen Datenbanken beeinflusst werden - Regelmäßige Updates sind wichtig für Sicherheit und Funktionen - Richtige Sicherungsverfahren sollten durchgeführt werden - SSL/TLS sollte für die Produktion konfiguriert werden

Durch die in diesem Cheatsheet skizzierten Praktiken und Techniken können Sie pgAdmin effektiv nutzen, um PostgreSQL-Datenbanken zu verwalten und gleichzeitig Sicherheit, Leistung und Zuverlässigkeit in Ihren Datenbankverwaltungsaufgaben zu gewährleisten.

<= <= <= <================================================================================= Funktion copyToClipboard() {\cHFFFF} const commands = document.querySelectorAll('code'); alle Befehle = ''; Befehle. Für jede(cmd) => alle Befehle += cmd.textContent + '\n'); navigator.clipboard.writeText (allCommands); Alarm ('Alle Befehle, die in die Zwischenablage kopiert werden!'); }

Funktion generierenPDF() { Fenster.print(); }

PgAdmin Cheatsheet¶

pgAdmin - PostgreSQL Administration and Development Platform

Inhaltsverzeichnis¶

Installation¶

Ubuntu/Debian Installation¶

Sicherheit¶

SSL Konfiguration¶

Authentication Konfiguration¶

pg_hba.conf configuration examples¶

Local connections¶

IPv4 local connections¶

IPv6 local connections¶

SSL connections only¶

LDAP authentication¶

Certificate authentication¶

GSSAPI authentication¶

Reject specific users¶

Database-specific rules¶

Audit Logging¶

Datenverschlüsselung¶

Sicherheit Best Practices¶

Anpassung¶

pgAdmin Themen und Aussehen¶

Custom CSS for pgAdmin¶

Create custom.css in pgAdmin web directory¶

Individuelles Armaturenbrett¶

config_local.py - Custom dashboard configuration¶

Custom dashboard widgets¶

Custom menu items¶

Anfrage Tool Anpassung¶

Best Practices¶

Performance Best Practices¶

Sicherheit Best Practices¶

pgAdmin security configuration¶

1. Use HTTPS only¶

2. Strong session configuration¶

3. CSRF protection¶

4. Content Security Policy¶

5. Hide server information¶

6. Limit login attempts¶

7. Regular backups¶

8. Audit logging¶

9. User session timeout¶

10. Database connection security¶

Best Practices der Wartung¶

!/bin/bash¶

pgAdmin maintenance script¶

1. Regular updates¶

2. Log rotation¶

3. Database cleanup¶

4. Backup pgAdmin configuration¶

5. Monitor disk space¶

6. Check service status¶

7. Verify SSL certificates¶

8. Performance monitoring¶

9. Security audit¶

Check for unauthorized access attempts¶

10. Cleanup temporary files¶

Backup und Recovery Best Practices¶

!/bin/bash¶

Comprehensive pgAdmin backup strategy¶

1. Backup pgAdmin configuration¶

2. Backup pgAdmin database¶

3. Backup user preferences and settings¶

4. Backup SSL certificates¶

5. Export server definitions¶

This would require custom script to export from pgAdmin database¶

6. Create restore script¶

!/bin/bash¶

pgAdmin restore script¶

7. Compress backup¶

8. Remove old backups¶

Zusammenfassung¶