1. PostgreSQL-12.0安装环境准备

在CentOS 7.6系统上部署PostgreSQL-12.0数据库前，需要做好以下准备工作：

1.1 系统要求检查

首先确认你的CentOS 7.6系统满足以下基本要求：

• 最小内存：2GB（生产环境建议4GB以上）
• 磁盘空间：至少1GB可用空间（实际需求取决于数据量）
• 系统架构：x86_64
• 内核版本：3.10或更高

可以通过以下命令检查系统信息：

# 查看系统版本 cat /etc/redhat-release # 查看内存信息 free -h # 查看磁盘空间 df -h # 查看CPU架构 uname -m

1.2 网络连接确认

PostgreSQL安装过程中需要下载依赖包，因此必须确保服务器能够访问外部网络。测试网络连通性：

ping -c 4 www.postgresql.org

如果服务器位于内网环境，需要提前配置好代理或确保本地yum源包含所有必要依赖包。

1.3 关闭SELinux和防火墙（临时）

为避免安装过程中的权限问题，建议临时关闭SELinux：

setenforce 0 sed -i 's/SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config

同时停止防火墙服务：

systemctl stop firewalld systemctl disable firewalld

注意：生产环境中应在安装完成后重新配置SELinux和防火墙规则，而不是永久禁用它们。

2. PostgreSQL-12.0安装包获取

2.1 官方下载PostgreSQL源码

访问PostgreSQL官方FTP站点获取源码包：

wget https://ftp.postgresql.org/pub/source/v12.0/postgresql-12.0.tar.gz

下载完成后验证文件完整性：

md5sum postgresql-12.0.tar.gz # 官方MD5值应为：a3d8e0c66a7f6c44a069a3a4a8a0e3b1

2.2 备选下载方式

如果官方下载速度较慢，可以使用国内镜像源：

# 清华大学镜像站 wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v12.0/postgresql-12.0.tar.gz

3. 安装依赖包

PostgreSQL编译安装需要以下开发工具和库文件：

3.1 基础编译工具安装

yum groupinstall -y "Development Tools"

3.2 必需依赖包安装

yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel \ libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ \ openssl-devel cmake

3.3 可选依赖包（按需安装）

如果需要额外的功能支持，可以安装以下可选包：

# 用于地理空间数据支持 yum install -y geos-devel proj-devel gdal-devel # 用于UUID支持 yum install -y libuuid-devel # 用于加密函数 yum install -y libgcrypt-devel

4. PostgreSQL源码编译安装

4.1 准备安装目录

创建PostgreSQL的安装目录和数据目录：

mkdir -p /opt/pgsql/postgresql mkdir -p /opt/pgsql/postgresql/data

4.2 解压源码包

tar -zxvf postgresql-12.0.tar.gz -C /opt/pgsql/

4.3 配置编译选项

进入源码目录并配置编译参数：

cd /opt/pgsql/postgresql-12.0 ./configure --prefix=/opt/pgsql/postgresql \ --with-openssl \ --with-libxml \ --with-libxslt \ --with-python \ --with-perl \ --with-pam \ --with-ldap

常用配置选项说明：

• --prefix：指定安装目录
• --with-openssl：启用SSL加密支持
• --with-libxml：启用XML支持
• --with-python：启用Python过程语言支持
• --with-perl：启用Perl过程语言支持

4.4 编译和安装

make -j $(nproc) && make install

提示：-j $(nproc)参数表示使用所有可用的CPU核心并行编译，可以显著加快编译速度。

4.5 验证安装

检查安装是否成功：

/opt/pgsql/postgresql/bin/postgres --version # 应输出：postgres (PostgreSQL) 12.0

5. 创建PostgreSQL系统用户

5.1 创建专用用户和组

为PostgreSQL创建专用的系统用户和组：

groupadd postgres useradd -g postgres postgres -s /bin/bash passwd postgres

5.2 设置目录权限

将安装目录和数据目录的所有权赋予postgres用户：

chown -R postgres:postgres /opt/pgsql/postgresql chown -R postgres:postgres /opt/pgsql/postgresql/data

6. 配置环境变量

6.1 设置postgres用户环境

编辑postgres用户的.bash_profile文件：

su - postgres vim ~/.bash_profile

添加以下内容：

export PGHOME=/opt/pgsql/postgresql export PGDATA=/opt/pgsql/postgresql/data export PATH=$PATH:$PGHOME/bin export LD_LIBRARY_PATH=$PGHOME/lib export MANPATH=$PGHOME/share/man:$MANPATH

使环境变量生效：

source ~/.bash_profile

7. 初始化数据库集群

7.1 初始化数据库

使用initdb命令初始化数据库集群：

initdb -D $PGDATA --encoding=UTF8 --locale=en_US.UTF-8

关键参数说明：

• -D：指定数据库集群目录
• --encoding：设置默认数据库编码
• --locale：设置区域设置

7.2 验证初始化结果

检查数据目录是否已生成必要文件：

ls -l $PGDATA

应该看到以下关键文件：

• postgresql.conf：主配置文件
• pg_hba.conf：客户端认证配置文件
• pg_ident.conf：用户名映射文件

8. 配置PostgreSQL服务器

8.1 修改postgresql.conf

编辑主配置文件：

vim $PGDATA/postgresql.conf

建议修改以下参数：

listen_addresses = '*' # 允许所有IP连接 port = 5432 # 默认端口 max_connections = 100 # 最大连接数 shared_buffers = 128MB # 共享内存缓冲区大小 work_mem = 4MB # 每个查询的工作内存 maintenance_work_mem = 64MB # 维护操作的内存

8.2 配置客户端认证

编辑pg_hba.conf文件：

vim $PGDATA/pg_hba.conf

添加以下行允许远程连接：

# TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5

认证方法说明：

• trust：无条件允许连接
• md5：要求密码MD5加密认证
• password：明文密码认证（不安全）
• peer：使用操作系统用户认证

9. 启动PostgreSQL服务

9.1 手动启动服务

使用pg_ctl命令启动数据库：

pg_ctl -D $PGDATA -l logfile start

验证服务状态：

pg_ctl -D $PGDATA status

9.2 配置系统服务

创建systemd服务单元文件：

sudo vim /etc/systemd/system/postgresql.service

添加以下内容：

[Unit] Description=PostgreSQL database server After=network.target [Service] Type=forking User=postgres Group=postgres Environment=PGDATA=/opt/pgsql/postgresql/data OOMScoreAdjust=-1000 ExecStart=/opt/pgsql/postgresql/bin/pg_ctl start -D ${PGDATA} -l /opt/pgsql/postgresql/logfile ExecStop=/opt/pgsql/postgresql/bin/pg_ctl stop -D ${PGDATA} ExecReload=/opt/pgsql/postgresql/bin/pg_ctl reload -D ${PGDATA} TimeoutSec=300 [Install] WantedBy=multi-user.target

启用并启动服务：

sudo systemctl daemon-reload sudo systemctl enable postgresql sudo systemctl start postgresql

10. 基本数据库操作

10.1 连接数据库

使用psql客户端连接：

psql -U postgres

10.2 创建新用户和数据库

在psql中执行：

CREATE USER myuser WITH PASSWORD 'mypassword'; CREATE DATABASE mydb OWNER myuser; GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

10.3 远程连接测试

从另一台机器测试连接：

psql -h <服务器IP> -U myuser -d mydb

11. 性能调优建议

11.1 内存配置

根据服务器内存调整以下参数（在postgresql.conf中）：

shared_buffers = 25% of RAM # 通常为总内存的25% work_mem = (RAM - shared_buffers) / (max_connections * 3) maintenance_work_mem = 5% of RAM effective_cache_size = 50-75% of RAM

11.2 磁盘I/O优化

random_page_cost = 1.1 # 对于SSD存储 effective_io_concurrency = 200 # 对于SSD存储 wal_buffers = 16MB # WAL缓冲区大小 synchronous_commit = off # 对于非关键应用可提高性能

12. 常见问题排查

12.1 连接问题

错误：psql: could not connect to server: Connection refused解决方案：

1. 检查服务是否运行：systemctl status postgresql
2. 检查监听地址：netstat -tulnp | grep postgres
3. 检查pg_hba.conf配置

12.2 认证失败

错误：password authentication failed for user解决方案：

1. 确认用户名密码正确
2. 检查pg_hba.conf中的认证方法
3. 可能需要重置密码：ALTER USER username WITH PASSWORD 'newpassword'

12.3 性能问题

慢查询排查步骤：

1. 启用慢查询日志：

   log_min_duration_statement = 1000 # 记录执行超过1秒的语句

2. 使用EXPLAIN分析查询计划
3. 检查是否有缺失的索引

13. 安全加固建议

13.1 基本安全措施

1. 修改默认postgres用户密码：

   ALTER USER postgres WITH PASSWORD 'strongpassword';

2. 限制超级用户访问：

   # 在pg_hba.conf中 host all postgres 0.0.0.0/0 reject

3. 定期备份重要数据

13.2 网络层安全

1. 配置防火墙只允许特定IP访问5432端口
2. 考虑使用SSL加密连接：

   # 在postgresql.conf中 ssl = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key'

14. 备份与恢复

14.1 使用pg_dump备份单个数据库

pg_dump -U postgres -d mydb -f mydb_backup.sql

14.2 使用pg_dumpall备份所有数据库

pg_dumpall -U postgres -f alldbs_backup.sql

14.3 恢复数据库

psql -U postgres -f alldbs_backup.sql

14.4 设置定时备份

创建每日备份脚本：

#!/bin/bash DATE=$(date +%Y%m%d) pg_dumpall -U postgres | gzip > /backups/pg_backup_$DATE.sql.gz find /backups -type f -mtime +7 -delete

添加到crontab：

0 2 * * * /path/to/backup_script.sh

15. 升级与维护

15.1 小版本升级

对于PostgreSQL的小版本升级（如12.0到12.1）：

# 停止服务 systemctl stop postgresql # 备份数据 pg_dumpall -U postgres -f pre_upgrade_backup.sql # 安装新版本 ./configure --prefix=/opt/pgsql/postgresql make && make install # 重启服务 systemctl start postgresql

15.2 大版本升级

对于大版本升级（如11.x到12.x），需要使用pg_upgrade工具：

# 安装新版本到不同目录 ./configure --prefix=/opt/pgsql/postgresql12 make && make install # 停止旧版本服务 systemctl stop postgresql # 执行升级 /opt/pgsql/postgresql12/bin/pg_upgrade \ -b /opt/pgsql/postgresql/bin \ -B /opt/pgsql/postgresql12/bin \ -d /opt/pgsql/postgresql/data \ -D /opt/pgsql/postgresql12/data

16. 监控与日志管理

16.1 配置日志记录

在postgresql.conf中配置：

log_destination = 'stderr' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 1d log_rotation_size = 100MB log_min_duration_statement = 1000 # 记录慢查询

16.2 使用pg_stat_activity监控

查看当前活动连接：

SELECT * FROM pg_stat_activity;

16.3 安装pgBadger分析日志

安装pgBadger：

yum install -y pgbadger

生成报告：

pgbadger /opt/pgsql/postgresql/data/pg_log/postgresql-*.log -o report.html

17. 扩展与插件

17.1 常用扩展安装

安装PostGIS空间数据库扩展：

CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;

安装pgcrypto加密扩展：

CREATE EXTENSION pgcrypto;

17.2 管理扩展

查看可用扩展：

SELECT * FROM pg_available_extensions;

查看已安装扩展：

SELECT * FROM pg_extension;

18. 高可用方案简介

18.1 流复制设置

配置主从流复制：

在主服务器postgresql.conf中：

wal_level = replica max_wal_senders = 3 wal_keep_segments = 32

在从服务器上创建基础备份：

pg_basebackup -h 主服务器IP -D /opt/pgsql/postgresql/data -U replicator -P -v -R

18.2 使用pgpool-II实现负载均衡

安装pgpool-II：

yum install -y pgpool-II

基本配置：

backend_hostname0 = '主服务器IP' backend_port0 = 5432 backend_weight0 = 1 backend_hostname1 = '从服务器IP' backend_port1 = 5432 backend_weight1 = 1

19. 定期维护任务

19.1 自动清理（Autovacuum）

确保autovacuum正常运行：

# 在postgresql.conf中 autovacuum = on autovacuum_max_workers = 3 autovacuum_naptime = 1min

19.2 手动执行VACUUM和ANALYZE

对于大型数据库，可能需要手动执行：

VACUUM FULL VERBOSE ANALYZE;

19.3 重建索引

定期重建索引提高性能：

REINDEX TABLE large_table;

20. 卸载PostgreSQL

如果需要卸载PostgreSQL：

20.1 停止服务

systemctl stop postgresql systemctl disable postgresql

20.2 删除安装文件

rm -rf /opt/pgsql/postgresql

20.3 删除数据目录（谨慎操作）

rm -rf /opt/pgsql/postgresql/data

20.4 删除系统用户

userdel postgres groupdel postgres

20.5 删除systemd服务文件

rm /etc/systemd/system/postgresql.service systemctl daemon-reload