PostgreSql 快速实践

从Postgresql 官方网站下载repo源

https://www.postgresql.org/download/

建立YUM缓存并安装数据库

# yum makecache
# yum install postgresql-server

初始化数据库、创建数据库文件，适合Postgresql 10.0以上版本

# postgresql-setup --initdb

全库备份

pg_dump dbname > dbname.sql

不备份所有者信息

pg_dump --no-owner dbname > dbname.sql

恢复数据库

pgsql -c 'create database dbname'
pgsql -d dbname < dbname.sql

仅备份表结构信息

pg_dump -s dbname -t tbl_name > tbl_name.sql
## 从导出的表结构中创建表格，当前用户需要具有相关权限，否则应该带上-U参数
psql -d dbname < tbl_name.sql

select relname,reltuples from pg_class where relkind='r' order by reltuples desc;

以下代码查询数据库log每个表格记录数

# psql -c "select relname,reltuples from pg_class where relkind='r' order by reltuples desc limit 20;" -d log

## 为环境变量PGPASSWORD设置值
PGPASSWORD='user1_pass'
psql -U user1 -d dbname

1. 通过SQL语句 以下代码查看c_log表格下的字段名称及数据类型

  select column_name,data_type from infomation_schema.columns where table_schema='public' and table_name='c_log';

2. 数字列表项目通过pgsql命令行自带命令 \d c_log

comment on table t2 is '表格2';

\d+

可以通过命令编辑函数及视图定义

-- 注意编辑完成后输入分号";"并回车提交，有些视图如果无法修改，则可以通过修改视图、函数名称新建一个
-- 然后删除旧的定义
-- vi 全局替换命令是: %s/o_str/n_str/
\ev view_name
\ef func_name

## 物化视图
## 物化视图是保存有实际数据的视图
## 不会自动更新、适合更新不频繁的数据
## 创建物化视图
create materialized view view_name as select_stmt;
## 创建唯一性索引，并发更新时需要此索引
create unique index index_name on view_name(field);
## 刷新索引
refresh materialized view concurrently view_name;
## 修改名称
alter materialized view name rename to new_name;

-- PostgreSQL 查看剩余连接数
select max_conn-now_conn as resi_conn from (select setting::int8 as max_conn,(select count(*) from pg_stat_activity) as now_conn from pg_settings where name = 'max_connections') t;

对表或视图授权

grant select on view_name to role_name;
## 查看定义
\d+ view_name

-- PostgreSQL 用户和组
create role role_stk; #这是一个角色，不能登录
create user user_stk; #这是一个用户，可以登录
grant select on tbl_set1 to role_stk;
grant select on tbl_set2 to role_stk;
grant select on tbl_set3 to role_stk; #将一系列权限授予角色
grant role_stk to user_stk; #为这个用户添加角色，这个用户就具有角色的权限

## 模式 Schema
## 创建模式
create schema s_name;
## 授权
grant usage on s_name to ovwx;
## 显示模式
\dn+
## 查询模式下的对象
\dt s_name.* --查询表
\dv s_name.* --查询视图

select md5(random()::text);

– 在postgres中 选择随机的几条数据出来，sql: select * from TABLE_NAME order by random() limit N;

-- Extract 解析时间与日期数据
select Extract(field from timestamp)::int;
其中，field可以是:
1) min: 时间中的分钟数
2) sec: 时间中的秒数
3) hour: 时间中的小时数
4) year: 年数
5) doy: 一年中的天数
6) dow: 一周中的天数
7) day: 一月中的天数
8) ....不常用的忽略

select date_trunc('month',now()); --月初
select date_trunc('month',now()+'1 months')+'-1 days'; --月末
select date_trunc('week',now()); --周初

-- PostgreSQL percentile_cont窗口函数
select percentile_cont(frac) within group (order by column [desc]) from table group by ....
说明: percentile_cont(frac) within group (order by ...) 是一种整体表达法，frac为百分位，order by 是需要处理的数据列，后面group by为分组依据

下列命令将t1/t2的所有行组成一个新的记录集

select * from t1 union select * from t2;

将两个记录集按照特定条件联接为一个

select t1.*,t2.* from t1 join t2 on ?cond?

## 运算符可以重载（即参数类型不相同）
## 自定义运算符优先级最低，要让它优先时需要加括号
create operator // (procedure=func,leftarg=float,rightarg=float);

## 分区表可以将大表转化为若干小表，同时提升数据库性能。

create table tb_name(
    ...
    cname type,
    other col_spec
    ...
) partition by range(cname);

表示创建基于列<cname>的范围分区表，范围格式与相应列类型一致。

create table tb_name_s_ra_1 partition of tb_name for values from (start) to (end);
create table tb_name_s_ra_2 partition of tb_name for values from (start2) to (end2);
create table tb_name_s_ra_3 partition of tb_name for values from (start3) to (end3);

注意划分范围是包括start，不包括end的。

按照列值分区，适用于列值为离散的情况。
-- Master table
create table tb_name(
    ...
    cname type,
    other col_spec
    ...
) partition by list(cname);

-- Slave table
create table tb_name_s_va_1 partition of tb_name for values in (a,b,c);

用于提高复杂查询的可读性
with tb_name as (
  select col_spec,.... from .....
),
tb2 as (
 select .... from ....
)
select col1,col2,col3 from tb1, tb2 where ....;

# 定义交叉表查询列(b列)为: s_col
# 定义交叉表分组列(a列): g_col
# 定义数据列: d_col
select g_col,
 sum(case when s_col = 'a' then d_col else 0 end) as a,
 sum(case when s_col = 'b' then d_col else 0 end) as b,
 sum(case when s_col = 'c' then d_col else 0 end) as c
from s_tbl group by g_col;

这会产生一个g_col值为index, column=[a,b,c]的交叉表