PostgreSQL documentation
PostgreSQL Documentation: A Server Configuration Guide
This article provides a comprehensive guide to configuring PostgreSQL for use with a MediaWiki 1.40 installation. PostgreSQL is a powerful, open-source object-relational database system known for its reliability and feature robustness. Properly configuring it is crucial for the performance and stability of your wiki. This guide is geared towards newcomers to server administration and assumes a basic understanding of Linux command-line operations.
Prerequisites
Before beginning, ensure you have:
- A server with a supported Linux distribution (Debian, Ubuntu, CentOS, etc.).
- Root or sudo access to the server.
- Basic familiarity with the command line.
- PostgreSQL installed. If not, use your distribution's package manager (e.g., `apt-get install postgresql` on Debian/Ubuntu, `yum install postgresql-server` on CentOS).
- A MediaWiki installation (version 1.40 or later). See Manual:Installation guide for details.
- **Strong Passwords:** Use strong, unique passwords for all PostgreSQL users.
- **Firewall:** Configure the firewall to allow connections only from trusted sources.
- **Regular Updates:** Keep PostgreSQL updated with the latest security patches.
- **Backup:** Implement a robust backup and recovery strategy. See Manual:Backups for MediaWiki backup guidance.
- [PostgreSQL Documentation](https://www.postgresql.org/docs/)
- [MediaWiki Database Documentation](https://www.mediawiki.org/wiki/Manual:Database)
- [PostgreSQL Tuning](https://wiki.postgresql.org/wiki/Tuning)
- Telegram: @powervps Servers at a discounted price
Initial PostgreSQL Configuration
After installation, the default PostgreSQL configuration is often not optimal for a production MediaWiki environment. Several settings within the `postgresql.conf` file require adjustment. This file's location varies by distribution; common locations include `/etc/postgresql/
Memory Allocation
PostgreSQL utilizes shared memory for caching data, which significantly improves performance. The following parameters control memory usage:
| Parameter | Description | Recommended Value (Example) |
|---|---|---|
| `shared_buffers` | Specifies the amount of memory PostgreSQL uses for shared memory buffers. | `4GB` (adjust based on server RAM, typically 25-50%) |
| `work_mem` | Specifies the amount of memory used by internal sort operations and hash tables before writing to disk. | `64MB` (increase if you observe disk sorts in logs) |
| `maintenance_work_mem` | Specifies the amount of memory used for maintenance operations like `VACUUM`, `CREATE INDEX`, etc. | `512MB` (can be significantly higher for large databases) |
Remember to restart the PostgreSQL service after modifying `postgresql.conf`. For example: `sudo systemctl restart postgresql`. Consult your distribution’s documentation for the correct restart command. See Manual:Configuration for more MediaWiki configuration details.
Connection Settings
Configure PostgreSQL to listen for connections from the MediaWiki server. Edit the `postgresql.conf` file again:
| Parameter | Description | Recommended Value |
|---|---|---|
| `listen_addresses` | Specifies the IP addresses to listen on. `*` listens on all interfaces. | `*` (for remote access, otherwise the server's IP) |
| `port` | The port PostgreSQL listens on. | `5432` (default) |
Also, configure `pg_hba.conf` (usually located in the same directory as `postgresql.conf`) to allow connections from the MediaWiki server. Add a line similar to the following:
```
host all all
Replace `
Database Creation and User Management
Create a dedicated database and user for MediaWiki. Log in to the PostgreSQL shell as the `postgres` user:
```bash sudo -u postgres psql ```
Then, execute the following SQL commands:
```sql CREATE USER wikimedia WITH PASSWORD 'your_password'; CREATE DATABASE wikimedia OWNER wikimedia ENCODING 'UTF8'; GRANT ALL PRIVILEGES ON DATABASE wikimedia TO wikimedia; \q ```
Replace `'your_password'` with a strong, unique password. This user will be used by MediaWiki to connect to the database. See Configuration Form for details on configuring the connection in MediaWiki.
Performance Tuning
PostgreSQL offers several features to optimize performance.
Autovacuum
Autovacuum automatically reclaims storage occupied by deleted or updated tuples. Properly configured autovacuum prevents table bloat and maintains query performance.
| Parameter | Description | Recommended Value |
|---|---|---|
| `autovacuum` | Enables or disables autovacuum. | `on` |
| `autovacuum_max_workers` | The maximum number of concurrent autovacuum processes. | `3` (adjust based on server resources) |
| `autovacuum_vacuum_threshold` | The minimum number of updated tuples before autovacuum is triggered. | `50` |
Indexing
Properly chosen indexes can dramatically speed up query execution. Analyze your query patterns and create indexes on frequently queried columns. Use the `EXPLAIN` command in `psql` to analyze query plans and identify potential indexing opportunities. See Database indexes for more information.
Connection Pooling
For high-traffic wikis, consider using a connection pooler like `pgbouncer` or `pgpool-II`. These tools maintain a pool of database connections, reducing the overhead of establishing new connections for each request. See Performance tuning for more advanced optimization techniques.
Monitoring
Regularly monitor PostgreSQL performance using tools like `pg_stat_statements`, `top`, and `iotop`. Check the PostgreSQL logs for errors and warnings. Monitoring helps identify bottlenecks and proactively address performance issues. See also Server monitoring.
Security Considerations
Resources
Database Manual:Configuration Manual:Installation guide Configuration Form Database indexes Performance tuning Server monitoring Manual:Backups Security PostgreSQL Database software Server administration Linux Command line SQL pg_hba.conf postgresql.conf
Intel-Based Server Configurations
| Configuration | Specifications | Benchmark |
|---|---|---|
| Core i7-6700K/7700 Server | 64 GB DDR4, NVMe SSD 2 x 512 GB | CPU Benchmark: 8046 |
| Core i7-8700 Server | 64 GB DDR4, NVMe SSD 2x1 TB | CPU Benchmark: 13124 |
| Core i9-9900K Server | 128 GB DDR4, NVMe SSD 2 x 1 TB | CPU Benchmark: 49969 |
| Core i9-13900 Server (64GB) | 64 GB RAM, 2x2 TB NVMe SSD | |
| Core i9-13900 Server (128GB) | 128 GB RAM, 2x2 TB NVMe SSD | |
| Core i5-13500 Server (64GB) | 64 GB RAM, 2x500 GB NVMe SSD | |
| Core i5-13500 Server (128GB) | 128 GB RAM, 2x500 GB NVMe SSD | |
| Core i5-13500 Workstation | 64 GB DDR5 RAM, 2 NVMe SSD, NVIDIA RTX 4000 |
AMD-Based Server Configurations
| Configuration | Specifications | Benchmark |
|---|---|---|
| Ryzen 5 3600 Server | 64 GB RAM, 2x480 GB NVMe | CPU Benchmark: 17849 |
| Ryzen 7 7700 Server | 64 GB DDR5 RAM, 2x1 TB NVMe | CPU Benchmark: 35224 |
| Ryzen 9 5950X Server | 128 GB RAM, 2x4 TB NVMe | CPU Benchmark: 46045 |
| Ryzen 9 7950X Server | 128 GB DDR5 ECC, 2x2 TB NVMe | CPU Benchmark: 63561 |
| EPYC 7502P Server (128GB/1TB) | 128 GB RAM, 1 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (128GB/2TB) | 128 GB RAM, 2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (128GB/4TB) | 128 GB RAM, 2x2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (256GB/1TB) | 256 GB RAM, 1 TB NVMe | CPU Benchmark: 48021 |
| EPYC 7502P Server (256GB/4TB) | 256 GB RAM, 2x2 TB NVMe | CPU Benchmark: 48021 |
| EPYC 9454P Server | 256 GB RAM, 2x2 TB NVMe |
Order Your Dedicated Server
Configure and order your ideal server configurationNeed Assistance?
⚠️ *Note: All benchmark scores are approximate and may vary based on configuration. Server availability subject to stock.* ⚠️