Postgres DB demo
- Table of content:
Reference:
Guidelines
============ env ============
PG_HOME=/global/ocean/data/ocean/postgre
PG_BIN=$PG_HOME/current/bin
PGLOG=$PG_HOME/data/logs/pg96
PGLOG_FILE=$PGLOG/postgre_server.log
PGDATA=$PG_HOME/data/pg96
============ control ============
${PG_BIN}/initdb -U $PGUSER -W #run once
${PG_BIN}/pg_ctl -l $PGLOG_FILE start
#${PG_BIN}/pg_ctl -l $PGLOG_FILE -o "-F -p 5480" start
${PG_BIN}/pg_ctl -l $PGLOG_FILE restart
${PG_BIN}/pg_ctl status
${PG_BIN}/pg_ctl -l $PGLOG_FILE stop -m fast
============ create ============
${PG_BIN}/createdb -O $PGUSER $PGDATABASE
${PG_BIN}/createuser -P -E -e $PGUSER
${PG_BIN}/dropuser $PGUSER
${PG_BIN}/dropdb $PGDATABASE
============ login psql ============
psql -U username -h hostname -p port -d dbname
PGUSER=admin
PGPASSWORD=password
PGDATABASE=meta_int
============ ops in psql =============
help: \h
quit: \q
list DB: \l
list tables: \dt *.* (all table of all schema)
list index: \di
lsit schema: \dn
switch DB: \c dbname:
desc table: \d tblname
alter table [tblname] add column [col_name] [type];
install DB
// find rpm
ls -lt /net/stealth.nj.ssmb.com/export1/home1.localhost/sw/Linux | grep -i postgre
// install
rpm -ivh postgresql96-libs-9.6.15-1PGDG.rhel6.x86_64.rpm
rpm -ivh postgresql96-9.6.15-1PGDG.rhel6.x86_64
rpm -ivh postgresql96-server-9.6.15-1PGDG.rhel6.x86_64
ln -s /usr/pgsql-9.6/bin $PG_HOME/current
// config file:
$PGDATA\pg_hba.conf
$PGDATA\postgresql.conf
// uninstall
rpm -e postgresql96-server-9.6.15-1PGDG.rhel6.x86_64
PG features
connect local
- connect by vscode sqltools:
[图片上传失败...(image-fc2ec7-1572518730678)]
jdbc_url：jdbc:postgresql://oceanap02d.nam.nsroot.net:5432/meta_int
url: postgresql://oceanap02d.nam.nsroot.net:5432/meta_int
username: admin
password: password
Json support
there are 2 types to save json data:
Json type: will save as document
Jsonb type: has only useful value for higher performance (GIN index)
- Jsonb/Json query ops
[图片上传失败...(image-7e61ea-1572518730678)]
- Jsonb specially supported query
[图片上传失败...(image-ba7b0e-1572518730678)]
- example
-- @block return json object
select '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json->'tags' as tags;
-- @block return text
select '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json->>'tags' as tags;
-- @block path select: will return "bar"
select '{"a": [1, "bar", 2], "tags": {"a": 1, "b": null}}'::json#>'{a,1}';
-- @block contain value
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb @> '{"nickname": "gs"}'::jsonb;
--select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::jsonb->'nickname' = '"gs"';
-- @block select content of accounts_jsonb
-- @conn PGSQL
select content from test.accounts_jsonb
-- @block contain nickname = 'cowww'
-- @conn PGSQL
select content from test.accounts_jsonb where content @> '{"nickname" : "cowww"}'
-- @block select content tags contain 'scala'
-- @conn PGSQL
select content from test.accounts_jsonb where content::jsonb->'tags' ? 'scala'
-- @block map normal row to json object
select row_to_json(company) from public.company;
- more details refer to: here
Server push
可以将关于特定主题的消息广播给正在监听该主题的所有连接订户。
消息由Postgres服务器推送到侦听客户端。不需要轮询，
但您的数据库驱动程序应支持异步向应用程序传递通知。
- SQL
-- create a listener on a channel
LISTEN 'my_channel';
-- send message to channel
NOTIFY 'my_channel', 'my message text';
-- 结合 trigger 触发器 pg_notify:
SELECT pg_notify('foo_events', '{"userid":42,"action":"grok"}');
- Java client
connection.notificationHandler(notification -> {
System.out.println("Received " + notification.getPayload() +
" on channel " + notification.getChannel());
});
connection.query("LISTEN some-channel", ar -> {
System.out.println("Subscribed to channel");
});
Table inherit
- example
CREATE TABLE invoices (
invoice_number int NOT NULL PRIMARY KEY,
issued_on date NOT NULL DEFAULT now()
);
CREATE TABLE government_invoices (
department_id text NOT NULL
) INHERITS (invoices);
PARTITION 拆分表: postgreSQL 10
将一个逻辑的大表，分割成物理的小表。从而获得几点优势：
1.提高查询性能，特别是对于某些只落在某一段区间的查询；分块可以减少索引的大小，从而可以全部放在内存中
2.当查询访问某一块的大部分数据时，原来需要随机访问的，现在可以seq scan；避免随机扫描
3.块操作（加载或者删除）都可以直接对partition操作
ALTER TABLE DETACH PARTITION或者直接 DROP TABLE）进行处理，避免了vacuum的负载
4.冷热数据分离存储
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
CREATE TABLE measurement_y2008m01 PARTITION OF measurement
FOR VALUES FROM ('2008-01-01') TO ('2008-02-01')
WITH (parallel_workers = 4)
TABLESPACE fasttablespace;
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');
CREATE TABLE measurement_y2006m03 PARTITION OF measurement
FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
postgres_fdw 外包装数据
Hash，GIN, BRIN index
Array type 数组类型， range 区间类型
https://www.kancloud.cn/hfpp2012/postgresqlbook/467209
