打工人的Docs

外观

PostgreSQL 18 单机版安装指南 ​

1. 环境准备 ​

1.1 系统要求 ​

  • 操作系统：Ubuntu 22.04 LTS 或 Ubuntu 24.04 LTS
  • 内存：至少 2GB（建议 4GB 或更高）
  • CPU：1 核心（建议 2 核心或更高）

1.2 服务器规划示例 ​

主机名IP 地址角色配置
pg-standalone192.168.199.111PostgreSQL 单机服务器2C4G

1.3 磁盘配置要求 ​

合理的磁盘配置对 PostgreSQL 性能至关重要。本文档基于双磁盘架构设计：

1.3.1 磁盘配置详情 ​

text
磁盘配置：
- 系统盘：40GB (virtio-scsi, /dev/sda)
  - 挂载点：/ (根分区)
  - 文件系统：ext4
  - 用途：操作系统、基础软件、系统日志、PostgreSQL程序文件

- 数据盘：100GB+ (virtio-scsi, /dev/sdb)
  - 挂载点：/data
  - 文件系统：ext4
  - 用途：PostgreSQL 数据文件、WAL日志、备份文件
  - 建议：使用SSD存储以提高性能

1.3.2 /data 目录结构规划 ​

text
/data/
├── postgresql/              # PostgreSQL 数据目录
│   ├── 18/                  # 版本目录
│   │   └── main/            # 主数据目录
│   │       ├── base/        # 数据库文件
│   │       ├── global/      # 全局数据
│   │       ├── pg_wal/      # WAL 日志
│   │       └── log/         # 数据库日志
│   └── backup/              # 备份目录
└── tmp/                     # 临时文件目录（可选）

1.3.3 存储性能建议 ​

  • 系统盘 (/)：可使用普通存储，对性能要求不高
  • 数据盘：强烈建议使用SSD存储，承载所有数据库相关文件
  • 备份策略：建议定期将备份导出到外部存储或远程服务器

注意：目录的所有者和权限将在 PostgreSQL 安装并创建 postgres 用户后进行调整。

2. 系统初始化 ​

2.1 更新系统包 ​

bash
sudo apt update && sudo apt upgrade -y

2.2 安装必要的工具 ​

bash
sudo apt install -y wget curl vim net-tools telnet tree htop

2.3 配置主机名解析 ​

bash
sudo tee -a /etc/hosts <<EOF
192.168.199.111 pg-standalone
EOF

2.4 配置防火墙（如启用） ​

bash
sudo ufw allow 22/tcp
sudo ufw allow 5432/tcp
sudo ufw --force enable

3. 安装 PostgreSQL 18 ​

3.1 方法一：使用官方自动化脚本（推荐） ​

bash
# 安装 postgresql-common 包
sudo apt install -y postgresql-common

# 运行官方自动化配置脚本
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# 安装 PostgreSQL 18
sudo apt install -y postgresql-18

3.2 方法二：手动配置官方仓库 ​

bash
# 安装依赖
sudo apt install -y curl ca-certificates

# 创建密钥目录
sudo install -d /usr/share/postgresql-common/pgdg

# 导入 PostgreSQL 官方 GPG 密钥
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# 添加 PostgreSQL 官方仓库
. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"

# 更新包索引
sudo apt update

# 安装 PostgreSQL 18
sudo apt install -y postgresql-18

3.3 方法三：使用国内镜像加速 ​

bash
# 安装依赖
sudo apt install -y curl ca-certificates gnupg2 lsb-release

# 导入 GPG 密钥
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# 添加清华大学镜像源
echo "deb [signed-by=/etc/apt/trusted.gpg.d/postgresql.gpg] https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 更新包索引并安装
sudo apt update
sudo apt install -y postgresql-18

4. 配置数据目录到 /data ​

安装完成后，需要将 PostgreSQL 数据目录迁移到 /data 磁盘：

4.1 停止服务并创建目录 ​

bash
# 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 创建数据目录结构
sudo mkdir -p /data/postgresql/18/main
sudo mkdir -p /data/postgresql/backup

# 确保权限正确
sudo chown -R postgres:postgres /data/postgresql
sudo chmod 700 /data/postgresql/18/main

4.2 迁移数据 ​

bash
# 迁移现有数据
sudo rsync -av /var/lib/postgresql/18/main/ /data/postgresql/18/main/

# 备份原始配置文件
sudo cp /etc/postgresql/18/main/postgresql.conf /etc/postgresql/18/main/postgresql.conf.backup

# 修改配置文件指向新位置
sudo sed -i "s|data_directory = .*|data_directory = '/data/postgresql/18/main'|g" /etc/postgresql/18/main/postgresql.conf

# 更新服务配置
sudo sed -i "s|/var/lib/postgresql/18/main|/data/postgresql/18/main|g" /etc/postgresql/18/main/environment

4.3 验证目录配置 ​

bash
# 检查目录结构
ls -la /data/postgresql/18/main/

# 检查权限是否正确
sudo -u postgres ls -la /data/postgresql/18/main/

4.4 启动 PostgreSQL 服务 ​

bash
# 启动服务并设置开机自启
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 检查服务状态
sudo systemctl status postgresql

# 验证数据目录位置
sudo -u postgres psql -c "SHOW data_directory;"

5. PostgreSQL 基础配置 ​

5.1 切换到 postgres 用户 ​

bash
sudo -i -u postgres

5.2 设置 postgres 用户密码 ​

bash
psql -c "ALTER USER postgres PASSWORD 'your_secure_password';"

5.3 退出 postgres 用户 ​

bash
exit

6. 配置远程访问（可选） ​

如果您需要从其他机器访问 PostgreSQL，需要进行以下配置：

6.1 修改 postgresql.conf ​

bash
# 查找配置文件位置
sudo -u postgres psql -c "SHOW config_file;"

# 编辑配置文件
sudo vim /etc/postgresql/18/main/postgresql.conf

在配置文件中找到并修改以下行：

conf
# 监听所有地址
listen_addresses = '*'

# 启用日志收集
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'

6.2 配置客户端认证 ​

编辑 pg_hba.conf 文件：

bash
# 查找 pg_hba.conf 文件位置
sudo -u postgres psql -c "SHOW hba_file;"

# 编辑认证配置文件
sudo vim /etc/postgresql/18/main/pg_hba.conf

在文件末尾添加允许远程连接的规则：

conf
# 允许特定网络访问
host    all             all             192.168.1.0/24          scram-sha-256
# 或者允许所有网络访问（不推荐用于生产环境）
host    all             all             0.0.0.0/0               scram-sha-256

6.3 重启 PostgreSQL 服务 ​

bash
sudo systemctl restart postgresql

7. 创建业务数据库和用户 ​

7.1 连接数据库 ​

bash
sudo -u postgres psql

7.2 创建数据库和用户 ​

sql
-- 创建业务数据库
CREATE DATABASE myapp_db;

-- 创建业务用户
CREATE USER myapp_user WITH PASSWORD 'secure_password';

-- 授权
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- 退出
\q

8. 基本性能优化配置 ​

8.1 内存相关配置 ​

编辑 postgresql.conf 文件：

bash
sudo vim /etc/postgresql/18/main/postgresql.conf

根据您的服务器内存调整以下参数（以 4GB 内存为例）：

conf
# 共享缓冲区（建议设为物理内存的 25%）
shared_buffers = 1GB

# 有效缓存大小（建议设为物理内存的 50-75%）
effective_cache_size = 3GB

# 工作内存（每个查询的内存）
work_mem = 4MB

# 维护工作内存
maintenance_work_mem = 256MB

8.2 连接相关配置 ​

conf
# 最大连接数（根据应用需求调整）
max_connections = 100

8.3 检查点相关配置 ​

conf
# 检查点间隔
checkpoint_completion_target = 0.9

# WAL 缓冲区
wal_buffers = 16MB

8.4 PostgreSQL 18 新特性配置 ​

conf
# 并行查询优化
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# 逻辑复制优化（PostgreSQL 18 增强）
max_replication_slots = 10
max_wal_senders = 10

# 新增：改进的统计信息收集
default_statistics_target = 100

8.5 重启服务使配置生效 ​

bash
sudo systemctl restart postgresql

9. 常用管理命令 ​

9.1 服务管理 ​

bash
# 启动 PostgreSQL
sudo systemctl start postgresql

# 停止 PostgreSQL
sudo systemctl stop postgresql

# 重启 PostgreSQL
sudo systemctl restart postgresql

# 查看 PostgreSQL 状态
sudo systemctl status postgresql

# 设置开机自启
sudo systemctl enable postgresql

9.2 连接数据库 ​

bash
# 使用 postgres 用户连接
sudo -u postgres psql

# 使用指定用户连接指定数据库
psql -U myapp_user -d myapp_db -h localhost

# 远程连接示例
psql -U finadmin -d mydb -h 10.10.0.13 -p 5432

9.3 常用 SQL 命令 ​

sql
-- 列出所有数据库
\l

-- 切换数据库
\c database_name

-- 列出所有表
\dt

-- 查看表结构
\d table_name

-- 查看当前连接信息
\conninfo

-- 退出
\q

10. 备份与恢复 ​

10.1 逻辑备份（SQL 格式） ​

bash
# 备份整个数据库集群
sudo -u postgres pg_dumpall > /data/postgresql/backup/backup_full_$(date +%Y%m%d).sql

# 备份单个数据库
sudo -u postgres pg_dump myapp_db > /data/postgresql/backup/myapp_db_$(date +%Y%m%d).sql

# 备份单个表
sudo -u postgres pg_dump -t table_name myapp_db > /data/postgresql/backup/table_$(date +%Y%m%d).sql

10.2 物理备份（文件系统级别） ​

bash
# 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 备份数据目录
sudo tar -czf /data/postgresql/backup/postgresql_backup_$(date +%Y%m%d).tar.gz /data/postgresql/18/main/

# 启动 PostgreSQL 服务
sudo systemctl start postgresql

10.3 恢复数据 ​

bash
# 恢复 SQL 备份
sudo -u postgres psql < /data/postgresql/backup/backup_full_20240101.sql

# 恢复单个数据库
sudo -u postgres psql myapp_db < /data/postgresql/backup/myapp_db_20240101.sql

11. 监控和日志 ​

11.1 查看日志文件 ​

bash
# 查看日志目录
sudo -u postgres psql -c "SHOW log_directory;"

# 查看最新日志文件
sudo tail -f /data/postgresql/18/main/log/postgresql-*.log

# 或者系统日志目录
sudo tail -f /var/log/postgresql/postgresql-18-main.log

11.2 基本监控查询 ​

sql
-- 查看当前活动连接
SELECT * FROM pg_stat_activity;

-- 查看数据库大小
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

-- 查看表大小
SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename)) FROM pg_tables WHERE schemaname='public';

11.3 磁盘空间监控 ​

bash
# 查看磁盘使用情况
df -h

# 查看 PostgreSQL 数据目录大小
sudo du -sh /data/postgresql/18/main/

# 查看各子目录大小
sudo du -sh /data/postgresql/18/main/*/

12. 安全加固建议 ​

12.1 修改默认端口（可选） ​

编辑 postgresql.conf 文件：

conf
port = 5433  # 修改默认端口

12.2 限制超级用户访问 ​

编辑 pg_hba.conf 文件，限制 postgres 用户只能本地登录：

conf
local   all             postgres                                peer
host    all             postgres             127.0.0.1/32       reject

12.3 定期更新 ​

bash
sudo apt update && sudo apt upgrade postgresql-18

13. 故障排除 ​

13.1 常见问题 ​

1. 无法连接到数据库 ​

bash
# 检查服务状态
sudo systemctl status postgresql

# 检查端口监听
sudo netstat -tlnp | grep 5432

# 查看日志
sudo tail -f /data/postgresql/18/main/log/postgresql-*.log

2. 权限拒绝错误 ​

bash
# 检查认证配置
sudo cat /etc/postgresql/18/main/pg_hba.conf

# 重启服务使配置生效
sudo systemctl restart postgresql

3. 磁盘空间不足 ​

bash
# 检查磁盘使用情况
df -h

# 检查数据目录大小
sudo du -sh /data/postgresql/18/main/*/

# 清理旧的备份文件
sudo find /data/postgresql/backup/ -name "*.sql" -mtime +30 -delete

13.2 日志分析 ​

bash
# 查看错误日志
sudo grep -i "error" /data/postgresql/18/main/log/postgresql-*.log

# 查看警告日志
sudo grep -i "warning" /data/postgresql/18/main/log/postgresql-*.log

14. PostgreSQL 18 新特性 ​

14.1 主要新特性 ​

PostgreSQL 18 带来了许多新特性和改进：

1. 性能改进 ​

  • 改进的并行查询性能
  • 更快的索引创建
  • 优化的 VACUUM 操作

2. SQL 增强 ​

  • 新增 JSON 函数和操作符
  • 改进的窗口函数
  • 增强的正则表达式支持

3. 复制和恢复 ​

  • 改进的逻辑复制性能
  • 增强的流复制功能
  • 更快的 PITR（时间点恢复）

4. 安全性 ​

  • 增强的加密支持
  • 改进的权限管理

14.2 版本升级注意事项 ​

如果从旧版本升级到 PostgreSQL 18：

bash
# 1. 备份数据
sudo -u postgres pg_dumpall > /data/postgresql/backup/backup_before_upgrade.sql

# 2. 停止旧版本服务
sudo systemctl stop postgresql

# 3. 使用 pg_upgrade 升级
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  -b /usr/lib/postgresql/16/bin \
  -B /usr/lib/postgresql/18/bin \
  -d /data/postgresql/16/main \
  -D /data/postgresql/18/main

# 4. 启动新版本服务
sudo systemctl start postgresql

15. 磁盘空间管理 ​

15.1 目录结构概览 ​

在双磁盘架构下，PostgreSQL 的目录结构如下：

text
/                                    # 系统盘
├── /etc/postgresql/18/main/         # 配置文件
├── /var/log/postgresql/             # 系统日志（可选）
└── /usr/lib/postgresql/18/          # 程序文件

/data                                # 数据盘
└── /data/postgresql/
    ├── 18/main/                     # 数据目录
    │   ├── base/                    # 数据库文件
    │   ├── global/                  # 全局数据
    │   ├── pg_wal/                  # WAL 日志
    │   └── log/                     # 数据库日志
    └── backup/                      # 备份目录

15.2 空间规划建议 ​

目录推荐大小说明
/ (系统盘)40GB操作系统、程序文件
/data/postgresql/18/main/根据数据量数据库核心数据
/data/postgresql/backup/数据量的1-2倍备份存储

15.3 数据目录迁移 ​

如果需要将数据目录从默认位置迁移到 /data：

bash
# 1. 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 2. 创建目标目录
sudo mkdir -p /data/postgresql/18/main
sudo chown postgres:postgres /data/postgresql/18/main
sudo chmod 700 /data/postgresql/18/main

# 3. 复制数据
sudo rsync -av /var/lib/postgresql/18/main/ /data/postgresql/18/main/

# 4. 修改配置文件
sudo vim /etc/postgresql/18/main/postgresql.conf
# 修改：data_directory = '/data/postgresql/18/main'

# 5. 更新环境配置
sudo vim /etc/postgresql/18/main/environment
# 修改数据目录路径

# 6. 启动服务
sudo systemctl start postgresql

# 7. 验证
sudo -u postgres psql -c "SHOW data_directory;"

15.4 性能优化建议 ​

磁盘 I/O 优化 ​

bash
# 1. 调整文件系统参数（在 /etc/fstab 中）
# 对于 /data 磁盘，添加 noatime 参数：
# /dev/sdb1 /data ext4 defaults,noatime 0 0

# 2. 调整内核参数
sudo vim /etc/sysctl.conf

# 添加以下参数：
# vm.swappiness = 1
# vm.dirty_ratio = 15
# vm.dirty_background_ratio = 5

# 应用配置
sudo sysctl -p

PostgreSQL 参数调优 ​

bash
# 编辑 postgresql.conf
sudo vim /etc/postgresql/18/main/postgresql.conf

# 根据磁盘配置调整以下参数：
# shared_buffers = 25% of RAM
# effective_cache_size = 75% of RAM
# wal_buffers = 16MB
# checkpoint_completion_target = 0.9
# random_page_cost = 1.1  # SSD 环境下

15.5 备份策略建议 ​

在双磁盘架构下，建议采用以下备份策略：

bash
# 1. 本地备份（存储在 /data/postgresql/backup/）
sudo -u postgres pg_dump myapp_db > /data/postgresql/backup/myapp_db_$(date +%Y%m%d).sql

# 2. 定期清理旧备份（保留30天）
sudo find /data/postgresql/backup/ -name "*.sql" -mtime +30 -delete

# 3. 建议将关键备份同步到远程存储
# rsync -av /data/postgresql/backup/ user@remote:/backup/postgresql/