Postgre 日常操作

words: 1.8k    views:    time: 8min
日常随笔
postgre

PostgreSQL中文手册：http://www.postgres.cn/docs/13/

简单记录了一些pg的日常使用命令，比较琐碎

1. 创建数据库

1.获取当前pg的data目录

1
pgdata=`su - postgres -c "echo $PGDATA"`

2.检查当前pg的home目录

1
pghome=`su - postgres -c "echo $PGHOME"`

3.检测当前pg状态

1
su - postgres -c "$pghome/bin/pg_ctl status -w -D $pgdata|grep 'server is running'|wc -l"

4.检查库实例是否已经存在

1
postgres psql -d postgres -U postgres -c "select count(1) from pg_database where datname='test';

5.检查用户是否已经存在

1
postgres psql -d postgres -U postgres -c "select count(1) from pg_roles where rolname='test';

6.创建用户

1
postgres psql -d postgres -U postgres -c "create user test password 'test';"

7.创建库实例

1
postgres psql -d postgres -U postgres -c "create database test owner test;"

8.用户赋权

1
postgres psql -d postgres -U postgres -c "grant postgres to test;"

2. 删除数据库

1
drop database [database_name];

当前用户需要有删除权限，如果有连接在使用数据库则需要先关闭：

1
select pg_terminate_backend(pid) from pg_stat_activity where datname = '[database_name]';

3. 登录执行

登录postgres执行sql文件

1
postgres psql -d postgres -U postgres -f test.sql

常用操作：

\? ：psql命令列表
\conninfo ：当前连接信息
\c [database_name] ：切换数据库
\password [user_name] ：修改用户密码（缺省则默认当前用户）
\q ：退出
\l ：查看所有数据库
\du ：查看所有用户
\df ：查看所有存储过程（函数）
\df [func_name] ：查看指定函数
\di ：查看所有索引
\dt ：查看所有表（\d 查看表和序列）
\d [table_name] ：查看指定表结构
\e ：以文本方式编辑sql，关闭时执行

常用sql：

查看版本 select version();
查看当前数据库 select current_database();
查看用户 select * from pg_roles;
查看权限 select * from information_schema.table_privileges where grantee='cc';
查看用户名密码 select rolname,rolpassword from pg_authid; select usename,passwd from pg_shadow;
创建用户并设置密码 CREATE USER shanhm WITH PASSWORD 'shanhm';
创建数据库并指定用户 CREATE DATABASE shanhm OWNER shanhm;
数据库赋权 GRANT ALL PRIVILEGES ON DATABASE shanhm TO shanhm;

4. 备份恢复

  • 备份：

备份数据库（-s：仅结构；-a：仅数据）：

1
2
3
pg_dump -h 192.168.141.13 -p 5432 -U postgres $dbname > db.sql

pg_dump "host=192.168.141.13 port=5432 user=postgres password=postgres dbname=test" -f db.sql

备份指定表：

1
2
3
pg_dump -h 192.168.141.13 -p 5432 -U postgres -d $dbname -t $tbname> tb.sql

pg_dump "host=192.168.141.13 port=5432 user=postgres password=postgres dbname=test" -t $tbname -f tb.sql

以二进制格式备份：

1
2
3
4
pg_dump "host=192.168.141.13 port=5432 user=postgres password=postgres dbname=test" -F c -f db.dump

# 恢复
pg_restore -h 192.168.141.13 -U postgres -d $dbname db.dump

5. sql

  • Array合并

经常有这样的场景：希望将表test1中某数组字段中的值合并到表test2中对应的数组字段，并要求去重

1
2
3
4
5
6
7
8
9
10
11
create table test1(
  id bigserial,
  type1 varchar(64),
  models1 varchar(64)[]
); 

create table test2(
  id bigserial,
  type2 varchar(64),
  models2 varchar(64)[]
);

这种场景可能有两种情况，一是以被合并的test2数据为准，只更新test2中已经存在的数据，则可以使用update：

1
2
3
update test2 set models2 = array(select k.mk from (select unnest(models2) as mk union select unnest(mm.models) as mk) k) 
from (select type1, array(select unnest(models1)) models from test1) mm
where type2 = mm.type1

有时也会要求以test1为准，即常见的如果存在则更新，没有则新增，那么此时可以通过conflict来实现，但是要先建立唯一索引：

1
2
3
4
5
6
create unique index unique_type on test2 using btree(type2);

insert into test2(type2, models2)
select type1, array(select distinct unnest(models1)) from test1 
on conflict(type2) do update set
models2 = array(select k.mk from (select unnest(test2.models2) as mk union select unnest(excluded.models2) as mk) k)

如果细心的话，会发现insert时的查询比update中多了一个distinct去重，这是因为虽然union会进行去重，但是如果insert是新增的话将不会执行union，所以要查询时就做好去重操作

  • array_agg/string_agg

array_agg经常与group by一起用，将分组结果以数组的形式给出，但是如果原来的字段已经是数组，则比较麻烦，array_agg要求原来的数组字段元素个数要一致才行

还是举一个场景：通过关键词定义模型对文本数据进行匹配，需要能满足类似(a or b) and (c or d) and not(e or f)的能力，比如定义一个模型好孩子：（品格好 或 学习好）并且 （爱干净 或 懂礼貌） 但是不能 （调皮 或 捣蛋）

这里可以定义几个概念：模型 -> 模型定义 -> 词组 -> 关键词
一个模型可以有若干个模型定义；
一个模型定义可以有若干个词组；
一个词组则包括若干个关键词；

然后便可以如下初始化表结构和数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
create table model(
  id bigserial,
  model varchar(64),
  model_define varchar(64),
  keyword_group varchar(64),
  negative int2 default 0
);

create table model_keyword(
  id bigserial,
  keyword_group varchar(64),
  keyword varchar(64)
);

insert into model(model, model_define, keyword_group, negative) values ('好孩子', '好孩子-定义1', '好孩子-定义1-词组1', 0);
insert into model(model, model_define, keyword_group, negative) values ('好孩子', '好孩子-定义1', '好孩子-定义1-词组2', 0);
insert into model(model, model_define, keyword_group, negative) values ('好孩子', '好孩子-定义1', '好孩子-定义1-词组3', 1);
insert into model(model, model_define, keyword_group, negative) values ('坏孩子', '坏孩子-定义1', '坏孩子-定义1-词组1', 0);

insert into model_keyword(keyword_group, keyword) values ('好孩子-定义1-词组1', '品格好');
insert into model_keyword(keyword_group, keyword) values ('好孩子-定义1-词组1', '成绩好');
insert into model_keyword(keyword_group, keyword) values ('好孩子-定义1-词组2', '爱干净');
insert into model_keyword(keyword_group, keyword) values ('好孩子-定义1-词组2', '懂礼貌');
insert into model_keyword(keyword_group, keyword) values ('好孩子-定义1-词组3', '调皮');
insert into model_keyword(keyword_group, keyword) values ('好孩子-定义1-词组3', '捣蛋');
insert into model_keyword(keyword_group, keyword) values ('坏孩子-定义1-词组1', '爱打架');

最后在查询组织数据时，先以词组进行分组，使用string_agg将单词以|进行拼接；然后再以模型定义进行分组，使用array_agg来组织词组；并使用string_agg根据negative的值将行转为列，这样最终每行结果就代表一个模型定义，如下：

1
2
3
4
5
6
7
8
9
10
select a.model, a.model_define, 
       string_agg((case a.negative when 0 then keywords::varchar end),'')::varchar[] as keywords,
       string_agg((case a.negative when 1 then keywords::varchar end),'')::varchar[] as negtaiveKeywords
  from(select t.model, t.model_define, array_agg(t.keywords) keywords, t.negative 
         from(select m.model, m.model_define, m.keyword_group, m.negative, string_agg(mk.keyword, '|') keywords
                from model m, model_keyword mk 
               where m.keyword_group = mk.keyword_group 
               group by m.model, m.model_define, m.keyword_group, m.negative
       ) t group by t.model, t.model_define, t.negative 													
) a group by a.model, a.model_define
  • json_agg/json_build_object

json_build_object可以将查询的结果直接以json的方式给出来

1
2
3
4
5
6
create table test3(
  id bigserial,
  user_name varchar(64),
  user_age int2,
	user_sex varchar(32)
);

比如：

1
2
3
4
5
6
select coalesce(json_agg(json_build_object(
'userName', user_name, 
'userAge', user_age, 
'userSex', user_sex																	 
)),'[]') 
from test3 t

或者分页查询：

1
2
3
4
5
6
7
8
9
10
select json_build_object(
'list', (select coalesce(json_agg(row_to_json(t) ),'[]') 
           from (select id, 
                        user_name "userName", 
                        user_age "userAge",
                        user_sex "userSex"
                   from test3 limit 10 offset 0
                ) t
        ),
'count', (select count(1) from test3))
  • 全文检索

有时我们希望当任意列匹配输入条件时都返回，这时再按列名一个个去判断显然很麻烦，于是可以利用全文检索的功能，如下所示。但是这里没能模糊匹配，后续再补充…

1
2
3
4
5
alter table test_user add column doc tsvector;
create index index_doc on test_user using gin(doc);

update test_user set doc = to_tsvector('simple', row_to_json(row(name, sex, age, qq, wx, mobile)));
select * from test_user where doc @@ to_tsquery('simple', 'shanhm');


参考：

  1. https://dbaplus.cn/news-11-3235-1.html