postgreSQL及pipelineDB常用命令

ps: 以下操作均假定开发者通过apt方式安装pg, 且当前用户为postgres

启动新实例

初始化数据目录

/usr/lib/postgresql/11/bin/pg_ctl -D /path/to/pgdata initdb

加载数据目录并启动

/usr/lib/postgresql/11/bin/pg_ctl -D /path/to/pgdata start

权限操作

修改密码

alter role postgres with password 'xxx';

创建只读用户

-- 1.创建用户
CREATE USER readonly WITH ENCRYPTED PASSWORD 'qazxcdew';

-- 2..用户只读事务
alter user readonly set default_transaction_read_only=on;

-- 3.把所有库的语言的USAGE权限给到readonly
GRANT USAGE ON SCHEMA public to readonly;  

-- 4.授予select权限(这句要进入具体数据库操作在哪个db环境执行就授予那个db的权)
grant select on all tables in schema public to readonly;

表、视图操作

foreign table

 /*
 创建一个包含用户操作记录的foreign table, 分别包含用户名(id), 操作时间(time), 接口名(method)
 foreign table中的数据无法直接查看, 必须创建相应的view
 */
 create foreign table log_foreign_table(
        id text,
        time timestamptz,
        method text
        )
server pipelinedb;

continuous view

 -- 创建一个view, 计算每个id每小时各接口的访问次数, 设置过期时间为1天
 create view method_stats_view with (ttl = '1 day', ttl_column = ‘hour') as
 	select
 		id,
 		hour(time) as hour,
 		method,
 		count(*) as num
 	from log_foreign_table
 	group by 1, 2, 3;

-- 修改ttl为2天
select pipelinedb.set_ttl('method_stats', '2 day', 'hour');

-- 中止/启动continuous view, 有时需要执行中止&启动操作才能让重设的ttl生效
select pipelinedb.deactivate('method_stats');
select pipelinedb.activate('method_stats');

索引

 -- 为method_stats的id字段创建索引, 默认为b-tree索引, 升序
 create index method_stats_view_id_index on method_stats_view(id); 

数据迁移

复制表结构

-- 复制索引和约束
CREATE TABLE method_stats_table (LIKE method_stats_view INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

-- 只复制表结构,约束和索引需要手动构建
CREATE TABLE method_stats_table (LIKE method_stats_view INCLUDING DEFAULTS EXCLUDING CONSTRAINTS EXCLUDING INDEXES);

查询结果导出为csv

\copy (select * from method_stats_view where hour >= hour(now()) to '/source/path/data_today.csv' with csv

csv导入到table

\copy method_stats_table from '/source/path/data_today.csv' with (format csv)

sql结果写入到table

insert into method_stats_table select id, hour, method, num from method_stats_view;

sql结果写入到view

insert into method_stats_view_mrel select id, hour, method, num, nextval(method_stats_view) from method_stats_table;