PostgreSQL documentation

From Server rental store
Jump to navigation Jump to search

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.

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

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 <MediaWiki Server IP>/32 md5 ```

Replace `<MediaWiki Server IP>` with the actual IP address of your MediaWiki server. The `md5` authentication method requires a password. See Database for more information about database setup.

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

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

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 configuration

Need Assistance?

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

Retrieved from "https://serverrental.store/index.php?title=PostgreSQL_documentation&oldid=2068"