Linux二进制部署PostgreSQL 13

  • A+
所属分类：PostgreSQL 数据库

数据目录结构

$PGDATA/
├── pg_hba.conf          # 客户端认证配置
├── postgresql.conf      # 主配置文件
├── pg_xact/             # 事务状态文件
├── pg_tblspc/           # 表空间目录
├── pg_wal/              # WAL日志目录
└── base/                # 数据库文件

用户与权限

角色系统

  • 用户即特殊角色：带WITH LOGIN属性的角色可登录数据库
  • 权限继承机制：角色可继承所属角色组的权限，形成权限树
  • 超级用户特权：postgres角色绕过所有权限检查，需严格限制使用

权限管理

CREATE ROLE developer;                  -- 创建角色
GRANT CREATE ON SCHEMA public TO dev;   -- 授予权限
REVOKE ALL ON TABLE users FROM dev;     -- 撤销权限
ALTER ROLE dev WITH PASSWORD 'xxx';     -- 设置密码

认证方法

认证方法安全性适用场景配置关键项
md5中等内网环境pg_hba.conf 中设置md5
scram-sha-256高生产环境PostgreSQL 10+ 默认启用
cert最高金融等敏感场景SSL 证书配置
peer低本地测试基于系统用户认证

查询优化

执行计划分析

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;
  • 关注 Total runtime 和 Rows Removed by Filter
  • 索引扫描（Index Scan）通常优于顺序扫描（Seq Scan）

索引策略

  • B-tree：最常用，支持 =、<、>、BETWEEN 等操作
  • GiST：适用于空间数据、全文搜索
  • GIN：适合多值索引（数组、JSONB）
  • 避免过度索引：每个索引都会降低写性能

统计信息

  • ANALYZE 命令更新表的统计信息
  • 影响查询优化器的执行计划选择

事务与并发控制

ACID 特性

  • 原子性（Atomicity）：通过 WAL 日志实现
  • 一致性（Consistency）：由约束和触发器保证
  • 隔离性（Isolation）：4 种隔离级别
  • 持久性（Durability）：WAL 强制同步到磁盘

隔离级别 

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

锁机制

  • 行级锁：FOR UPDATE、FOR SHARE
  • 表级锁：ACCESS EXCLUSIVE、SHARE UPDATE EXCLUSIVE
  • 死锁检测：自动检测并回滚代价最小的事务

安装PostgreSQL

安装编译依赖

yum -y install gcc make readline-devel zlib-devel openssl-devel

下载并解压源码包

# 下载PostgreSQL 13.4
wget https://ftp.postgresql.org/pub/source/v13.4/postgresql-13.4.tar.gz

# 解压
tar -xzvf postgresql-13.4.tar.gz
cd postgresql-13.4

配置编译选项

./configure --prefix=/usr/local/pgsql-13.4 \
            --with-openssl \
            --with-zlib \
            --with-readline \
            --enable-thread-safety

编译并安装

make world
sudo make install-world
ln -s /usr/local/pgsql-13.4 /usr/local/pgsql

创建数据目录

useradd postgres
mkdir -p /data/pgsql
chown -R postgres:postgres /data/pgsql
chmod 700 /data/pgsql

初始化数据目录

su - postgres -c "/usr/local/pgsql/bin/initdb -D /data/pgsql/ --encoding=UTF8 --locale=en_US.UTF-8 --pwfile=<(echo 'your_postgres_password')"

注意：替换your_postgres_password为强密码。

创建 systemd 服务文件

tee /usr/lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL 13 database server
After=network.target

[Service]
Type=forking
User=postgres
Group=postgres
Environment=PGDATA=/data/pgsql
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D \$PGDATA -s -w -t 300
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D \$PGDATA -s -m fast
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D \$PGDATA -s
Restart=on-failure

[Install]
WantedBy=multi-user.target
EOF

启动并启用服务

systemctl daemon-reload
systemctl start postgresql
systemctl enable postgresql

配置远程访问

 编辑 pg_hba.conf

vim /data/pgsql/pg_hba.conf
# IPv4 本地连接
host    all             all             127.0.0.1/32            md5
# IPv4 远程连接
host    all             all             0.0.0.0/0          md5
# IPv6 本地连接
host    all             all             ::1/128                 md5

编辑 postgresql.conf

vim /data/pgsql/postgresql.conf
listen_addresses = '*'      # 监听所有IP地址
port = 5432                 # 端口号
max_connections = 100       # 最大连接数
shared_buffers = 128MB      # 共享缓冲区大小（根据内存调整）

重启服务使配置生效

systemctl restart postgresql

配置环境变量

tee /etc/profile.d/pgsql.sh << EOF
export PATH=$PATH:/usr/local/pgsql/bin
EOF
source /etc/profile

本地登录测试

psql -U postgres -d postgres

查看数据库信息

SELECT version();         -- 查看版本
\l                        -- 列出所有数据库
\du                       -- 列出所有用户
\conninfo                 -- 查看当前连接信息

创建应用用户和数据库（避免使用超级用户postgres进行日常操作）

CREATE USER xxx_user WITH PASSWORD 'xxx.com';        //创建数据库用户
CREATE DATABASE xxx_db OWNER xxx_user;        //创建应用数据库
GRANT ALL PRIVILEGES ON DATABASE xxx_db TO xxx_user;        //授予权限
psql -U xxx_user -d xxx_db -h localhost        //使用应用用户登录

性能优化

调整内存分配编辑postgresql.conf

shared_buffers = 1GB       # 建议为内存的25%
work_mem = 32MB            # 每个查询的工作内存
maintenance_work_mem = 128MB
effective_cache_size = 3GB # 建议为内存的50%

启用日志编辑postgresql.conf

logging_collector = on
log_destination = 'csvlog'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'      # 记录所有SQL语句（调试用）

备份与恢复策略

pg_dump -U postgres -d xxx_db -f /backup/xxx_db_backup.sql        //逻辑备份
psql -U postgres -d xxx_db -f /backup/xxx_db_backup.sql        //恢复数据库
pg_basebackup -U postgres -D /backup/basebackup -Fp -Xs -v -P        //物理备份

设置自动备份

# 添加到crontab
0 2 * * * pg_dump -U postgres -d app_db | gzip > /backup/app_db_$(date +\%Y\%m\%d).sql.gz

常用 psql 命令

命令功能描述
\l列出所有数据库
\c database_name连接到指定数据库
\dt列出当前数据库中的所有表
\du列出所有用户和角色
\d table_name显示表的结构
\q退出 psql
\hSQL 命令帮助
\?psql 命令帮助
Linux二进制部署PostgreSQL 13
xxx