When setting up a PostgreSQL master-slave replication or general database storage management, you should consider mounting specific storage paths to optimize performance, ensure data integrity, and manage disk usage efficiently. Below is a list of important PostgreSQL storage paths and their purposes:
1. Data Directory (Main Database Storage)
Path: /var/lib/postgresql/<version>/main/
Purpose: Stores all PostgreSQL database files, including tables, indexes, and internal metadata.
Mount Recommendation: High-performance SSD with enough capacity for database growth.
If you have 30 GB allocated for database storage, ensure at least 50% headroom for future expansion, making a total of 45 GB recommended.
2. WAL (Write-Ahead Logging) Files
Path: /var/lib/postgresql/<version>/main/pg_wal/
Purpose: Stores transaction logs for crash recovery and replication.
Mount Recommendation: Fast SSD or NVMe disk with high IOPS to improve database write performance.
PostgreSQL retains WAL logs based on wal_keep_size or max_wal_size, which can vary. For a moderate workload, 10-15 GB recommended.
3. Replication Slots (if used for logical replication)
Path: /var/lib/postgresql/<version>/main/pg_replslot/
Purpose: Stores replication slots for logical replication.
Mount Recommendation: SSD or same disk as WAL to maintain replication efficiency.
Each replication slot can accumulate WAL logs if the replica lags behind, so allocate 5-10 GB recommended.
4. Temporary Files (Sort & Query Execution)
Path: /var/lib/postgresql/<version>/main/base/pgsql_tmp/
Purpose: Stores temporary files created during query execution and sorting operations.
Mount Recommendation: Separate fast disk (optional) if handling large temporary data.
For high-performance queries, 5-10 GB recommended.
5. Log Files
Path: /var/log/postgresql/
Purpose: Stores PostgreSQL logs, including query logs, errors, and slow query logs.
Mount Recommendation: Separate disk or partition for better log management and prevent logs from filling up system storage.
Log rotation should be configured properly. 3-5 GB recommended.
6. Archive Logs (for Point-in-Time Recovery – PITR, if enabled)
Path: /var/lib/postgresql/<version>/archive
Purpose: Stores archived WAL files for backups and point-in-time recovery.
Mount Recommendation: Separate storage or remote storage (NFS, object storage, or backup server). This depends on your backup retention policy.
10-20 GB recommended.
7. Configuration Files
Path: /etc/postgresql/<version>/main/
Purpose: Stores PostgreSQL configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf).
Mount Recommendation: Typically left on the system disk but can be backed up separately.
Minimal storage required, <1 GB sufficient.
8. Tablespaces (if using custom storage for specific tables/indexes)
Path: Custom path defined when creating a tablespace.
Purpose: Used for storing specific tables and indexes on different storage devices.
Mount Recommendation: Dedicated high-performance storage for large tables or indexes.
If large datasets require separate storage, allocate based on table sizes, 20-50 GB recommended.
9. Backup Storage (Optional)
Path: /mnt/backup_postgres/
Purpose: A separate mounted disk or network storage for scheduled database backups.
Mount Recommendation: External storage or remote backup server.
This should be sized based on your backup retention policy, at least 100 GB recommended.
Recommended Mount Strategy
| Storage Path | Recommended Mount Type | Purpose | Estimated Disk Size |
|---|---|---|---|
/var/lib/postgresql/<version>/main/ | High-performance SSD/NVMe | Main database storage | 45 GB |
/var/lib/postgresql/<version>/main/pg_wal/ | SSD/NVMe | Write-Ahead Logs (WAL) | 10-15 GB |
/var/lib/postgresql/<version>/main/pg_replslot/ | SSD/NVMe | Replication slots | 5-10 GB |
/var/lib/postgresql/<version>/main/base/pgsql_tmp/ | SSD/NVMe (if needed) | Temporary query storage | 5-10 GB |
/var/log/postgresql/ | Separate disk or partition | Logs storage | 3-5 GB |
/var/lib/postgresql/<version>/archive/ | Remote/NFS or separate disk | WAL archive for PITR | 10-20 GB |
/mnt/backup_postgres/ | Remote or external storage | Backup location | 100 GB+ |
For a master-slave setup, ensure that WAL and replication slots have adequate storage to prevent replication lag or failures. If your replication lag is high, increase the allocated space for WAL to avoid losing data during recovery.
Let me know if you need further customizations for your specific setup!