PostgreSQL Staging Environment

Introduction

Efficient performance of the PostgreSQL Database server is crucial for the optimal functioning of the enov8 Test Data Management (TDM) Tool. This document outlines the standard configurations required for a PostgreSQL staging server and provides a comprehensive set of troubleshooting steps for Database Administrators (DBAs) to ensure that the database aligns with these standards and operates at peak efficiency.

Disclaimer

This documentation is written for PostgreSQL versions 13 and above, specifically for use in staging environments. The configurations and SQL queries provided are recommendations based on typical staging setups and may not be suitable for all environments. It is essential to evaluate these recommendations in the context of your specific staging environment and requirements. Consult with your organization's database administrators and PostgreSQL documentation for guidance tailored to your specific needs.

---

1. Standard Database Server Configuration

1.1 Hardware Requirements

The hardware requirements should scale based on the size of the database and the volume of data being masked. Below is a comprehensive guideline for CPU, memory, disk, and network:

CPU

• Cores: Start with 4-6 cores, add 2 cores per 1 TB of data.
• Example:
  • For a 1 TB database: 4 cores.
  • For a 5 TB database: 12 cores.

Memory (RAM)

• Base Requirement: Start with 16 GB, add 16 GB per 1 TB of data.
• Example:
  • For a 1 TB database: 16 GB of RAM.
  • For a 5 TB database: 80 GB of RAM.

Disk

• Type: Use SSDs for optimal performance, especially for I/O-intensive operations.
• Capacity: Ensure sufficient storage for data, logs, and backups.
• IOPS: Minimum of 10,000 IOPS for SSDs; 300 IOPS for HDDs.
• Example:
  • For a 1 TB database: 2 TB of SSD storage.

Network

• Bandwidth: At least 1 Gbps network interface card (NIC).
• Latency: Low-latency network connections to minimize delays.

---

1.2 System-Level Database Optimizations

Optimizing for Bulk Data Masking

• Disable Write-Ahead Logging (WAL) During Masking:

  • Purpose: Reduces I/O overhead by not writing changes to WAL logs during bulk operations.
  • Implementation:

    ALTER SYSTEM SET max_wal_senders = 0;
    ALTER SYSTEM SET wal_level = 'minimal';
    SELECT pg_reload_conf();
  • Important Considerations:
    • Ensure max_wal_senders is set to 0 before changing wal_level to prevent replication conflicts.
    • Check the current PostgreSQL configuration to confirm changes persist.
    • Restart PostgreSQL after making this change to fully apply the settings.
    • Validate the settings post-change using:

      SHOW wal_level;
      SHOW max_wal_senders;
    • If encountering startup issues, review postgresql.conf and logs (journalctl -u postgresql).

• Adjust Work Memory and Temp Buffers:

  • Purpose: Optimize query execution for large data modifications.
  • Implementation:

    ALTER SYSTEM SET work_mem = '256MB';
    SELECT pg_reload_conf();
    ALTER SYSTEM SET temp_buffers = '128MB';
    SELECT pg_reload_conf();

• Disable Triggers and Constraints (if applicable):

  • Purpose: Improves performance by preventing additional processing.
  • Implementation:

    ALTER TABLE your_table DISABLE TRIGGER ALL;

Other Operating System Configurations

• Filesystem: Use ext4 or XFS for database files.

• Kernel Parameters (Linux):

  Parameter	Recommended Setting
  vm.swappiness	10
  kernel.shmmax	Half of physical memory (e.g., 16 GB for a 32 GB system)

• User Limits (Linux):

  Limit	Recommended Setting
  Open Files (nofile)	65536
  Max Processes (nproc)	16384

---

1.3 PostgreSQL Configuration

Memory Management

• Parameters:
  • shared_buffers: 25-40% of total RAM (e.g., 16 GB on a 64 GB system)
  • effective_cache_size: 50-75% of total RAM
• Implementation:

  ALTER SYSTEM SET shared_buffers = '16GB';
  ALTER SYSTEM SET effective_cache_size = '48GB';
  SELECT pg_reload_conf();

Process and Connection Parameters

• Parameters:
  • max_connections: 100-500 depending on workload
  • work_mem: Allocate sufficient memory per query
• Implementation:

  ALTER SYSTEM SET max_connections = 300;
  ALTER SYSTEM SET work_mem = '256MB';
  SELECT pg_reload_conf();

Checkpoint and Logging Configuration

• Disable Full Page Writes for Staging:

  ALTER SYSTEM SET full_page_writes = OFF;
  SELECT pg_reload_conf();

• Tune Checkpointing for Bulk Data Processing:

  ALTER SYSTEM SET checkpoint_timeout = '30min';
  ALTER SYSTEM SET checkpoint_completion_target = 0.9;
  SELECT pg_reload_conf();

---

1.4 Network Configuration

• Connection Pooling: Use pgbouncer for better connection management.
• Optimize PostgreSQL TCP settings:

  ALTER SYSTEM SET tcp_keepalives_idle = 600;
  ALTER SYSTEM SET tcp_keepalives_interval = 60;
  ALTER SYSTEM SET tcp_keepalives_count = 5;
  SELECT pg_reload_conf();

---

1.5 Security and Patch Management

• Patching:
  • Regularly apply the latest PostgreSQL updates and security patches.
• Security Parameters:
  • password_encryption: scram-sha-256
  • log_connections: on

---

2. Troubleshooting Steps for DBAs

2.1 Verify Hardware and OS Configuration

Check CPU and Memory Usage

• Purpose: Ensure that the server hardware meets the required specifications and is not experiencing resource bottlenecks.
• Commands:

  lscpu
  free -h
  top
• Expected Results:
  • CPU Usage: Should not be consistently above 80%.
  • Memory Available: At least 10% of total RAM should be free.

Check Disk I/O Performance

• Purpose: Identify any disk bottlenecks impacting database performance.
• Commands:

  iostat -x 1 5
• Expected Results: %util should ideally be below 80%.

---

2.2 Validate PostgreSQL Configuration Settings

Check Shared Buffers

• Purpose: Ensure the memory buffer allocation is properly set for PostgreSQL performance.
• Commands:

  SHOW shared_buffers;
• Expected Output: Confirm shared_buffers is set to 25-40% of total RAM.

Verify Max Connections

• Purpose: Validate the maximum allowed database connections to prevent resource exhaustion.
• Commands:

  SHOW max_connections;
• Expected Output: Ensure max_connections is set to the recommended value (e.g., 300).

---

2.3 Assess Database Performance

Monitor Active Queries

• Purpose: Identify long-running queries that may affect performance.
• Commands:

  SELECT pid, age(clock_timestamp(), query_start), query FROM pg_stat_activity WHERE state != 'idle' ORDER BY age DESC;

Find Locks Blocking Queries

• Purpose: Detect queries that are blocked by other sessions.
• Commands:

  SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

---

2.4 Check Session and Query Performance

Identify Active Sessions

• Purpose: Monitor currently running queries and session activity.
• Commands:

  SELECT pid, query, state FROM pg_stat_activity ORDER BY backend_start DESC;

Check Slow Queries

• Purpose: Identify queries taking a long time to execute.
• Commands:

  SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

---

2.5 Evaluate Storage and Space Usage

Check Database Sizes

• Purpose: Monitor storage allocation for PostgreSQL databases.
• Commands:

  SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database;

Monitor Disk Usage

• Purpose: Ensure sufficient disk space is available.
• Commands:

  df -h

---

2.6 Review Indexes and Optimize Tables

Check for Missing Indexes

• Purpose: Identify tables that may require additional indexing.
• Commands:

  EXPLAIN ANALYZE SELECT * FROM your_table WHERE column_name = 'value';

Optimize Tables

• Purpose: Reorganize and analyze tables for optimal performance.
• Commands:

  VACUUM ANALYZE;

---

2.7 Network Diagnostics

Test Connectivity

• Purpose: Validate connectivity to the PostgreSQL instance.
• Commands:

  psql -U username -h hostname -d database_name

Check for Network Latency

• Purpose: Measure response time between the database and application.
• Commands:

  ping hostname

---

2.8 Verify Security and Patch Levels

Check PostgreSQL Version

• Purpose: Ensure the database is running an up-to-date version.
• Commands:

  SELECT version();

Review Security Settings

• Purpose: Verify user privileges and authentication settings.
• Commands:

  SELECT usename, useconfig FROM pg_user;

---

2.9 Application Interaction

Confirm Connection Management

• Purpose: Ensure the application is using database connections efficiently.
• Commands:

  SELECT datname, numbackends FROM pg_stat_database;

---

2.10 System Resource Monitoring

Monitor System Performance

• Purpose: Track CPU, memory, and I/O performance in real-time.
• Commands:

  top
  htop
  vmstat 1 5