PGSQL的TimescaleDB

1、查询数据库的表

SELECT table_name FROM information_schema.tables
WHERE table_schema = '模式名称' and "table_name" like 'data_010521118%'

2、创建超表

先创建表：CREATE TABLE  表名 （time TIMESTAMPTZ NOT NULL, 字段。。。）

通过 time 进行分区将普通表转换为超表：SELECT  create_hypertable('表名'，'time')

3、移除旧的数据保存策略

SELECT remove_retention_policy('表名', true);

4、新建数据库保留策略（day、weeks、months、years）

SELECT add_retention_policy('表名', INTERVAL '3 weeks');

5、查看表的保留策略

SELECT * FROM timescaledb_information.jobs WHERE hypertable_schema = '模式' AND hypertable_name = '表名';

6、查看超表的块列表

SELECT show_chunks('表名')

7、手动删除块数据

SELECT drop_chunks('模式.表名', INTERVAL '3 months');

8、查看数据库的大小

SELECT pg_size_pretty(pg_database_size('mom')) AS schema_size;

select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;

9、循环删除超表数据，重新设置表的保存策略（'内'用''）

DO $$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'data' AND "table_name" like 'data_0105211161%' LOOP
        EXECUTE format('SELECT drop_chunks( ''%I'' , INTERVAL ''3 weeks'');SELECT remove_retention_policy(''%I'', true);SELECT add_retention_policy(''%I'', INTERVAL ''3 weeks'');', row.table_name, row.table_name, row.table_name);
    END LOOP;
END $$;

10、查看数据库各个模式的大小

SELECT
    schema_name,
    pg_size_pretty(sum(table_size)::bigint) AS schema_size
FROM (
    SELECT
        table_schema AS schema_name,
        pg_total_relation_size(table_schema || '.' || table_name) AS table_size
    FROM
        information_schema.tables
    WHERE
        table_schema NOT IN ('pg_catalog', 'information_schema')
) AS schema_tables
GROUP BY
    schema_name
ORDER BY
    sum(table_size) DESC;