1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 显示最大连接数
SHOW max_connections;

-- 查总连接数
SELECT count(*) FROM pg_stat_activity;

-- 加载配置（部分参数能热加载）
SELECT pg_reload_conf();

-- 查看配置文件地址
SHOW config_file;

1
2
3
4
5
6
7
8
9

-- 在线调（部分参数）
-- 注意：max_connections 不能 SELECT pg_reload_conf() 生效，必须重启

-- 持久化
vim /var/lib/postgresql/data/postgresql.conf
# max_connections = 10000   # 默认 100 改成 10000

# 重启
docker restart timescaledb

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

psql -U postgres
# 默认连 postgres 库

# 切到指定库
\c videohub
# You are now connected to database "videohub" as user "postgres".

# 查所有库
\l+

# 查当前库所有表
\dt

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

postgres=# \l+
                                                                       List of databases
       Name        |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |   Size   | Tablespace |                Description
-------------------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 plsdb             | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 8401 kB  | pg_default |
 postgis_25_sample | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 11 MB    | pg_default |
 postgres          | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 8553 kB  | pg_default | default administrative connection database
 template0         | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 8401 kB  | pg_default | unmodifiable empty database
 template1         | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 8401 kB  | pg_default | unmodifiable empty database
 videohub          | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 10 MB    | pg_default |

1
2
3
4
5
6

-- 1. 创建扩展（一次）
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 2. 验证
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
SELECT extversion FROM pg_extension WHERE extname = 'pg_stat_statements';

1
2
3
4
5
6
7
8

# /var/lib/postgresql/data/postgresql.conf

# 768 行：把 pg_stat_statements 加入 shared_preload_libraries
shared_preload_libraries = 'timescaledb,pg_stat_statements'

# 642 行：默认 none，建议 all
track_functions = all
pg_stat_statements.track = all

1
2
3
4
5
6
7
8
9

SELECT
    query,
    calls,
    total_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_total, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 50;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    COALESCE(d.description, '无注释') AS table_comment
FROM
    pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    LEFT JOIN pg_description d
        ON c.oid = d.objoid AND d.objsubid = 0
WHERE
    c.relkind = 'r'  -- 普通用户表
    AND n.nspname = 'mine_dev'  -- 替换为实际 schema
    AND NOT EXISTS (
        SELECT 1 FROM pg_constraint con
        WHERE con.conrelid = c.oid AND con.contype = 'p'  -- p = PRIMARY KEY
    )
ORDER BY c.relname;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT
    'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" ADD PRIMARY KEY ("id");'
    AS add_pk_sql
FROM
    pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
    c.relkind = 'r'
    AND n.nspname = 'mine_dev'
    AND NOT EXISTS (
        SELECT 1 FROM pg_constraint con
        WHERE con.conrelid = c.oid AND con.contype = 'p'
    )
    AND EXISTS (
        -- 确保表有 id 字段
        SELECT 1 FROM pg_attribute att
        WHERE att.attrelid = c.oid
          AND att.attname = 'id'
          AND att.attnum > 0
          AND NOT att.attisdropped
    )
ORDER BY n.nspname, c.relname;

1
2
3
4
5

add_pk_sql
-----------------------------------------------------------------
ALTER TABLE "mine_dev"."dps_risk_task" ADD PRIMARY KEY ("id");
ALTER TABLE "mine_dev"."dps_risk_object" ADD PRIMARY KEY ("id");
...

1

TRUNCATE TABLE table1, table2, table3;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 查所有 schema
SELECT nspname FROM pg_namespace;

-- 跨 schema 查询
SELECT * FROM mine_dev.risk_task
UNION ALL
SELECT * FROM other_dev.risk_task;

-- 用 schema 隔离多租户
CREATE SCHEMA tenant_company_a;
CREATE SCHEMA tenant_company_b;

1
2

原库：industry_iot_dev   postgres/****
目标库：internal.example.com:5432/postgres/mine_dev

1
2
3
4
5
6
7
8
9

[DTF] 106> dps_risk_task_record_history: Create index "uk_task_dup"
[ERR] 343> CREATE UNIQUE INDEX "uk_task_dup" ON "mine_dev"."dps_risk_task_record_history" USING btree (
  "control_id",
  "task_begin_date",
  "task_end_date"
)
[ERR] 343> ERROR:  relation "uk_task_dup" already exists

[DTF] Process terminated

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# 安装（Debian/Ubuntu）
apt install pgloader

# 配置文件
cat > mysql_to_pg.load << 'EOF'
LOAD DATABASE
    FROM      mysql://root:****@internal.example.com:3306/industry_iot_dev
    INTO postgresql://postgres:****@internal.example.com:5432/mine_dev

WITH include drop, create tables, no truncate, create indexes,
     reset sequences, foreign keys

ALTER schema 'industry_iot_dev' rename to 'mine_dev'
;
EOF

# 执行
pgloader mysql_to_pg.load

1
2
3

1. 增加"租户管理"菜单
2. 域名区分租户（如 a.example.com → schema_a，b.example.com → schema_b）
3. 开关控制是否多租户（兼容单体部署）

1
2
3
4

- MySQL → PostgreSQL 17
- 去除 InfluxDB，时序数据存到 TimescaleDB
- 多租户数据隔离：每个租户一个 schema
- 动态路由：根据租户 ID 切换 schema

1
2
3
4
5
6
7

@Component
public class SchemaContext {
    private static final ThreadLocal<String> CURRENT_SCHEMA = new ThreadLocal<>();
    public static void set(String schema) { CURRENT_SCHEMA.set(schema); }
    public static String get() { return CURRENT_SCHEMA.get(); }
    public static void clear() { CURRENT_SCHEMA.remove(); }
}

1
2
3
4
5
6
7
8

@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource() {
        // AbstractRoutingDataSource + schema 路由
        return new TenantRoutingDataSource();
    }
}

1
2
3
4

<select id="selectRiskTask" resultType="RiskTask">
    SELECT * FROM "${schema}".risk_task WHERE id = #{id}
    <!-- schema 来自 SchemaContext.get() -->
</select>

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 1. 启用扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 2. 转为 hypertable
SELECT create_hypertable('sensor_data', 'time');

-- 3. 时序插入（每 30 秒一条）
INSERT INTO sensor_data (time, sensor_id, value)
VALUES (NOW(), 'sensor_001', 23.5);

-- 4. 查过去 1 小时
SELECT time_bucket('5 minutes', time) AS bucket,
       AVG(value) AS avg_value
FROM sensor_data
WHERE time > NOW() - INTERVAL '1 hour'
GROUP BY bucket
ORDER BY bucket;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- 1. 启用（PG 16+ 官方扩展）
CREATE EXTENSION vector;

-- 2. 存向量
CREATE TABLE docs (
    id BIGSERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)        -- OpenAI text-embedding-3 维度
);

-- 3. 查最相似（余弦距离）
SELECT id, content
FROM docs
ORDER BY embedding <=> '[0.1, 0.2, ...]'  -- '<=>' = cosine distance
LIMIT 5;