Home

深色模式

PostgreSQL 安装配置 ​

安装 ​

macOS ​

使用 Homebrew 安装：

sh
brew install postgresql@16

启动服务：

sh
brew services start postgresql@16

Linux (Ubuntu/Debian) ​

安装 PostgreSQL：

sh
sudo apt update
sudo apt install postgresql postgresql-contrib

启动服务：

sh
sudo systemctl start postgresql
sudo systemctl enable postgresql

Linux (CentOS/RHEL) ​

安装 PostgreSQL：

sh
sudo yum install postgresql-server postgresql-contrib

初始化数据库：

sh
sudo postgresql-setup --initdb

启动服务：

sh
sudo systemctl start postgresql
sudo systemctl enable postgresql

连接 PostgreSQL ​

本地连接 ​

连接到本地 PostgreSQL 服务器：

sh
# 使用默认用户 postgres 连接
psql -U postgres

# 连接到指定数据库
psql -U postgres -d mydb

# 使用当前系统用户连接（如果存在同名数据库用户）
psql

# 指定主机和端口
psql -h localhost -p 5432 -U postgres -d mydb

连接参数说明：

  • -U 或 --username: 指定用户名
  • -d 或 --dbname: 指定数据库名
  • -h 或 --host: 指定主机地址，默认为 localhost
  • -p 或 --port: 指定端口，默认为 5432

远程连接 ​

连接到远程 PostgreSQL 服务器：

sh
# 基本远程连接命令
psql -h 192.168.1.100 -p 5432 -U postgres -d mydb

# 使用连接字符串
psql "postgresql://username:password@host:port/database"

# 示例：使用连接字符串连接
psql "postgresql://postgres:mypassword@192.168.1.100:5432/mydb"

连接字符串格式：

postgresql://[user[:password]@][host][:port][/database][?param1=value1&...]

设置密码 ​

首次连接或修改密码：

sh
# 方法一：使用 psql 命令时提示输入密码
psql -U postgres -W

# 方法二：在 psql 中修改密码
psql -U postgres
ALTER USER postgres WITH PASSWORD 'newpassword';

# 方法三：使用命令行工具修改密码
psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

环境变量 ​

可以使用环境变量简化连接：

sh
# 设置环境变量
export PGHOST=192.168.1.100
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=mypassword
export PGDATABASE=mydb

# 直接连接，无需指定参数
psql

连接测试 ​

测试连接是否成功：

sh
# 测试连接并执行简单查询
psql -h 192.168.1.100 -U postgres -d postgres -c "SELECT version();"

# 测试连接并显示连接信息
psql -h 192.168.1.100 -U postgres -d postgres -c "\conninfo"

常见连接问题 ​

  1. 连接被拒绝

检查 PostgreSQL 服务是否运行：

sh
# Linux
sudo systemctl status postgresql

# macOS
brew services list | grep postgresql
  1. 认证失败

检查 pg_hba.conf 配置文件，确保允许相应的连接方式。

  1. 无法连接到远程服务器

确保：

  • PostgreSQL 配置了 listen_addresses = '*'（监听所有网络接口）
  • 防火墙允许 5432 端口
  • pg_hba.conf 中配置了允许远程连接的规则

用户与权限 ​

用户和角色 ​

PostgreSQL 中，用户（USER）和角色（ROLE）本质上是相同的，CREATE USER 等同于 CREATE ROLE ... WITH LOGIN。

角色可以拥有登录权限（LOGIN），也可以没有登录权限（用于权限分组）。

创建用户 ​

创建新用户：

sql
-- 创建用户（可以登录）
CREATE USER username WITH PASSWORD 'password';

-- 创建角色（默认不能登录）
CREATE ROLE rolename;

-- 创建用户并指定属性
CREATE USER username WITH 
    PASSWORD 'password'
    CREATEDB          -- 可以创建数据库
    CREATEROLE        -- 可以创建角色
    SUPERUSER         -- 超级用户
    LOGIN             -- 可以登录
    REPLICATION;      -- 可以用于复制

修改用户 ​

修改用户属性：

sql
-- 修改密码
ALTER USER username WITH PASSWORD 'newpassword';

-- 添加权限
ALTER USER username WITH CREATEDB;

-- 移除权限
ALTER USER username WITH NOCREATEDB;

-- 重命名用户
ALTER USER oldname RENAME TO newname;

-- 设置用户有效期
ALTER USER username VALID UNTIL '2025-12-31';

删除用户 ​

删除用户：

sql
-- 删除用户（如果用户拥有对象，会报错）
DROP USER username;

-- 删除用户及其拥有的对象
DROP USER username CASCADE;

查看用户信息 ​

查看所有用户：

sql
-- 查看所有用户
SELECT usename, usecreatedb, usesuper, usecanlogin FROM pg_user;

-- 或使用
\du

-- 查看用户详细信息
SELECT * FROM pg_user WHERE usename = 'username';

-- 查看角色
SELECT * FROM pg_roles;

权限类型 ​

PostgreSQL 的权限包括：

  • SELECT - 查询数据
  • INSERT - 插入数据
  • UPDATE - 更新数据
  • DELETE - 删除数据
  • TRUNCATE - 清空表
  • REFERENCES - 创建外键
  • TRIGGER - 创建触发器
  • CREATE - 创建对象（数据库、表等）
  • CONNECT - 连接数据库
  • TEMPORARY - 创建临时表
  • EXECUTE - 执行函数
  • USAGE - 使用模式、序列等

数据库权限 ​

授予数据库权限：

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

-- 授予创建模式权限
GRANT CREATE ON DATABASE dbname TO username;

-- 撤销权限
REVOKE CONNECT ON DATABASE dbname FROM username;

模式权限 ​

授予模式权限：

sql
-- 授予使用模式权限
GRANT USAGE ON SCHEMA schemaname TO username;

-- 授予在模式中创建对象权限
GRANT CREATE ON SCHEMA schemaname TO username;

-- 授予模式中所有表的权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schemaname TO username;

-- 授予模式中所有序列的权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schemaname TO username;

-- 授予模式中所有函数的权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schemaname TO username;

表权限 ​

授予表权限：

sql
-- 授予表的所有权限
GRANT ALL PRIVILEGES ON TABLE tablename TO username;

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON TABLE tablename TO username;

-- 授予所有表的权限
GRANT SELECT ON ALL TABLES IN SCHEMA schemaname TO username;

-- 授予未来创建的表权限
ALTER DEFAULT PRIVILEGES IN SCHEMA schemaname 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

列权限 ​

授予列权限：

sql
-- 授予特定列的权限
GRANT SELECT (column1, column2), UPDATE (column1) ON TABLE tablename TO username;

序列权限 ​

授予序列权限：

sql
-- 授予序列权限
GRANT USAGE, SELECT ON SEQUENCE sequencename TO username;

-- 授予所有序列权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schemaname TO username;

-- 授予未来创建的序列权限
ALTER DEFAULT PRIVILEGES IN SCHEMA schemaname 
    GRANT USAGE, SELECT ON SEQUENCES TO username;

函数权限 ​

授予函数权限：

sql
-- 授予执行函数权限
GRANT EXECUTE ON FUNCTION functionname(argument_types) TO username;

-- 授予所有函数权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schemaname TO username;

-- 授予未来创建的函数权限
ALTER DEFAULT PRIVILEGES IN SCHEMA schemaname 
    GRANT EXECUTE ON FUNCTIONS TO username;

角色继承 ​

创建角色组并分配权限：

sql
-- 创建角色组
CREATE ROLE readonly;
CREATE ROLE readwrite;

-- 授予角色组权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;

-- 将用户添加到角色组
GRANT readonly TO username;
GRANT readwrite TO anotheruser;

-- 查看角色成员
SELECT r.rolname, m.rolname AS member 
FROM pg_roles r 
JOIN pg_auth_members am ON r.oid = am.roleid 
JOIN pg_roles m ON am.member = m.oid;

查看权限 ​

查看用户权限：

sql
-- 查看表的权限
SELECT grantee, privilege_type 
FROM information_schema.table_privileges 
WHERE table_name = 'tablename';

-- 查看数据库权限
SELECT datname, usename, usecreatedb, usesuper 
FROM pg_database d 
JOIN pg_user u ON d.datdba = u.usesysid;

-- 查看模式权限
SELECT nspname, grantee, privilege_type 
FROM pg_namespace n 
JOIN information_schema.usage_privileges p ON n.nspname = p.object_schema 
WHERE nspname = 'schemaname';

-- 使用 psql 命令查看
\dp tablename          -- 查看表权限
\dn+ schemaname        -- 查看模式权限
\l+ dbname             -- 查看数据库权限

默认权限 ​

设置默认权限，使新创建的对象自动授予权限：

sql
-- 为特定用户设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT USAGE, SELECT ON SEQUENCES TO username;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT EXECUTE ON FUNCTIONS TO username;

-- 为角色组设置默认权限
ALTER DEFAULT PRIVILEGES FOR ROLE creator IN SCHEMA public 
    GRANT SELECT ON TABLES TO readonly;

权限实践建议 ​

  1. 最小权限原则

只授予用户完成任务所需的最小权限。

  1. 使用角色组

创建角色组管理权限，而不是直接授予用户：

sql
-- 创建只读角色组
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 将用户添加到角色组
GRANT readonly TO username;
  1. 定期审查权限

定期检查用户权限，移除不必要的权限：

sql
-- 查看所有用户及其权限
SELECT 
    r.rolname AS role,
    r.rolsuper AS superuser,
    r.rolcreatedb AS createdb,
    r.rolcreaterole AS createrole,
    r.rolcanlogin AS canlogin
FROM pg_roles r
WHERE r.rolname NOT LIKE 'pg_%'
ORDER BY r.rolname;
  1. 使用默认权限

为新创建的对象设置默认权限，避免手动授予每个对象权限。

配置文件位置 ​

PostgreSQL 的主要配置文件位于数据目录中，默认位置：

  • macOS (Homebrew): /opt/homebrew/var/postgresql@16/postgresql.conf
  • Linux: /etc/postgresql/{version}/main/postgresql.conf

查看配置文件位置：

sh
psql -U postgres -c "SHOW config_file;"

查看数据目录：

sh
psql -U postgres -c "SHOW data_directory;"

配置文件结构 ​

PostgreSQL 配置文件使用键值对格式，以 # 开头的行为注释。

配置文件格式 ​

conf
# 这是注释
参数名 = 值

参数值可以是：

  • 数字：max_connections = 100
  • 字符串：listen_addresses = 'localhost'
  • 布尔值：ssl = on 或 ssl = off
  • 单位值：shared_buffers = 128MB

常用配置项 ​

连接配置 ​

conf
# 监听地址，'*' 表示监听所有网络接口
listen_addresses = '*'

# 监听端口
port = 5432

# 最大连接数
max_connections = 100

# 超级用户保留的连接数
superuser_reserved_connections = 3

# 连接超时时间（秒）
connect_timeout = 10

# TCP keepalive 参数
tcp_keepalives_idle = 600
tcp_keepalives_interval = 30
tcp_keepalives_count = 3

内存配置 ​

conf
# 共享缓冲区大小，建议设置为物理内存的 25%
shared_buffers = 128MB

# 工作内存，用于排序和哈希操作
work_mem = 4MB

# 维护操作的工作内存
maintenance_work_mem = 64MB

# 有效缓存大小，用于查询规划器
effective_cache_size = 4GB

# 临时文件缓冲区大小
temp_buffers = 8MB

日志配置 ​

conf
# 日志级别：DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, PANIC
log_min_messages = warning

# 日志输出目标：stderr, csvlog, syslog
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB

# 记录慢查询
log_min_duration_statement = 1000  # 单位：毫秒

# 记录连接和断开
log_connections = on
log_disconnections = on

# 记录锁等待
log_lock_waits = on

# 记录检查点
log_checkpoints = on

查询优化配置 ​

conf
# 随机页面成本，影响查询规划器选择索引还是全表扫描
random_page_cost = 4.0

# 顺序页面成本
seq_page_cost = 1.0

# 查询规划器统计信息
default_statistics_target = 100

# 自动清理
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

字符集配置 ​

PostgreSQL 支持多种字符集，默认使用数据库创建时指定的字符集。

查看支持的字符集：

sh
psql -U postgres -c "SELECT datname, pg_encoding_to_char(encoding) FROM pg_database;"

创建数据库时指定字符集：

sh
CREATE DATABASE mydb WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8';

查看数据库字符集：

sh
SELECT datname, pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'mydb';

查看和修改配置 ​

查看当前配置 ​

查看所有配置参数：

sh
psql -U postgres -c "SHOW ALL;"

查看特定配置：

sh
psql -U postgres -c "SHOW max_connections;"

查看配置文件位置：

sh
psql -U postgres -c "SHOW config_file;"

运行时修改配置 ​

某些配置可以在运行时修改：

sh
-- 全局修改
ALTER SYSTEM SET max_connections = 200;

-- 会话级修改
SET work_mem = '8MB';

-- 仅当前事务有效
SET LOCAL work_mem = '16MB';

使用 ALTER SYSTEM 修改的配置会写入 postgresql.auto.conf 文件，重启后仍然有效。

重新加载配置（无需重启）：

sh
-- 方法一：使用 SQL
SELECT pg_reload_conf();

-- 方法二：使用命令行
pg_ctl reload -D /path/to/data/directory

-- 方法三：使用 systemctl (Linux)
sudo systemctl reload postgresql

配置文件优先级 ​

PostgreSQL 按以下顺序加载配置：

  1. postgresql.conf - 主配置文件
  2. postgresql.auto.conf - 自动生成的配置文件（ALTER SYSTEM 命令写入）
  3. 命令行参数（postgres 命令的 -c 选项）

后加载的配置会覆盖先加载的配置。

客户端认证配置 ​

PostgreSQL 使用 pg_hba.conf 文件配置客户端认证规则。

文件位置：

sh
psql -U postgres -c "SHOW hba_file;"

配置格式：

conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
local   all             all                                     peer

认证方法：

  • trust - 无条件允许连接
  • reject - 无条件拒绝连接
  • md5 - 使用 MD5 加密密码
  • scram-sha-256 - 使用 SCRAM-SHA-256 加密密码（推荐）
  • peer - 使用操作系统用户名
  • ident - 使用 ident 协议

修改 pg_hba.conf 后需要重新加载配置：

sh
SELECT pg_reload_conf();

实践建议 ​

内存配置建议 ​

  • shared_buffers: 物理内存的 25%，但不超过 8GB
  • work_mem: 根据并发连接数和可用内存计算，(总内存 - shared_buffers) / (max_connections * 2)
  • effective_cache_size: 设置为物理内存的 50-75%
  • maintenance_work_mem: 可以设置较大，如 1-2GB

日志配置建议 ​

生产环境建议：

  • log_min_messages = warning - 只记录警告及以上级别
  • log_min_duration_statement = 1000 - 记录执行时间超过 1 秒的查询
  • log_connections = on - 记录连接信息
  • log_lock_waits = on - 记录锁等待，有助于发现性能问题

安全建议 ​

  • 使用 scram-sha-256 作为密码认证方法
  • 限制 pg_hba.conf 中的访问范围
  • 定期更新 PostgreSQL 版本
  • 使用 SSL 连接（生产环境）

参考 ​