Postgresql

# 创建db
createdb -U <username> <dbName>
# 删除b
dropdb -U <username> <dbName>
# 重建索引
reindexdb -d <dbName>
# 对数据库物理文件垃圾回收
vacuumdb <dbName>
# 清除数据库中未引用的大对象
vacuumlo <dbName>

pgdump
pgrestore
pgbench
# 登录数据库
psql [-h HOST] [-p PORT] DB [USERNAME]
# 获取pg配置
pg_config
# 获取数据库状态
pg_ctl -D <data-dir> status
# 启动数据库
postgres -D <data-dir> &
# 关闭数据库
pg_ctl -D <data-dir> -m [fast|smart|immediate]  -t <timeout> stop

-- 计算字符串中的字符数
select char_length('abcd'); -- 4
-- 计算字符串占用的字节数
select octet_length('abcd'); -- 4
-- 获取字符在字符串中的位置, 位置从1开始
select position('bc' in 'abcd'); -- 2
-- 提取字符串中的子串
select substring('abcd' from 2 for 3); -- bcd
-- 分割字符串
select split_part('abc,def,ghi', ',', 2); -- def

-- 时间字段提取
select extract(year from '2019-07-15:12:34:56'::timestamp); -- 2019

-- 数组两种形式
select array[1,2,3];
select '{1,2,3}';
-- 获取数组指定下标元素, 位置从1开始
select arr[1] from (select array[1,2,3] arr) a; -- 1
-- 数组追加元素
select array_append(array[1,2,3],4); -- {1,2,3,4}
select array[1,2,3]||4;
select array[1,2,3] || array[1,2,3]; -- {1,2,3,1,2,3}
-- 数组删除元素
select array_remove(array[1,2,2,2,3],2); -- {1,3}
-- 判断数组是否相等
select array[1,2,3] = array [1,2,2,2,3]; -- false
-- 判断数组是否不相等
select array[1,2,3] <> array [1,2,2,2,3]; -- true
-- 比较数组
select array[1,2,3] <= array[2,1]; -- true
select array[1,2,3] >= array[2,1]; -- false
-- 判断数组包含关系
select array[1,2,3] @> array[1]; -- true
select array[1,2,3] <@ array[1,2,3,4,5]; -- true
-- 判断数组是否有公共元素
select array[1,2,3] && array[22]; -- false
-- 获取数组维度
select array_dims(array[[4],[3],[2],[1]]); -- [1:4] [1:1]
-- 获取数组指定维度的长度
select array_length(array[1,2,3,4],1); -- 3
select array_length(array[[4],[3],[2],[1]],2); -- 1
-- 获取数组某一个元素第一次出现的位置, 位置从1开始
select array_position(array[1,2,3],3); -- 3
-- 替换数组指定元素
select array_replace(array[1,2,3],2,11); -- {1,11,3}
-- 数组转为字符串
select array_to_string(array[1,2,3,null],',','99'); -- 1,2,3

-- 范围
select int4range(1,10,'[]'); -- [1,11)
select daterange('2019-06-01','2019-07-02'); -- [2019-06-01,2019-07-02)
-- 获取范围下界
select lower(int4range(1,10));
-- 获取范围上界
select upper(int4range(1,10));
-- 判断范围是否为空
select isempty(int4range(1,10));

-- json表示
select '{"a":1}'::json;
-- json字段值获取
select j -> 'a' from ( select '{"a":1}'::json j) sub; -- 1
-- 提取json中的键值对
select * from json_each('{"a":1,"b":2}'::json); -- a 1 b 2
select * from json_each_text('{"a":"aaa","b":2}'::json); -- a 1 b 2
-- 删除jsonb中的key
select '{"a":1,"b":2}'::jsonb - 'a'; -- {"b":2}
-- 判断key是否为顶层key
select '{"a":1,"b":2, "c":{"d":4}}'::jsonb ? 'd'; -- false
-- 获取json所有key
select json_object_keys('{"a":1,"b":2}'); -- a b
-- 删除json指定key
select '{"a":1,"b":2, "c":{"d":4}}'::jsonb - 'a'; -- {"b": 2, "c": {"d": 4}}
-- 删除json嵌套key
select '{"a":1,"b":2, "c":{"d":4}}'::jsonb #- '{c,d}'::text[];

pg_archivecleanup -d <archive_location> <oldest_kept_walfile>

# 固定参数
listen_addresses = '*'
superuser_reserved_connections = 10
unix_socket_directories = '., /tmp'
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
huge_pages = try
dynamic_shared_memory_type = posix
vacuum_cost_delay = 0
vacuum_cost_limit = 10000
bgwriter_delay = 10ms
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 10.0
bgwriter_flush_after = 512kB
effective_io_concurrency = 0
max_worker_processes = 256
parallel_leader_participation = on
old_snapshot_threshold = 6h
wal_level = replica
synchronous_commit = off
full_page_writes = on
wal_compression = on
wal_buffers = 16MB
wal_writer_delay = 10ms
wal_writer_flush_after = 1MB
checkpoint_timeout = 30min
checkpoint_completion_target = 0.2
checkpoint_flush_after = 256kB
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 64
max_replication_slots = 64
hot_standby = on
max_standby_archive_delay = 120s
max_standby_streaming_delay = 120s
wal_receiver_status_interval = 1s
hot_standby_feedback = off
max_logical_replication_workers = 64
enable_partitionwise_join = on
enable_partitionwise_aggregate = on
random_page_cost = 1.1
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 1GB
log_min_duration_statement = 5s
log_checkpoints = on
log_connections = off
log_disconnections = off
log_error_verbosity = verbose
log_lock_waits = on
log_statement = 'ddl'
log_temp_files = 256MB
track_io_timing = on
track_functions = pl
autovacuum = on
log_autovacuum_min_duration = 0
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01
autovacuum_freeze_max_age = 1200000000
autovacuum_multixact_freeze_max_age = 1250000000
autovacuum_vacuum_cost_delay = 0ms
idle_in_transaction_session_timeout = '6h'
vacuum_freeze_table_age = 200000000
vacuum_multixact_freeze_table_age = 200000000
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'pg_stat_statements'
deadlock_timeout = 1s
log_timezone = 'PRC'
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'

# 动态参数(以16C64G为例)
max_connections = 3200                 # 物理内存(GB)*1000*(1/4)/5
# 共享内存, 读取数据后缓存在此处
shared_buffers = 16GB                  # IF use hugepage: 主机内存*(1/4)   ELSE: min(32GB, 主机内存*(1/4))
max_prepared_transactions = 3200       # max_prepared_transactions=max_connections
# 单个SQL用于排序, HASH JOIN的内存区域
work_mem = 16MB                        # max(min(物理内存/4096, 64MB), 4MB)
maintenance_work_mem = 1GB             # min( 8G, (主机内存*1/8)/max_parallel_maintenance_workers )
autovacuum_work_mem = 1GB              # min( 8G, (主机内存*1/8)/autovacuum_max_workers )
max_parallel_maintenance_workers = 8   # min( max(2, CPU核数/2) , 16 )
max_parallel_workers_per_gather = 12   # min( max(2, CPU核数-4) , 24 )
max_parallel_workers = 12              # max(2, CPU核数-4)
max_wal_size = 32GB                    # shared_buffers*2
min_wal_size = 8GB                     # shared_buffers/2
max_sync_workers_per_subscription = 12 # min ( 32 , max(2, CPU核数-4) )
effective_cache_size = 32GB            # 主机内存/2
autovacuum_max_workers = 8             # max(min( 8 , CPU核数/2 ) , 5)