跳到主要内容

PostgreSQL 单机版安装指南

1. 环境准备​

1.1 系统要求​

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

1.2 服务器规划示例​

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

1.3 磁盘配置要求​

合理的磁盘配置对 PostgreSQL 性能至关重要。在单机部署中，建议按以下方式分配磁盘：

1.3.1 磁盘配置详情​

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

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

- WAL日志盘：50GB (virtio-scsi, /dev/sdc)
  - 挂载点：/data/postgresql/wal
  - 文件系统：ext4
  - 用途：PostgreSQL WAL日志文件
  - 建议：独立高速存储，提高写入性能

1.3.2 存储性能建议​

  • 系统盘：可使用普通存储，对性能要求不高
  • 数据盘：强烈建议使用SSD存储
  • WAL日志盘：建议使用高速SSD，独立于数据盘
  • 备份存储：可使用大容量机械硬盘，建议至少50GB

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

2. 系统初始化​

2.1 更新系统包​

# Ubuntu/Debian
sudo apt update && sudo apt upgrade -y

# CentOS/RHEL
sudo yum update -y

2.2 安装必要的工具​

# Ubuntu/Debian
sudo apt install -y wget curl vim net-tools telnet tree htop

# CentOS/RHEL
sudo yum install -y wget curl vim net-tools telnet tree htop

2.3 配置主机名解析​

sudo tee -a /etc/hosts <<EOF
192.168.192.111 pg-standalone
EOF

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

# Ubuntu/Debian (ufw)
sudo ufw allow 22/tcp
sudo ufw allow 5432/tcp
sudo ufw --force enable

# CentOS/RHEL (firewalld)
sudo firewall-cmd --permanent --add-port=22/tcp
sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

3. 安装 PostgreSQL​

3.1 Ubuntu/Debian 安装方式​

方法一：使用官方仓库安装（推荐）​

# 导入 PostgreSQL 官方 GPG 密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# 添加 PostgreSQL 官方仓库
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# 更新包索引
sudo apt update

# 安装 PostgreSQL 16
sudo apt install -y postgresql-16 postgresql-client-16 postgresql-contrib-16

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

# 添加清华大学镜像源
echo "deb 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-16 postgresql-client-16 postgresql-contrib-16

3.2 CentOS/RHEL 安装方式​

方法一：使用官方仓库安装（推荐）​

# 安装 PostgreSQL 官方仓库
sudo yum install -y epel-release
sudo rpm -Uvh https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装 PostgreSQL 16
sudo yum install -y postgresql16-server postgresql16-contrib

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

# 使用阿里云镜像
sudo yum install -y epel-release
sudo rpm -Uvh https://mirrors.aliyun.com/postgresql/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 修改 repo 文件使用阿里云镜像
sudo sed -i 's#download.postgresql.org/pub#mirrors.aliyun.com/postgresql#' /etc/yum.repos.d/pgdg-redhat-all.repo

# 安装 PostgreSQL 16
sudo yum install -y postgresql16-server postgresql16-contrib

4. 初始化和启动 PostgreSQL​

4.1 初始化数据库（CentOS/RHEL 特有步骤）​

# CentOS/RHEL 需要手动初始化数据库
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# Ubuntu/Debian 系统会在安装时自动初始化

4.2 配置数据目录和 WAL 目录​

安装完成后，需要配置 PostgreSQL 使用我们预设的数据目录和 WAL 目录：

# 1. 停止 PostgreSQL 服务（如果已启动）
sudo systemctl stop postgresql

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

# 创建 WAL 日志目录
sudo mkdir -p /data/postgresql/wal

# 确保权限正确
sudo chown -R postgres:postgres /data/postgresql
sudo chmod 700 /data/postgresql         # PostgreSQL要求数据目录权限为700
sudo chown -R postgres:postgres /data/postgresql/wal
sudo chmod 700 /data/postgresql/wal

# 2. 初始化数据库到我们指定的数据目录
# 初始化数据目录（磁盘1），并指定WAL目录为磁盘2的pg_wal
sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /data/postgresql -X /data/postgresql/wal

4.3 验证目录配置​

# 检查目录链接是否正确
ls -la /data/postgresql

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

4.6 启动 PostgreSQL 服务​

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

# 检查服务状态
sudo systemctl status postgresql

5. PostgreSQL 基础配置​

5.1 切换到 postgres 用户​

# 切换到 postgres 用户
sudo -i -u postgres

5.2 设置 postgres 用户密码​

# 连接到 PostgreSQL 并设置密码
psql -c "ALTER USER postgres PASSWORD 'your_secure_password';"

5.3 退出 postgres 用户​

# 退出 postgres 用户回到 root 用户
exit

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

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

6.1 修改 postgresql.conf​

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

# 编辑配置文件（通常位于 /etc/postgresql/16/main/postgresql.conf 或 /var/lib/pgsql/16/data/postgresql.conf）
sudo vim /etc/postgresql/16/main/postgresql.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 文件：

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

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

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

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

6.3 重启 PostgreSQL 服务​

sudo systemctl restart postgresql

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

7.1 连接数据库​

# 使用 postgres 用户连接数据库
sudo -u postgres psql

7.2 创建数据库和用户​

-- 创建业务数据库
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 文件：

sudo vim /etc/postgresql/16/main/postgresql.conf

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

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

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

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

# 维护工作内存
maintenance_work_mem = 256MB

8.2 连接相关配置​

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

8.3 检查点相关配置​

# 检查点间隔
checkpoint_completion_target = 0.9

# WAL 缓冲区
wal_buffers = 16MB

8.4 重启服务使配置生效​

sudo systemctl restart postgresql

9. 常用管理命令​

9.1 服务管理​

# 启动 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 连接数据库​

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

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

9.3 常用 SQL 命令​

-- 列出所有数据库
\l

-- 切换数据库
\c database_name

-- 列出所有表
\dt

-- 查看表结构
\d table_name

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

-- 退出
\q

10. 备份与恢复​

10.1 逻辑备份（SQL 格式）​

# 备份整个数据库集群
sudo -u postgres pg_dumpall > backup_full.sql

# 备份单个数据库
sudo -u postgres pg_dump myapp_db > backup_myapp_db.sql

# 备份单个表
sudo -u postgres pg_dump -t table_name myapp_db > backup_table.sql

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

# 停止 PostgreSQL 服务
sudo systemctl stop postgresql

# 备份数据目录（路径可能因系统而异）
sudo tar -czf postgresql_backup.tar.gz /var/lib/postgresql/16/main/

# 启动 PostgreSQL 服务
sudo systemctl start postgresql

10.3 恢复数据​

# 恢复 SQL 备份
sudo -u postgres psql < backup_full.sql

# 恢复单个数据库
sudo -u postgres psql myapp_db < backup_myapp_db.sql

11. 监控和日志​

11.1 查看日志文件​

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

# 查看最新日志文件（路径可能因系统而异）
sudo tail -f /var/log/postgresql/postgresql-16-main.log
# 或者
sudo tail -f /var/lib/pgsql/16/data/log/postgresql-*.log

11.2 基本监控查询​

-- 查看当前活动连接
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';

12. 安全加固建议​

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

编辑 postgresql.conf 文件：

port = 5433  # 修改默认端口

12.2 限制超级用户访问​

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

local   all             postgres                                peer
host    all             postgres             127.0.0.1/32       reject

12.3 定期更新​

# Ubuntu/Debian
sudo apt update && sudo apt upgrade postgresql-16

# CentOS/RHEL
sudo yum update postgresql16-server

13. 故障排除​

13.1 常见问题​

1. 无法连接到数据库​

# 检查服务状态
sudo systemctl status postgresql

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

# 查看日志
sudo tail -f /var/log/postgresql/postgresql-16-main.log

2. 权限拒绝错误​

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

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

3. 磁盘空间不足​

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

# 清理旧的日志文件
sudo -u postgres psql -c "SELECT pg_size_pretty(pg_database_size('myapp_db'));"

13.2 日志分析​

# 查看错误日志
sudo grep -i "error" /var/log/postgresql/postgresql-16-main.log

# 查看警告日志
sudo grep -i "warning" /var/log/postgresql/postgresql-16-main.log

14. 磁盘配置建议​

合理的磁盘配置对 PostgreSQL 性能至关重要。在单机部署中，重点关注以下几个方面：

14.1 基本目录结构​

在单机部署中，PostgreSQL 主要涉及以下几个重要的目录：

/                           # 系统根目录
├── /var/lib/postgresql/    # PostgreSQL 数据目录（最重要）
├── /var/log/               # 系统日志目录
├── /tmp/                   # 临时文件目录
└── /backup/                # 备份目录（建议）

14.2 推荐磁盘分配方案​

目录推荐大小存储类型说明
系统根目录 (/)20-40GB普通存储操作系统和基本软件
PostgreSQL 数据目录根据数据量确定SSD（推荐）数据库存储核心数据
WAL 日志目录10-20GB高速 SSD事务日志（建议独立）
日志目录5-10GB普通存储系统和数据库日志
临时目录2-5GB普通存储查询临时文件
备份目录根据备份策略大容量存储数据备份存放

14.3 数据目录配置​

查看当前数据目录​

# 通过 SQL 命令查看
sudo -u postgres psql -c "SHOW data_directory;"

# 通过系统命令查看
sudo -u postgres psql -c "SELECT setting FROM pg_settings WHERE name = 'data_directory';"

更改数据目录位置​

如果需要将数据目录迁移到其他磁盘：

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

# 2. 创建新的数据目录（假设新磁盘挂载在 /data）
sudo mkdir -p /data/postgresql/16/main
sudo chown postgres:postgres /data/postgresql/16/main

# 3. 复制现有数据到新位置
sudo rsync -av /var/lib/postgresql/16/main/ /data/postgresql/16/main/

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

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

# 6. 更新服务配置（Ubuntu/Debian）
sudo sed -i "s|/var/lib/postgresql/16/main|/data/postgresql/16/main|g" /etc/postgresql/16/main/environment

# 7. 启动 PostgreSQL 服务
sudo systemctl start postgresql

14.4 WAL 日志目录配置​

WAL（Write-Ahead Logging）是 PostgreSQL 保证数据一致性的关键机制，将其放置在独立的高速磁盘上可以显著提高性能。

创建独立的 WAL 目录​

# 1. 创建 WAL 目录（假设使用高速 SSD 挂载在 /wal）
sudo mkdir -p /wal/postgresql/16
sudo chown postgres:postgres /wal/postgresql/16

# 2. 停止 PostgreSQL 服务
sudo systemctl stop postgresql

配置 WAL 目录​

有两种方式配置 WAL 目录：

方法一：使用软链接（推荐）​
# 1. 移动现有的 WAL 目录
sudo mv /var/lib/postgresql/16/main/pg_wal /wal/postgresql/16/

# 2. 创建软链接
sudo ln -s /wal/postgresql/16/pg_wal /var/lib/postgresql/16/main/pg_wal

# 3. 设置权限
sudo chown -h postgres:postgres /var/lib/postgresql/16/main/pg_wal
方法二：修改配置文件​
# 编辑 postgresql.conf
sudo vim /etc/postgresql/16/main/postgresql.conf

# 添加或修改以下配置：
# wal_dir = '/wal/postgresql/16/pg_wal'

启动服务并验证​

# 启动 PostgreSQL 服务
sudo systemctl start postgresql

# 验证 WAL 目录位置
sudo -u postgres psql -c "SHOW data_directory;"
sudo ls -la /wal/postgresql/16/

14.5 性能优化建议​

磁盘 I/O 优化​

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

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

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

PostgreSQL 参数调优​

# 编辑 postgresql.conf
sudo vim /etc/postgresql/16/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 环境下