PostgreSQL FAQ
note
- 无 Foreign Key Arrays
FOREIGN KEY (EACH ELEMENT OF userids) REFERENCES users,
- https://stackoverflow.com/a/50441059/1870054
- https://commitfest.postgresql.org/17/1252/
- S094
- Unsupported Features
- SQL:2016
限制
- db 名字
- 最长 63 byte
- 最多 32767 参数占位 -
?
- 范围为 smallint - https://www.postgresql.org/docs/current/limits.html
unique constraint vs unique index
核心业务语义尽量用 constraint
- unique constraint
unique(tid,entity_id)
->flow_tid_entity_id_key
- 可以延后
- 通过 unique index 实现 - 自动创建
- 附带在 TABLE 上
unique
在 create table 时定义alter table TABLE add unique (tid,rid,cid);
在 create table 之后定义
- 不支持
ADD CONSTRAINT IF NOT EXISTS
- 支持
DROP CONSTRAINT IF EXISTS
- unique index
create unique index on flow(tid,entity_id)
->flow_tid_entity_id_idx
- 可以并发
- 可以 带条件
- 独立 INDEX 概念
- 不能在 create table 时定义 - inline index 概念
- UNIQUE 只是 INDEX 的一个限制
- https://stackoverflow.com/a/6804058/1870054
- add constraint if not exists
- https://stackoverflow.com/questions/23542794
- 都支持 UNIQUE NULLS NOT DISTINCT
- 因为是 INDEX 的能力
TOAST
- TOAST = The Oversized-Attribute Storage Technique
- 一行 2kB - TOAST_TUPLE_THRESHOLD
- 超过先尝试压缩到 2kB - TOAST_TUPLE_TARGET
- 默认 default_toast_compression=pglz, 支持 lz4
- 可按列设置 COMPRESSION
- 压缩不足,则分 chunk 存储到关联的 toast 表
- 超过先尝试压缩到 2kB - TOAST_TUPLE_TARGET
- TOAST 使用 oid 跟踪 - 最多 2^32 = 4 十亿 条记录
- 存储
- PLAIN - 不压缩,无 out-of-line - 超过则异常
- EXTENDED - 压缩+out-of-line
- EXTERNAL - out-of-line
- MAIN - 压缩+尝试尽量不 out-of-line
- 参考
- TOAST storage
- wiki TOAST
-- 默认 EXTENDED
ALTER TABLE users SET STORAGE EXTENDED;
-- 修改 TOAST_TUPLE_TARGET
ALTER TABLE users SET (toast_tuple_target = N);
服务重载
- pg_ctl
su postgres
pg_ctl reload
- sql
psql -U postgres
SELECT pg_reload_conf();
- service
service postgresql restart
维护
- postgres_queries_and_commands.sql - Useful PostgreSQL Queries and Commands
升级
- https://www.xf.is/2019/02/26/convert-postgresql-cluster-to-use-page-checksums/
show data_checksums
可以构造一个包含所有版本的镜像,然后进行升级 - Dockerfile
# https://github.com/tianon/docker-postgres-upgrade
docker run --rm \
-v PGDATAOLD:/var/lib/postgresql/OLD/data \
-v PGDATANEW:/var/lib/postgresql/NEW/data \
tianon/postgres-upgrade:OLD-to-NEW
pg_upgrade --old-datadir /var/lib/pgsql/data/ --new-datadir /var/lib/pgsql/10/data/ \
--old-bindir /usr/bin/ --new-bindir /usr/pgsql-10/bin/
Dump
- pg_dumpall
- https://www.postgresql.org/docs/current/app-pgdump.html
- https://www.postgresql.org/docs/current/app-pgrestore.html
- https://www.percona.com/blog/2019/03/27/postgresql-upgrade-using-pg_dump-pg_restore/
# -Z compress
pg_dump -Fc -Z 9 -j $(nproc) --file=file.dump myDb
pg_restore -Fc -j $(nproc) file.dump
CTID
https://postgresql.verite.pro/blog/2019/04/24/oid-column.html
时区问题
- PG 实际存储的是 UTC 不会存储时区
- 时区信息会用于转换
-- 当前时区
show timezone;
show timezone_abbreviations;
-- 可用时区
SELECT * FROM pg_timezone_names;
-- date 转 tz 应该是正常的
select ('2020-01-02'::date)::TIMESTAMPTZ;
-- session 有效
SET TIME ZONE TO 'UTC';
SET TIMEZONE TO 'UTC';
-- 也可以直接指定 offset
SET timezone=-4;
-- 当前时间
-- UTC 可以使用 localtime
SELECT LOCALTIMESTAMP AT TIME ZONE 'Asia/Shanghai';
-- timestamp 不显示 TZ 信息
SELECT NOW()::TIMESTAMP;
-- current_timestamp 是 timestamptz
-- 可以提取 tz 信息 秒、小时
select current_timestamp,
pg_typeof(current_timestamp),
extract(timezone from current_timestamp::timestamptz),
extract(timezone_h from current_timestamp::timestamptz)
;
-- db 配置
ALTER SYSTEM SET timezone = 'UTC';
-- 修改角色时区
ALTER ROLE my_role SET TIMEZONE = '+1';
NULL 字符 / \0
字符
- PG 不允许字符串包 含
\0
- 传入之前替换或用 bytea 存储
- JSON 也不允许包含空字符
unsupported Unicode escape sequence
一般是因为 \u0000
, 替换掉即可, pg 的字符串不支持 \u0000
切换用户
set role user;