PostgreSQL 数据库技术详解

1-- 示例：MVCC 工作原理
2BEGIN;
3UPDATE users SET name = '新名字' WHERE id = 1;
4-- 此时其他事务仍能看到旧数据
5COMMIT;
6-- 提交后，新数据对所有事务可见
7
8

1-- 整数类型
2CREATE TABLE numbers (
3    id SERIAL PRIMARY KEY,           -- 自增整数
4    small_num SMALLINT,              -- 2 字节整数 (-32768 到 32767)
5    normal_num INTEGER,              -- 4 字节整数
6    big_num BIGINT,                  -- 8 字节整数
7    decimal_num DECIMAL(10,2),       -- 精确小数
8    float_num REAL,                  -- 单精度浮点数
9    double_num DOUBLE PRECISION      -- 双精度浮点数
10);
11
12

1-- 字符类型示例
2CREATE TABLE text_examples (
3    id SERIAL PRIMARY KEY,
4    fixed_char CHAR(10),             -- 固定长度字符
5    var_char VARCHAR(255),           -- 可变长度字符
6    unlimited_text TEXT,             -- 无限制文本
7    name CITEXT                      -- 大小写不敏感文本
8);
9
10-- 插入数据
11INSERT INTO text_examples (fixed_char, var_char, unlimited_text, name)
12VALUES ('Hello', 'World', 'This is a long text...', 'JOHN DOE');
13
14

1-- 日期时间类型
2CREATE TABLE datetime_examples (
3    id SERIAL PRIMARY KEY,
4    birth_date DATE,                 -- 日期
5    work_time TIME,                  -- 时间
6    created_at TIMESTAMP,            -- 时间戳
7    updated_at TIMESTAMPTZ,          -- 带时区的时间戳
8    duration INTERVAL                -- 时间间隔
9);
10
11-- 插入数据
12INSERT INTO datetime_examples (birth_date, work_time, created_at, updated_at, duration)
13VALUES ('1990-01-01', '09:30:00', '2025-10-03 10:30:00', 
14        '2025-10-03 10:30:00+08', '1 day 2 hours 30 minutes');
15
16

1-- 数组类型示例
2CREATE TABLE array_examples (
3    id SERIAL PRIMARY KEY,
4    numbers INTEGER[],               -- 整数数组
5    names TEXT[],                    -- 文本数组
6    matrix INTEGER[][]               -- 二维数组
7);
8
9-- 插入数组数据
10INSERT INTO array_examples (numbers, names, matrix)
11VALUES ('{1,2,3,4,5}', '{"张三","李四","王五"}', '{{1,2},{3,4}}');
12
13-- 查询数组
14SELECT * FROM array_examples WHERE 2 = ANY(numbers);
15SELECT names[1] FROM array_examples;  -- 获取第一个名字
16
17

1-- JSON 类型示例
2CREATE TABLE json_examples (
3    id SERIAL PRIMARY KEY,
4    user_info JSON,                  -- JSON 类型
5    user_data JSONB                  -- 二进制 JSON 类型
6);
7
8-- 插入 JSON 数据
9INSERT INTO json_examples (user_info, user_data)
10VALUES ('{"name": "张三", "age": 25, "city": "北京"}',
11        '{"name": "李四", "age": 30, "hobbies": ["读书", "游泳"]}');
12
13-- JSON 查询
14SELECT user_data->>'name' FROM json_examples;  -- 获取名字
15SELECT user_data->'hobbies' FROM json_examples;  -- 获取爱好数组
16SELECT * FROM json_examples WHERE user_data @> '{"age": 30}';  -- 查询年龄为 30 的用户
17
18

1-- 创建 B-Tree 索引
2CREATE INDEX idx_users_email ON users(email);
3CREATE INDEX idx_users_name_age ON users(name, age);  -- 复合索引
4
5

1-- 创建 Hash 索引（仅支持等值查询）
2CREATE INDEX idx_users_id_hash ON users USING hash(id);
3
4

1-- 用于数组和 JSON 数据
2CREATE INDEX idx_users_tags_gin ON users USING gin(tags);
3CREATE INDEX idx_users_data_gin ON users USING gin(user_data);
4
5

1-- 用于地理空间数据
2CREATE INDEX idx_locations_gist ON locations USING gist(coordinates);
3
4

1-- 用于大表的范围查询
2CREATE INDEX idx_logs_brin ON logs USING brin(created_at);
3
4

1-- 设置事务隔离级别
2BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
3-- 执行操作
4COMMIT;
5
6-- 隔离级别说明
7-- READ UNCOMMITTED: 读取未提交数据（PostgreSQL 中实际为 READ COMMITTED）
8-- READ COMMITTED: 读取已提交数据（默认）
9-- REPEATABLE READ: 可重复读取
10-- SERIALIZABLE: 串行化
11
12

1-- 表级锁
2LOCK TABLE users IN SHARE MODE;     -- 共享锁
3LOCK TABLE users IN EXCLUSIVE MODE; -- 排他锁
4
5-- 行级锁（自动）
6UPDATE users SET name = '新名字' WHERE id = 1;  -- 自动加行级排他锁
7
8-- 查看锁信息
9SELECT * FROM pg_locks WHERE relation = 'users'::regclass;
10
11

1-- 会话 1
2BEGIN;
3UPDATE users SET balance = balance - 100 WHERE id = 1;
4-- 此时事务未提交
5
6-- 会话 2（同时执行）
7SELECT balance FROM users WHERE id = 1;  -- 仍看到旧值
8
9-- 会话 1
10COMMIT;  -- 提交后，会话 2 的下次查询将看到新值
11
12

1# 检查 PostgreSQL 服务状态
2sc query postgresql-x64-16
3
4# 连接到数据库
5psql -U postgres -h localhost -p 5432
6
7

1# 更新包列表
2sudo apt update
3
4# 安装 PostgreSQL
5sudo apt install postgresql postgresql-contrib
6
7# 启动服务
8sudo systemctl start postgresql
9sudo systemctl enable postgresql
10
11# 切换到 postgres 用户
12sudo -u postgres psql
13
14

1# 安装 PostgreSQL
2sudo yum install postgresql-server postgresql-contrib
3
4# 初始化数据库
5sudo postgresql-setup initdb
6
7# 启动服务
8sudo systemctl start postgresql
9sudo systemctl enable postgresql
10
11

1# 连接设置
2listen_addresses = '*'          # 监听所有地址
3port = 5432                     # 端口号
4max_connections = 100           # 最大连接数
5
6# 内存设置
7shared_buffers = 256MB          # 共享缓冲区
8work_mem = 4MB                  # 工作内存
9maintenance_work_mem = 64MB     # 维护工作内存
10
11# 日志设置
12log_destination = 'stderr'      # 日志目标
13logging_collector = on          # 启用日志收集
14log_directory = 'log'           # 日志目录
15log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
16log_min_duration_statement = 1000  # 记录慢查询（毫秒）
17
18# 性能设置
19random_page_cost = 1.1          # 随机页面成本
20effective_cache_size = 1GB      # 有效缓存大小
21
22

1# 本地连接
2local   all             all                                     trust
3# IPv4 本地连接
4host    all             all             127.0.0.1/32            md5
5# IPv6 本地连接
6host    all             all             ::1/128                 md5
7# 网络连接
8host    all             all             0.0.0.0/0               md5
9
10

1-- 基本查询分析
2EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
3
4-- 详细分析（包含实际执行时间）
5EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
6SELECT u.name, p.title 
7FROM users u 
8JOIN posts p ON u.id = p.user_id 
9WHERE u.active = true;
10
11

1-- 创建合适的索引
2CREATE INDEX CONCURRENTLY idx_users_email_active 
3ON users(email) WHERE active = true;
4
5-- 复合索引优化
6CREATE INDEX idx_posts_user_status_date 
7ON posts(user_id, status, created_at);
8
9-- 部分索引
10CREATE INDEX idx_recent_posts 
11ON posts(created_at) WHERE created_at > '2025-01-01';
12
13

1-- 优化前：使用子查询
2SELECT * FROM users WHERE id IN (
3    SELECT user_id FROM orders WHERE total > 1000
4);
5
6-- 优化后：使用 JOIN
7SELECT DISTINCT u.* FROM users u
8JOIN orders o ON u.id = o.user_id
9WHERE o.total > 1000;
10
11

1# 根据系统内存调整
2shared_buffers = 25% of RAM        # 共享缓冲区
3work_mem = 4MB                     # 工作内存
4maintenance_work_mem = 256MB       # 维护工作内存
5effective_cache_size = 75% of RAM  # 有效缓存大小
6
7

1# 随机页面成本（SSD 推荐 1.1，机械硬盘 4.0）
2random_page_cost = 1.1
3
4# 顺序页面成本
5seq_page_cost = 1.0
6
7# 检查点设置
8checkpoint_completion_target = 0.9
9wal_buffers = 16MB
10
11

1-- 启用慢查询日志
2ALTER SYSTEM SET log_min_duration_statement = 1000;
3SELECT pg_reload_conf();
4
5-- 查看当前活动连接
6SELECT * FROM pg_stat_activity WHERE state = 'active';
7
8-- 查看数据库统计信息
9SELECT * FROM pg_stat_database WHERE datname = current_database();
10
11

1-- 更新表统计信息
2ANALYZE users;
3
4-- 查看表大小
5SELECT 
6    schemaname,
7    tablename,
8    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
9FROM pg_tables 
10ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
11
12

1# 创建复制用户
2sudo -u postgres psql
3CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'password';
4
5# 配置 pg_hba.conf
6echo "host replication replicator 192.168.1.0/24 md5" >> /etc/postgresql/16/main/pg_hba.conf
7
8# 重启服务
9sudo systemctl restart postgresql
10
11

1# 停止 PostgreSQL
2sudo systemctl stop postgresql
3
4# 备份主服务器数据
5pg_basebackup -h 192.168.1.100 -D /var/lib/postgresql/16/main -U replicator -v -P -W
6
7# 配置 recovery.conf
8echo "standby_mode = 'on'" > /var/lib/postgresql/16/main/recovery.conf
9echo "primary_conninfo = 'host=192.168.1.100 port=5432 user=replicator password=password'" >> /var/lib/postgresql/16/main/recovery.conf
10
11# 启动从服务器
12sudo systemctl start postgresql
13
14

1# 备份整个数据库
2pg_dump -h localhost -U postgres -d mydb > backup.sql
3
4# 备份特定表
5pg_dump -h localhost -U postgres -d mydb -t users > users_backup.sql
6
7# 压缩备份
8pg_dump -h localhost -U postgres -d mydb | gzip > backup.sql.gz
9
10# 并行备份
11pg_dump -h localhost -U postgres -d mydb -j 4 -f backup_dir/
12
13

1# 完整备份
2pg_basebackup -h localhost -U postgres -D /backup/basebackup -Ft -z -P
3
4# 增量备份（需要 WAL 归档）
5pg_basebackup -h localhost -U postgres -D /backup/incremental -Ft -z -P --checkpoint=fast
6
7

1# 配置 WAL 归档
2echo "archive_mode = on" >> postgresql.conf
3echo "archive_command = 'cp %p /backup/wal/%f'" >> postgresql.conf
4
5# 恢复数据库
6pg_restore -h localhost -U postgres -d mydb backup.sql
7
8

1-- 安装 PostGIS
2CREATE EXTENSION postgis;
3
4-- 创建地理空间表
5CREATE TABLE locations (
6    id SERIAL PRIMARY KEY,
7    name VARCHAR(100),
8    coordinates GEOMETRY(POINT, 4326)
9);
10
11-- 插入地理数据
12INSERT INTO locations (name, coordinates) 
13VALUES ('北京', ST_GeomFromText('POINT(116.3974 39.9093)', 4326));
14
15-- 地理查询
16SELECT name, ST_Distance(coordinates, ST_GeomFromText('POINT(121.4737 31.2304)', 4326)) as distance
17FROM locations;
18
19

1-- 安装扩展
2CREATE EXTENSION pg_stat_statements;
3
4-- 查看最耗时的查询
5SELECT query, calls, total_time, mean_time, rows
6FROM pg_stat_statements 
7ORDER BY total_time DESC 
8LIMIT 10;
9
10

1-- 安装扩展
2CREATE EXTENSION "uuid-ossp";
3
4-- 使用 UUID
5CREATE TABLE users (
6    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
7    name VARCHAR(100)
8);
9
10

1-- 安装扩展
2CREATE EXTENSION hstore;
3
4-- 创建 hstore 列
5CREATE TABLE products (
6    id SERIAL PRIMARY KEY,
7    name VARCHAR(100),
8    attributes HSTORE
9);
10
11-- 插入数据
12INSERT INTO products (name, attributes) 
13VALUES ('手机', 'brand=>"苹果", color=>"黑色", price=>"6999"');
14
15-- 查询
16SELECT * FROM products WHERE attributes @> 'brand=>苹果';
17
18

1-- 创建扩展目录结构
2-- myextension/
3--   ├── myextension.control
4--   ├── myextension--1.0.sql
5--   └── Makefile
6
7-- myextension.control
8comment = 'My custom extension'
9default_version = '1.0'
10module_pathname = '$libdir/myextension'
11relocatable = true
12
13-- myextension--1.0.sql
14CREATE OR REPLACE FUNCTION my_function()
15RETURNS TEXT AS $$
16BEGIN
17    RETURN 'Hello from my extension!';
18END;
19$$ LANGUAGE plpgsql;
20
21

1-- 表名：使用复数形式，小写，下划线分隔
2CREATE TABLE user_accounts (
3    id SERIAL PRIMARY KEY,
4    email VARCHAR(255) UNIQUE NOT NULL,
5    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
6);
7
8-- 列名：小写，下划线分隔
9CREATE TABLE blog_posts (
10    post_id SERIAL PRIMARY KEY,
11    user_id INTEGER REFERENCES user_accounts(id),
12    post_title VARCHAR(255) NOT NULL,
13    post_content TEXT,
14    is_published BOOLEAN DEFAULT FALSE,
15    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
16    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
17);
18
19-- 索引名：idx_表名_列名
20CREATE INDEX idx_blog_posts_user_id ON blog_posts(user_id);
21CREATE INDEX idx_blog_posts_created_at ON blog_posts(created_at);
22
23

1-- 选择合适的数值类型
2CREATE TABLE products (
3    id SERIAL PRIMARY KEY,           -- 自增主键
4    price DECIMAL(10,2),             -- 价格使用 DECIMAL
5    quantity INTEGER,                -- 数量使用 INTEGER
6    weight REAL                      -- 重量使用 REAL
7);
8
9-- 选择合适的字符类型
10CREATE TABLE articles (
11    id SERIAL PRIMARY KEY,
12    title VARCHAR(255),              -- 标题使用 VARCHAR
13    content TEXT,                    -- 内容使用 TEXT
14    slug VARCHAR(100) UNIQUE         -- URL 标识使用 VARCHAR
15);
16
17

1-- 外键约束
2CREATE TABLE orders (
3    id SERIAL PRIMARY KEY,
4    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
5    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount > 0),
6    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'delivered'))
7);
8
9-- 唯一约束
10CREATE TABLE user_profiles (
11    user_id INTEGER PRIMARY KEY REFERENCES users(id),
12    username VARCHAR(50) UNIQUE NOT NULL,
13    email VARCHAR(255) UNIQUE NOT NULL
14);
15
16

1-- 创建应用用户
2CREATE USER app_user WITH PASSWORD 'secure_password';
3
4-- 创建只读用户
5CREATE USER readonly_user WITH PASSWORD 'readonly_password';
6
7-- 授予权限
8GRANT CONNECT ON DATABASE mydb TO app_user;
9GRANT USAGE ON SCHEMA public TO app_user;
10GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
11GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
12
13-- 只读权限
14GRANT CONNECT ON DATABASE mydb TO readonly_user;
15GRANT USAGE ON SCHEMA public TO readonly_user;
16GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
17
18

1# pg_hba.conf 安全配置
2# 本地连接使用 peer 认证
3local   all             all                                     peer
4
5# 网络连接使用 SCRAM-SHA-256
6host    all             all             127.0.0.1/32            scram-sha-256
7host    all             all             ::1/128                 scram-sha-256
8
9# 特定 IP 段使用证书认证
10hostssl all             all             10.0.0.0/8              cert
11
12

1-- 使用 LIMIT 限制结果集
2SELECT * FROM users WHERE active = true LIMIT 100;
3
4-- 使用适当的 WHERE 条件
5SELECT * FROM orders WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';
6
7-- 避免 SELECT *
8SELECT id, name, email FROM users WHERE active = true;
9
10-- 使用 EXISTS 而不是 IN（对于大表）
11SELECT * FROM users u WHERE EXISTS (
12    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
13);
14
15

1-- 为经常查询的列创建索引
2CREATE INDEX idx_users_email ON users(email);
3CREATE INDEX idx_users_active ON users(active) WHERE active = true;
4
5-- 复合索引优化
6CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at);
7
8-- 表达式索引
9CREATE INDEX idx_users_lower_email ON users(lower(email));
10
11

1-- 按时间分区
2CREATE TABLE logs (
3    id SERIAL,
4    log_level VARCHAR(20),
5    message TEXT,
6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
7) PARTITION BY RANGE (created_at);
8
9-- 创建分区
10CREATE TABLE logs_2025_01 PARTITION OF logs
11FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
12
13CREATE TABLE logs_2025_02 PARTITION OF logs
14FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
15
16

1-- 更新统计信息
2ANALYZE;
3
4-- 重建索引
5REINDEX DATABASE mydb;
6
7-- 清理死元组
8VACUUM ANALYZE;
9
10-- 完全清理
11VACUUM FULL;
12
13

1#!/bin/bash
2# monitor_postgresql.sh
3
4# 检查连接数
5CONNECTIONS=$(psql -t -c "SELECT count(*) FROM pg_stat_activity;")
6echo "当前连接数: $CONNECTIONS"
7
8# 检查数据库大小
9DB_SIZE=$(psql -t -c "SELECT pg_size_pretty(pg_database_size('mydb'));")
10echo "数据库大小: $DB_SIZE"
11
12# 检查慢查询
13SLOW_QUERIES=$(psql -t -c "SELECT count(*) FROM pg_stat_statements WHERE mean_time > 1000;")
14echo "慢查询数量: $SLOW_QUERIES"
15
16