PostgreSQL 数据库操作 #

┌─────────────────────────────────────────────────────────────┐
│                    PostgreSQL 架构                           │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│   PostgreSQL 服务器                                         │
│   ├── postgres (系统数据库)                                 │
│   ├── template0 (原始模板)                                  │
│   ├── template1 (自定义模板)                                │
│   ├── mydb1 (用户数据库)                                    │
│   ├── mydb2 (用户数据库)                                    │
│   └── ...                                                   │
│                                                             │
│   每个数据库包含：                                           │
│   ├── 表 (Tables)                                          │
│   ├── 索引 (Indexes)                                       │
│   ├── 视图 (Views)                                         │
│   ├── 函数 (Functions)                                     │
│   ├── 序列 (Sequences)                                     │
│   └── 其他对象...                                           │
│                                                             │
└─────────────────────────────────────────────────────────────┘

CREATE DATABASE database_name
    [ WITH ]
    [ OWNER = user_name ]
    [ TEMPLATE = template_name ]
    [ ENCODING = encoding_name ]
    [ LC_COLLATE = collate_name ]
    [ LC_CTYPE = ctype_name ]
    [ TABLESPACE = tablespace_name ]
    [ CONNECTION LIMIT = limit ];

-- 创建简单数据库
CREATE DATABASE myapp;

-- 创建带参数的数据库
CREATE DATABASE myapp
    WITH 
    OWNER = myuser
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    CONNECTION LIMIT = 100;

┌─────────────────┬─────────────────────────────────────────────┐
│ 参数            │ 说明                                        │
├─────────────────┼─────────────────────────────────────────────┤
│ OWNER           │ 数据库所有者，默认当前用户                  │
│ TEMPLATE        │ 模板数据库，默认 template1                  │
│ ENCODING        │ 字符编码，如 UTF8, SQL_ASCII               │
│ LC_COLLATE      │ 字符串排序规则                              │
│ LC_CTYPE        │ 字符分类规则                                │
│ TABLESPACE      │ 表空间，默认 pg_default                     │
│ CONNECTION LIMIT│ 最大连接数，-1 表示无限制                   │
└─────────────────┴─────────────────────────────────────────────┘

-- PostgreSQL 有两个内置模板数据库
-- template0: 原始模板，不能修改
-- template1: 可自定义模板

-- 使用 template0 创建（纯净数据库）
CREATE DATABASE mydb TEMPLATE template0;

-- 使用 template1 创建（继承模板修改）
CREATE DATABASE mydb TEMPLATE template1;

-- 创建自定义模板
CREATE DATABASE my_template;
-- 在 my_template 中创建通用表、函数等
-- 然后用它作为模板
CREATE DATABASE new_project TEMPLATE my_template;

-- 创建中文数据库
CREATE DATABASE mydb_cn
    ENCODING = 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'
    LC_CTYPE = 'zh_CN.UTF-8';

-- 查看可用的编码
SELECT * FROM pg_encoding;

-- 查看可用的区域设置（Linux）
-- locale -a

-- 在 psql 中列出所有数据库
\l

-- 输出示例：
--                                                  List of databases
--    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-- -----------+----------+----------+-------------+-------------+-----------------------
--  myapp     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
--  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
--  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          + postgres=CTc/postgres
--  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          + postgres=CTc/postgres

-- 查看详细列表
\l+

-- 查看特定数据库
\l myapp

-- 查询所有数据库
SELECT 
    datname AS name,
    pg_catalog.pg_get_userbyid(datdba) AS owner,
    pg_encoding_to_char(encoding) AS encoding,
    datcollate AS collate,
    datctype AS ctype
FROM pg_database
WHERE datistemplate = false
ORDER BY datname;

-- 查看数据库大小
SELECT 
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- 输出示例：
--   datname  |  size
-- -----------+---------
--  myapp     | 15 MB
--  postgres  | 8497 kB
--  template1 | 8497 kB
--  template0 | 8497 kB

-- 查看当前连接的数据库
SELECT current_database();

-- 在 psql 中
\conninfo
-- 输出：You are connected to database "myapp" as user "postgres" on host "localhost" at port "5432".

# 连接指定数据库
psql -d myapp
psql -d myapp -U myuser -h localhost -p 5432

# 使用连接字符串
psql "postgresql://myuser:mypassword@localhost:5432/myapp"

-- 在 psql 中切换数据库
\c myapp

-- 切换数据库和用户
\c myapp myuser

-- 切换数据库、用户和主机
\c myapp myuser localhost

ALTER DATABASE database_name 
    [ WITH ] [ option [ ... ] ];

-- 可用选项
ALTER DATABASE database_name RENAME TO new_name;
ALTER DATABASE database_name OWNER TO new_owner;
ALTER DATABASE database_name SET TABLESPACE new_tablespace;
ALTER DATABASE database_name SET configuration_parameter = value;
ALTER DATABASE database_name RESET configuration_parameter;

-- 重命名数据库（需要断开所有连接）
ALTER DATABASE myapp RENAME TO myapp_v2;

-- 如果有连接，需要先断开
-- 查看连接
SELECT pid, usename, application_name, client_addr 
FROM pg_stat_activity 
WHERE datname = 'myapp';

-- 断开所有连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' AND pid <> pg_backend_pid();

-- 修改数据库所有者
ALTER DATABASE myapp OWNER TO myuser;

-- 设置数据库级别的参数
ALTER DATABASE myapp SET work_mem = '256MB';
ALTER DATABASE myapp SET log_statement = 'all';
ALTER DATABASE myapp SET timezone = 'Asia/Shanghai';

-- 重置参数
ALTER DATABASE myapp RESET work_mem;

-- 查看数据库参数
SELECT * FROM pg_db_role_setting;

DROP DATABASE [ IF EXISTS ] database_name;

-- 删除数据库
DROP DATABASE myapp;

-- 如果存在则删除
DROP DATABASE IF EXISTS myapp;

-- 注意：删除数据库会永久删除所有数据！

-- 方法1：先断开连接再删除
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' AND pid <> pg_backend_pid();

DROP DATABASE myapp;

-- 方法2：使用函数（需要创建）
CREATE OR REPLACE FUNCTION drop_database_force(dbname TEXT) 
RETURNS VOID AS $$
DECLARE
    pid INTEGER;
BEGIN
    FOR pid IN 
        SELECT pid FROM pg_stat_activity WHERE datname = dbname AND pid <> pg_backend_pid()
    LOOP
        EXECUTE 'SELECT pg_terminate_backend(' || pid || ')';
    END LOOP;
    EXECUTE 'DROP DATABASE ' || quote_ident(dbname);
END;
$$ LANGUAGE plpgsql;

-- 使用函数
SELECT drop_database_force('myapp');

-- 授予连接权限
GRANT CONNECT ON DATABASE myapp TO myuser;

-- 授予创建 schema 权限
GRANT CREATE ON DATABASE myapp TO myuser;

-- 授予所有权限
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

-- 授予给角色
GRANT ALL PRIVILEGES ON DATABASE myapp TO myrole;

-- 撤销连接权限
REVOKE CONNECT ON DATABASE myapp FROM myuser;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON DATABASE myapp FROM myuser;

-- 查看数据库权限
SELECT 
    datname,
    datacl
FROM pg_database
WHERE datname = 'myapp';

-- 解析权限
-- r = SELECT, w = UPDATE, d = DELETE, D = TRUNCATE
-- C = CREATE, c = CONNECT, T = TEMPORARY

# 主要配置参数

# 连接设置
listen_addresses = '*'        # 监听地址
port = 5432                   # 端口
max_connections = 100         # 最大连接数

# 内存设置
shared_buffers = 256MB        # 共享缓冲区
work_mem = 4MB                # 工作内存
maintenance_work_mem = 64MB   # 维护内存

# 日志设置
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_statement = 'ddl'         # none, ddl, mod, all

# 时区
timezone = 'Asia/Shanghai'

# 客户端认证配置

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# 本地连接
local   all             all                                     trust

# IPv4 连接
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.1.0/24          scram-sha-256

# IPv6 连接
host    all             all             ::1/128                 scram-sha-256

# 复制连接
host    replication     replicator      192.168.1.100/32        scram-sha-256

-- 在 psql 中重载配置
SELECT pg_reload_conf();

-- 或使用命令行
-- pg_ctl reload

-- 清理死元组，更新统计信息
VACUUM mytable;

-- 完整清理（会锁表）
VACUUM FULL mytable;

-- 分析表（更新统计信息）
ANALYZE mytable;

-- 清理并分析
VACUUM ANALYZE mytable;

-- 查看表的膨胀情况
SELECT 
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    n_dead_tup::float / NULLIF(n_live_tup, 0) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

# 使用 pg_dump 备份单个数据库
pg_dump -U postgres myapp > myapp_backup.sql

# 备份为压缩格式
pg_dump -U postgres -Fc myapp > myapp_backup.dump

# 备份所有数据库
pg_dumpall -U postgres > all_backup.sql

# 使用 pg_restore 恢复
pg_restore -U postgres -d myapp myapp_backup.dump

-- 推荐
CREATE DATABASE my_app;        -- 小写，下划线分隔
CREATE DATABASE ecommerce;     -- 简洁明了
CREATE DATABASE app_dev;       -- 环境标识
CREATE DATABASE app_test;
CREATE DATABASE app_prod;

-- 不推荐
CREATE DATABASE My_App;        -- 大小写混合
CREATE DATABASE my-app;        -- 使用连字符
CREATE DATABASE 1app;          -- 数字开头

┌─────────────────────────────────────────────────────────────┐
│                    数据库设计原则                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  1. 单一职责                                                │
│     一个数据库服务于一个应用或业务领域                      │
│                                                             │
│  2. 命名清晰                                                │
│     数据库名称应反映其用途                                  │
│                                                             │
│  3. 权限最小化                                              │
│     应用程序使用最小必要权限                                │
│                                                             │
│  4. 定期备份                                                │
│     制定备份策略，定期测试恢复                              │
│                                                             │
│  5. 监控维护                                                │
│     监控数据库大小、连接数、性能                            │
│                                                             │
└─────────────────────────────────────────────────────────────┘

基础阶段
├── 数据库操作（本文）
├── 表操作
├── 数据CRUD
└── 基础查询