PostgreSQL database

From Server rent store
Jump to navigation Jump to search
  1. 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.

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/<version>/main/postgresql.conf` or `/var/lib/pgsql/<version>/data/postgresql.conf`.

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:

  • `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.

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

  • **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.

Troubleshooting

  • **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.

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 configuration

Need Assistance?

⚠️ *Note: All benchmark scores are approximate and may vary based on configuration. Server availability subject to stock.* ⚠️

Retrieved from "https://server-rental.store/index.php?title=PostgreSQL_database&oldid=2067"