PostgreSQL指南：安装、配置与性能优化 – wiki基地

作者： /

PostgreSQL 指南：安装、配置与性能优化

PostgreSQL，也被称为 Postgres，是一种强大的开源对象关系型数据库系统 (ORDBMS)，它使用和扩展了 SQL 语言，并结合了许多安全地存储和扩展最复杂数据工作负载的功能。因其可靠性、数据完整性和兼容标准而闻名，PostgreSQL 已经成为从小型创业公司到大型企业广泛应用的热门选择。

本文旨在提供一个全面的 PostgreSQL 指南，涵盖安装、配置以及性能优化的各个方面，帮助你有效地利用 PostgreSQL 的强大功能。

一、安装 PostgreSQL

PostgreSQL 的安装过程因操作系统而异，以下分别介绍在 Linux、Windows 和 macOS 上的安装方法。

1. 在 Linux 上安装

大多数 Linux 发行版都提供了 PostgreSQL 包管理器。

  • Debian/Ubuntu:

    bash
    sudo apt update
    sudo apt install postgresql postgresql-contrib

    postgresql 包安装数据库服务器，postgresql-contrib 包包含额外的实用工具和扩展。
    * CentOS/RHEL:

    bash
    sudo yum install postgresql-server postgresql-contrib
    sudo postgresql-setup initdb
    sudo systemctl start postgresql
    sudo systemctl enable postgresql

    postgresql-server 安装数据库服务器，postgresql-contrib 包包含额外的实用工具和扩展。 postgresql-setup initdb 初始化数据库集群。 systemctl 用于启动和启用 PostgreSQL 服务。
    * Arch Linux:

    bash
    sudo pacman -S postgresql
    sudo su - postgres -c "initdb --locale=$LANG -D '/var/lib/postgres/data'"
    sudo systemctl start postgresql
    sudo systemctl enable postgresql

    initdb 初始化数据库集群。 systemctl 用于启动和启用 PostgreSQL 服务。

安装完成后，可以使用以下命令检查 PostgreSQL 的状态：

bash
sudo systemctl status postgresql

2. 在 Windows 上安装

可以从 PostgreSQL 官方网站下载 Windows 安装程序：https://www.postgresql.org/download/windows/

下载后，运行安装程序并按照提示进行操作。安装程序会引导你完成以下步骤：

  • 选择安装目录： 选择 PostgreSQL 的安装位置。
  • 选择数据目录： 选择存储数据库文件的数据目录。 重要: 确保数据目录有足够的空间。
  • 设置密码： 为 postgres 用户设置密码。 这是一个重要的安全步骤。
  • 选择端口： 选择 PostgreSQL 使用的端口 (默认为 5432)。
  • 选择本地设置： 选择数据库的默认区域设置。

安装完成后，安装程序会提供启动 Stack Builder 的选项，Stack Builder 允许你安装额外的工具和扩展，例如 pgAdmin (PostgreSQL 管理工具)。

3. 在 macOS 上安装

可以从 PostgreSQL 官方网站下载 macOS 安装程序：https://www.postgresql.org/download/macosx/

或者，可以使用 Homebrew 包管理器进行安装：

bash
brew install postgresql

安装完成后，可以启动 PostgreSQL 服务：

bash
brew services start postgresql

二、PostgreSQL 配置

PostgreSQL 的配置主要通过修改 postgresql.conf 文件进行。该文件通常位于数据目录下。 使用以下命令可以找到 data 目录：

bash
psql -U postgres -c "SHOW data_directory;"

以下是一些重要的配置参数以及其优化策略：

1. 内存配置

  • shared_buffers： 指定 PostgreSQL 可以使用的共享内存量。 通常建议将其设置为服务器总内存的 25%，但具体数值取决于工作负载。如果你的服务器主要运行 PostgreSQL，可以将此值设置得更高。

    shared_buffers = 4GB # 如果总内存是16GB

  • work_mem： 指定单个查询操作 (例如排序或哈希连接) 可以使用的最大内存量。 增加此值可以加快复杂查询的速度，但也可能导致服务器内存耗尽。 建议从小处开始增加，并监控服务器的内存使用情况。

    work_mem = 64MB

  • maintenance_work_mem： 指定维护操作 (例如 VACUUM、CREATE INDEX) 可以使用的最大内存量。 增加此值可以加快维护操作的速度。

    maintenance_work_mem = 512MB

  • effective_cache_size: 估算操作系统可用于磁盘缓存的内存量。 这有助于查询规划器做出更明智的决策。 通常建议将其设置为服务器总内存减去 shared_buffers 的剩余部分的 50%-75%。

    effective_cache_size = 8GB # 如果总内存是16GB 并且 shared_buffers 是 4GB

2. 连接配置

  • max_connections： 指定服务器允许的最大并发连接数。 默认值为 100。 如果服务器需要处理大量的并发连接，则需要增加此值。 但增加此值也会增加服务器的内存消耗。

    max_connections = 200

  • superuser_reserved_connections: 保留给超级用户的连接数。 确保即使所有普通连接都已用尽，管理员仍然可以连接到服务器。

    superuser_reserved_connections = 3

3. 日志配置

  • log_destination： 指定日志输出的目标。 可以设置为 stderr (标准错误输出)、csvlog (CSV 格式的文件)、syslog (系统日志) 或 eventlog (Windows 事件日志)。

    log_destination = 'stderr'

  • logging_collector： 启用或禁用日志收集器进程。 如果启用，日志将被收集到文件中。

    logging_collector = on

  • log_directory： 指定日志文件的存储目录。

    log_directory = 'pg_log'

  • log_filename： 指定日志文件的名称模式。

    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

  • log_rotation_age： 指定日志文件轮换的年龄。

    log_rotation_age = 1d

  • log_rotation_size： 指定日志文件轮换的大小。

    log_rotation_size = 10MB

  • log_statement： 控制记录哪些 SQL 语句。 可以设置为 none (不记录任何语句)、ddl (记录所有数据定义语言语句) 或 all (记录所有语句)。 在开发和调试阶段，all 可能很有用，但在生产环境中，建议设置为 ddl 或 none，以减少日志记录开销。

    log_statement = 'ddl'

  • log_min_duration_statement： 仅记录执行时间超过指定毫秒数的语句。 设置此值可以帮助你识别慢查询。

    log_min_duration_statement = 500 # 500毫秒

4. WAL（预写式日志）配置

  • wal_level： 指定 WAL 的级别。 可以设置为 minimal (仅记录恢复所需的信息)、replica (记录用于流复制的信息) 或 logical (记录用于逻辑复制的信息)。 replica 是最常见的选择。

    wal_level = replica

  • wal_buffers： 指定 WAL 缓冲区的大小。 增加此值可以减少 WAL 写入的延迟。

    wal_buffers = 16MB

  • checkpoint_timeout： 指定自动 WAL 检查点之间的时间间隔。 较短的间隔可以减少恢复时间，但会增加 I/O 开销。 较长的间隔会减少 I/O 开销，但会增加恢复时间。

    checkpoint_timeout = 30min

  • checkpoint_completion_target： 指定检查点完成的目标比例。 这控制了检查点传播到后台需要多长时间。

    checkpoint_completion_target = 0.9

  • max_wal_size: 允许 WAL 增长到的最大总大小。

    max_wal_size = 10GB

  • min_wal_size: 只要 WAL 使用量低于此设置，WAL 就可以被回收以供将来的检查点使用，无需先将其归档。

    min_wal_size = 1GB

5. 其他配置

  • default_statistics_target： 指定统计信息目标的默认值。 统计信息用于查询规划器，以做出更好的决策。 增加此值可以提高查询计划的准确性，但也可能会增加统计信息收集的时间。

    default_statistics_target = 100

  • autovacuum： 启用或禁用自动 VACUUM。 VACUUM 用于清理死元组并更新统计信息。 建议启用自动 VACUUM。

    autovacuum = on

  • autovacuum_max_workers： 指定自动 VACUUM 工作进程的最大数量。

    autovacuum_max_workers = 3

  • autovacuum_vacuum_scale_factor 和 autovacuum_analyze_scale_factor: 这些参数控制何时自动启动 VACUUM 和 ANALYZE。 它们被应用到表的大小来确定自动任务何时应该启动。 降低这些值会使自动任务更频繁的发生。

    autovacuum_vacuum_scale_factor = 0.2
    autovacuum_analyze_scale_factor = 0.1

应用配置更改：

修改 postgresql.conf 文件后，需要重新启动 PostgreSQL 服务才能使更改生效：

bash
sudo systemctl restart postgresql

三、PostgreSQL 性能优化

除了配置参数外，还有许多其他方法可以优化 PostgreSQL 的性能。

1. 索引

  • 选择正确的索引类型： PostgreSQL 提供了多种索引类型，例如 B-tree、Hash、GiST、SP-GiST、GIN 和 BRIN。 选择正确的索引类型对于查询性能至关重要。
    • B-tree: 用于范围查询和精确匹配查询。 这是最常用的索引类型。
    • Hash: 仅用于精确匹配查询。
    • GiST 和 SP-GiST: 用于空间数据和全文搜索。
    • GIN: 用于复合数据类型 (例如数组和 JSONB)。
    • BRIN: 用于大型、线性排序的数据集。
  • 避免过度索引： 过多的索引会降低写入性能，并增加数据库的大小。 只创建必要的索引。
  • 定期重建索引： 随着时间的推移，索引可能会变得碎片化，从而降低查询性能。 定期使用 REINDEX 命令重建索引。

  • 使用表达式索引： 如果查询使用表达式来过滤数据，可以使用表达式索引来加快查询速度。 例如:

    “`sql
    CREATE INDEX idx_lower_email ON users (lower(email));

    SELECT * FROM users WHERE lower(email) = ‘[email protected]’;
    “`

2. 查询优化

  • 使用 EXPLAIN 命令： EXPLAIN 命令可以显示查询规划器如何执行查询。 这可以帮助你识别查询瓶颈，并优化查询。

    sql
    EXPLAIN SELECT * FROM users WHERE age > 30;

  • 避免使用 SELECT *： 只选择需要的列。 这可以减少 I/O 开销和网络传输。

  • 使用 JOIN 而不是子查询： 在许多情况下，JOIN 的性能优于子查询。
  • 使用 LIMIT 限制结果集： 如果只需要部分结果，请使用 LIMIT 子句。
  • 使用 WHERE 子句过滤数据： 在查询的早期阶段使用 WHERE 子句过滤数据，以减少需要处理的数据量。
  • 使用预处理语句 (Prepared Statements)： 预处理语句可以提高重复执行的查询的性能。

3. 硬件优化

  • 使用快速存储： 使用 SSD 或 NVMe 存储可以显著提高数据库的性能。
  • 增加内存： 增加服务器的内存可以提高查询性能，并减少磁盘 I/O。
  • 使用多核 CPU： PostgreSQL 可以利用多核 CPU 并行处理查询。
  • 优化操作系统设置： 优化操作系统的文件系统和网络设置可以提高数据库的性能。

4. 定期维护

  • VACUUM： VACUUM 命令回收死元组占用的空间并更新表统计信息。 频繁地运行VACUUM能够优化查询性能。
  • ANALYZE： ANALYZE 命令收集表和索引的统计信息，供查询优化器使用。 定期运行ANALYZE确保优化器做出正确的决定。
  • REINDEX： REINDEX 命令重建索引，能够提高查询性能。

5. 连接池

在高并发场景下，频繁地创建和销毁数据库连接会带来很大的开销。 使用连接池可以减少连接创建和销毁的开销，从而提高性能。 常用的连接池工具包括 pgBouncer 和 pgbouncer。

四、监控 PostgreSQL

监控 PostgreSQL 的性能对于识别和解决问题至关重要。

  • pgAdmin: pgAdmin 提供了图形化界面，可以用于监控 PostgreSQL 的性能。
  • pg_stat_statements： pg_stat_statements 扩展可以跟踪执行的 SQL 语句的统计信息。 这可以帮助你识别慢查询。
  • 操作系统监控工具： 使用操作系统监控工具 (例如 top、vmstat 和 iostat) 可以监控服务器的 CPU、内存和 I/O 使用情况。

结论

PostgreSQL 是一种功能强大的数据库系统，通过合理的安装、配置和性能优化，可以满足各种应用程序的需求。 本文提供了一个全面的 PostgreSQL 指南，涵盖了安装、配置和性能优化的各个方面。 希望本文能够帮助你有效地利用 PostgreSQL 的强大功能。 记住，性能优化是一个持续的过程，需要不断地监控和调整，才能获得最佳的性能。 定期回顾和调整你的配置，并不断学习新的优化技术，将确保你的PostgreSQL数据库保持最佳状态。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部