【postgres】1、超详细介绍

部署

设置用户

#!/bin/bash

YELLOW="\033[33m"
COLOR_END="\033[0m"

echo -e "${YELLOW}[开始] ${FUNCNAME[@]} ${COLOR_END}"

# 设置家目录
POSTGRES_HOME_PATH="/home/postgres"
mkdir -p -m 776 $POSTGRES_HOME_PATH
chown -R postgres.postgres $POSTGRES_HOME_PATH

# 创建用户, 设置密码
useradd postgres
USER_INFO=$(id postgres)     # uid=113(postgres) gid=119(postgres) groups=119(postgres),118(ssl-cert)
USERID_PAIR=${USER_INFO%%(*} # uid=113
USERID=${USERID_PAIR:4}      # 113
POSTGRES_PASSWD="postgres"
(echo "${POSTGRES_PASSWD}" && echo "${POSTGRES_PASSWD}") | sudo passwd postgres
usermod -d $POSTGRES_HOME_PATH -u $USERID postgres

echo -e "${YELLOW}[结束] ${FUNCNAME[@]} ${COLOR_END}"

环境变量

为了方便 psql 命令的执行，可在 /etc/profile 设置一些环境变量如下，完整参考

export PGUSER=postgres
export PGDATABASE=mydb
export LANG=en_US.UTF-8
export LC_ALL=en_US.UTF-8
alias ll='ls -l'

备份恢复

pg_dump、pg_restore

-- 造数据
create table tb(id integer);
insert into tb values (1), (2),(3);

-- 开始备份
---- 其中-Fc表示使用custom格式的（只有这种格式支持压缩--有四种-F（Format）格式，分别为custom，tar，plain，directory），其中-a表示只拷贝数据（无表结构）
---- 各分区表需要单独导出
-- head 文件, 如果有 pgdump 字样说明是 pgdump 导出的
pg_dump -Fc -dpostgres -h127.0.0.1 -p5432 -ttb -a -f 'aaa'

-- 暂时清空原表
truncate table tb;

-- 开始恢复
---- 注意pg_restore的-a表示 data only 即不会复制表结构
pg_restore -Fc -dpostgres -h 127.0.0.1 -p5432 -ttb -a aaa

-- 验证数据
postgres=# select * from tb;
 id
----
  1
  2
  3
(3 rows)

• 查看表定义

-- 查看表定义
pg\_dump -Upostgres -p5432 mydb -s -t tb

-- 输出如下
CREATE TABLE public.tb (
    id integer
);

• 导出为insert形式的sql (注意这个命令无法导出 pg 的 partition 表）

pg_dump -Upostgres --column-inserts --table=mytb --db=mydb --file=a.sql
用 psql -f 即可导入

参考：https://www.osyunwei.com/archives/14077.html

copy to、copy from

\copy可以不用管用户权限的问题，如果copy则需要管用户权限（即目录）的问题。

csv

-- 导出
\copy (select * from tb limit 1) to stdout with csv;
\copy (select * from tb limit 1) to '/home/tb.csv' with csv;

-- 数据如此
540,102547,zhangsan
539,102546,lisi
538,102545,wangwu

-- 导入
\copy tb from /home/tb.sql with csv;

pg_settings

用 ALTER SYSTEM SET max_worker_processes = ‘2000’; 可修改参数。
maintenance-work-mem 设置大如30720000 用于临时建索引
checkpoint-timeout可调大，如120in，使写磁盘更平缓
checkpoint-completion-target设置大，如0.9使刷盘更平缓
effective-cache-size设置大，如一半机器内存
max-wal-size设置大，一般设置三个ckpt能产生的wal容量，如96GB，防止因达到此值产生的ckpt
wal-buffers设置大，如1024MB，预留更多磁盘
session_replication_role = ‘origin’，应设置为此，来使触发器生效

流复制

max_worker_processes 需要重启生效。

from pg_settings
where name in ('wal_level', 'session_replication_role', 'max_worker_processes', 'max_logical_replication_workers',
               'max_replication_slots', 'max_wal_senders', 'max_connections', 'max_sync_workers_per_subscription')
order by name asc;

               name                | setting | pending_restart
-----------------------------------+---------+-----------------
 max_connections                   | 5000    | f
 max_logical_replication_workers   | 4       | f
 max_replication_slots             | 10      | f
 max_sync_workers_per_subscription | 2       | f
 max_wal_senders                   | 10      | f
 max_worker_processes              | 256     | f
 session_replication_role          | origin  | f
 wal_level                         | replica | f
(8 rows)

分区表

create table tb_before_20220425 partition of tb for values from ('0') to ('1650816000000');

pg_resetwal

若 WAL 文件损坏（可能由于 WAL 被删、磁盘坏道、机器频繁重启导致文件损坏），会使数据库启动失败，此时 pg_log 的日志会显示如下：

2022-11-11 09:00:00 CST [5929-1] postgres@mydb FATAL: the database system is starting up
2022-11-11 09:00:00 CST [5929-1] LOG: startup process (PID 5562) was terminated by signal 6: Aborted
2022-11-11 09:00:00 CST [5929-1] LOG: aborting startup due to startup process failure
2022-11-11 09:00:00 CST [5929-1] postgres@mydb FATAL: the database system is starting up
2022-11-11 09:00:00 CST [5929-1] postgres@mydb FATAL: the database system is starting up
2022-11-11 09:00:00 CST [5929-1] LOG: database system is shut down
2022-11-11 09:00:00 CST [5929-1] LOG: invalid primary checkpoint record
2022-11-11 09:00:00 CST [5929-1] PANIC: could not locate a valid checkpoint record
2022-11-11 09:00:00 CST [5929-1] postgres@mydb FATAL: the database system is starting up
2022-11-11 09:00:00 CST [5929-1] postgres@mydb FATAL: the database system is starting up

可通过 pg_resetwal 解决：

• pg_resetwal -f /pgdata/main/，然后 systemctl restart postgresql 即可
• 更精确的pg_resetwal方式

pg_archivecleanup 清理 WAL 日志

• 检查哪个文件是可以被清理，找到编号 cd /usr/lib/postgresql/11/bin./pg_controldata -D /pgdata/main/ 找到 Latest checkpoint's REDO WAL file: 字样，找到类似的编号00000xxxxx
• 执行清理命令 pg_archivecleanup -d /pgdata/main/pg_wal 10000007000000C ，等待删除完毕

统计对象的磁盘占用

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS SIZE,
  table_size
FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         pg_relation_size(pg_catalog.pg_class.oid) AS table_size
       FROM pg_catalog.pg_class
         JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
     ) t
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

• 查index 和 table 大小

SELECT
 *,
 pg_size_pretty (
 pg_table_size ( indexrelid )) AS index_size,
 pg_size_pretty (
 pg_relation_size ( relid )) AS table_size,
 pg_size_pretty (
 pg_total_relation_size ( relid )) AS table_all_size,
 pg_size_pretty (
 pg_indexes_size ( relid )) AS index_all_size
FROM
 pg_stat_user_indexes
WHERE
 schemaname = 'public'
 AND relname = 'faces_index' ## zai_test_2020_1_103480'
ORDER BY
 idx_scan DESC;

• 查索引

SELECT
A.SCHEMANAME,
A.TABLENAME,
A.INDEXNAME,
A.TABLESPACE,
A.INDEXDEF,
B.AMNAME,
C.INDEXRELID,
C.INDNATTS,
C.INDISUNIQUE,
C.INDISPRIMARY,
C.INDISCLUSTERED,
D.DESCRIPTION
FROM
PG_AM B
LEFT JOIN PG_CLASS F ON B.OID = F.RELAM
LEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELID
LEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELID
LEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID,
PG_INDEXES A
WHERE
A.SCHEMANAME = E.SCHEMANAME AND A.TABLENAME = E.RELNAME AND A.INDEXNAME = E.INDEXRELNAME
AND E.SCHEMANAME = 'public' AND E.RELNAME = '';

• 表的整体观察

SELECT row_number() OVER ()                                                                                          AS id,
       a.relname,
       pg_size_pretty(pg_relation_size(a.relid))                                                                     AS relation_size,
       ((((COALESCE(a.seq_tup_read, (0)::bigint) + COALESCE(a.idx_tup_fetch, (0)::bigint)) +
          COALESCE(a.n_tup_ins, (0)::bigint)) + COALESCE(a.n_tup_upd, (0)::bigint)) +
        COALESCE(a.n_tup_del, (0)::bigint))                                                                          AS total_tuple,
       (COALESCE(a.seq_tup_read, (0)::bigint) +
        COALESCE(a.idx_tup_fetch, (0)::bigint))                                                                      AS total_select,
       COALESCE(a.n_tup_ins, (0)::bigint)                                                                            AS tup_insert,
       COALESCE(a.n_tup_upd, (0)::bigint)                                                                            AS tup_update,
       COALESCE(a.n_tup_del, (0)::bigint)                                                                            AS tup_delete,
       a.n_live_tup                                                                                                  AS live_tup,
       a.n_dead_tup                                                                                                  AS dead_tup,
       a.n_dead_tup::double precision > round(
                   current_setting('autovacuum_vacuum_threshold'::text)::integer::double precision +
                   current_setting('autovacuum_vacuum_scale_factor'::text)::numeric::double precision *
                   c.reltuples)                                                                                      AS need_vacuum,
       CASE
           WHEN a.n_dead_tup > 0 THEN round(a.n_dead_tup * 100 / (a.n_dead_tup + a.n_live_tup), 2)::numeric
           ELSE 0::numeric END                                                                                       AS dead_tup_ratio,
       coalesce(round(b.heap_blks_hit * 100 / (CASE
                                                   WHEN b.heap_blks_read + b.heap_blks_hit = 0 THEN NULL
                                                   ELSE b.heap_blks_read + b.heap_blks_hit END), 2),
                0.00)::numeric                                                                                       AS table_hit_ratio,
       coalesce(round(b.idx_blks_hit * 100 / (CASE
                                                  WHEN b.idx_blks_read + b.idx_blks_hit = 0 THEN NULL
                                                  ELSE b.idx_blks_read + b.idx_blks_hit END), 2),
                0.00)::numeric                                                                                       AS index_hit_ratio,
       coalesce(round(a.idx_scan * 100 / ((a.idx_scan) + (a.seq_scan) + 1), 2),
                0.00)::numeric                                                                                       AS index_used_ratio,
       t.spcname                                                                                                     AS tablespace
FROM pg_stat_user_tables a
         JOIN pg_statio_user_tables b ON a.relid = b.relid
         LEFT JOIN pg_class c ON c.relname = a.relname
         LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid AND c.relname = a.relname
GROUP BY a.relname, a.relid, a.seq_tup_read, a.idx_tup_fetch, a.n_tup_ins, a.n_tup_upd, a.n_tup_del, b.heap_blks_read,
         b.heap_blks_hit, b.idx_blks_read, b.idx_blks_hit, a.n_live_tup, a.n_dead_tup, a.idx_scan, a.seq_scan,
         t.spcname, c.reltuples
ORDER BY relname

• 找到未使用过的索引

下述排除了Primary key, Unique constraints及Unique index, 因为这些约束依赖的index, 虽然没有使用, 但是作为约束而言, 还有约束业务逻辑unique的功能, 所以不可以轻易drop。

SELECT pi.schemaname,
       pi.relname,
       pi.indexrelname,
       pg_size_pretty(pg_table_size(pi.indexrelid))
FROM pg_indexes pis
         JOIN pg_stat_user_indexes pi
              ON pis.schemaname = pi.schemaname AND pis.tablename = pi.relname AND pis.indexname = pi.indexrelname
         LEFT JOIN pg_constraint pco
                   ON pco.conname = pi.indexrelname AND pco.conrelid = pi.relid
WHERE pi.schemaname = 'public'
  AND pco.contype IS DISTINCT
    FROM 'p'
  AND pco.contype IS DISTINCT
    FROM 'u'
  AND (idx_scan, idx_tup_read, idx_tup_fetch) = (0, 0, 0)
  AND pis.indexdef !~ 'UNIQUE INDEX'
ORDER byrelname, pg_table_size(indexrelid) DESC;

• 找重复的索引

SELECT pg_size_pretty(SUM(pg_relation_size(idx))::BIGINT) AS SIZE,
       (array_agg(idx))[1]                                AS idx1,
       (array_agg(idx))[2]                                AS idx2,
       (array_agg(idx))[3]                                AS idx3,
       (array_agg(idx))[4]                                AS idx4
FROM (SELECT indexrelid::regclass                                                   AS idx,
             (indrelid::text || E'\n' || indclass::text || E'\n' || indkey::text || E'\n' ||
              COALESCE(indexprs::text, '') || E'\n' || COALESCE(indpred::text, '')) AS KEY
      FROM pg_index) sub
GROUP BY KEY
HAVING COUNT(*) > 1
ORDER BY SUM(pg_relation_size(idx)) DESC;

输出如下：
size  |  idx1   |  idx2  | idx3 | idx4
---------+-----------+----------+------+------ 
1984 kB | test_idx2 | test_idx |    |

SELECT *
FROM (SELECT tablespace,
             schemaname,
             tablename,
             indexname,
             pg_size_pretty(pg_table_size(schemaname || '."' || indexname || '"')) AS                                index_size,
             indexdef,
             count(1)
             OVER (PARTITION BY schemaname,tablename,regexp_replace(indexdef, E'(INDEX)(.+)(ON)(.+)', E'\\1\\3\\4')) frompg_indexes) AS foo
WHERE count > 1;

输出如下：
tablespace | schemaname | tablename | indexname | index_size |             indexdef             | count
------------+------------+-----------+-----------+------------+---------------------------------------------------------+-------      | 
public   | test1   | test_idx  | 992 kB   | CREATE INDEX test_idx ON public.test1 USING btree (id)  |   2      | 
public   | test1   | test_idx2 | 992 kB   | CREATE INDEX test_idx2 ON public.test1 USING btree (id) |   2
(2 rows)

• 调整索引

SELECT CASE
           WHEN flag = 1 THEN CASE
                                  WHEN indexdef !~ ' WHERE ' THEN
                                          regexp_replace(indexdef, E'(INDEX )(.+)( ON )(.+\\)\$)',
                                                         E' \\1 CONCURRENTLY \\3 \\4 ', 'g') || '; '
                                  ELSE regexp_replace(indexdef, E'(INDEX )(.+)( ON )(.+)( WHERE )',
                                                      E' \\1 CONCURRENTLY \\3 \\4 \\5 ', 'g') ||
                                       '; ' END
           WHEN flag = 2 THEN 'ANALYZE VERBOSE ' || schemaname || '.' || tablename ||
                              ';select pg_sleep(600); DROP INDEX CONCURRENTLY ' || schemaname || '.' || indexname || ';'
           END AS reindex_concurrently_sql
FROM (SELECT generate_series(1, 2) AS flag, indexdef, indexname, tablename, pi.schemaname
      FROM pg_indexes pi
               JOIN pg_namespace n ON pi.schemaname = n.nspname
               JOIN pg_class pcl
                    ON pcl.relnamespace = n.oid AND pcl.relname = pi.tablename
               LEFT JOIN pg_constraint pco ON pco.conname = pi.indexname AND
                                              pco.conrelid = pcl.oidwhere(pi.schemaname, pi.tablename) =
                                              ('public', 'test') AND pco.contype IS DISTINCT
              FROM 'p' AND pco.contype IS DISTINCT
              FROM 'u'
      ORDER BY pi.schemaname, tablename, indexname, pg_table_size(schemaname || '.' || indexname::TEXT) DESC,
               flag ASC) AS foo
ORDER BY schemaname,
         tablename,
         indexname,
         pg_table_size(schemaname || '.' || indexname::TEXT) DESC, flag ASC;

上面的生成的 CREATE INDEX CONCURRENTLY ; DROP INDEX CONCURRENTLY SQL 可以用来方便的进行对用户透明的 REINDEX。此 SQL 排除了对 Unique constraints, Primary key 的维护,因为二者是虽然都带 index,但表面上是以 constraint 的形式存在的。

使用该sql的时候主要将(pi.schemaname,pi.tablename) = (‘public’,‘test’)替换为相应的模式和表名即可

输出如下：

reindex_concurrently_sql
----------------------------------------------------------------------------------------------
create  index  concurrently  on  public.test1 using btree (id) ; 
analyze verbose public.test1;
select pg_sleep(600); 
drop index concurrently public.test_idx; 
create  index  concurrently  on  public.test1 using btree (id) ; 
analyze verbose public.test1;
select pg_sleep(600); 
drop index concurrently public.test_idx2;
(4 rows)

灌数据

-- 灌数据函数
-- random(): 0~1的数字
-- random() * 29+1: 1~30的数字
-- generate_series ( 1, FLOOR ( random() * 29+1 ) :: INTEGER ): 从 (start:1) 生成到 (end:1~30之间的随机数) 
-- ( FLOOR ( random() * 299+1 ) :: INTEGER ) : 产生1~300之间的随机数
-- random_int_array(): 产生数组, 数组长度为1~30的随机, 其中每个数组的元素是1~300的数字, 如{151,163,143,156,279,66,154,201,106,143}, 或 {111,138,159,41,191,139,151,85,120,89,23,107,95,202,143}
CREATE 
    OR REPLACE FUNCTION random_int_array () RETURNS INTEGER [] AS $$ SELECT ARRAY_AGG
    ( FLOOR ( random() * 299+1 ) :: INTEGER ) 
FROM
    generate_series ( 1, FLOOR ( random() * 29+1 ) :: INTEGER ) $$ LANGUAGE SQL;

-- 建立模拟表
CREATE TABLE tb(
    ts BIGINT,
    id VARCHAR ( 36 ) NOT NULL DEFAULT '',
    ID INT,
    c1 INT,
    c2 INT,
    c3 INT,
    c4 INT,
    c5 INT,
    c6 INT,
    c7 INT,
    c8 INT,
    c9 INT,
    c10 INT,
    c11 INT,
    c12 INT,
    c13 INT,
    c14 INT [],
    c15 INT [],
    c16 INT [],
    c17 INT [],
    c18 INT [] 
) PARTITION BY RANGE ( ts );

-- 开始灌数据
INSERT INTO tbSELECT
( 1577808000001 + random() * 31622300000 ),
CONCAT (
    '1000000000000000000000000000000',
CAST (( 10000+ random() * 10000 ) AS INT )),
generate_series ( 1, 100000 ),
random() * 10000,
random() * 2,
random() * 20,
random() * 10,
random() * 20,
random() * 10,
random() * 100,
random() * 100,
random() * 20,
random() * 20,
random() * 20,
random() * 20,
random() * 20,
random_int_array (),
random_int_array (),
random_int_array (),
random_int_array (),
random_int_array ();

-- 灌出来的数据示例如下
--1602886485804.94	100000000000000000000000000000019125	2	5496.47071398795	0.165149843320251	5.00236728228629	7.60367393493652	1.98087342083454	6.82561558205634	49.6744624339044	82.3436231352389	12.4926865659654	17.1848535817116	15.7304072380066	0.462746527045965	16.1724585760385	{252,71,170,29,284,190}	{49,110,128,82,115}	{180,231,192,154,96,216,229,24,246,134,173,193,22,131,129,28,74,182,280,144,53,10,129,242,52}	{53,179,259,167,133,140,98,25,293,193,241,222,217,188,56,91,81,77}	{210,105,294,93,86,139,145,95,267,87,146,145,139,26,104,7,159,243,104,184,237,297}

函数

时间转换

• 可视化 =》 unix 时间戳

select extract(epoch from timestamptz '2021-05-04 00:00:00')*1000;
select EXTRACT(epoch from cast ('2020-07-16 14:00:00+08' AS TIMESTAMP WITH TIME ZONE));

• unix 时间戳 =》 可视化

to_timestamp(ts/1000)

除0

SELECT COALESCE( (SUM(sum_data) / NULLIF(SUM(count), 0)), 0);
-- 内层NULLIF(a, b)保证: a=b时返回NULL, 即保证分母为0时分母为NULL;
-- 外层COALESCE(c, d)保证: 返回第一个非NUL值, 即保证除法结果为NULL时返回自定义的d=0值

参考:

• https://stackoverflow.com/questions/17681375/avoid-division-by-zero-in-postgresql
• https://www.codegrepper.com/code-examples/sql/division+by+zero+postgres
• https://blog.csdn.net/neweastsun/article/details/119120078

filter

可以实现行转列，是一种很好用的方言

可以代替sum(case gender when ‘男孩’ then cnt else 0), 本质上else里的0是gender的零元

相当于multi(case gender when ‘男孩’ then cnt else 1), 本质上else里的1是gender的零元

group by

https://zhuanlan.zhihu.com/p/86613661，本质是排序

• 做GROUP BY或DISTINCT, 最快的办法是O(N)遍历一遍, 但是因为内存有限, 不能把大量数据都放在内存里(程序如果要利用map的话, 肯定是要用内存的, 所有程序的数据结构都得放在内存里, 才能把程序加载为进程run起来), 那么内存利用不了, 就只能利用磁盘
• 利用磁盘的办法就是: 外排序(即归并排序), 场景: 比如一亿行的文件, 希望利用100MB的内存来排序, 那只能分治为N份, 每份内部排序, 然后把N份归并
• 归并排序的极端情况: 分成一亿个文件, 每个文件只有一个数字(即已经有序了), 然后两两归并(归并只需要极少的内存, 此极端情况下只需要1Byte), 然后就利用CPU并行排序即可

模糊通配符：Like与~

参考pg官网
like 是sql默认的符号，如果希望任意位置匹配，需要头尾均有%，例如：

select 'abc' like 'a';		false
select 'abc' like 'a%';		true
select 'abc' ~ 'a';			true

cte

WITH sensors AS (SELECT 'a' AS sensor_id
)
SELECT *
FROM sensors;

WITH tss AS (
    SELECT generate_series(1, 3, 1) AS ts)
SELECT *
FROM tss;
WITH sensors AS (SELECT 'a' AS sensor_id
),
     tss AS (
         SELECT generate_series(1, 3, 1) AS ts)
SELECT *
FROM sensors
         CROSS JOIN tss;

case when

select s.name as name, (case WHEN s.status = 1 THEN 'normal' WHEN s.status = 2 THEN 'unmormal' ELSE 'unknown' END ) from sensors s;

参考

聚合函数中的filter

可以实现行转列
是一种很好用的方言
可以代替sum(case gender when '男孩' then cnt else 0), 本质上else里的0是gender的零元
相当于multi(case gender when '男孩' then cnt else 1), 本质上else里的1是gender的零元

复制表insert select

postgres=# create table t(id int8);
CREATE TABLE
postgres=# insert into t select generate_series(1,10);
INSERT 0 10
postgres=# create index i on t(id);
CREATE INDEX
postgres=# select * into t1 from t;
SELECT 10
postgres=# \d t;
                 Table "public.t"
 Column |  Type  | Collation | Nullable | Default
--------+--------+-----------+----------+---------
 id     | bigint |           |          |
Indexes:
    "i" btree (id)
    
postgres=# \d+ t1;
                                    Table "public.t1"
 Column |  Type  | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
 id     | bigint |           |          |         | plain   |              |
Access method: heap

复制表create table like

postgres=# create table t2 as select * from t;
SELECT 10

postgres=# \d+ t2;
                                    Table "public.t2"
 Column |  Type  | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
 id     | bigint |           |          |         | plain   |              |
Access method: heap

copy与/copy

区别

• 下面是客户端的

本地mac连接到2.47
 ✘  ~  psql -h192.168.2.47 -p5432 -Upostgres
psql (13.2, server 13.1)
Type "help" for help.

postgres=# create table t5 (like t);
CREATE TABLE
postgres=# \! cat 123.txt
1
2
3
postgres=# \copy t5 from '123.txt' ;
COPY 3
postgres=# select * from t5;
 id
----
  1
  2
  3
(3 rows)

• 下面是服务器端的

postgres=# \! cat /platformData/4.txt
1
2
3

join

去重表与非去重表 join，得到非去重表

postgres=# create table a(id bigint primary key);
CREATE TABLE
postgres=# create table b(id bigint);
CREATE TABLE
postgres=# insert into a values (1), (2);
INSERT 0 2
postgres=# insert into b values (1),(1),(1),(2),(2),(2);
INSERT 0 6
postgres=# select * from a inner join b on a.id = b.id;
 id | id
----+----
  1 |  1
  1 |  1
  1 |  1
  2 |  2
  2 |  2
  2 |  2
(6 rows)

postgres=# select * from a left join b on a.id = b.id;
 id | id
----+----
  1 |  1
  1 |  1
  1 |  1
  2 |  2
  2 |  2
  2 |  2
(6 rows)

自关联

需求：关联查询，即希望根据「表自身」的某字段，找出其「前后跨度」的数据。可用如下三种方式实现：

• join 自己
• with 表达式
• 子查询

drop table if exists mytb;
create table mytb
(
    vid text,
    ts  bigint
);
-- 输入数据如下：
insert into mytb
values ('b', 9),
       ('a', 10),
       ('b', 11),
       ('b', 19),
       ('a', 20),
       ('b', 21),
       ('b', 100),
       ('b', 200),
       ('b', 300);
-- 查询条件为 where vid = 'a', 希望查出其前后各1s的数据，即结果集为 9,10,11,19,20,21

-- 方法1: join 自己
select tgt.*
from mytb src
         inner join mytb tgt
                    on tgt.ts >= src.ts - 1 and tgt.ts <= src.ts + 1
where src.vid = 'a'
order by ts asc;
-- vid,ts
-- b,9
-- a,10
-- b,11
-- b,19
-- a,20
-- b,21

-- 方法2: with表达式
with src as (select * from mytb where vid = 'a')
select tgt.*
from mytb tgt,
     src
where tgt.ts >= src.ts - 1
  and tgt.ts <= src.ts + 1
order by ts asc;
-- vid,ts
-- b,9
-- a,10
-- b,11
-- b,19
-- a,20
-- b,21

-- 方法3: 子查询
select tgt.*
from mytb tgt,
     (select * from mytb where vid = 'a') as src
where tgt.ts >= src.ts - 1
  and tgt.ts <= src.ts + 1
order by ts asc;
-- vid,ts
-- b,9
-- a,10
-- b,11
-- b,19
-- a,20
-- b,21

-- 如果捞的数据量太大内存放不下，可以按步长一点点捞（比如一次 10 个设备，或者一次两个小时）

distinct和distinct on

https://developer.aliyun.com/article/228277

jsonb

update jsonb数组中的某属性

原始数据是[{"type": "OutTotalCount", "count": 10777, "plate_count": 180}], 希望通过sql来update其中的count值
思路如下:
先通过::json->0取出json数组的第一项
再通过->'count'取出其中的count属性
再做业务逻辑(如下例的乘以1.9倍)
再用||符号,拼接出完整的字符串, 并转为jsonb类型
注意这种操作较危险: 故开事务: 先begin, 再update, 再commit; 有问题记得rollback.

— 原始数据
deepface_data=# select * from statistic_day where category = 'OutTotalCount' and id = 1178701032776056832;
         id          |   category    |      ts       | gas_station_code |      date_time      |                           group_data
---------------------+---------------+---------------+------------------+---------------------+-----------------------------------------------------------------
 1178701032776056832 | OutTotalCount | 1569772800000 | 101112           | 2019-09-30 00:00:00 | [{"type": "OutTotalCount", "count": 10777, "plate_count": 180}]

— 获取数组
deepface_data=# select group_data::json->0 from statistic_day where category = 'OutTotalCount' and id = 1178701032776056832;
                           ?column?
---------------------------------------------------------------
 {"type": "OutTotalCount", "count": 10777, "plate_count": 180}
(1 row)

— 获取某属性
deepface_data=# select (group_data::json->0)->'count' from statistic_day where category = 'OutTotalCount' and id = 1178701032776056832;
 ?column?
----------
 10777
(1 row)

— 乘法和拼接
deepface_data=# select 'a' || ((((group_data::json->0)->'count'))::text)::bigint * 2 from statistic_day where category = 'OutTotalCount' and id = 1178701032776056832;
 ?column?
----------
 a21554
(1 row)

— 获取拼接的text
deepface_data=# select 'a' || ((((group_data::json->0)->'count'))::text)::bigint * 1.9 from statistic_day where category = 'OutTotalCount' and id = 1178701032776056832;
 ?column?
----------
 a21554
(1 row)

— 业务拼接并转为jsonb
deepface_data=# select (‘[{“type": “OutTotalCount", "count": ' || round(((((group_data::json->0)->'count'))::text)::bigint * 1.9) || ', "plate_count": ' || round(((((group_data::json->0)->'plate_count'))::text)::bigint * 1.9) || ‘}]’)::jsonb from statistic_day where id = 1178701032776056832;
                             ?column?
-------------------------------------------------------------------
 [{“type": "OutTotalCount", "count": 20476, "plate_count": 342}]
(1 row)

— update
deepface_data=# begin;
BEGIN
deepface_data=# select group_data from statistic_day where id = 1178701032776056832;
                           group_data
-----------------------------------------------------------------
 [{"type": "OutTotalCount", "count": 10777, "plate_count": 180}]
(1 row)

deepface_data=# update statistic_day set group_data = (‘[{“type": "OutTotalCount", "count": ' || round(((((group_data::json->0)->'count'))::text)::bigint * 1.9) || ', "plate_count": ' || round(((((group_data::json->0)->'plate_count'))::text)::bigint * 1.9) || ‘}]’)::jsonb where id = 1178701032776056832;
UPDATE 1
deepface_data=# select group_data from statistic_day where id = 1178701032776056832;
                            group_data
-------------------------------------------------------------------
 [{“type": "OutTotalCount", "count": 20476, "plate_count": 342}]
(1 row)

deepface_data=# commit;
COMMIT

根据某属性过滤

若某表有id和info两列，其中某行id位1，info为 {"sn": "789", "ver": "2022"}
则可用 SELECT * FROM tb WHERE info->>'sn' = '789' 来检索。注意在WHERE中

hba

在线修改hba配置文件 而不需要ssh到机器上改pg_hba.conf

查进程

SELECT * FROM pg_stat_activity WHERE state = 'active'; SELECT pg_terminate_backend(PID);

查容量

SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20;
select pg_size_pretty(pg_table_size('sensors')); 
select pg_size_pretty(pg_total_relation_size('sensors');
select pg_size_pretty(pg_indexes_size('sensors'))

进程管理

SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT pg_cancel_backend(<pid of the process>); -- Find the process you want to kill
SELECT pg_terminate_backend(<pid of the process>); -- If the process cannot be killed, try:

comment

COMMENT ON TABLE user IS '用户';
COMMENT ON COLUMN user.id IS 'id';
COMMENT ON FUNCTION create_partition_table(table_name TEXT, partition_table_name TEXT, from_value BIGINT, to_value BIGINT) IS '创建分区表函数';

psql

设置locale

如果输入psql, 但报错如下的话, 可以通过在~/.profile中添加unset LC_CTYPE来解决

ubuntu@k8s-master-163:~$ psql
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_TERMINAL_VERSION = "3.4.6",
	LC_CTYPE = "zh_CN.UTF-8",
	LC_TERMINAL = "iTerm2",
	LANG = "zh_CN.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
psql (11.4 (Ubuntu 11.4-1.pgdg16.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

改了之后就正常了

root@k8s-master-163:~# psql
psql (11.4 (Ubuntu 11.4-1.pgdg16.04+1))
Type "help" for help.

中文编码

我们直接 psql 查询中文可能有乱码，可以将 client_coding 在 UTF-8 和 GBK 之间切换。

postgres=# select * from mytb limit 1;
x | y | z
---------
烫 |烫 |烫

postgres=# show client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

postgres=# show server_encoding;
 server_encoding
-----------------
 UTF8
(1 row)

postgres=# \encoding GBK;
postgres=# show server_encoding;
 server_encoding
-----------------
 GBK
(1 row)

postgres=# select * from mytb limit 1;
x | y | z
---------
你 |好 |世界

postgres=# \reset encoding
RESET

参考

交互模式

\copyright显示PostgreSQL使用和分发条款

\g [文件] or ;执行查询（并将结果发送给文件或|管道）

deepface_data=# select gas_station_code from dim_road_org limit 1;
 gas_station_code
------------------
 33252385
(1 row)

deepface_data=# \g
 gas_station_code
------------------
 33252385
(1 row)

\gset[PREFIX]执行查询并将结果存储到psql变量中

\h [名称]关于SQL命令语法的帮助，*代表所有命令

\q退出psql

\watch[SEC]每隔SEC秒执行一次查询 select count(*) from sensors; \watch 1;

查询缓冲区相关命令

\e [FILE] [LINE]使用外部编辑器编辑查询缓冲区（或文件）

\ef [FUNCNAME [LINE]]使用外部编辑器编辑函数定义

\p 显示查询缓冲区的内容

\r 重置（清除）查询缓冲区

\w 文件将查询缓冲区写入到文件

输入/输出相关命令

\copy …执行SQL COPY，将数据流发送到客户端主机

\echo[字符串] 将字符串写到标准输出

\i 文件从文件执行命令

\ir FILE与\i类似，但是在脚本中执行时，认为目标文件的位置是当前脚本所在的目录

\o [文件]将所有查询结果发送到文件或|管道

\qecho[字符串]将字符串写入到查询输出流，该命令等效于\echo，区别是所有输出将写入由\o设置的输出通道

信息查询命令

S表示显示系统对象，+表示附加的详细信息

\d [S+]输出表、视图和序列列表

\d [S+]名称描述表、视图、序列或索引

\da [S][模式]输出聚合函数列表

\db [+][模式] 输出表空间列表

\dc [S] [模式]输出编码转换（conversion）列表

\dC [模式]输出类型强制转换（cast）列表

                                         List of casts
         Source type         |         Target type         |      Function      |   Implicit?
-----------------------------+-----------------------------+--------------------+---------------
 abstime                     | date                        | date               | in assignment
 abstime                     | integer                     | (binary coercible) | no
 abstime                     | timestamp without time zone | timestamp          | yes
 abstime                     | timestamp with time zone    | timestamptz        | yes
 abstime                     | time without time zone      | time               | in assignment

\dd [S][模式]显示对象上的注释

\ddp[模式]输出默认权限列表

\dD [S] [模式]输入域列表

\det [+] [模式]输入外部表列表

\des [+] [模式]输出外部服务器列表

\deu [+] [模式]输出用户映射列表

\dew [+] [模式]输出外部数据封装器列表

\df [antw] [S+] [模式]输出特定类型函数（仅a-聚合函数/n-常规函数/t-触发器函数/w-窗口函数）列表

\dF [+] [模式]输出文本搜索配置列表

deepface_data=# \dF
               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
(16 rows)

\dFd [+][模式]输出文本搜索字典列表

deepface_data=# \dFd
                             List of text search dictionaries
   Schema   |      Name       |                        Description
------------+-----------------+-----------------------------------------------------------
 pg_catalog | danish_stem     | snowball stemmer for danish language
 pg_catalog | dutch_stem      | snowball stemmer for dutch language
 pg_catalog | english_stem    | snowball stemmer for english language
 pg_catalog | finnish_stem    | snowball stemmer for finnish language
 pg_catalog | french_stem     | snowball stemmer for french language
 pg_catalog | german_stem     | snowball stemmer for german language
 pg_catalog | hungarian_stem  | snowball stemmer for hungarian language
 pg_catalog | italian_stem    | snowball stemmer for italian language
 pg_catalog | norwegian_stem  | snowball stemmer for norwegian language
 pg_catalog | portuguese_stem | snowball stemmer for portuguese language
 pg_catalog | romanian_stem   | snowball stemmer for romanian language
 pg_catalog | russian_stem    | snowball stemmer for russian language
 pg_catalog | simple          | simple dictionary: just lower case and check for stopword
 pg_catalog | spanish_stem    | snowball stemmer for spanish language
 pg_catalog | swedish_stem    | snowball stemmer for swedish language
 pg_catalog | turkish_stem    | snowball stemmer for turkish language
(16 rows)

\dFp [+][模式]输出文本搜索解析器列表

deepface_data=# \dFp
        List of text search parsers
   Schema   |  Name   |     Description
------------+---------+---------------------
 pg_catalog | default | default word parser
(1 row)

\dFt [+] [模式]输出文本搜索模板列表

deepface_data=# \dFt
                           List of text search templates
   Schema   |   Name    |                        Description
------------+-----------+-----------------------------------------------------------
 pg_catalog | ispell    | ispell dictionary
 pg_catalog | simple    | simple dictionary: just lower case and check for stopword
 pg_catalog | snowball  | snowball stemmer
 pg_catalog | synonym   | synonym dictionary: replace word by its synonym
 pg_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution
(5 rows)

\dg [+] [模式]输出角色列表

\di [S+][模式]输出索引列表

\dl输出大对象列表，与\lo_list相同

\dL [S+] [模式]输出过程

deepface_data=# \dL
                      List of languages
  Name   |  Owner   | Trusted |         Description
---------+----------+---------+------------------------------
 plpgsql | postgres | t       | PL/pgSQL procedural language
(1 row)

\dm [S+][模式]输出物化视图列表

\dn [S+] [模式]输出schema列表

\do [S] [模式]输出运算符列表

deepface_data=# \do
                              List of operators
 Schema | Name  | Left arg type | Right arg type | Result type | Description
--------+-------+---------------+----------------+-------------+-------------
 public | %     | text          | text           | boolean     |
 public | %>    | text          | text           | boolean     |
 public | %>>   | text          | text           | boolean     |
 public | <%    | text          | text           | boolean     |
 public | <->   | text          | text           | real        |
 public | <->>  | text          | text           | real        |
 public | <->>> | text          | text           | real        |
 public | <<%   | text          | text           | boolean     |
 public | <<->  | text          | text           | real        |
 public | <<<-> | text          | text           | real        |
(10 rows)

\dO[S+][模式]输出排序规则列表

\dp[模式]输出表、视图和序列访问权限列表

\drds [模式1[模式2]]输出每个database的角色设置列表

\ds [S+][模式]输出序列列表

\dt [S+][模式]输出表列表

\dT [S+] [模式]输出数据类型列表

\du [+][模式]输出角色列表

\dv [S+][模式]输出视图列表

\dE [S+][模式]输出外部表列表

\dx [+][模式]输出扩展列表

deepface_data=# \dx
                                     List of installed extensions
    Name    | Version |   Schema   |                            Description
------------+---------+------------+-------------------------------------------------------------------
 btree_gin  | 1.3     | public     | support for indexing common datatypes in GIN
 file_fdw   | 1.0     | public     | foreign-data wrapper for flat file access
 pg_trgm    | 1.4     | public     | text similarity measurement and index searching based on trigrams
 pipelinedb | 1.0.0   | public     | PipelineDB
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
 uuid-ossp  | 1.1     | public     | generate universally unique identifiers (UUIDs)
(6 rows)

\dy[模式]输出事件触发器列表

\l [+]输出数据库列表

\sf [+]FUNCNAME显示函数定义

\z[模式]和\dp的功能相同

格式化相关命令

\a在非对齐输出模式和对齐输出模式之间切换

\C[字符串]设置表标题；如果没有，则不设置

\f[字符串]显示或设置非对齐查询输出的字段分隔符

deepface_data=# \f
Field separator is "|".

\H切换HTML输出模式（当前关闭）

deepface_data=# \H
Output format is html.
deepface_data=# \H
Output format is aligned.

\pset NAME [VALUE]设置表输出选项（NAME的可选项有format、border、expanded、fieldsep、fieldsep_zero、footer、null、numericlocale、recordsep、tuples_only、title、tableattr、pager）

\t [on|off] 仅显示行（当前关闭）

deepface_data=# \t
Tuples only is on.

deepface_data=# select gas_station_code from dim_road_org limit 1;
 33252385
 
 
deepface_data=# \t
Tuples only is off.

deepface_data=# select gas_station_code from dim_road_org limit 1;
 gas_station_code
------------------
 33252385
(1 row)

\T[字符串]设置HTML

\x [on|off]切换扩展输出（当前关闭）

连接相关命令

\c [nonect] [DBNAME|- USER |- HOST|- PORT|- ] 连接到新的database（当前是postgres）

\encoding [编码名称]显示或设置客户端编码

\password[USERNAME]安全地为用户更改密码

\conninfo显示当前连接的相关信息

deepface_data=# \conninfo
You are connected to database "deepface_data" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

操作系统相关命令

\cd[目录]更改当前工作目录

\setenv NAME[VALUE]设置或取消设置环境变量

\timing [on|off]切换命令计时开关（当前关闭）

! [命令]在shell中执行命令或启动交互式shell

非交互模式下的psql命令

psql的“非交互模式”是指在调用psql时直接以选项的形式指定要执行的脚本，脚本中可以含有任意数量的SQL和psql语句，然后psql会自动执行此脚本的内容，期间无需与用户进行交互。

psql --help

psql是PostgreSQL的交互式终端。

使用方法：psql [选项] … [database名称[用户名]]

-c，–command=命令仅运行单个命令（SQL或内部命令），然后退出

-d，–dbname=数据库名称要连接到的数据库名称

-f，–file=文件名从文件执行命令，然后退出

-l，–list列出可用的数据库，然后退出

-v，–set=，–varible=名称=值将psql变量NAME设置为VALUE

-X，–no-psqlrc不读取启动文件（~/.psqlrc）

-1 （“one”），–single-transaction将命令文件作为单一事务执行

–help显示此帮助信息并退出

–version输出版本信息并退出

输入和输出选项

-a，–echo-all回显所有来在于脚本的输入

-e，–echo-queries回显发送给服务器的命令

-E，–echo-hidden显示内部命令生成的查询

-L，–log-file=文件名将会话日志发送给文件

-n，–no-readline禁用增强命令行编辑功能（readline）

-o，–output=FILENAME将查询结果发送给文件（或|管道）

-q，–quiet以静默方式运行（不显示消息，仅显示查询输出）

-s，–single-step单步模式（每个模式均需确认）

-S，–single-line单行模式（SQL命令不允许跨行）

输出格式选项

-A，–no-align非对齐表输出模式

-F，–file-separator=字符串设置字段分隔符（默认为“|”）

-H， --htmlHTML表输出模式

-P，–pset=VAR[=ARG]将打印选项VAR设置为ARG（参照\pset命令）

-R，–record-separator=字符串设置记录分隔符（默认为换行符）

-t，–tuples-only仅打印行

-T，–table-attr=文本设置HTML表标记属性（例如：宽度、边框等）

-x， --expanded打开扩展表输出

-z，–field-separator-zero将字段分隔符设置为零字节

-0，–record-separator-zero将记录分隔符设置为零字节

连接选项

-h，–host=主机名数据库服务器主机或套接字目录

-p，–port=端口数据库服务器端口（默认为5432）

-U，–username=用户名数据库用户名

-w，–no-password永远不提示输入密码

-W，–password强制要求输入密码（应该自动发生）

\gdesc 显示最近查询的列名, 列数据类型

postgres=# \gdesc
 Column |  Type
--------+--------
 id     | bigint
(1 row)

\pset pager off 查询结果不分页

不用一行一行敲空格, 看下一页了

存储过程

https://www.cnblogs.com/kuang17/p/8143814.html

• 当存储过程中引用的table不存在时，存储依然可以建立，只是会在执行存储过程时报错

postgres=# CREATE OR REPLACE FUNCTION execute(filter TEXT)
postgres-# RETURNS TABLE (a TEXT, b INTEGER)
postgres-# AS $$
postgres$# BEGIN
postgres$#     RETURN QUERY EXECUTE
postgres$#         'SELECT * FROM testExecute where b = '''
postgres$#         || filter || '''';
postgres$# END;
postgres$# $$ LANGUAGE PLPGSQL;
CREATE FUNCTION

• \sf看详细定义, \df看入参出参概述定义

postgres=# \df+ akeys
                                                                            List of functions
 Schema | Name  | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | L
anguage | Source code  | Description
--------+-------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+--
--------+--------------+-------------
 public | akeys | text[]           | hstore              | func | immutable  | safe     | postgres | invoker  |                   | c
        | hstore_akeys |
(1 row)

postgres=# \sf akeys
CREATE OR REPLACE FUNCTION public.akeys(hstore)
 RETURNS text[]
 LANGUAGE c
 IMMUTABLE PARALLEL SAFE STRICT
AS '$libdir/hstore', $function$hstore_akeys$function$
postgres=# \sf+ akeys
        CREATE OR REPLACE FUNCTION public.akeys(hstore)
         RETURNS text[]
         LANGUAGE c
         IMMUTABLE PARALLEL SAFE STRICT
1       AS '$libdir/hstore', $function$hstore_akeys$function$

自动创建分区表

CREATE OR REPLACE FUNCTION create_partition_table(table_name TEXT, partition_table_name TEXT, from_value BIGINT,
                                                  to_value BIGINT) RETURNS void AS
$$
DECLARE
    sql TEXT;
BEGIN
    sql := 'CREATE TABLE if not exists ' || partition_table_name || ' partition of ' || table_name ||
           ' for values from (' || from_value || ')' || ' to ' || '(' || to_value || ');';
    EXECUTE (sql);
    return;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION create_partition_table(table_name TEXT, partition_table_name TEXT, from_value BIGINT, to_value BIGINT) IS '创建分区表函数';

DROP FUNCTION IF EXISTS auto_create_partition_table(table_name TEXT);
CREATE OR REPLACE FUNCTION auto_create_partition_table(table_name TEXT) RETURNS void AS
$acpt$
DECLARE
    date_str      TEXT;
    value_01      BIGINT;
    value_10      BIGINT;
    value_20      BIGINT;
    next_value_01 BIGINT;
BEGIN
    select to_char(NOW(), 'YYYY_MM') into date_str;
    EXECUTE $$ SELECT extract(epoch from DATE_TRUNC('month', CURRENT_DATE))*1000 $$ INTO value_01 USING date_str || '_01';
    EXECUTE $$ SELECT extract(epoch from DATE_TRUNC('month', CURRENT_DATE))*1000 + 9 * 86400000 $$ INTO value_10 USING date_str || '_10';
    EXECUTE $$ SELECT extract(epoch from DATE_TRUNC('month', CURRENT_DATE))*1000 + 19 * 86400000$$ INTO value_20 USING date_str || '_20';
    EXECUTE $$ SELECT extract(epoch from DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') AT TIME ZONE 'Asia/Shanghai')*1000 $$ INTO next_value_01 USING date_str || '_01';
    PERFORM public.create_partition_table(table_name, table_name || '_' || date_str || '_01', value_01, value_10);
    PERFORM public.create_partition_table(table_name, table_name || '_' || date_str || '_10', value_10, value_20);
    PERFORM public.create_partition_table(table_name, table_name || '_' || date_str || '_20', value_20, next_value_01);
    return;
END;
$acpt$ LANGUAGE plpgsql;
COMMENT ON FUNCTION auto_create_partition_table(table_name TEXT) IS '自动创建本月分区表函数-按10天分区';

DROP FUNCTION IF EXISTS auto_create_partition_table_monthly(table_name TEXT);
CREATE FUNCTION auto_create_partition_table_monthly(table_name TEXT) RETURNS void AS
$acpt$
DECLARE
    date_str      TEXT;
    value_01      BIGINT;
    next_value_01 BIGINT;
BEGIN
    select to_char(NOW(), 'YYYY_MM') into date_str;

    EXECUTE $$ SELECT extract(epoch from DATE_TRUNC('month', CURRENT_DATE))*1000 $$ INTO value_01 USING date_str || '_01';
    EXECUTE $$ SELECT extract(epoch from DATE_TRUNC('month', CURRENT_DATE + INTERVAL '1 MONTH') AT TIME ZONE 'Asia/Shanghai')*1000 $$ INTO next_value_01 USING date_str || '_01';
    PERFORM public.create_partition_table(table_name, table_name || '_' || date_str || '_01', value_01, next_value_01);
    return;
END;
$acpt$ LANGUAGE plpgsql;
COMMENT ON FUNCTION auto_create_partition_table_monthly(table_name TEXT) IS '自动创建本月分区表函数-按1月分区';

文件结构

pg_internal.init文件

• 至少pg9就引入这个文件的机制了
• 在pg11中pg_basebackup的备份结果中不包括pg_internal.init文件

[pg1110@iZm5ehqfjhnsbtxrzrnh2zZ pgbackup05]$ pwd
/home/pg1110/pgbackup05
[pg1110@iZm5ehqfjhnsbtxrzrnh2zZ pgbackup05]$ find . -name pg_internal.init
[pg1110@iZm5ehqfjhnsbtxrzrnh2zZ pgbackup05]$
[pg1011@iZm5ehqfjhnsbtxrzrnh2zZ pgbackup01]$ find . -name pg_internal.init
./base/16422/pg_internal.init
./base/16445/pg_internal.init
./base/16462/pg_internal.init
./base/13808/pg_internal.init
./base/16955/pg_internal.init
./global/pg_internal.init
[pg1011@iZm5ehqfjhnsbtxrzrnh2zZ pgbackup01]$ pwd
/home/pg1011/pgbackup01
[pg1011@iZm5ehqfjhnsbtxrzrnh2zZ pgbackup01]$

• pg_internal.init文件是系统信息的缓存文件，当PG实例被重启之后，本文件会被重新创建。
• 在恢复时始终会对其进行重建

https://www.docs4dev.com/docs/zh/postgre-sql/11.2/reference/continuous-archiving.html

只要找到具有相同名称的文件，就可以从备份中删除pg_internal.init个文件。这些文件包含关系高速缓存数据，在恢复时始终会对其进行重建。

backup_label文件

文件中带有START TIMELINE, 在pg12之前就引入了

http://postgres.cn/docs/13/continuous-archiving.html

这里label是任何你希望用来唯一标识这个备份操作的字符串。 pg_start_backup在集簇目录中创建一个关于备份信息的 备份标签文件，也被称为backup_label， 其中包括了开始时间和标签字符串。

service、systemctl、init.d 启动

apt-get后, 会自动注册为systemctl和init.d, service就是init.d

service  postgresql status
systemctl status postgresql
vim /lib/systemd/system/postgresql@.service

表结构设计

枚举默认值

为了防止歧义，枚举默认值尽量不要 DEFAULT 0（如选 DEFAULT 1），这样可将数据库的 DEFAULT 值 和 编程语言的数字类型的零值（为0） 区分开 ，例如如下代码：

CREATE TABLE IF NOT EXISTS "tb" (
	"id" 		BIGINT 	NOT NULL, -- 唯一ID
	"status"	INT		NOT NULL DEFAULT 1, --1为正常，2为异常，数据库此字段的值域只应为 [1,2]
);

NULL 值

NULL 值和 DEFAULT 以 TEXT 类型为例：

• NULL 是未知的，数据库不知道其值是什么。
• 而 DEFAULT ''，则表明数据库知道其值，其值就是空值。

ID 选 TEXT 还是选 BIGINT

通常因为数字类型比字符类型存储占用更少，因此比较操作更快，更推荐做主键来加速检索性能，但具体在机器规模也有不同场景的考虑：

• 选 TEXT 常用 UUID（36位的UUID.V4()类型），其因为不依赖分布式的node_id，因此在数量未知的移动端也可保证唯一性。
• 选 BIGINT 常用分布式雪花算法，因为该算法依赖分布式的 node_id，因此适用于固定数量的机器（如机房的主机），而不适用于数量未知的移动端（如手机、平板等）。

索引

IN

• pg 的 btree，=（等值查询）生效，in、or 都没生效
• pg 的 gin，=（等值查询）生效，in、or 都没生效
• pg 的 hash，=（等值查询）生效，in、or 都没生效

而 mysql 的 btree 生效：

外键索引

pg 外键文档

CREATE TABLE a(id INTEGER);
CREATE TABLE b(id INTEGER PRIMARY KEY);

-- 建立 a 的外键，使 a 的 id 依赖于 b（注意，依赖的 b.id 必须是 b 的主键）
-- 即必须 b.id='123' 存在的情况下，才能写入 a.id='123', 否则会报错。
-- 且必须先删除 b.id='123' 后，才能删除 a.id='123', 否则会报错。
ALTER TABLE a ADD CONSTRAINT a_id_fk FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE;


-- 可看出 a 有 Foreign-key，即 a 依赖于 b
postgres=# \d+ a
                                     Table "public.a"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              |
Foreign-key constraints:
    "a_id_fk" FOREIGN KEY (id) REFERENCES b(id) ON DELETE CASCADE


-- 可看出 b 有 Referenced by，即 b 被 a 依赖
postgres=# \d+ b
                                     Table "public.b"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           | not null |         | plain   |              |
Indexes:
    "b_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "a" CONSTRAINT "a_id_fk" FOREIGN KEY (id) REFERENCES b(id) ON DELETE CASCADE

删除主键

alter table schemaxyz.tb1 drop constraint my_pkey;

常见报错

fatal: the database is starting up

如果 pg_log 一直刷新此日志，是正常的，因为 pg 启动需要时间。此时可 ps -ef | grep postgres 很可能是正在 recover from wal。