PostgreSQL 安装和使用指南
登录
标签搜索
侧边栏壁纸
博主昵称
TomHanck4

  • 累计撰写 35 篇文章
  • 累计收到 6 条评论

PostgreSQL 安装和使用指南

TomHanck4
TomHanck4
2026-01-07 / 0 评论 / 1 阅读 / 正在检测是否收录...

PostgreSQL 安装和使用指南

目录

  1. 概述
  2. 安装 PostgreSQL
  3. 基本配置
  4. 启动和停止服务
  5. 用户和权限管理
  6. 数据库操作
  7. 表和数据操作
  8. 备份和恢复
  9. 性能调优基础
  10. 常用命令总结
  11. 故障排除

概述

PostgreSQL 是一个功能强大的开源关系型数据库管理系统，支持 SQL 标准，并提供了许多现代特性，如复杂查询、外键、触发器、视图、事务完整性等。

主要特性

  • ACID 事务支持
  • 多版本并发控制 (MVCC)
  • 支持多种数据类型
  • 可扩展的类型系统
  • 丰富的索引类型
  • 全文搜索
  • JSON 和 JSONB 支持
  • 存储过程和函数

安装 PostgreSQL

在 Ubuntu/Debian 上安装

# 更新包列表
sudo apt update

# 安装 PostgreSQL
sudo apt install postgresql postgresql-contrib

# 检查版本
psql --version

在 CentOS/RHEL 上安装

# 安装 PostgreSQL 仓库
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装 PostgreSQL
sudo yum install -y postgresql15-server postgresql15-contrib

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

# 启动服务
sudo systemctl start postgresql-15
sudo systemctl enable postgresql-15

在 macOS 上安装

# 使用 Homebrew 安装
brew install postgresql

# 启动服务
brew services start postgresql

使用 Docker 安装

# 拉取 PostgreSQL 镜像
docker pull postgres:15

# 运行容器
docker run --name postgres-db -e POSTGRES_PASSWORD=mypassword -e POSTGRES_DB=mydb -p 5432:5432 -d postgres:15

基本配置

配置文件位置

PostgreSQL 的主要配置文件通常位于：

  • /etc/postgresql/15/main/postgresql.conf (Ubuntu/Debian)
  • /var/lib/pgsql/15/data/postgresql.conf (CentOS/RHEL)
  • /usr/local/var/postgres/postgresql.conf (macOS with Homebrew)

常用配置参数

# 监听地址 (默认只监听本地)
listen_addresses = '*'

# 端口 (默认 5432)
port = 5432

# 最大连接数
max_connections = 100

# 共享缓冲区大小 (设置为系统内存的 25%)
shared_buffers = 256MB

# 工作内存
work_mem = 4MB

# 维护工作内存
maintenance_work_mem = 64MB

# 检查点间隔
checkpoint_completion_target = 0.9

# WAL 日志级别
wal_level = replica

# 最大 WAL 发送者进程数
max_wal_senders = 10

修改配置后重启服务

# Ubuntu/Debian
sudo systemctl restart postgresql

# CentOS/RHEL
sudo systemctl restart postgresql-15

# macOS
brew services restart postgresql

启动和停止服务

系统服务管理

# 启动服务
sudo systemctl start postgresql

# 停止服务
sudo systemctl stop postgresql

# 重启服务
sudo systemctl restart postgresql

# 查看状态
sudo systemctl status postgresql

# 开机自启
sudo systemctl enable postgresql

手动启动

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

# 启动 PostgreSQL
pg_ctl -D /var/lib/postgresql/15/main start

# 停止 PostgreSQL
pg_ctl -D /var/lib/postgresql/15/main stop

用户和权限管理

切换到 postgres 用户

sudo -u postgres psql
# 或者
sudo su - postgres
psql

创建用户

-- 创建普通用户
CREATE USER username WITH PASSWORD 'password';

-- 创建超级用户
CREATE USER admin WITH SUPERUSER PASSWORD 'password';

-- 创建用户并指定权限
CREATE USER app_user WITH PASSWORD 'password'
    CREATEDB
    CREATEROLE;

修改用户密码

-- 修改密码
ALTER USER username PASSWORD 'new_password';

-- 强制修改密码 (下次登录时)
ALTER USER username PASSWORD NULL;

删除用户

DROP USER username;

角色和权限

-- 创建角色
CREATE ROLE readonly;

-- 授予权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 撤销权限
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM readonly;

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE mydb TO username;

-- 授予表权限
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO username;

数据库操作

创建数据库

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库并指定所有者
CREATE DATABASE mydb OWNER username;

-- 创建数据库并指定编码
CREATE DATABASE mydb
    OWNER username
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

连接数据库

# 使用 psql 连接
psql -h localhost -U username -d mydb

# 或者直接连接默认数据库
psql -U username mydb

列出数据库

-- 列出所有数据库
\l

-- 或者
SELECT datname FROM pg_database;

删除数据库

-- 删除数据库 (需要超级用户权限)
DROP DATABASE mydb;

表和数据操作

创建表

-- 创建简单表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建带有外键的表
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published_at TIMESTAMP
);

数据类型

数据类型描述示例
SERIAL自增整数id SERIAL
INTEGER整数age INTEGER
VARCHAR(n)可变长度字符串name VARCHAR(100)
TEXT无限长度文本content TEXT
BOOLEAN布尔值active BOOLEAN
TIMESTAMP时间戳created_at TIMESTAMP
DATE日期birth_date DATE
NUMERIC(p,s)精确数值price NUMERIC(10,2)
JSONBJSON 数据metadata JSONB

插入数据

-- 插入单行数据
INSERT INTO users (name, email) VALUES ('张三', 'zhangsan@example.com');

-- 插入多行数据
INSERT INTO users (name, email) VALUES
    ('李四', 'lisi@example.com'),
    ('王五', 'wangwu@example.com');

-- 插入并返回数据
INSERT INTO users (name, email) VALUES ('赵六', 'zhaoliu@example.com')
RETURNING id, name, email;

查询数据

-- 查询所有数据
SELECT * FROM users;

-- 选择特定列
SELECT name, email FROM users;

-- 带条件查询
SELECT * FROM users WHERE name LIKE '张%';

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 聚合查询
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users WHERE age IS NOT NULL;

-- 连接查询
SELECT u.name, p.title
FROM users u
JOIN posts p ON u.id = p.user_id;

更新数据

-- 更新单行
UPDATE users SET name = '张三新' WHERE id = 1;

-- 更新多行
UPDATE users SET active = true WHERE created_at < '2024-01-01';

-- 更新并返回
UPDATE users SET email = 'newemail@example.com' WHERE id = 1
RETURNING id, name, email;

删除数据

-- 删除特定行
DELETE FROM users WHERE id = 1;

-- 删除所有数据
DELETE FROM users;

-- 清空表 (保留表结构)
TRUNCATE TABLE users;

索引

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_name ON users(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_unique_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_posts_user_published ON posts(user_id, published_at);

-- 查看索引
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- 删除索引
DROP INDEX idx_users_email;

备份和恢复

使用 pg_dump 备份

# 备份整个数据库
pg_dump -U username -h localhost mydb > backup.sql

# 备份压缩格式
pg_dump -U username -h localhost -Fc mydb > backup.dump

# 备份特定表
pg_dump -U username -h localhost -t users mydb > users_backup.sql

# 只备份数据
pg_dump -U username -h localhost --data-only mydb > data_only.sql

# 只备份结构
pg_dump -U username -h localhost --schema-only mydb > schema_only.sql

使用 pg_restore 恢复

# 从 SQL 文件恢复
psql -U username -h localhost -d mydb < backup.sql

# 从压缩文件恢复
pg_restore -U username -h localhost -d mydb backup.dump

# 创建数据库并恢复
createdb -U username newdb
pg_restore -U username -h localhost -d newdb backup.dump

逻辑备份脚本

#!/bin/bash
# 自动备份脚本

BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
DB_USER="postgres"

mkdir -p $BACKUP_DIR

pg_dump -U $DB_USER -h localhost -Fc $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# 删除7天前的备份
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.dump"

物理备份 (使用 pg_basebackup)

# 创建基础备份
pg_basebackup -U postgres -h localhost -D /var/lib/postgresql/backup -Fp -Xs -P

# 恢复时停止服务，清理数据目录，复制备份文件，重启服务

性能调优基础

查看系统状态

-- 查看当前连接
SELECT * FROM pg_stat_activity;

-- 查看表大小
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 查看索引使用情况
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查看慢查询
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

配置参数调优

# 内存配置
shared_buffers = 256MB          # 通常设置为系统内存的 25%
work_mem = 4MB                  # 单个查询可用的内存
maintenance_work_mem = 64MB     # 维护操作内存
effective_cache_size = 1GB      # 操作系统缓存大小

# 检查点配置
checkpoint_completion_target = 0.9
wal_buffers = 16MB
max_wal_size = 1GB
min_wal_size = 80MB

# 查询规划器
random_page_cost = 1.1          # SSD 设为 1.1，HDD 设为 4.0
effective_io_concurrency = 200  # SSD 设为 200，HDD 设为 2

索引优化

-- 查看未使用的索引
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

-- 查看重复索引
SELECT pg_size_pretty(sum(pg_relation_size(indexrelid))) as size,
       array_agg(indexrelid::regclass) as indexes
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1;

-- EXPLAIN 分析查询
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

表优化

-- 重新组织表 (VACUUM)
VACUUM users;

-- 完全清理表
VACUUM FULL users;

-- 重新建立索引
REINDEX TABLE users;

-- 分析表统计信息
ANALYZE users;

常用命令总结

psql 命令

# 连接数据库
psql -U username -d dbname -h host -p port

# 执行 SQL 文件
psql -U username -d dbname -f file.sql

# 导出查询结果
psql -U username -d dbname -c "SELECT * FROM users" -o output.txt

psql 内部命令

-- 帮助
\?

-- 列出数据库
\l

-- 切换数据库
\c dbname

-- 列出表
\dt

-- 描述表结构
\d table_name

-- 列出用户
\du

-- 查看查询历史
\s

-- 执行操作系统命令
\! ls -la

-- 退出
\q

系统管理命令

# 查看 PostgreSQL 进程
ps aux | grep postgres

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

# 检查配置文件语法
postgres -C config_file

# 重新加载配置
pg_ctl reload -D /var/lib/postgresql/15/main

故障排除

常见问题

  1. 连接被拒绝

    • 检查服务是否启动：sudo systemctl status postgresql
    • 检查监听地址配置
    • 检查防火墙设置

  2. 权限不足

    -- 检查用户权限
SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username';

-- 授予权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO username;

  3. 磁盘空间不足

    -- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('mydb'));

-- 查看表大小
SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables ORDER BY pg_total_relation_size DESC LIMIT 10;

  4. 查询性能问题

    -- 启用查询统计
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看慢查询
SELECT query, calls, total_time/calls as avg_time
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 5;

  5. 恢复数据库

    # 停止服务
sudo systemctl stop postgresql

# 清理数据目录
sudo rm -rf /var/lib/postgresql/15/main/*

# 从备份恢复
sudo -u postgres pg_restore -C -d postgres /path/to/backup.dump

# 启动服务
sudo systemctl start postgresql

监控和维护

-- 查看当前活动连接
SELECT datname, usename, client_addr, state, query_start
FROM pg_stat_activity
WHERE state != 'idle';

-- 查看锁定情况
SELECT blocked_locks.pid as blocked_pid,
       blocked_activity.usename as blocked_user,
       blocking_locks.pid as blocking_pid,
       blocking_activity.usename as blocking_user,
       blocked_activity.query as blocked_statement
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

扩展和插件

-- 查看已安装扩展
SELECT * FROM pg_extension;

-- 安装常用扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- 查询统计
CREATE EXTENSION IF NOT EXISTS uuid-ossp;           -- UUID 支持
CREATE EXTENSION IF NOT EXISTS postgis;             -- 地理信息支持
CREATE EXTENSION IF NOT EXISTS pg_trgm;             -- 模糊匹配

这个指南涵盖了 PostgreSQL 的基本安装、配置、使用和维护。如果你在使用过程中遇到特定问题，请提供详细的错误信息以获得更精确的帮助。

0
暂无标签
版权属于： TomHanck4
本文链接： http://blog.fangsiyuan.top/index.php/archives/161/
作品采用： 《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权

评论 (0)

取消