常用命令记录
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;