Author: yuanhong
create index idx_tbl_1 on tbl using spgist (gis) include (col1,col2); -- 叶子结点加入col1,col2内容
select col1,col2,gis from tbl where gis xx; -- VM clearly heap page 可以不需要回表, 提高性能
create or replace function xx.... return query select xx from xx ... -- 这里到select xx query可以支持并行计算
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name -- 支持并行计算
create foreign table ft1 AT Server1;
...
create foreign table ftn AT ServerN;
create partition table tbl partition by ....
alter table ft1 attach to tbl ...
...
alter table ftn attach to tbl ...
select count(*),avg(x),max(x)... from tbl group by xxxx -- 支持N个Server同时计算.
IMPORT FOREIGN SCHEMA remote_schema
[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] table_name -- 支持子分区.
truncate 外部表;
DEMO:
ALTER TABLE [ IF EXISTS ] name
DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]
select x from tbl where id in (1,2,3,4,5,6,7,8,9);
CREATE TABLE cmdata(f1 text COMPRESSION pglz);
CREATE INDEX idx ON cmdata(f1);
INSERT INTO cmdata VALUES(repeat('1234567890', 1000));
\d+ cmdata
Table "public.cmdata"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
f1 | text | | | | extended | pglz | |
Indexes:
"idx" btree (f1)
CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4);
INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004));
\d+ cmdata1
Table "public.cmdata1"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------+-----------+----------+---------+----------+-------------+--------------+-------------
f1 | text | | | | extended | lz4 | |
注意：如果要使用 lz4 压缩，则 PostgreSQL 编译时需要带上--with-lz4选项
postgres=# select * from pg_roles ;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
pg_database_owner | f | t | f | f | f | f | -1 | ******** | | f | | 6171
pg_read_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6181
pg_write_all_data | f | t | f | f | f | f | -1 | ******** | | f | | 6182
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569
pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570
pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
(12 rows)
-- 创建全局只读，全局只写用户
postgres=# create user global_read_only password '******';
CREATE ROLE
postgres=# create user global_write_only password '******';
CREATE ROLE
-- 授权
postgres=# grant pg_read_all_data to global_read_only ;
GRANT ROLE
postgres=# grant pg_write_all_data to global_write_only ;
GRANT ROLE
-- 全局只读用户只有只读权限
postgres=# \c - global_read_only
Password for user global_read_only:
psql (12.4, server 14.1)
WARNING: psql major version 12, server major version 14.
Some psql features might not work.
You are now connected to database "postgres" as user "global_read_only".
postgres=> select * from employees limit 1;
employee_id | full_name | manager_id
-------------+---------------+------------
1 | Michael North |
(1 row)
postgres=> insert into employees (employee_id, full_name, manager_id) values(22, 'xgq', 2);
ERROR: permission denied for table employees
-- 全局只写用户只有只写权限
postgres=> \c - global_write_only ;
Password for user global_write_only:
psql (12.4, server 14.1)
WARNING: psql major version 12, server major version 14.
Some psql features might not work.
You are now connected to database "postgres" as user "global_write_only".
postgres=> select * from employees limit 1;
ERROR: permission denied for table employees
postgres=> insert into employees (employee_id, full_name, manager_id) values(22, 'xgq', 2);
INSERT 0 1
postgres=> select * from pg_settings where name='password_encryption';
-[ RECORD 1 ]---+------------------------------------------------
name | password_encryption
setting | md5
unit |
category | Connections and Authentication / Authentication
short_desc | Chooses the algorithm for encrypting passwords.
extra_desc |
context | user
vartype | enum
source | configuration file
min_val |
max_val |
enumvals | {md5,scram-sha-256}
boot_val | scram-sha-256
reset_val | md5
sourcefile |
sourceline |
pending_restart | f
注意：较旧的客户端库不支持 SCRAM-SHA-256认证，会有类似报错psycopg2.OperationalError: authentication method 10 not supported，升级客户端库到最新版本即可支持
SELECT '{}'::int4multirange;
SELECT '{[3,7)}'::int4multirange;
SELECT '{[3,7), [8,9)}'::int4multirange;
select ('{"postgres": {"release": "Alibaba Cloud RDS PG 14"}}'::jsonb)['postgres']['release'];
jsonb
---------------------------
"Alibaba Cloud RDS PG 14"
(1 row)
-- 表结构
postgres=> \d range_test
Table "public.range_test"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+----------------------------------------
id | integer | | not null | nextval('range_test_id_seq'::regclass)
date_time | tsrange | | |
Indexes:
"range_test_pkey" PRIMARY KEY, btree (id)
-- 表数据
postgres=> select * from range_test;
id | date_time
----+-----------------------------------------------
1 | ["2010-01-01 17:00:00","2010-01-01 18:00:00")
2 | ["2010-01-01 15:00:00","2010-01-01 16:00:00")
(2 rows)
-- 定义存储过程，o_date_time 使用 out 类型
postgres=> create or replace procedure p1 ( i_id in int, o_date_time out tsrange )
as $$
declare
begin
select date_time
into o_date_time
from range_test where id=i_id;
end;
$$ language plpgsql;
CREATE PROCEDURE
-- 调用p1存储过程通过n返回数据
postgres=> do
$$
declare
n tsrange;
begin
call p1(1, n);
raise notice '%', n;
end;
$$;
NOTICE: ["2010-01-01 17:00:00","2010-01-01 18:00:00")
DO
-- 创建表
CREATE TABLE employees (
employee_id serial PRIMARY KEY,
full_name VARCHAR NOT NULL,
manager_id INT
);
-- 插入数据
INSERT INTO employees (
employee_id,
full_name,
manager_id
)
VALUES
(1, 'Michael North', NULL),
(2, 'Megan Berry', 1),
(3, 'Sarah Berry', 1),
(4, 'Zoe Black', 1),
(5, 'Tim James', 1),
(6, 'Bella Tucker', 2),
(7, 'Ryan Metcalfe', 2),
(8, 'Max Mills', 2),
(9, 'Benjamin Glover', 2),
(10, 'Carolyn Henderson', 3),
(11, 'Nicola Kelly', 3),
(12, 'Alexandra Climo', 3),
(13, 'Dominic King', 3),
(14, 'Leonard Gray', 4),
(15, 'Eric Rampling', 4),
(16, 'Piers Paige', 7),
(17, 'Ryan Henderson', 7),
(18, 'Frank Tucker', 8),
(19, 'Nathan Ferguson', 8),
(20, 'Kevin Rampling', 8);
-- 深度优先
WITH RECURSIVE subordinates(employee_id, manager_id, full_name) AS (
SELECT
employee_id,
manager_id,
full_name
FROM
employees
WHERE
employee_id = 2
UNION
SELECT
e.employee_id,
e.manager_id,
e.full_name
FROM
employees e, subordinates s
WHERE
s.employee_id = e.manager_id
) SEARCH DEPTH FIRST BY employee_id SET ordercol
SELECT * FROM subordinates;
-- 广度优先
WITH RECURSIVE subordinates(employee_id, manager_id, full_name) AS (
SELECT
employee_id,
manager_id,
full_name
FROM
employees
WHERE
employee_id = 2
UNION
SELECT
e.employee_id,
e.manager_id,
e.full_name
FROM
employees e, subordinates s
WHERE
s.employee_id = e.manager_id
) SEARCH BREADTH FIRST BY employee_id SET ordercol
SELECT * FROM subordinates;
-- 循环语法
WITH RECURSIVE subordinates(employee_id, manager_id, full_name) AS (
SELECT
employee_id,
manager_id,
full_name
FROM
employees
WHERE
employee_id = 2
UNION
SELECT
e.employee_id,
e.manager_id,
e.full_name
FROM
employees e, subordinates s
WHERE
s.employee_id = e.manager_id
) CYCLE employee_id SET is_cycle USING path
SELECT * FROM subordinates;
语法：
date_bin(stride, source, origin)
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
Result: 2020-02-11 15:30:00
SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
Result: 2020-02-11 15:32:30
select e.*, e.ctid from employees e;
employee_id | full_name | manager_id | ctid
-------------+-------------------+------------+--------
1 | Michael North | | (0,1)
2 | Megan Berry | 1 | (0,2)
3 | Sarah Berry | 1 | (0,3)
4 | Zoe Black | 1 | (0,4)
5 | Tim James | 1 | (0,5)
6 | Bella Tucker | 2 | (0,6)
7 | Ryan Metcalfe | 2 | (0,7)
8 | Max Mills | 2 | (0,8)
9 | Benjamin Glover | 2 | (0,9)
10 | Carolyn Henderson | 3 | (0,10)
11 | Nicola Kelly | 3 | (0,11)
12 | Alexandra Climo | 3 | (0,12)
13 | Dominic King | 3 | (0,13)
14 | Leonard Gray | 4 | (0,14)
15 | Eric Rampling | 4 | (0,15)
16 | Piers Paige | 7 | (0,16)
17 | Ryan Henderson | 7 | (0,17)
18 | Frank Tucker | 8 | (0,18)
19 | Nathan Ferguson | 8 | (0,19)
20 | Kevin Rampling | 8 | (0,20)
(20 rows)
select * from employees where ctid > '(0,10)' and ctid < '(0,20)';
employee_id | full_name | manager_id
-------------+-----------------+------------
11 | Nicola Kelly | 3
12 | Alexandra Climo | 3
13 | Dominic King | 3
14 | Leonard Gray | 4
15 | Eric Rampling | 4
16 | Piers Paige | 7
17 | Ryan Henderson | 7
18 | Frank Tucker | 8
19 | Nathan Ferguson | 8
(9 rows)
-- 以下操作不管持续多久, 不影响vacuum回收这之间产生的垃圾
create index CONCURRENTLY abc ...
reindex CONCURRENTLY xxx ...
select * from pg_settings where name ~ 'failsafe';
-[ RECORD 1 ]---+------------------------------------------------------------------------------------
name | vacuum_failsafe_age
setting | 1600000000
unit |
category | Client Connection Defaults / Statement Behavior
short_desc | Age at which VACUUM should trigger failsafe to avoid a wraparound outage.
extra_desc |
context | user
vartype | integer
source | default
min_val | 0
max_val | 2100000000
enumvals |
boot_val | 1600000000
reset_val | 1600000000
sourcefile |
sourceline |
pending_restart | f
-[ RECORD 2 ]---+------------------------------------------------------------------------------------
name | vacuum_multixact_failsafe_age
setting | 1600000000
unit |
category | Client Connection Defaults / Statement Behavior
short_desc | Multixact age at which VACUUM should trigger failsafe to avoid a wraparound outage.
extra_desc |
context | user
vartype | integer
source | default
min_val | 0
max_val | 2100000000
enumvals |
boot_val | 1600000000
reset_val | 1600000000
sourcefile |
sourceline |
pending_restart | f
postgres=> \d pg_stat_progress_copy
View "pg_catalog.pg_stat_progress_copy"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+---------
pid | integer | | |
datid | oid | | |
datname | name | | |
relid | oid | | |
command | text | | |
type | text | | |
bytes_processed | bigint | | |
bytes_total | bigint | | |
tuples_processed | bigint | | |
tuples_excluded | bigint | | |
postgres=> \d pg_stat_wal
View "pg_catalog.pg_stat_wal"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
wal_records | bigint | | |
wal_fpi | bigint | | |
wal_bytes | numeric | | |
wal_buffers_full | bigint | | |
wal_write | bigint | | |
wal_sync | bigint | | |
wal_write_time | double precision | | |
wal_sync_time | double precision | | |
stats_reset | timestamp with time zone | | |
postgres=> \d pg_stat_replication_slots
View "pg_catalog.pg_stat_replication_slots"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
slot_name | text | | |
spill_txns | bigint | | |
spill_count | bigint | | |
spill_bytes | bigint | | |
stream_txns | bigint | | |
stream_count | bigint | | |
stream_bytes | bigint | | |
total_txns | bigint | | |
total_bytes | bigint | | |
stats_reset | timestamp with time zone | | |
postgres=> \d pg_locks
View "pg_catalog.pg_locks"
Column | Type | Collation | Nullable | Default
--------------------+--------------------------+-----------+----------+---------
locktype | text | | |
database | oid | | |
relation | oid | | |
page | integer | | |
tuple | smallint | | |
virtualxid | text | | |
transactionid | xid | | |
classid | oid | | |
objid | oid | | |
objsubid | smallint | | |
virtualtransaction | text | | |
pid | integer | | |
mode | text | | |
granted | boolean | | |
fastpath | boolean | | |
waitstart | timestamp with time zone | | |
postgres=> \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
--------------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
session_time | double precision | | |
active_time | double precision | | |
idle_in_transaction_time | double precision | | |
sessions | bigint | | |
sessions_abandoned | bigint | | |
sessions_fatal | bigint | | |
sessions_killed | bigint | | |
stats_reset | timestamp with time zone | | |
postgres=> \d pg_prepared_statements
View "pg_catalog.pg_prepared_statements"
Column | Type | Collation | Nullable | Default
-----------------+--------------------------+-----------+----------+---------
name | text | | |
statement | text | | |
prepare_time | timestamp with time zone | | |
parameter_types | regtype[] | | |
from_sql | boolean | | |
generic_plans | bigint | | |
custom_plans | bigint | | |
postgres=> select * from pg_settings where name='log_recovery_conflict_waits';
-[ RECORD 1 ]---+--------------------------------------
name | log_recovery_conflict_waits
setting | off
unit |
category | Reporting and Logging / What to Log
short_desc | Logs standby recovery conflict waits.
extra_desc |
context | sighup
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
postgres=> select * from pg_settings where name='track_wal_io_timing';
-[ RECORD 1 ]---+--------------------------------------------------
name | track_wal_io_timing
setting | off
unit |
category | Statistics / Query and Index Statistics Collector
short_desc | Collects timing statistics for WAL I/O activity.
extra_desc |
context | superuser
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
postgres=> select * from pg_settings where name='idle_session_timeout';
-[ RECORD 1 ]---+-------------------------------------------------------------------------------
name | idle_session_timeout
setting | 0
unit | ms
category | Client Connection Defaults / Statement Behavior
short_desc | Sets the maximum allowed idle time between queries, when not in a transaction.
extra_desc | A value of 0 turns off the timeout.
context | user
vartype | integer
source | default
min_val | 0
max_val | 2147483647
enumvals |
boot_val | 0
reset_val | 0
sourcefile |
sourceline |
pending_restart | f
postgres=> select * from pg_settings where name='client_connection_check_interval';
-[ RECORD 1 ]---+-------------------------------------------------------------------------------
name | client_connection_check_interval
setting | 0
unit | ms
category | Connections and Authentication / Connection Settings
short_desc | Sets the time interval between checks for disconnection while running queries.
extra_desc |
context | user
vartype | integer
source | configuration file
min_val | 0
max_val | 2147483647
enumvals |
boot_val | 0
reset_val | 0
sourcefile |
sourceline |
pending_restart | f
REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
where option can be one of:
CONCURRENTLY [ boolean ]
TABLESPACE new_tablespace
VERBOSE [ boolean ]
ALTER SUBSCRIPTION name CONNECTION 'conninfo'
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name RENAME TO new_name
postgres=> select * from pg_settings where name='remove_temp_files_after_crash';
-[ RECORD 1 ]---+--------------------------------------------
name | remove_temp_files_after_crash
setting | on
unit |
category | Developer Options
short_desc | Remove temporary files after backend crash.
extra_desc |
context | sighup
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | on
reset_val | on
sourcefile |
sourceline |
pending_restart | f
参考文档：https://github.com/digoal/blog/blob/master/202105/20210513_02.md