EDB Postgres Advanced Server (EPAS) Deep Dive
1. Overview of EDB Postgres Advanced Server (EPAS)
EDB Postgres Advanced Server is an enhanced version of open-source PostgreSQL designed for enterprise
use. It includes advanced performance, security, and compatibility features.
2. Architecture of EDB Postgres
2.1 Core Components
• Client Applications: psql, pgAdmin, EDB Postgres Enterprise Manager
• Postmaster: The master server process that handles incoming connections
• Backend Processes: Handle client sessions
• Shared Memory: Memory pool shared across all server processes
• Background Processes: Perform maintenance, logging, checkpoints, etc.
• Data Directory: Stores configuration files, data files, WAL, etc.
2.2 EDB Extensions
• EDB*Wrap: PL/SQL compiler wrapper
• EDB*Plus: Oracle-compatible SQL client
• SQL Profiler
• Resource Manager
• Advanced Security Features
3. Memory Components
3.1 Shared Memory
• Shared Buffers: Caches data pages read from disk (default: 25%-40% of RAM)
• WAL Buffers: Temporarily store WAL records before flushing to disk
• Work Mem: Allocated per sort/hash operation
• Maintenance Work Mem: For maintenance tasks like vacuum, reindex
• Temp Buffers: Used for temporary table operations
• Effective Cache Size: Hint for planner about OS-level cache
1
4. Background Processes
Process Description
Checkpointer Flushes dirty buffers to disk periodically
Writer (DBWriter) Writes data from shared buffer to disk
WAL Writer Writes WAL from WAL buffer to disk
Autovacuum Daemon Maintains table health by vacuuming dead tuples
Archiver Archives WAL files for PITR
Logger Writes logs
Stats Collector Tracks performance stats
Background Worker Runs user-defined or system extensions like logical replication
5. Configuration Files
File Description
postgresql.conf Main configuration file (memory, logging, connections)
pg_hba.conf Host-based authentication rules
pg_ident.conf User mapping for external auth
recovery.conf (in standby) Controls recovery during failover/replication
postgresql.auto.conf Dynamic settings via ALTER SYSTEM
6. Important Scenarios
6.1 Crash Recovery
• WAL replayed during startup
• Ensures atomicity and durability (ACID)
6.2 PITR (Point-in-Time Recovery)
• Restore from base backup
• Replay WAL files to desired point
2
6.3 Replication Delay
• Monitor pg_stat_replication
• Use wal_receiver_status_interval and wal_sender_timeout
6.4 WAL File Handling
• Default size: 16MB
• Archive via archive_command
• WAL segment recycled or removed based on retention
7. Clustering Options in EDB
7.1 Always On Architecture
• Primary + Multiple Standbys
• EDB Failover Manager (EFM) for automatic failover
7.2 Connection Routing
• EDB PgPool-II: Connection pooler, load balancer, read/write splitting
• PgBouncer: Lightweight connection pooler
7.3 High Availability
• Asynchronous or synchronous streaming replication
• Auto failover using EFM
8. Replication Types
8.1 Physical Replication
• Block-level, exact copy
• Uses WAL shipping
• Synchronous or asynchronous
8.2 Logical Replication
• Row-level replication
• Can replicate specific tables
• Use pglogical , CREATE PUBLICATION , CREATE SUBSCRIPTION
3
9. Disaster Recovery (DR)
DR Components
• Regular base backups
• WAL Archiving
• Monitoring & Alerting (using PEM)
DR Strategy
• Offsite standby with archived WALs
• Test PITR periodically
• Implement backup automation with tools like Barman or pgBackRest
10. Tools
• PEM: EDB’s monitoring and management tool
• EFM: EDB Failover Manager
• PgBouncer: Lightweight pooler
• pgBackRest: Advanced backup & recovery
11. Summary Diagram (Text Representation)
Client
↓
Postmaster
↓
Backend Process ←→ Shared Memory (Buffers)
↓ ↑
Checkpointer, WAL Writer, DBWriter
↓ ↑
Data Files ←→ WAL Files
↓ ↑
Base Backup & PITR Archiver
12. Best Practices
• Set shared_buffers to 25%-40% of total RAM
• Enable WAL archiving for DR
• Regularly monitor pg_stat_activity and pg_stat_replication
• Use vacuumdb and autovacuum tuning
4
• Secure pg_hba.conf and apply least privilege
