zhouzhongping/wiki
1
0
Fork 0
Code
tmp-virt
wiki/Tech/database-system/PostgreSQL/安装配置.md

742 lines
36 KiB
Markdown
Raw Permalink Normal View History

Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
---
title: 安装配置
description: Postgresql 安装配置
keywords:
- Postgresql
- 安装配置
tags:
项目：重构
2023-11-09 17:30:33 +08:00
- 数据库系统/PostgreSQL
技术：标签调整
2024-04-26 21:42:22 +08:00
- 技术/数据库
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
author: 7Wate
date: 2023-09-20
---
## 序言
对于初学者和有经验的开发者来说，正确地安装和配置 PostgreSQL 是至关重要的。一个恰当的配置不仅可以确保数据库的稳定运行，还可以充分发挥其性能，满足复杂的业务需求。然而，由于 PostgreSQL 的功能丰富和配置项繁多，很多人在安装和配置过程中可能会遇到困惑和挑战。
PostgreSQL 提供了多种安装方式，其中源码安装和编译安装是最为基础和原始的方法。这两种方法虽然相对复杂，但为用户提供了极大的灵活性，允许他们根据自己的需求进行定制和优化。
## 依赖
| 依赖 | 版本 | 用途 | 状态 | 备注 |
| -------------------- | ------------ | ------------------------------------------- | ---- | --------------------------------------------- |
| GNU make | 3.81 或更高 | 构建 PostgreSQL | ✅ | 必需; 其他版本或其他 make 程序不可用 |
| ISO/ANSI C compiler | C99 兼容 | 编译 PostgreSQL | ✅ | 必需; 推荐使用 GCC 的最新版本 |
| tar | 无特定版本 | 解压源代码分发包 | ✅ | 必需; 需要配合 gzip 或 bzip2 使用 |
| GNU Readline library | 无特定版本 | 提高 PostgreSQL 命令行 SQL 解释器的用户体验 | ✅ | 必需; 可以选择不使用或使用 libedit 库作为替代 |
| zlib | 无特定版本 | 支持 pg_dump 和 pg_restore 中的压缩存档功能 | ✅ | 必需; 可以选择不使用 |
| Perl | 5.8.3 或更高 | 构建 PL/Perl 服务器编程语言 | 🟢 | 可选; 需要 libperl 共享库和头文件 |
| Python | 3.2 或更高 | 构建 PL/Python 服务器编程语言 | 🟢 | 可选; 需要 libpython 共享库和头文件 |
| Tcl | 8.4 或更高 | 构建 PL/Tcl 程序语言 | 🟢 | 可选; 需要 Tcl 安装 |
| OpenSSL | 1.0.1 或更高 | 支持加密的客户端连接和随机数生成 | 🟢 | 可选; 用于支持加密的客户端连接和随机数生成 |
| Gettext API | 无特定版本 | 实现 Native Language Support (NLS) | 🟢 | 可选; 用于显示程序的非英语消息 |
| Kerberos | 无特定版本 | 支持 Kerberos 认证 | 🟢 | 可选; 用于支持 Kerberos 认证 |
| OpenLDAP | 无特定版本 | 支持 OpenLDAP 认证 | 🟢 | 可选; 用于支持 OpenLDAP 认证 |
| PAM | 无特定版本 | 支持 PAM 认证 | 🟢 | 可选; 用于支持 PAM 认证 |
| LZ4 | 无特定版本 | 支持 LZ4 数据压缩 | 🟢 | 可选; 用于支持 LZ4 数据压缩 |
| Zstandard | 1.4.0 或更高 | 支持 Zstandard 数据压缩 | 🟢 | 可选; 用于支持 Zstandard 数据压缩 |
- ✅ 表示必需
- 🟢 表示可选
### 基本依赖
```shell
# YUM 更新软件仓库 && 安装必需的依赖
sudo yum update -y && sudo yum install -y make gcc tar gzip bzip2 readline readline-devel zlib zlib-devel
# APT 更新软件仓库 && 安装必需的依赖
sudo apt update && sudo apt install -y make gcc tar gzip bzip2 libreadline-dev zlib1g-dev
```
### 拓展依赖
```shell
# YUM 更新软件仓库 && 安装可选的依赖
sudo yum update -y && sudo yum install -y perl perl-devel python3 python3-devel tcl tcl-devel openssl openssl-devel gettext krb5 krb5-devel openldap openldap-devel pam pam-devel lz4 lz4-devel zstd zstd-devel
# APT 更新软件仓库 && 安装可选的依赖
sudo apt update && sudo apt install -y perl libperl-dev python3 python3-dev tcl tcl-dev libssl-dev gettext krb5-user libkrb5-dev libldap2-dev libpam0g-dev liblz4-dev libzstd-dev
```
### 依赖检查
```shell
#!/bin/bash
# 检查包管理器
# 可自定义依赖检查项目
if command -v yum &> /dev/null; then
PACKAGE_MANAGER="yum"
REQUIRED_DEPENDENCIES=("make" "gcc" "tar" "gzip" "bzip2" "readline" "readline-devel" "zlib" "zlib-devel")
OPTIONAL_DEPENDENCIES=("perl" "perl-devel" "python3" "python3-devel" "tcl" "tcl-devel" "openssl" "openssl-devel" "gettext" "krb5" "krb5-devel" "openldap" "openldap-devel" "pam" "pam-devel" "lz4" "lz4-devel" "zstd" "zstd-devel")
elif command -v apt-get &> /dev/null; then
PACKAGE_MANAGER="apt-get"
REQUIRED_DEPENDENCIES=("make" "gcc" "tar" "gzip" "bzip2" "libreadline-dev" "zlib1g-dev")
OPTIONAL_DEPENDENCIES=("perl" "libperl-dev" "python3" "python3-dev" "tcl" "tcl-dev" "libssl-dev" "gettext" "krb5-user" "libkrb5-dev" "libldap2-dev" "libpam0g-dev" "liblz4-dev" "libzstd-dev")
else
echo "❌ 不支持的包管理器。"
exit 1
fi
# 定义一个函数来检查依赖是否安装，并显示版本信息
check_dependency() {
local package_name=$1
if [ -z "$package_name" ]; then
echo "错误：未提供依赖项名称。"
exit 1
fi
if [ "$PACKAGE_MANAGER" == "yum" ]; then
if rpm -q $package_name &> /dev/null; then
version=$(rpm -q $package_name)
printf " ✅ %-20s 版本: %s\n" $package_name "$version"
else
printf " ❌ %-20s 未安装\n" $package_name
fi
elif [ "$PACKAGE_MANAGER" == "apt-get" ]; then
if dpkg -s $package_name &> /dev/null; then
version=$(dpkg -s $package_name | grep Version | cut -d' ' -f2)
if [ -z "$version" ]; then
version="未知"
fi
printf " ✅ %-20s 版本: %s\n" $package_name "$version"
else
printf " ❌ %-20s 未安装\n" $package_name
fi
fi
}
# 定义一个函数来检查依赖项数组
check_dependencies() {
local dependencies=("$@")
for dependency in "${dependencies[@]}"; do
check_dependency "$dependency"
done
}
# 检查必需的依赖
echo "Postgres 依赖检查"
echo "=> 必需的依赖："
check_dependencies "${REQUIRED_DEPENDENCIES[@]}"
# 检查可选的依赖
echo ""
echo "=> 可选的依赖："
check_dependencies "${OPTIONAL_DEPENDENCIES[@]}"
# 输出检查完成信息
echo ""
echo "=> 依赖检查完成！"
```
## 安装
### 源码安装
#### 1. 准备工作
```shell
# 创建用户
sudo adduser postgres
sudo passwd postgres
# 创建目录
sudo mkdir -p /opt/postgresql/{data,archive,scripts,backup,pg16,source,logs,config,temp,extensions,upgrade_scripts}
# 配置目录权限，后续默认以 postgres 用户操作
sudo chown -R postgres:postgres /opt/postgresql
sudo chmod -R 775 /opt/postgresql
```
postgresql 目录下文件夹其对应的用途。
| 目录 | 用途 |
| :--------------- | :------------------- |
| /data | 存储 PostgreSQL 数据 |
| /archive | 存储归档日志文件 |
| /scripts | 存储 PostgreSQL 脚本 |
| /backup | 存储数据库备份 |
| /pgsql | 软连接到 /pg16 |
| /pg16 | PostgreSQL 16 版本 |
| /source | 存储源代码或配置文件 |
| /logs | 存储 PostgreSQL 日志 |
| /config | 存储 PostgreSQL 配置 |
| /temp | 存储临时文件 |
| /extensions | 存储 PostgreSQL 扩展 |
| /upgrade_scripts | 存储升级脚本 |
#### 2. 下载源码
项目：重构
2023-11-09 17:30:33 +08:00
PostgreSQL 源码可以在官方提供的 [FTP 渠道](https://www.postgresql.org/ftp/source/) 下载。
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
![PostgreSQL 源码](https://static.7wate.com/img/2023/09/22/7f25aaababea4.png)
```shell
# 切换用户
su - postgres
# 下载 PG16
postgres@debian:/opt/postgresql/source$ wget https://ftp.postgresql.org/pub/source/v16.0/postgresql-16.0.tar.gz
# 解压 PG16
postgres@debian:/opt/postgresql/source$ tar -zxvf postgresql-16.0.tar.gz
```
#### 3. 配置源码
首先，你需要配置源代码树以适应你的系统并选择你想要的选项。这是通过运行 `configure` 脚本完成的。你可以使用以下命令进行默认安装：
```shell
# 安装 /opt/postgresql/pg16/ 路径
postgres@debian:/opt/postgresql/source/postgresql-16.0$ ./configure --prefix=/opt/postgresql/pg16/
```
```shell
# 默认安装
./configure
# 指定多个选项
./configure --prefix=/usr/local/pgsql --bindir=/usr/local/pgsql/bin --datadir=/usr/local/pgsql/data --libdir=/usr/local/pgsql/lib
# 禁用某些功能
./configure --without-readline --without-zlib
# 启用调试和指定编译器
./configure --enable-debug --with-pgport=5432 CC=gcc CFLAGS='-O2 -g'
```
| 选项 | 描述 |
| :--------------------- | :----------------------------------------------------------- |
| `--prefix=<目录>` | 指定安装目录的前缀，即 PostgreSQL 将被安装到的基本目录。 |
项目：重构
2023-11-09 17:30:33 +08:00
| `--bindir=<目录>` | 指定可执行文件的目录，例如 `pg_ctl` 和 `psql`。 |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| `--sbindir=<目录>` | 指定系统管理员可执行文件的目录，例如 `pg_ctl` 和 `pg_config`。 |
| `--datadir=<目录>` | 指定数据文件的根目录，例如数据表空间。 |
| `--libdir=<目录>` | 指定库文件的目录，例如共享库文件。 |
| `--sysconfdir=<目录>` | 指定配置文件的目录。 |
| `--docdir=<目录>` | 指定文档文件的目录。 |
| `--includedir=<目录>` | 指定包含头文件的目录，用于开发 PostgreSQL 扩展。 |
| `--with-openssl` | 启用 OpenSSL 支持，用于加密通信。 |
| `--with-zlib` | 启用 Zlib 支持，用于数据压缩。 |
| `--with-libxml` | 启用 Libxml2 支持，用于 XML 数据类型。 |
| `--with-python` | 启用 Python 支持，用于编写存储过程和触发器。 |
| `--with-perl` | 启用 Perl 支持，用于编写存储过程和触发器。 |
| `--with-tcl` | 启用 Tcl 支持，用于编写存储过程和触发器。 |
| `--with-pam` | 启用 PAM (Pluggable Authentication Modules) 支持。 |
| `--with-ldap` | 启用 LDAP 支持，用于身份验证和用户管理。 |
| `--with-krb5` | 启用 Kerberos 支持，用于身份验证。 |
| `--with-system-tzdata` | 使用操作系统提供的时区信息。 |
| `--enable-debug` | 启用调试支持，构建时包括调试信息。 |
| `--disable-ipv6` | 禁用 IPv6 支持。 |
#### 4. 构建二进制
可以输入以下任一命令开始构建，可以使用 -j 选项指定线程数加快速度。
```shell
make
# 多线程构建
make -j 8
```
如果你想构建所有可以构建的内容，包括文档（HTML 和 man 页面）和附加模块（contrib），则可以输入：
```shell
make world
```
或者如果你想构建所有可以构建的内容，包括附加模块（contrib），但不包括文档，可以输入：
```shell
make world-bin
```
#### 5. 测试软件
如果你想在安装之前测试新构建的服务器，你可以在此时运行回归测试。这是一个测试套件，用于验证 PostgreSQL 是否按照开发人员预期的方式在你的机器上运行。可以使用以下命令进行测试：
```shell
make check
```
#### 6. 安装
要安装 PostgreSQL，可以输入：
```shell
make install
```
此步骤将把文件安装到第一步中指定的目录中。**确保你有适当的权限写入该区域。**或者，你可以提前创建目标目录并安排授予适当的权限。
如果你构建了上面的 world，可以输入：
```shell
make install-world
```
这也会安装文档。如果你在上面构建了不包含文档的 world，可以输入：
```shell
make install-world-bin
```
#### 7. 配置软连
设置 postgres 软链接，方便系统后续升级。
```shell
ln -s /opt/postgres/pg16 /opt/postgresql/pgsql
```
#### 8. 卸载和清理
如果要撤销安装，可以使用命令 `make uninstall`。但是，这不会删除任何创建的目录。
安装后，你可以通过从源代码树中删除构建文件来释放磁盘空间，命令是 `make clean`。这将保留 `configure` 程序创建的文件，以便你稍后可以使用 `make` 重新构建所有内容。要将源代码树重置为分发时的状态，请使用 `make distclean`。
### 二进制安装
#### 1. 下载
在开始二进制安装之前，你需要首先下载适用于你的操作系统的 PostgreSQL 二进制包。你可以从 PostgreSQL 官方网站或者你的操作系统的软件库中获取它。
```shell
# 通过 wget 命令下载
wget https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 通过 curl 命令下载
curl -O https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
```
#### 2. 安装
安装二进制包通常比源码安装更快更简单。根据你的操作系统和包管理器，这里介绍几种常用的安装方法：
```shell
# 在 Red Hat/CentOS 中安装
sudo rpm -ivh pgdg-redhat-repo-latest.noarch.rpm
sudo yum install postgresql-server
# 在 Debian/Ubuntu 中安装
sudo apt-get update
sudo apt-get install postgresql
# 在 Debian/Ubuntu 中使用自定义选项进行安装
sudo apt install postgresql --prefix=/usr/local/pgsql --datadir=/usr/local/pgsql/data
# 在 Red Hat/CentOS 中使用自定义选项进行安装
sudo yum install postgresql-server --prefix=/usr/local/pgsql --datadir=/usr/local/pgsql/data
```
安装过程中，你可以使用以下选项来自定义安装：
| 选项 | 描述 |
| ----------------------- | -------------------------------------------------- |
| `--prefix=PREFIX` | 定义安装的根目录，所有文件和目录都将放在这个目录下 |
| `--exec-prefix=EPREFIX` | 定义架构相关文件的安装目录，通常与 `--prefix` 相同 |
| `--bindir=DIR` | 定义用户可执行文件（如 psql）的安装目录 |
| `--datadir=DIR` | 定义只读架构独立数据的安装目录 |
| `--libdir=DIR` | 定义库文件的安装目录 |
| `--sysconfdir=DIR` | 定义系统级配置文件的安装目录 |
| `--sharedstatedir=DIR` | 定义可修改的单主机数据的安装目录 |
| `--localstatedir=DIR` | 定义可修改的多主机数据的安装目录 |
| `--libexecdir=DIR` | 定义可执行程序和其他程序运行的安装目录 |
项目：重构
2023-11-09 17:30:33 +08:00
| `--includedir=DIR` | 定义 C 头文件的安装目录 |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| `--mandir=DIR` | 定义 man 文档的安装目录 |
| `--docdir=DIR` | 定义文档的根目录 |
#### 3. 验证
安装完成后，验证 PostgreSQL 是否已成功安装和配置。可以通过以下命令来验证安装：
```shell
# 检查 PostgreSQL 服务状态
sudo systemctl status postgresql
# 使用 psql 命令行工具连接到 PostgreSQL 服务器
psql -U postgres
# 显示 PostgreSQL 服务器的版本信息
psql -U postgres -c 'SELECT version();'
```
二进制安装成功后，可以直接使用，默认不需要后续的配置。
## 设置
### 1. 共享库设置 (Shared Libraries)
在某些系统中，你需要告诉系统如何找到新安装的共享库。这不包括 FreeBSD, HP-UX, Linux, NetBSD, OpenBSD 和 Solaris 等系统。设置共享库搜索路径的方法因平台而异，但最常用的方法是设置环境变量 `LD_LIBRARY_PATH`。以下是如何在 Bourne shell (如 sh, ksh, bash, zsh) 中设置：
```shell
LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH
```
### 2. 环境变量设置 (Environment Variables)
如果你将 PostgreSQL 安装到 `/usr/local/pgsql` 或其他默认情况下不搜索程序的位置，你应该将 `/usr/local/pgsql/bin`（或你在第一步中设置的 `--bindir` 选项）添加到你的 `PATH` 中。这样可以使 PostgreSQL 的使用更加方便。以下是如何在 Bourne shell 中设置：
```shell
PATH=/usr/local/pgsql/bin:$PATH
export PATH
```
为了让你的系统找到 man 文档，你需要将以下行添加到一个 shell 启动文件中，除非你安装到了默认搜索的位置：
```shell
MANPATH=/usr/local/pgsql/share/man:$MANPATH
export MANPATH
```
环境变量 `PGHOST` 和 `PGPORT` 指定了数据库服务器的主机和端口，覆盖了编译时的默认设置。如果你打算远程运行客户端应用程序，那么每个计划使用数据库的用户都应设置 `PGHOST`。这不是必需的，但可以通过大多数客户端程序的命令行选项来传递设置。
### 3. 配置脚本示例
```shell
#!/bin/bash
# 将配置写入 ~/.bash_profile 文件
cat >> ~/.bash_profile <<EOF
export PGDATA=/opt/postgres/data
export PGHOME=/opt/postgres/pgsql
export LD_LIBRARY_PATH=$pg_home/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$pg_home/bin:$PATH:.
export MANPATH=$pg_home/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF
# 重新加载 ~/.bash_profile
source ~/.bash_profile
```
## 使用
项目：重构
2023-11-09 17:30:33 +08:00
### Psql 命令
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| 内置命令 | 作用描述 |
| --------------- | ------------------------------------------------ |
| `\q` | 退出 psql |
| `\l` 或 `\list` | 列出所有数据库 |
| `\c [数据库名]` | 连接到新数据库 |
| `\dt` | 列出当前数据库中的所有表 |
| `\d [表名]` | 显示表的结构（列、数据类型、权限等） |
| `\du` | 列出所有 PostgreSQL 用户角色 |
| `\e` | 打开文本编辑器 |
| `\a` | 切换输出格式（对齐或非对齐） |
| `\x` | 切换扩展显示模式 |
| `\timing` | 显示 SQL 查询的执行时间 |
| `\i [文件名]` | 执行文件中的 SQL 命令 |
| `\o [文件名]` | 将查询结果输出到文件 |
| `\s` | 显示命令历史 |
| `\?` | 显示所有 psql 命令的帮助信息 |
| `\h [SQL命令]` | 显示 SQL 命令的语法和选项 |
| `\set` | 列出所有 psql 变量及其值 |
| `\unset` | 删除一个或多个 psql 变量 |
| `\g` 或 `;` | 执行查询 |
| `\qecho [文本]` | 在输出中显示文本（通常用于脚本中添加注释或空行） |
| `\conninfo` | 显示当前数据库连接的详细信息 |
### 1. 初始化数据库目录
首先，需要使用 `initdb` 命令来初始化数据库目录。该命令会创建必要的目录结构和系统目录，以便 PostgreSQL 数据库能够运行。
```shell
# -D 选项指定数据库目录的路径
initdb -D /path/to/data/directory
```
### 2. 配置文件
#### postgresql.conf
PostgreSQL 的主配置文件，可以在这里设置各种参数，如内存使用、连接数等。下面是常用配置：
| 配置选项 | 作用 | 示例配置 |
| --------------------------------- | ------------------------------------------ | ------------------------------------------------- |
| **连接设置** | | |
| `listen_addresses` | 定义服务器监听的地址 | `listen_addresses = '*'` |
| `port` | 定义服务器监听的端口 | `port = 5432` |
| `max_connections` | 最大并发连接数 | `max_connections = 100` |
| `superuser_reserved_connections` | 为超级用户保留的连接数 | `superuser_reserved_connections = 3` |
项目：重构
2023-11-09 17:30:33 +08:00
| `unix_socket_directories` | Unix 域套接字的目录 | `unix_socket_directories = '/var/run/postgresql'` |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| **认证和安全** | | |
项目：重构
2023-11-09 17:30:33 +08:00
| `ssl` | 是否启用 SSL | `ssl = on` |
| `ssl_cert_file` | SSL 证书文件的位置 | `ssl_cert_file = '/path/to/server.crt'` |
| `ssl_key_file` | SSL 私钥文件的位置 | `ssl_key_file = '/path/to/server.key'` |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| `password_encryption` | 密码加密方式 | `password_encryption = scram-sha-256` |
| `hba_file` | `pg_hba.conf` 文件的位置 | `hba_file = '/path/to/pg_hba.conf'` |
| **内存使用** | | |
| `shared_buffers` | 用于缓存的内存大小 | `shared_buffers = 128MB` |
| `work_mem` | 查询操作可以使用的内存量 | `work_mem = 4MB` |
项目：重构
2023-11-09 17:30:33 +08:00
| `maintenance_work_mem` | 维护操作（如 VACUUM）可以使用的内存量 | `maintenance_work_mem = 64MB` |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| `effective_cache_size` | 估计的操作系统缓存大小 | `effective_cache_size = 512MB` |
| **查询调优** | | |
| `seq_page_cost` | 顺序磁盘页的成本 | `seq_page_cost = 1.0` |
| `random_page_cost` | 随机磁盘页的成本 | `random_page_cost = 4.0` |
项目：重构
2023-11-09 17:30:33 +08:00
| `cpu_tuple_cost` | 处理每个元组的 CPU 成本 | `cpu_tuple_cost = 0.01` |
| `cpu_index_tuple_cost` | 处理每个索引元组的 CPU 成本 | `cpu_index_tuple_cost = 0.005` |
| `cpu_operator_cost` | 执行操作符的 CPU 成本 | `cpu_operator_cost = 0.0025` |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| **日志记录** | | |
| `logging_collector` | 是否启用日志收集 | `logging_collector = on` |
| `log_directory` | 日志文件的存储目录 | `log_directory = 'pg_log'` |
| `log_filename` | 日志文件的命名方式 | `log_filename = 'postgresql.log'` |
项目：重构
2023-11-09 17:30:33 +08:00
| `log_statement` | 记录的 SQL 语句类型（例如所有语句或只有 DDL） | `log_statement = 'all'` |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| `log_duration` | 是否记录语句的执行时间 | `log_duration = on` |
| `log_min_duration_statement` | 记录执行时间超过指定毫秒数的语句 | `log_min_duration_statement = 500` |
| **运行时统计** | | |
| `track_activities` | 是否跟踪服务器进程的活动 | `track_activities = on` |
| `track_counts` | 是否跟踪访问统计信息 | `track_counts = on` |
项目：重构
2023-11-09 17:30:33 +08:00
| `track_io_timing` | 是否跟踪 I/O 计时信息 | `track_io_timing = on` |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| `stats_temp_directory` | 临时统计数据的目录 | `stats_temp_directory = 'pg_stat_tmp'` |
| **锁定** | | |
| `deadlock_timeout` | 死锁检测的超时时间 | `deadlock_timeout = 1s` |
| `max_locks_per_transaction` | 每个事务可以持有的最大锁数 | `max_locks_per_transaction = 64` |
| **自动 VACUUM** | | |
项目：重构
2023-11-09 17:30:33 +08:00
| `autovacuum` | 是否启用自动 VACUUM | `autovacuum = on` |
| `autovacuum_vacuum_scale_factor` | 触发自动 VACUUM 的脏数据比例 | `autovacuum_vacuum_scale_factor = 0.2` |
| `autovacuum_analyze_scale_factor` | 触发自动 ANALYZE 的脏数据比例 | `autovacuum_analyze_scale_factor = 0.1` |
| `autovacuum_vacuum_cost_limit` | 自动 VACUUM 操作的成本限制 | `autovacuum_vacuum_cost_limit = 200` |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| **客户端连接默认值** | | |
| `DateStyle` | 默认的日期格式 | `DateStyle = 'ISO, MDY'` |
| `TimeZone` | 默认的时区 | `TimeZone = 'UTC'` |
| `lc_messages` | 消息的本地化设置 | `lc_messages = 'en_US.UTF-8'` |
| `lc_monetary` | 货币格式的本地化设置 | `lc_monetary = 'en_US.UTF-8'` |
| `lc_numeric` | 数字格式的本地化设置 | `lc_numeric = 'en_US.UTF-8'` |
| `lc_time` | 时间和日期格式的本地化设置 | `lc_time = 'en_US.UTF-8'` |
| **LOCALE** | | |
| `lc_collate` | 字符串排序的本地化设置 | `lc_collate = 'en_US.UTF-8'` |
| `lc_ctype` | 字符分类的本地化设置 | `lc_ctype = 'en_US.UTF-8'` |
#### pg_hba.conf
`pg_hba.conf` 文件用于控制哪些客户端可以连接到数据库，以及它们可以使用的认证方法。以下是 `pg_hba.conf` 中的常见配置项、作用和示例配置：
| 类型 | 数据库 | 用户 | 地址 | 认证方法 | 说明 |
| ----------- | ------ | -------- | ---------------- | -------- | -------------------------------------------- |
| `local` | `all` | `all` | | `trust` | 本地所有用户对所有数据库的连接不需要密码 |
项目：重构
2023-11-09 17:30:33 +08:00
| `host` | `all` | `all` | `127.0.0.1/32` | `md5` | 从本地通过 TCP/IP 连接的所有用户需要密码 |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
| `host` | `mydb` | `myuser` | `192.168.1.0/24` | `md5` | 从指定网络连接到指定数据库的指定用户需要密码 |
项目：重构
2023-11-09 17:30:33 +08:00
| `hostssl` | `all` | `all` | `0.0.0.0/0` | `md5` | 从任何地址通过 SSL 连接的所有用户需要密码 |
| `hostnossl` | `all` | `all` | `0.0.0.0/0` | `reject` | 拒绝非 SSL 连接 |
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
项目：重构
2023-11-09 17:30:33 +08:00
- **类型**：连接类型。常见的有 `local`（Unix 域套接字）、`host`（TCP/IP 套接字）、`hostssl`（仅限 SSL 的 TCP/IP 连接）和 `hostnossl`（不使用 SSL 的 TCP/IP 连接）。
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
- **数据库**：适用的数据库名。`all` 表示所有数据库。
- **用户**：适用的用户名。`all` 表示所有用户。
项目：重构
2023-11-09 17:30:33 +08:00
- **地址**：客户端的 IP 地址。对于 `local` 类型，这一列是空的。对于 `host` 类型，这是一个 CIDR 地址或一个 IP 地址和一个掩码。
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
- **认证方法**：常见的有 `trust`（不需要密码）、`reject`（拒绝连接）、`md5`（密码认证）、`password`（明文密码）等。
项目：重构
2023-11-09 17:30:33 +08:00
`pg_hba.conf` 文件的每一行都是一个连接规则，PostgreSQL 会按照文件中的顺序评估这些规则，直到找到一个匹配的规则为止。因此，顺序很重要，特别是当有多个规则可能匹配同一个连接时。
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
### 3. 启动数据库
一旦数据库目录被成功初始化，可以使用 `pg_ctl` 命令来启动 PostgreSQL 数据库。
```shell
# -D 选项指定了数据库目录的路径。
pg_ctl start -D /path/to/data/directory
# 或者
postgres -D /path/to/data/directory
```
### 4. 验证数据库是否正在运行
为了验证 PostgreSQL 数据库是否正在运行，可以使用 `psql` 命令连接到数据库并执行一个简单的查询，例如：
```shell
psql -U postgres -c "SELECT version();"
```
项目：重构
2023-11-09 17:30:33 +08:00
### 5. 配置 Systemctl 服务
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
1. 关闭 postgresql 数据库。
2. 创建一个 Systemd 服务文件，通常位于 `/etc/systemd/system/postgresql.service`。
3. 添加以下内容（需要结合配置脚本示例）。
```shell
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGPORT=5432
Environment=PGDATA=/opt/postgresql/data
OOMScoreAdjust=-1000
ExecStart=/opt/postgresql/pgsql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/opt/postgresql/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/opt/postgresql/pgsql//bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=30
[Install]
WantedBy=multi-user.target
```
项目：重构
2023-11-09 17:30:33 +08:00
1. 重新加载 Systemd 配置：`sudo systemctl daemon-reload`
2. 启动服务：`sudo systemctl start postgresql`
3. 设置开机启动：`sudo systemctl enable postgresql`
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
### 6. 创建用户和数据库
```shell
# 创建一个新用户
sudo -u postgres createuser -P <username>
# 创建一个新数据库
sudo -u postgres createdb -O <username> <dbname>
# 为用户分配特定权限
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <username>;"
```
### 7. 用户和角色管理
```shell
# 创建角色
sudo -u postgres createuser <role_name>
# 为角色分配权限
sudo -u postgres psql -c "GRANT <permission> ON <object> TO <role_name>;"
# 删除角色
sudo -u postgres dropuser <role_name>
# 设置密码策略和验证
sudo -u postgres psql -c "ALTER USER <username> WITH PASSWORD '<password>';"
```
### 8. 备份和恢复
备份和恢复是数据库管理的关键部分，确保数据的安全和完整性。PostgreSQL 提供了 `pg_dump` 和 `pg_restore` 工具来帮助管理员进行这些操作。
#### `pg_dump` 备份
项目：重构
2023-11-09 17:30:33 +08:00
`pg_dump` 是用于备份 PostgreSQL 数据库的工具。它可以生成包含数据库内容的 SQL 脚本文件或其他格式，如 tar 和 custom。使用以下命令进行全量备份：
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
```shell
pg_dump -U <username> -h <host> -p <port> -F c -b <dbname> > backup.dump
```
额外的选项包括 `--data-only` 和 `--schema-only`，这些选项可以让用户选择备份数据或者仅备份架构。
使用 `pg_restore` 来验证备份文件的完整性。即使你不打算立即恢复，也应该定期验证备份。
```shell
pg_restore --list backup.dump
```
#### `pg_restore` 恢复
`pg_restore` 是用于恢复由 `pg_dump` 创建的备份的工具。使用以下命令进行恢复：
```shell
pg_restore -U <username> -h <host> -p <port> -d <dbname> backup.dump
```
**注意**：
- 在恢复之前，确保目标数据库是空的或你已经备份了任何重要的数据。
- 如果备份是在不同版本的 PostgreSQL 上创建的，可能会遇到兼容性问题。在这种情况下，确保先测试恢复过程。
- 如果你正在恢复到一个活动的数据库，你可能需要在恢复前停止数据库服务。
## 更新
### 小版本升级（Minor Version Upgrade）
**小版本升级通常涉及到安全修复和错误修复，而不会改变数据库的内部格式。**这些升级通常比较简单，并且可以通过替换二进制文件来完成。如下是 PostgreSQL 15.3 升级到 15.4 的小版本升级示例：
**步骤**:
1. **备份数据**:
在进行任何升级之前，始终确保备份您的数据库。
```shell
pg_dumpall > backup.sql
```
2. **停止 PostgreSQL 服务**:
使用 `pg_ctl stop` 或您的系统的服务管理工具停止 PostgreSQL 服务。
```shell
pg_ctl stop
```
3. **替换二进制文件**:
下载并安装 PostgreSQL 15.4 的二进制文件。
项目：重构
2023-11-09 17:30:33 +08:00
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
4. **重启 PostgreSQL 服务**:
使用 `pg_ctl start` 或您的系统的服务管理工具重新启动 PostgreSQL 服务。
```shell
pg_ctl start
```
5. **检查日志和运行测试**:
检查 `/usr/local/pgsql/data/pg_log/` 中的日志文件以确保没有错误，并运行任何必要的测试以验证系统的功能。
### 跨版本升级（Major Version Upgrade）
跨版本升级涉及到从一个主版本升级到另一个主版本，例如从 PostgreSQL 15 升级到 PostgreSQL 16。**这些升级通常会涉及到内部格式的变化，因此需要更多的注意和准备。**如下是 PostgreSQL 15.4 升级到 16.0 的大版本升级示例：
**步骤**:
1. **备份数据**:
在进行任何升级之前，始终确保备份您的数据库。
```shell
pg_dumpall > backup.sql
```
2. **检查兼容性**:
检查新版本的 PostgreSQL 文档以确保您的应用程序和数据库架构与新版本兼容。
项目：重构
2023-11-09 17:30:33 +08:00
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
3. **安装新版本的 PostgreSQL**:
在同一系统上安装新版本的 PostgreSQL，但不要覆盖旧版本。
项目：重构
2023-11-09 17:30:33 +08:00
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
4. **使用 pg_upgrade 或逻辑备份和恢复**:
使用 `pg_upgrade` 工具快速升级。
```shell
pg_upgrade \
--old-datadir "/usr/local/pgsql/15.4/data" \
--new-datadir "/usr/local/pgsql/16.0/data" \
--old-bindir "/usr/local/pgsql/15.4/bin" \
--new-bindir "/usr/local/pgsql/16.0/bin"
```
5. **测试新集群**:
在新集群上运行测试以确保它按预期工作。
项目：重构
2023-11-09 17:30:33 +08:00
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
6. **切换到新集群**:
一旦满意，可以切换到新集群并更新任何连接字符串和配置。
项目：重构
2023-11-09 17:30:33 +08:00
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
7. **监控性能和错误**:
在升级后监控系统性能和错误，以确保一切正常。
项目：重构
2023-11-09 17:30:33 +08:00
Postgresql: 安装配置
2023-09-27 12:23:43 +08:00
8. **（可选）删除旧集群**:
确保新集群运行正常后，可以选择删除旧集群以释放空间。
Copy Permalink