PostgreSQL 单机版安装指南

1. 环境准备

1.1 系统要求

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

1.2 服务器规划示例

主机名	IP 地址	角色	配置
pg-standalone	192.168.199.111	PostgreSQL 单机服务器	2C4G

1.3 磁盘配置要求

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

1.3.1 磁盘配置详情

text

磁盘配置：
- 系统盘：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 更新系统包

bash

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

# CentOS/RHEL
sudo yum update -y

2.2 安装必要的工具

bash

# 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 配置主机名解析

bash

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

2.4 配置防火墙（如启用）

bash

# 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 安装方式

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

bash

# 导入 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

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

bash

# 添加清华大学镜像源
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 安装方式

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

bash

# 安装 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

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

bash

# 使用阿里云镜像
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 特有步骤）

bash

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

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

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

bash

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

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

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

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

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

4.3 验证目录配置

bash

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

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

4.6 启动 PostgreSQL 服务

bash

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

# 检查服务状态
sudo systemctl status postgresql

5. PostgreSQL 基础配置

5.1 切换到 postgres 用户

bash

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

5.2 设置 postgres 用户密码

bash

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

5.3 退出 postgres 用户

bash

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

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

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

6.1 修改 postgresql.conf

bash

# 查找配置文件位置
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

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

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/16/main/pg_hba.conf

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

conf

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

6.3 重启 PostgreSQL 服务

bash

sudo systemctl restart postgresql

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

7.1 连接数据库

bash

# 使用 postgres 用户连接数据库
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/16/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 重启服务使配置生效

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

9.3 常用 SQL 命令

sql

-- 列出所有数据库
\l

-- 切换数据库
\c database_name

-- 列出所有表
\dt

-- 查看表结构
\d table_name

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

-- 退出
\q

10. 备份与恢复

10.1 逻辑备份（SQL 格式）

bash

# 备份整个数据库集群
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 物理备份（文件系统级别）

bash

# 停止 PostgreSQL 服务
sudo systemctl stop postgresql

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

# 启动 PostgreSQL 服务
sudo systemctl start postgresql

10.3 恢复数据

bash

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

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

11. 监控和日志

11.1 查看日志文件

bash

# 查看日志目录
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 基本监控查询

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';

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

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

# CentOS/RHEL
sudo yum update postgresql16-server

13. 故障排除

13.1 常见问题

1. 无法连接到数据库

bash

# 检查服务状态
sudo systemctl status postgresql

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

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

2. 权限拒绝错误

bash

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

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

3. 磁盘空间不足

bash

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

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

13.2 日志分析

bash

# 查看错误日志
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 主要涉及以下几个重要的目录：

text

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

14.2 推荐磁盘分配方案

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

14.3 数据目录配置

查看当前数据目录

bash

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

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

更改数据目录位置

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

bash

# 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 目录

bash

# 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 目录：

方法一：使用软链接（推荐）

bash

# 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

方法二：修改配置文件

bash

# 编辑 postgresql.conf
sudo vim /etc/postgresql/16/main/postgresql.conf

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

启动服务并验证

bash

# 启动 PostgreSQL 服务
sudo systemctl start postgresql

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

14.5 性能优化建议

磁盘 I/O 优化

bash

# 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 参数调优

bash

# 编辑 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 环境下

PostgreSQL 单机版安装指南 ​

1. 环境准备 ​

1.1 系统要求 ​

1.2 服务器规划示例 ​

1.3 磁盘配置要求 ​

1.3.1 磁盘配置详情 ​

1.3.2 存储性能建议 ​

2. 系统初始化 ​

2.1 更新系统包 ​

2.2 安装必要的工具 ​

2.3 配置主机名解析 ​

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

3. 安装 PostgreSQL ​

3.1 Ubuntu/Debian 安装方式 ​

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

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

3.2 CentOS/RHEL 安装方式 ​

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

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

4. 初始化和启动 PostgreSQL ​

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

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

4.3 验证目录配置 ​

4.6 启动 PostgreSQL 服务 ​

5. PostgreSQL 基础配置 ​

5.1 切换到 postgres 用户 ​

5.2 设置 postgres 用户密码 ​

5.3 退出 postgres 用户 ​

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

6.1 修改 postgresql.conf ​

6.2 配置客户端认证 ​

6.3 重启 PostgreSQL 服务 ​

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

7.1 连接数据库 ​

7.2 创建数据库和用户 ​

8. 基本性能优化配置 ​

8.1 内存相关配置 ​

8.2 连接相关配置 ​

8.3 检查点相关配置 ​

8.4 重启服务使配置生效 ​

9. 常用管理命令 ​

9.1 服务管理 ​

9.2 连接数据库 ​

9.3 常用 SQL 命令 ​

10. 备份与恢复 ​

10.1 逻辑备份（SQL 格式） ​

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

10.3 恢复数据 ​

11. 监控和日志 ​

11.1 查看日志文件 ​

11.2 基本监控查询 ​

12. 安全加固建议 ​

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

12.2 限制超级用户访问 ​

12.3 定期更新 ​

13. 故障排除 ​

13.1 常见问题 ​

1. 无法连接到数据库 ​

2. 权限拒绝错误 ​

3. 磁盘空间不足 ​

13.2 日志分析 ​

14. 磁盘配置建议 ​

14.1 基本目录结构 ​

14.2 推荐磁盘分配方案 ​

14.3 数据目录配置 ​

查看当前数据目录 ​

更改数据目录位置 ​

14.4 WAL 日志目录配置 ​

创建独立的 WAL 目录 ​

配置 WAL 目录 ​

方法一：使用软链接（推荐） ​

方法二：修改配置文件 ​

启动服务并验证 ​

14.5 性能优化建议 ​

磁盘 I/O 优化 ​

PostgreSQL 参数调优 ​

PostgreSQL 单机版安装指南

1. 环境准备

1.1 系统要求

1.2 服务器规划示例

1.3 磁盘配置要求

1.3.1 磁盘配置详情

1.3.2 存储性能建议

2. 系统初始化

2.1 更新系统包

2.2 安装必要的工具

2.3 配置主机名解析

2.4 配置防火墙（如启用）

3. 安装 PostgreSQL

3.1 Ubuntu/Debian 安装方式

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

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

3.2 CentOS/RHEL 安装方式

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

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

4. 初始化和启动 PostgreSQL

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

4.2 配置数据目录和 WAL 目录

4.3 验证目录配置

4.6 启动 PostgreSQL 服务

5. PostgreSQL 基础配置

5.1 切换到 postgres 用户

5.2 设置 postgres 用户密码

5.3 退出 postgres 用户

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

6.1 修改 postgresql.conf

6.2 配置客户端认证

6.3 重启 PostgreSQL 服务

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

7.1 连接数据库

7.2 创建数据库和用户

8. 基本性能优化配置

8.1 内存相关配置

8.2 连接相关配置

8.3 检查点相关配置

8.4 重启服务使配置生效

9. 常用管理命令

9.1 服务管理

9.2 连接数据库

9.3 常用 SQL 命令

10. 备份与恢复

10.1 逻辑备份（SQL 格式）

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

10.3 恢复数据

11. 监控和日志

11.1 查看日志文件

11.2 基本监控查询

12. 安全加固建议

12.1 修改默认端口（可选）

12.2 限制超级用户访问

12.3 定期更新

13. 故障排除

13.1 常见问题

1. 无法连接到数据库

2. 权限拒绝错误

3. 磁盘空间不足

13.2 日志分析

14. 磁盘配置建议

14.1 基本目录结构

14.2 推荐磁盘分配方案

14.3 数据目录配置

查看当前数据目录

更改数据目录位置

14.4 WAL 日志目录配置

创建独立的 WAL 目录

配置 WAL 目录

方法一：使用软链接（推荐）

方法二：修改配置文件

启动服务并验证

14.5 性能优化建议

磁盘 I/O 优化

PostgreSQL 参数调优