pgAdmin Cheatsheet

< < > > > > > > > "Clase de inscripción" pgAdmin es la plataforma de administración y desarrollo de código abierto más popular y rico en características para PostgreSQL. Proporciona una interfaz web para gestionar bases de datos PostgreSQL con herramientas integrales para la administración de bases de datos, el desarrollo de consultas y la vigilancia. ▪/p] ■/div titulada

########################################################################################################################################################################################################################################################## Copiar todos los comandos

########################################################################################################################################################################################################################################################## Generar PDF seleccionado/button

■/div titulada ■/div titulada

Cuadro de contenidos

• Instalación
• Configuración
• Server Management
• Operaciones de base de datos
• herramienta de preguntas
• Schema Management
• Manejo del usuario
• Backup and Restore
• Monitoreo
• Seguridad
• Customization
• Las mejores prácticas

Instalación

Instalación Ubuntu/Debian

# 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 Instalación

# 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 Instalación

# 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

Instalación de Windows

# 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 Instalación

# 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

Configuración

Configuración inicial

# 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)

Archivo de configuración

# 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'
}

Configuración de 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>

Configuración 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";
    }
}

Gestión de servidores

Agregar PostgreSQL Servidores

-- 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)

Ejemplos de conexión de servidor

# 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"
        }
    }
}

Solución de problemas

# 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

Operaciones de base de datos

Creación de bases de datos

-- 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)

Gestión de bases de datos

-- 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();

Operaciones de esquema

-- 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;

Herramienta de consultas

Operaciones básicas de consulta

-- 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;

Ejecución y análisis de consultas

-- 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;

Características de la herramienta de consulta

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

Características de la consulta avanzada

-- 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;

Gestión de esquemas

Operaciones

-- 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;

Función y gestión de procedimientos

-- 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));

Gestión de usuarios

Crear usuarios y 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;

Gestión de los privilegios

-- 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;

Gestión del usuario mediante 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

Respaldo y restauración

Base de datos

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

Línea de comandos 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

Restaurar la base de datos

# 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

Supervisión

Supervisión de la actividad del servidor

-- 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');

Estadísticas del cuadro e índice

-- 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;

Supervisión de la ejecución

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

Seguridad

Configuración SSL

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

Configuración de autenticación

# 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

Auditoría

-- 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;

Encriptación de datos

-- 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);

Prácticas óptimas de seguridad

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

Personalización

pgAdmin Temas y Apariencia

# 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;
}

Dashboards personalizados

# 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'
    }
]

Personalización de herramientas de consulta

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

## Buenas prácticas

### Prácticas óptimas de rendimiento
```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');

Prácticas óptimas de seguridad

# 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

Mejores prácticas de mantenimiento

#!/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)"

Respaldo y recuperación mejores prácticas

#!/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"

-...

Resumen

pgAdmin es una plataforma integral de administración y desarrollo de PostgreSQL que ofrece potentes herramientas para la gestión de bases de datos, el desarrollo de consultas y la vigilancia. Esta hoja de trampa cubre todos los aspectos de pgAdmin de la instalación a la administración avanzada.

Key Strengths - Interfaz amplia: Capacidades completas de administración PostgreSQL - Cross-Platform: Disponible en Windows, macOS y Linux - Basado en Internet: Accesible desde cualquier navegador moderno - Rich Query Tool: Editor SQL avanzado con resaltado de sintaxis y autocompleción - Monitoring: Monitorización de servidores y bases de datos en tiempo real - Backup/Restore: Función de copia de seguridad integrada y restauración

Mejores casos de uso: - Administración de bases de datos PostgreSQL - Desarrollo y prueba de consultas SQL - Supervisión de bases de datos y análisis del desempeño - Gestión de usuarios y seguridad - Operaciones de respaldo y restauración - Gestión de bases de datos sobre desarrollo y producción

** Consideraciones importantes:** - La configuración de seguridad es fundamental para los despliegues de producción - El rendimiento puede ser impactado con bases de datos muy grandes - Las actualizaciones regulares son esenciales para la seguridad y las características - Deben aplicarse procedimientos adecuados de copia de seguridad - SSL/TLS debe configurarse para el uso de la producción

Al seguir las prácticas y técnicas descritas en esta hoja de trampa, puede utilizar con eficacia pgAdmin para gestionar bases de datos PostgreSQL manteniendo la seguridad, el rendimiento y la fiabilidad en sus tareas de administración de bases de datos.

" copia de la funciónToClipboard() {} comandos const = document.querySelectorAll('code'); que todos losCommands = '; comandos. paraCada(cmd = confianza allCommands += cmd.textContent + '\n'); navigator.clipboard.writeText(allCommands); alerta ('Todos los comandos copiados a portapapeles!'); }

función generaPDF() { ventana.print(); } ■/script título