PostgreSQL database
# PostgreSQL Database Configuration for MediaWiki
This article details the configuration of a PostgreSQL database server for use with MediaWiki 1.40. A properly configured database is crucial for optimal MediaWiki performance and stability. This guide is intended for system administrators and newcomers to MediaWiki server administration.
Prerequisites
Before beginning, ensure you have:
- A server with a supported operating system (e.g., Linux, FreeBSD).
- PostgreSQL installed and running. See the https://www.postgresql.org/docs/ for installation instructions specific to your operating system.
- Basic familiarity with the command line interface and database administration.
- Access to a user account with sufficient privileges to create databases and users in PostgreSQL.
- A working understanding of PHP and its configuration.
- `VACUUM`: Reclaims storage occupied by deleted or updated rows. Run regularly.
- `ANALYZE`: Updates statistics used by the query planner. Run after significant data changes.
- `REINDEX`: Rebuilds indexes. Run periodically to improve query performance.
- **Connection Pooling:** For high-traffic sites, consider using a connection pooler like `pgbouncer` to reduce the overhead of establishing new database connections. See Connection Pooling for more details.
- **Indexing:** Properly indexed tables are crucial for query performance. Analyze slow queries using PostgreSQL's `EXPLAIN` command and add indexes as needed. Refer to the Database Indexing article for guidance.
- **Monitoring:** Monitor PostgreSQL performance metrics (CPU usage, memory usage, disk I/O, query performance) to identify potential bottlenecks. Tools like `pgAdmin` or system monitoring tools can be helpful.
- **Replication:** For high availability and disaster recovery, consider setting up PostgreSQL replication. See Database Replication for more information.
- **Large MediaWiki Installations**: For extremely large installations, consider partitioning tables to improve performance. This is an advanced topic covered in the Database Partitioning documentation.
- **Connection Errors:** Verify the database credentials in `LocalSettings.php` and ensure that PostgreSQL is running and accessible. Check firewall settings. Review the Error Log for clues.
- **Slow Queries:** Use `EXPLAIN` to analyze slow queries and identify missing indexes or inefficient query plans.
- **Database Bloat:** Regularly run `VACUUM` and `ANALYZE` to prevent database bloat.
- Telegram: @powervps Servers at a discounted price
Database Creation and User Setup
First, you need to create a dedicated database for your MediaWiki installation. It is strongly recommended *not* to use the default "postgres" database. You also need to create a user with appropriate permissions to access this database.
1. Connect to the PostgreSQL server as a privileged user (typically `postgres`).
2. Execute the following SQL commands. Replace `mw_database`, `mw_user`, and `your_password` with your desired values:
```sql CREATE DATABASE mw_database WITH ENCODING 'UTF8'; CREATE USER mw_user WITH PASSWORD 'your_password'; GRANT ALL PRIVILEGES ON DATABASE mw_database TO mw_user; ```
These commands create a new database named `mw_database` with UTF-8 encoding, create a user named `mw_user` with a strong password, and grant all privileges on the database to that user. UTF-8 encoding is *essential* for handling the wide range of characters used in many languages. Consider restricting privileges further for enhanced security in a production environment; `GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO mw_user;` is a more restrictive example.
PostgreSQL Configuration Parameters
Several PostgreSQL configuration parameters can significantly impact MediaWiki performance. These are typically found in the `postgresql.conf` file. The location of this file varies depending on your operating system and installation method. Common locations include `/etc/postgresql/
Here's a table of recommended settings:
| Parameter | Description | Recommended Value | Notes |
|---|---|---|---|
| `shared_buffers` | Amount of memory dedicated to shared memory buffers. | 256MB - 1GB | Adjust based on available RAM. Higher values generally improve performance, but don't exceed 25% of system RAM. |
| `work_mem` | Amount of memory used by internal sort operations and hash tables before writing to disk. | 64MB – 256MB | Increase if you experience slow sorting operations. |
| `maintenance_work_mem` | Amount of memory used for maintenance tasks like `VACUUM`, `CREATE INDEX`, etc. | 64MB – 512MB | Increase for faster maintenance, but be mindful of system resources. |
| `effective_cache_size` | Estimate of the size of the disk cache. Used by the query planner. | 50% of system RAM | Helps the query planner make informed decisions. |
| `wal_level` | Level of WAL (Write-Ahead Logging). | `replica` | Enables archiving and replication. `logical` is also an option. |
After modifying `postgresql.conf`, restart the PostgreSQL server for the changes to take effect. Use the appropriate command for your system (e.g., `sudo systemctl restart postgresql`).
MediaWiki Configuration
Now, configure MediaWiki to connect to the PostgreSQL database. Edit your `LocalSettings.php` file (located in the `/etc/mediawiki/` directory or the directory where you installed MediaWiki). Add or modify the following lines:
```php $wgDBtype = 'pgsql'; $wgDBserver = 'localhost'; // Or the IP address of your PostgreSQL server $wgDBname = 'mw_database'; $wgDBuser = 'mw_user'; $wgDBpassword = 'your_password'; $wgDBport = '5432'; // Default PostgreSQL port ```
Replace the placeholders with the values you used when creating the database and user. Ensure that the `wgDBport` matches the port PostgreSQL is listening on. Incorrect settings here will prevent MediaWiki from connecting to the database.
Database Maintenance
Regular database maintenance is essential for maintaining performance. PostgreSQL provides several tools for this purpose:
You can automate these tasks using `cron` or a similar scheduling tool. Consider using `pg_autovacuum` which is enabled by default in most modern PostgreSQL installations, and automatically handles these tasks.
Here's a table illustrating a basic maintenance schedule:
| Task | Frequency | Command Example |
|---|---|---|
| `VACUUM` | Daily | `pg_autovacuum` (Automatic) or `VACUUM FULL mw_database;` |
| `ANALYZE` | Daily | `ANALYZE mw_database;` |
| `REINDEX` | Weekly | `REINDEX DATABASE mw_database;` |
Advanced Considerations
Troubleshooting
This guide provides a comprehensive overview of configuring PostgreSQL for MediaWiki. Remember to adapt the settings to your specific environment and monitor performance regularly. Consult the MediaWiki Installation Guide for additional information. Also, review the PHP Configuration page for related settings.
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.* ⚠️