印刻数据库

外观

PostgreSQL 二进制包安装 ​

二进制包安装的优势 ​

1. 简化安装过程 ​

  • 无需编译源码，直接安装预编译的二进制文件
  • 自动处理依赖关系
  • 安装过程快速，适合大规模部署

2. 系统集成性好 ​

  • 自动配置系统服务
  • 遵循系统的文件布局规范
  • 便于系统管理和监控

3. 版本管理方便 ​

  • 支持通过包管理器进行版本升级和降级
  • 自动处理配置文件的备份和迁移
  • 便于维护多个版本

4. 安全性高 ​

  • 官方发布的二进制包经过严格测试
  • 支持数字签名验证
  • 便于获取安全更新

CentOS/RHEL 系统安装 ​

1. 使用 PostgreSQL 官方仓库 ​

安装仓库配置 ​

bash
# 下载仓库配置文件
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 对于 CentOS/RHEL 8
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 对于 CentOS/RHEL 9
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装 PostgreSQL ​

bash
# 安装 PostgreSQL 服务器和客户端
sudo yum install -y postgresql16-server postgresql16

# 对于特定版本，替换版本号即可
sudo yum install -y postgresql15-server postgresql15

2. 初始化数据库 ​

bash
# 初始化数据库集群
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

3. 配置服务 ​

启用并启动服务 ​

bash
# 启用服务开机自启
sudo systemctl enable postgresql-16

# 启动服务
sudo systemctl start postgresql-16

配置监听地址 ​

编辑 /var/lib/pgsql/16/data/postgresql.conf 文件：

bash
sudo vi /var/lib/pgsql/16/data/postgresql.conf

修改以下参数：

listen_addresses = '*'  # 允许所有 IP 地址访问

配置访问控制 ​

编辑 /var/lib/pgsql/16/data/pg_hba.conf 文件：

bash
sudo vi /var/lib/pgsql/16/data/pg_hba.conf

添加以下内容：

host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5

重启服务 ​

bash
sudo systemctl restart postgresql-16

Ubuntu/Debian 系统安装 ​

1. 使用 PostgreSQL 官方仓库 ​

安装仓库配置 ​

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

# 添加仓库密钥
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

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

# 更新包列表
sudo apt-get update

安装 PostgreSQL ​

bash
# 安装 PostgreSQL 服务器和客户端
sudo apt-get install -y postgresql-16 postgresql-client-16

# 对于特定版本，替换版本号即可
sudo apt-get install -y postgresql-15 postgresql-client-15

2. 服务管理 ​

查看服务状态 ​

bash
sudo systemctl status postgresql

启动/停止/重启服务 ​

bash
# 启动服务
sudo systemctl start postgresql

# 停止服务
sudo systemctl stop postgresql

# 重启服务
sudo systemctl restart postgresql

启用开机自启 ​

bash
sudo systemctl enable postgresql

3. 配置访问控制 ​

编辑 /etc/postgresql/16/main/pg_hba.conf 文件：

bash
sudo vi /etc/postgresql/16/main/pg_hba.conf

添加以下内容：

host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5

编辑 /etc/postgresql/16/main/postgresql.conf 文件，配置监听地址：

bash
sudo vi /etc/postgresql/16/main/postgresql.conf

修改以下参数：

listen_addresses = '*'

重启服务：

bash
sudo systemctl restart postgresql

macOS 系统安装 ​

1. 使用 Homebrew 安装 ​

安装 Homebrew ​

bash
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

安装 PostgreSQL ​

bash
# 安装最新版 PostgreSQL
brew install postgresql

# 安装特定版本
brew install postgresql@16

2. 服务管理 ​

启动服务 ​

bash
# 启动并设置开机自启
brew services start postgresql

# 对于特定版本
brew services start postgresql@16

停止服务 ​

bash
brew services stop postgresql

# 对于特定版本
brew services stop postgresql@16

3. 配置环境变量 ​

对于特定版本，需要将 PostgreSQL 加入 PATH：

bash
echo 'export PATH="/usr/local/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
echo 'export LDFLAGS="-L/usr/local/opt/postgresql@16/lib"' >> ~/.zshrc
echo 'export CPPFLAGS="-I/usr/local/opt/postgresql@16/include"' >> ~/.zshrc

# 使环境变量生效
source ~/.zshrc

Windows 系统安装 ​

1. 下载安装程序 ​

从 PostgreSQL 官方网站下载 Windows 安装程序：

https://www.postgresql.org/download/windows/

选择适合您系统的版本（32位或64位）。

2. 运行安装程序 ​

  1. 双击安装程序，启动安装向导
  2. 选择安装目录（建议使用默认目录）
  3. 选择要安装的组件（建议全选）
  4. 设置数据目录（建议使用默认目录）
  5. 设置超级用户密码
  6. 选择端口号（默认5432）
  7. 选择区域设置
  8. 点击"Install"开始安装

3. 安装后配置 ​

启动服务 ​

使用 Windows 服务管理器启动 PostgreSQL 服务，或通过命令行：

cmd
net start postgresql-x64-16

配置环境变量 ​

将 PostgreSQL 的 bin 目录添加到系统 PATH 环境变量：

C:\Program Files\PostgreSQL\16\bin

连接测试 ​

打开命令提示符，使用 psql 连接到数据库：

cmd
psql -U postgres

输入安装时设置的密码，即可连接到 PostgreSQL。

安装验证 ​

1. 检查版本 ​

bash
# 检查 PostgreSQL 版本
psql --version

# 检查服务器版本
psql -h localhost -U postgres -c "SELECT version();"

2. 连接测试 ​

bash
# 使用 psql 连接到数据库
psql -h localhost -U postgres

# 创建测试数据库
CREATE DATABASE testdb;
\c testdb
CREATE TABLE test_table (id serial primary key, name varchar(50));
INSERT INTO test_table (name) VALUES ('Test');
SELECT * FROM test_table;
\q

3. 查看服务状态 ​

bash
# Linux 系统
sudo systemctl status postgresql-16

# macOS 系统（使用 Homebrew）
brew services list

# Windows 系统
net start | findstr PostgreSQL

版本管理 ​

1. 安装多个版本 ​

CentOS/RHEL ​

bash
# 安装 PostgreSQL 15 和 16
sudo yum install -y postgresql15-server postgresql16-server

Ubuntu/Debian ​

bash
# 安装 PostgreSQL 15 和 16
sudo apt-get install -y postgresql-15 postgresql-16

macOS（使用 Homebrew） ​

bash
# 安装 PostgreSQL 15 和 16
brew install postgresql@15 postgresql@16

2. 切换版本 ​

CentOS/RHEL ​

bash
# 停止当前版本
sudo systemctl stop postgresql-16

# 启动目标版本
sudo systemctl start postgresql-15

Ubuntu/Debian ​

bash
# 停止当前版本
sudo systemctl stop postgresql@16-main

# 启动目标版本
sudo systemctl start postgresql@15-main

macOS（使用 Homebrew） ​

bash
# 停止当前版本
brew services stop postgresql@16

# 启动目标版本
brew services start postgresql@15

# 更新环境变量
echo 'export PATH="/usr/local/opt/postgresql@15/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

3. 升级版本 ​

CentOS/RHEL ​

bash
# 安装新版本
sudo yum install -y postgresql16-server

# 初始化新数据库
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# 停止旧版本
sudo systemctl stop postgresql-15

# 使用 pg_upgrade 升级数据
# 注意：请参考 pg_upgrade 官方文档进行详细操作

# 启动新版本
sudo systemctl start postgresql-16

Ubuntu/Debian ​

bash
# 安装新版本
sudo apt-get install -y postgresql-16

# 停止旧版本
sudo systemctl stop postgresql@15-main

# 使用 pg_upgrade 升级数据
# 注意：请参考 pg_upgrade 官方文档进行详细操作

# 启动新版本
sudo systemctl start postgresql@16-main

常见问题（FAQ） ​

Q1: 如何修改 PostgreSQL 超级用户密码？ ​

A1: 使用以下方法修改密码：

bash
# 方法1：使用 psql 命令
psql -h localhost -U postgres -c "ALTER USER postgres PASSWORD 'new_password';"

# 方法2：使用 pgadmin 图形界面
# 连接到数据库，右键点击 postgres 用户，选择"Properties"，修改密码

Q2: 如何允许远程连接到 PostgreSQL？ ​

A2: 按照以下步骤配置：

  1. 编辑 postgresql.conf 文件，设置 listen_addresses = '*'
  2. 编辑 pg_hba.conf 文件，添加允许访问的 IP 地址：
    host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5
  3. 重启 PostgreSQL 服务
  4. 确保防火墙允许 PostgreSQL 端口（默认5432）

Q3: 如何查看 PostgreSQL 日志？ ​

A3: 日志位置取决于操作系统和安装方式：

bash
# CentOS/RHEL
sudo tail -f /var/lib/pgsql/16/data/log/postgresql-Mon.log

# Ubuntu/Debian
sudo tail -f /var/log/postgresql/postgresql-16-main.log

# macOS（使用 Homebrew）
tail -f /usr/local/var/log/postgresql@16.log

# Windows
C:\Program Files\PostgreSQL\16\data\log

Q4: 如何备份和恢复 PostgreSQL 数据库？ ​

A4: 使用以下命令：

bash
# 备份数据库
pg_dump -h localhost -U postgres -F c -b -v -f /path/to/backup.dump database_name

# 恢复数据库
pg_restore -h localhost -U postgres -d database_name -v /path/to/backup.dump

Q5: 如何创建新用户和数据库？ ​

A5: 使用以下命令：

bash
# 连接到 PostgreSQL
psql -h localhost -U postgres

# 创建用户
CREATE USER new_user WITH PASSWORD 'password';

# 创建数据库
CREATE DATABASE new_database;

# 授予用户数据库权限
GRANT ALL PRIVILEGES ON DATABASE new_database TO new_user;

Q6: 如何查看 PostgreSQL 占用的端口？ ​

A6: 使用以下命令：

bash
# Linux/macOS
netstat -tuln | grep 5432
ss -tuln | grep 5432
lsof -i :5432

# Windows
netstat -an | findstr 5432

Q7: 如何查看 PostgreSQL 进程？ ​

A7: 使用以下命令：

bash
# Linux/macOS
ps aux | grep postgres

# Windows
tasklist | findstr postgres

Q8: 如何调整 PostgreSQL 内存配置？ ​

A8: 编辑 postgresql.conf 文件，调整以下参数：

shared_buffers = 2GB          # 共享缓冲区大小
work_mem = 16MB               # 每个查询工作内存
maintenance_work_mem = 1GB    # 维护操作内存
effective_cache_size = 6GB    # 有效的缓存大小

重启 PostgreSQL 服务使配置生效。

Q9: 如何查看 PostgreSQL 配置文件位置？ ​

A9: 使用以下命令：

bash
# 查看数据目录
psql -h localhost -U postgres -c "SHOW data_directory;"

# 查看配置文件位置
psql -h localhost -U postgres -c "SHOW config_file;"
psql -h localhost -U postgres -c "SHOW hba_file;"

Q10: 如何卸载 PostgreSQL？ ​

A10: 根据安装方式选择卸载方法：

CentOS/RHEL ​

bash
sudo yum remove -y postgresql16-server postgresql16
sudo rm -rf /var/lib/pgsql/16/

Ubuntu/Debian ​

bash
sudo apt-get purge -y postgresql-16 postgresql-client-16
sudo rm -rf /etc/postgresql/16/
sudo rm -rf /var/lib/postgresql/16/

macOS（使用 Homebrew） ​

bash
brew services stop postgresql@16
brew uninstall postgresql@16
rm -rf /usr/local/var/postgresql@16/

Windows ​

使用控制面板的"程序和功能"卸载 PostgreSQL。

版本差异 ​

PostgreSQL 15 及以上版本 ​

  • 改进了逻辑复制功能
  • 增强了分区表性能
  • 引入了新的权限管理功能
  • 改进了真空清理机制

PostgreSQL 13 及以上版本 ​

  • 引入了增量排序
  • 改进了并行查询
  • 增强了 JSONB 支持
  • 引入了存储过程支持

PostgreSQL 11 及以上版本 ​

  • 引入了声明式分区
  • 改进了并行查询
  • 增强了窗口函数
  • 引入了新的索引类型

最佳实践 ​

1. 安全配置 ​

  • 使用强密码
  • 限制访问 IP 地址
  • 启用 SSL 加密
  • 定期更新版本
  • 配置适当的权限

2. 性能优化 ​

  • 根据硬件配置调整内存参数
  • 合理设置 checkpoint 相关参数
  • 优化 WAL 配置
  • 定期运行 VACUUM ANALYZE
  • 合理设计索引

3. 备份策略 ​

  • 定期进行全量备份
  • 结合增量备份和 WAL 归档
  • 测试恢复过程
  • 存储备份到多个位置
  • 定期验证备份完整性

4. 监控和维护 ​

  • 监控数据库性能指标
  • 监控磁盘空间使用情况
  • 监控连接数和查询性能
  • 定期检查日志文件
  • 定期进行健康检查

5. 升级策略 ​

  • 制定详细的升级计划
  • 测试升级过程
  • 准备回滚方案
  • 升级前备份所有数据
  • 升级后验证功能和性能

系统集成 ​

1. 与系统监控集成 ​

Prometheus + Grafana ​

bash
# 安装 postgres_exporter
download_url=$(curl -s https://api.github.com/repos/prometheus-community/postgres_exporter/releases/latest | grep browser_download_url | grep linux-amd64 | cut -d '"' -f 4)
wget $download_url
tar xvf postgres_exporter-*.tar.gz
cd postgres_exporter-*

# 配置连接字符串
export DATA_SOURCE_NAME="postgresql://postgres:password@localhost:5432/postgres?sslmode=disable"

# 启动 exporter
./postgres_exporter

然后在 Prometheus 中配置抓取目标，并在 Grafana 中导入 PostgreSQL 仪表盘。

2. 与日志管理集成 ​

ELK Stack ​

配置 Filebeat 收集 PostgreSQL 日志：

yaml
# filebeat.yml
filebeat.inputs:
- type: log
  paths:
    - /var/log/postgresql/postgresql-16-main.log
  fields:
    service: postgresql
    environment: production

output.elasticsearch:
  hosts: ["localhost:9200"]
  indices:
    - index: "postgresql-%{+yyyy.MM.dd}"
      when.contains:
        fields.service: "postgresql"

setup.kibana:
  host: "localhost:5601"

启动 Filebeat 后，即可在 Kibana 中查看和分析 PostgreSQL 日志。

故障排除 ​

1. 无法连接到 PostgreSQL ​

  • 检查 PostgreSQL 服务是否正在运行
  • 检查监听地址和端口配置
  • 检查 pg_hba.conf 访问控制设置
  • 检查防火墙配置
  • 检查客户端连接字符串是否正确

2. 服务启动失败 ​

  • 查看日志文件，分析错误信息
  • 检查数据目录权限
  • 检查配置文件语法错误
  • 检查端口是否被占用
  • 检查磁盘空间是否充足

3. 性能问题 ​

  • 分析慢查询日志
  • 检查索引使用情况
  • 检查内存和 CPU 使用情况
  • 检查磁盘 I/O 性能
  • 检查连接数和锁等待情况

4. 数据损坏 ​

  • 使用 pg_checksums 检查数据完整性
  • 使用 pg_resetwal 修复 WAL 损坏（谨慎使用）
  • 从备份恢复数据
  • 联系 PostgreSQL 社区寻求帮助

5. 权限问题 ​

  • 检查用户权限设置
  • 检查文件系统权限
  • 检查 SELinux 或 AppArmor 配置
  • 检查数据库对象权限

自动化部署 ​

1. 使用 Ansible 部署 ​

创建 Ansible playbook：

yaml
---
- name: Install PostgreSQL
  hosts: all
  become: yes
  vars:
    postgresql_version: 16
    postgresql_password: "secure_password"
  
  tasks:
    - name: Install PostgreSQL repository
      yum:
        name: https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
        state: present
      when: ansible_os_family == "RedHat"
    
    - name: Install PostgreSQL packages
      package:
        name:
          - "postgresql{{ postgresql_version }}-server"
          - "postgresql{{ postgresql_version }}"
        state: present
    
    - name: Initialize PostgreSQL database
      command: "/usr/pgsql-{{ postgresql_version }}/bin/postgresql-{{ postgresql_version }}-setup initdb"
      args:
        creates: "/var/lib/pgsql/{{ postgresql_version }}/data/PG_VERSION"
    
    - name: Enable and start PostgreSQL service
      systemd:
        name: "postgresql-{{ postgresql_version }}"
        enabled: yes
        state: started
    
    - name: Set postgres password
      postgresql_user:
        name: postgres
        password: "{{ postgresql_password }}"
        login_unix_socket: "/var/run/postgresql"
      become: yes
      become_user: postgres
    
    - name: Configure listen_addresses
      lineinfile:
        path: "/var/lib/pgsql/{{ postgresql_version }}/data/postgresql.conf"
        regexp: "^listen_addresses"
        line: "listen_addresses = '*'"
      notify: Restart PostgreSQL
    
    - name: Configure pg_hba.conf
      lineinfile:
        path: "/var/lib/pgsql/{{ postgresql_version }}/data/pg_hba.conf"
        line: "host    all             all             0.0.0.0/0               md5"
        insertafter: "EOF"
      notify: Restart PostgreSQL
  
  handlers:
    - name: Restart PostgreSQL
      systemd:
        name: "postgresql-{{ postgresql_version }}"
        state: restarted

2. 使用 Docker 部署 ​

bash
# 拉取 PostgreSQL 镜像
docker pull postgres:16

# 运行 PostgreSQL 容器
docker run -d \
  --name postgresql \
  -e POSTGRES_PASSWORD=secure_password \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_DB=postgres \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  postgres:16

# 连接到 PostgreSQL
docker exec -it postgresql psql -U postgres