随笔分类 - PostgreSQL
摘要：快速创建索引show max_parallel_maintenance_workers;postgres=# show max_parallel_maintenance_workers; max_parallel_maintenance_workers 2(1 row) set max_parall
阅读全文
摘要：数据库字符集查看select datname,pg_encoding_to_char(encoding) as encoding from pg_database;建表时指定字符集CREATE TABLE customers ( id SERIAL PRIMARY KEY, name VARCHAR
阅读全文
摘要：SELECT pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_indexes_size(c.oid)) AS index_size, pg_size_pretty(pg_total_rela
阅读全文
摘要：select * from pg_indexes where tablename = 'table_name';
阅读全文
摘要：查看当前用户 select user； select oid from pg_class where relname='t2';select pid from pg_locks where relation=24627; select * from pg_stat_activity where st
阅读全文
摘要：test=# select * from pg_available_extensions where name like '%prewarm%' order by name; name | default_version | installed_version | comment + + + sys
阅读全文
摘要：1、查看kwr插件是否安装 ksql -U system ncdb \dx 如果没有kwr插件安装 CREATE EXTENSION sys_kwr; 关闭数据库 2、修改kingbase.conf参数 track_counts = on track_sql = on track_io_timing
阅读全文
摘要：查找阻塞者和被阻塞 SELECT blocking_activity.datname as "数据库", blocking_activity.application_name as "持锁会话程序名", blocking_activity.client_addr as "持锁会话地址", now()
阅读全文
摘要：explain analyze verbose select * from t2,t3 where t2.n1=t3.n2; QUERY PLAN Merge Join (cost=85.58..257.60 rows=6950 width=82) (actual time=0.029..0.031
阅读全文
摘要：backend 客户发起的会话进程后台进程进程 描述background writer 在这个过程中，共享缓冲池中的脏页会被逐步地定期写入持久存储(例如，HDD、SSD)。checkpointer 负责执行checkpoint。autovacuum launcher 负责执行autovacuum。W
阅读全文
摘要：基于PGPool的双机集群如下图所示：pg主节点和备节点实现流复制热备，pgpool1，pgpool2作为中间件，将主备pg节点加入集群，实现读写分离，负载均衡和HA故障自动切换。两pgpool节点可以委托一个虚拟ip节点作为应用程序访问的地址，两节点之间通过watchdog进行监控，当pgpool
阅读全文
摘要：postgres=# select * from pg_tables where tablename='t'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowse
阅读全文
摘要：pg_switch_xlog() 是9.6版本下的命令，强制手动切换归档日志 postgres=# select version(); version PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 201203
阅读全文
摘要：逻辑备份 postgres=# select * from t; id | name + 1 | http 2 | qdds (2 rows) postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Ac
阅读全文
摘要：修改postgresql.conf archive_mode =on%p = path of file to archive%f = file name only archive_command ='cp %p /u01/pgsql/archive/pg_%f' 重启postgresql pg_ct
阅读全文
摘要：postgresql11 有2种安装方法：二进制源码安装 、rpm包安装 不过在上述2中安装方式前都要先建用户和组及目录编辑用户环境变量 严格起来还需要设置sysctl.conf and limits.conf rpm安装完毕以后需要从/usr/local/pgsql-11下的东西拷贝到/u01/p
阅读全文
