PostgreSQL 实战指南

本文整合了 PostgreSQL 数据库的管理操作、查询技巧和实战经验。

数据类型

VARCHAR vs TEXT

在 PostgreSQL 中，VARCHAR 和 TEXT 的区别很小：

类型	说明
`VARCHAR(n)`	限制最大长度为 n
`VARCHAR`	不限长度（PostgreSQL 扩展）
`TEXT`	不限长度（常用）

性能对比：在 PostgreSQL 中，VARCHAR 和 TEXT 性能几乎相同。主要区别在于：

VARCHAR(n) 会在插入时检查长度限制
TEXT 更简洁，推荐用于不需要长度限制的场景

SQLAlchemy 映射：

from sqlalchemy import Column, String, Text

# 两者在 PostgreSQL 中效果相同
name = Column(String)      # 映射为 VARCHAR
bio = Column(Text)         # 映射为 TEXT

用户和数据库管理

创建用户和数据库

-- 连接到 PostgreSQL
psql -U postgres -h 127.0.0.1

-- 1. 创建用户（角色）
CREATE ROLE myuser WITH LOGIN PASSWORD 'yourpassword' VALID UNTIL 'infinity';

-- 2. PostgreSQL 15+ 需要先授权给当前用户
GRANT myuser TO postgres;

-- 3. 创建数据库并指定所有者
CREATE DATABASE mydb WITH ENCODING='UTF8' OWNER=myuser CONNECTION LIMIT=-1;

-- 4. 撤销临时授权
REVOKE myuser FROM postgres;

参数说明

参数	说明
`LOGIN`	允许该角色登录
`PASSWORD`	设置密码
`VALID UNTIL 'infinity'`	密码永不过期
`ENCODING='UTF8'`	数据库编码为 UTF-8
`OWNER`	指定数据库所有者
`CONNECTION LIMIT=-1`	不限制连接数

修改和删除

-- 修改用户密码
ALTER USER myuser WITH PASSWORD 'newpassword';

-- 修改数据库所有者
ALTER DATABASE mydb OWNER TO postgres;

-- 删除数据库
DROP DATABASE mydb;

常用初始化脚本示例

-- Authentik
CREATE ROLE authentik WITH LOGIN PASSWORD 'password' VALID UNTIL 'infinity';
GRANT authentik TO postgres;
CREATE DATABASE authentik WITH ENCODING='UTF8' OWNER=authentik CONNECTION LIMIT=-1;
REVOKE authentik FROM postgres;

-- Confluence
CREATE ROLE confluence WITH LOGIN PASSWORD 'password' VALID UNTIL 'infinity';
GRANT confluence TO postgres;
CREATE DATABASE confluence WITH ENCODING='UTF8' OWNER=confluence CONNECTION LIMIT=-1;
REVOKE confluence FROM postgres;

-- LobeChat
CREATE USER lobechat WITH LOGIN PASSWORD 'password' VALID UNTIL 'infinity';
GRANT lobechat TO postgres;
CREATE DATABASE lobechat WITH ENCODING='UTF8' OWNER=lobechat CONNECTION LIMIT=-1;
REVOKE lobechat FROM postgres;

查询技巧

查看表大小

方法一：查询单个表

1	SELECT pg_size_pretty(pg_relation_size('table_name'));

方法二：按大小排序所有表

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
LIMIT 20;

方法三：分离数据和索引大小

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

导入导出数据

# 导出数据库
pg_dump -U username -h host dbname > backup.sql

# 导入数据库
psql -U username -h host dbname < backup.sql

# 导出指定表
pg_dump -U username -t table_name dbname > table_backup.sql

INSERT INTO SELECT

-- 从一个表复制数据到另一个表
INSERT INTO npc_list (op_bind, uid, day)
SELECT op_bind, uid, first_date
FROM npc_white_list;

时间函数

-- 获取 UTC 时间
UPDATE npc_list SET update_time = timezone('utc', now());

-- 获取当前月份
SELECT date_part('month', now());

-- 获取当前日期
SELECT CURRENT_DATE;

进程管理

查找和终止挂起的查询：

-- 查看所有活动进程
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- 查看特定数据库的连接
SELECT * FROM pg_stat_activity WHERE datname = 'mydb';

-- 取消查询（温和方式）
SELECT pg_cancel_backend(PID);

-- 强制终止连接
SELECT pg_terminate_backend(PID);

-- 终止指定数据库的所有连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb' AND pid <> pg_backend_pid();

高级用法

CROSS JOIN 与 JSONB 数组

展开 JSONB 数组并进行计算：

SELECT
    attribute->>'uid' AS uid,
    attribute->>'day' AS day,
    (building->>'lv')::integer * (building->>'num')::integer AS total
FROM public.user_daily_report
CROSS JOIN jsonb_array_elements(metric->'building') AS building
WHERE attribute->>'day' = '2019-09-01';

Vector 类型转换

在使用向量数据库功能时（如 pgvector）：

1
2
3

-- 修改 embeddings 列的维度
ALTER TABLE embeddings
ALTER COLUMN embeddings TYPE vector(3072);

SQLAlchemy 集成

CastingArray 实现

在 SQLAlchemy 中处理 PostgreSQL 的 ARRAY 类型与 JSON/JSONB 组合：

from sqlalchemy import cast
from sqlalchemy.dialects.postgresql import ARRAY

class CastingArray(ARRAY):
    """自动转换绑定值为数组类型"""
    def bind_expression(self, bindvalue):
        return cast(bindvalue, self)

使用场景：当你需要将 Python 列表插入到 PostgreSQL 的 ARRAY 类型字段时，这个类可以自动处理类型转换。

运维笔记

DBeaver 工作空间

查找 DBeaver 工作空间路径：

1	Help > About DBeaver > Installation Details > Configuration

Ubuntu 默认路径：

1	/home/user/.local/share/.DBeaverData/workspace6/General/.dbeaver

DBeaver 密码解密

恢复 DBeaver 中保存的数据库密码：

openssl aes-128-cbc -d \
    -K babb4a9f774ab853c96c2d653dfe544a \
    -iv 00000000000000000000000000000000 \
    -in credentials-config.json | dd bs=1 skip=16 2>/dev/null

AWS RDS 连接问题

使用 Drizzle ORM 连接 AWS RDS 时可能遇到的问题：

参考解决方案：

Confluence 升级相关

查询和更新 Confluence 配置：

-- 查询配置
SELECT BANDANAVALUE
FROM BANDANA
WHERE BANDANACONTEXT = '_GLOBAL'
AND BANDANAKEY = 'atlassian.confluence.settings';

-- 更新备份路径
UPDATE BANDANA
SET BANDANAVALUE = REPLACE(
    BANDANAVALUE,
    '<backupPath>/var/atlassian/application-data/confluence/backups</backupPath>',
    '<backupPath>/var/atlassian/application-data/shared-home/backups</backupPath>'
)
WHERE BANDANACONTEXT = '_GLOBAL'
AND BANDANAKEY = 'atlassian.confluence.settings';