SRE文档1,查看正在执行的sql
sql
postgres=# select pid,usename,substring(query from 0 for 50),now()-query_start as time,wait_event,state from pg_stat_activity where state = 'active';
pid | usename | substring | time | wait_event | state
------+----------+---------------------------------------------------+----------+------------+--------
7962 | postgres | select pid,usename,substring(query from 0 for 50) | 00:00:00 | | active
(1 row)postgres=# select pid,usename,substring(query from 0 for 50),now()-query_start as time,wait_event,state from pg_stat_activity where state = 'active';
pid | usename | substring | time | wait_event | state
------+----------+---------------------------------------------------+----------+------------+--------
7962 | postgres | select pid,usename,substring(query from 0 for 50) | 00:00:00 | | active
(1 row)1.1查看执行超过2秒的sql
sql
postgres=# select pid,state,query_start,xact_start,now()-query_start,wait_event_type,wait_event,substring(query from 0 for 50) from pg_stat_activity where now()-query_start > '2 s' order by query_start;
pid | state | query_start | xact_start | ?column? | wait_event_type | wait_event | substring
-----+-------+-------------+------------+----------+-----------------+------------+-----------
(0 rows)postgres=# select pid,state,query_start,xact_start,now()-query_start,wait_event_type,wait_event,substring(query from 0 for 50) from pg_stat_activity where now()-query_start > '2 s' order by query_start;
pid | state | query_start | xact_start | ?column? | wait_event_type | wait_event | substring
-----+-------+-------------+------------+----------+-----------------+------------+-----------
(0 rows)1.2根据PID杀会话
select pg_cancel_backend(pid);
或者
select pg_terminate_backend(pid);select pg_cancel_backend(pid);
或者
select pg_terminate_backend(pid);这两个函数区别如下：
pg_cancel_backend()
- 只能关闭当前用户下的后台进程
- 向后台发送SIGINT信号，用于关闭事务，此时session还在，并且事务回滚
pg_terminate_backend()
- 需要superuser权限，可以关闭所有的后台进程
- 向后台发送SIGTERM信号，用于关闭事务，此时session也会被关闭，并且事务回滚
1.3查看锁问题
创建为视图
sql
create view v_locks_monitor as
with
t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.virtualxid is not distinct from w.virtualxid and
r.transactionid is not distinct from w.transactionid and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.pid <> w.pid
)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||
'SQL (Current SQL in Transaction): '||chr(10)||
case when query is null then 'NULL' else query::text end,
chr(10)||'--------'||chr(10)
order by
( case mode
when 'INVALID' then 0
when 'AccessShareLock' then 1
when 'RowShareLock' then 2
when 'RowExclusiveLock' then 3
when 'ShareUpdateExclusiveLock' then 4
when 'ShareLock' then 5
when 'ShareRowExclusiveLock' then 6
when 'ExclusiveLock' then 7
when 'AccessExclusiveLock' then 8
else 0
end ) desc,
(case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;create view v_locks_monitor as
with
t_wait as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,
a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,
b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name
from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted
),
t_overlap as
(
select r.* from t_wait w join t_run r on
(
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.virtualxid is not distinct from w.virtualxid and
r.transactionid is not distinct from w.transactionid and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.pid <> w.pid
)
),
t_unionall as
(
select r.* from t_overlap r
union all
select w.* from t_wait w
)
select locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,
string_agg(
'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||
'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||
'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||
'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||
'SQL (Current SQL in Transaction): '||chr(10)||
case when query is null then 'NULL' else query::text end,
chr(10)||'--------'||chr(10)
order by
( case mode
when 'INVALID' then 0
when 'AccessShareLock' then 1
when 'RowShareLock' then 2
when 'RowExclusiveLock' then 3
when 'ShareUpdateExclusiveLock' then 4
when 'ShareLock' then 5
when 'ShareRowExclusiveLock' then 6
when 'ExclusiveLock' then 7
when 'AccessExclusiveLock' then 8
else 0
end ) desc,
(case when granted then 0 else 1 end)
) as lock_conflict
from t_unionall
group by
locktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;- 查看
sql
postgres=# \x
Expanded display is on.
pos
postgres=# select * from v_locks_monitor ;postgres=# \x
Expanded display is on.
pos
postgres=# select * from v_locks_monitor ;1.4查看当前连接数
sql
#查看配置文件大小
show max_connections;
select count(*), usename from pg_stat_activity group by usename;
#或者
SELECT * FROM pg_stat_activity;
SELECT COUNT(*) from pg_stat_activity;
select min_val, max_val from pg_settings where name='max_connections';#查看配置文件大小
show max_connections;
select count(*), usename from pg_stat_activity group by usename;
#或者
SELECT * FROM pg_stat_activity;
SELECT COUNT(*) from pg_stat_activity;
select min_val, max_val from pg_settings where name='max_connections';查询空闲连接
sql
postgres=# select pid, state from pg_stat_activity ;
pid | state
-------+--------
7229 |
7232 |
13307 | idle
15853 | idle
16566 | active
30648 | idle
18090 | idle
10438 | idle
17360 | idle
24003 | idle
#查询下
select * from pg_settings where name = 'idle_in_transaction_session_timeout' \gx
----设置释放空闲事务退出时间
alter system set idle_in_transaction_session_timeout=30000postgres=# select pid, state from pg_stat_activity ;
pid | state
-------+--------
7229 |
7232 |
13307 | idle
15853 | idle
16566 | active
30648 | idle
18090 | idle
10438 | idle
17360 | idle
24003 | idle
#查询下
select * from pg_settings where name = 'idle_in_transaction_session_timeout' \gx
----设置释放空闲事务退出时间
alter system set idle_in_transaction_session_timeout=300002，日常清理
2.1日常清理（VACUUM）
在PostgreSQL中，使用delete和update语句删除或更新的数据行并没有被实际删除，而只是在旧版本数据行的物理地址上将该行的状态置为已删除或已过期。因此当数据表中的数据变化极为频繁时，那么在一段时间之后该表所占用的空间将会变得很大，然而数据量却可能变化不大。要解决该问题，需要定期对数据变化频繁的数据表执行VACUUM操作
VACUUM的变体：标准VACUUM和VACUUM FULL。
VACUUM FULL可以收回更多磁盘空间但是运行起来更慢。VACUUM FULL要求在其工作的表上得到一个排他锁，因此无法和对此表的其他使用并行。 标准形式的VACUUM可以和生产数据库操作并行运行（SELECT、INSERT、UPDATE和DELETE等命令将继续正常工作，但在清理期间你无法使ALTER TABLE。
VACUUM[( { FULL | FREEZE | VERBOSE | ANALYZE| DISABLE_ PAGE_ SKIPPING }[,..] )][ table_ name [ (column_ name [,..] ) ]]
VACUUM[ FULL ][ FREEZE ] [ VERBOSE ] [ table_ name ]
VACUUM [ FULL][ FREEZE ][ VERBOSE ] ANALYZE [ table name [ (column name[...])]]
FULL ------选择"完全"清理，这样可以恢复更多的空间， 但是花的时间更多并且在表上施加了排它锁。
FREEZE ---------选择激进的元组"冻结"。
VERBOSE --------- 为每个表打印一份详细的清理工作报告。
ANALYZE --------- 更新用于优化器的统计信息，以决定执行查询的最有效方法。
table ------- 要清理的表的名称（可以有模式修饰）。缺省时是当前数据库中的所有表。
column ---------要分析的具体的列/字段名称。缺省是所有列/字段
使用客户端vacuumdb命令
完全清理与统计更新postgres数据库。
[postgres@Redhat7 ~]$ vacuumdb --full --verbose --analyze postgres;
标准清理与统计更新postgres数据库。
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze postgres;
统计更新postgres数据库。
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze-only postgres;
完全清理与统计更新postgres数据库中的test1表。
[postgres@Redhat7 ~]$ vacuumdb --full --verbose --analyze test1;
vacuumdb: could not connect to database test1: FATAL: database "test1" does not exist
[postgres@Redhat7 ~]$ vacuumdb --full --verbose --analyze --table test1 postgres;
vacuumdb: vacuuming database "postgres"
INFO: vacuuming "public.test1"
INFO: "test1": found 0 removable, 4194816 nonremovable row versions in 28343 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.74 s, system: 0.47 s, elapsed: 2.08 s.
INFO: analyzing "public.test1"
INFO: "test1": scanned 22675 of 22675 pages, containing 4194816 live rows and 0 dead rows; 30000 rows in sample, 4194816 estimated total rows
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze-only --table test1 postgres;
vacuumdb: vacuuming database "postgres"
INFO: analyzing "public.test1"
INFO: "test1": scanned 22675 of 22675 pages, containing 4194816 live rows and 0 dead rows; 30000 rows in sample, 4194816 estimated total rows
标准清理与统计更新postgres数据库中的test1表。
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze --table test1 postgres;
统计更新postgres数据库中的test1表。
[postgres@Redhat7 ~]$ vacuumdb --verbose --analyze-only --table test1 postgres;
vacuumdb: vacuuming database "postgres"
INFO: analyzing "public.test1"
INFO: "test1": scanned 22675 of 22675 pages, containing 4194816 live rows and 0 dead rows; 30000 rows in sample, 4194816 estimated total rows
psql vacuum 命令
完全清理并统计更新数据库
postgres=# vacuum full verbose analyze;postgres=# vacuum full verbose analyze;标准清理并统计更新数据库
postgres=# vacuum verbose analyze;postgres=# vacuum verbose analyze;统计更新数据库
postgres=# analyze;postgres=# analyze;ANALYZE
完全清理并统计更新表test1
postgres=# vacuum full verbose analyze test1;postgres=# vacuum full verbose analyze test1;标准清理并统计更新表test1
postgres=# vacuum verbose analyze test1;postgres=# vacuum verbose analyze test1;统计更新表
postgres=# analyze test1;
ANALYZEpostgres=# analyze test1;
ANALYZEcheckpoint
checkpoint执行控制:
1,数据量达到checkpoint_segments*16M时，系统自动触发；
2,时间间隔达到checkpoint_timeout参数值时；
3,用户发出checkpoint命令时
3,查看Postgresql数据库表大小及物理位置
use_db=# create table emp1(id int,name char(10));
use_db=# insert into emp1 values (generate_series(1,100000),'test');
INSERT 0 100000
#查看单个表大小
use_db=# select pg_size_pretty(pg_relation_size('表名字'));
pg_size_pretty
----------------
43 MB
(1 row)
#查看单个表位置
d_qe=# select pg_relation_filepath('表名字');
pg_relation_filepath
----------------------
base/16385/19800
(1 row)
#查看物理位置
use_db=# \! du -sh /usr/local/pg12/data/base/16384/25386
43M /usr/local/pg9.5.2/data/base/16384/25386
#清空表
use_db=# truncate table 表名字;
TRUNCATE TABLEuse_db=# create table emp1(id int,name char(10));
use_db=# insert into emp1 values (generate_series(1,100000),'test');
INSERT 0 100000
#查看单个表大小
use_db=# select pg_size_pretty(pg_relation_size('表名字'));
pg_size_pretty
----------------
43 MB
(1 row)
#查看单个表位置
d_qe=# select pg_relation_filepath('表名字');
pg_relation_filepath
----------------------
base/16385/19800
(1 row)
#查看物理位置
use_db=# \! du -sh /usr/local/pg12/data/base/16384/25386
43M /usr/local/pg9.5.2/data/base/16384/25386
#清空表
use_db=# truncate table 表名字;
TRUNCATE TABLEsql
--当前数据库服务器ip和port
postgres=# select inet_server_addr(),inet_server_port();
inet_server_addr | inet_server_port
------------------+------------------
127.0.0.1 | 5532
(1 row)--当前数据库服务器ip和port
postgres=# select inet_server_addr(),inet_server_port();
inet_server_addr | inet_server_port
------------------+------------------
127.0.0.1 | 5532
(1 row)4.postgresql数据库统计对象大小
sql
--查看数据库大小，不计算索引
select pg_size_pretty(pg_database_size('mydb'));
--查看数据库大小，包含索引
select pg_size_pretty(pg_total_size('mydb'));
--查看表中索引大小
select pg_size_pretty(pg_indexes_size('table_name'));
--查看表大小,不包括索引
select pg_size_pretty(pg_relation_size('test_1'));
--or
\dt+ test_1
--查看表大小,包括索引
select pg_size_pretty(pg_total_relation_size('test_1'));
--查看某个模式大小，包括索引。不包括索引可用pg_relation_size
select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb" from pg_tables where schemaname='mysch' group by 1;
--查看表空间大小
select pg_size_pretty(pg_tablespace_size('pg_global'));
--查看表对应的数据文件
select pg_relation_filepath('test_1');
--切换log日志文件到下一个
select pg_rotate_logfile();
--切换日志
select pg_switch_xlog();
checkpoint--查看数据库大小，不计算索引
select pg_size_pretty(pg_database_size('mydb'));
--查看数据库大小，包含索引
select pg_size_pretty(pg_total_size('mydb'));
--查看表中索引大小
select pg_size_pretty(pg_indexes_size('table_name'));
--查看表大小,不包括索引
select pg_size_pretty(pg_relation_size('test_1'));
--or
\dt+ test_1
--查看表大小,包括索引
select pg_size_pretty(pg_total_relation_size('test_1'));
--查看某个模式大小，包括索引。不包括索引可用pg_relation_size
select schemaname,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Mb" from pg_tables where schemaname='mysch' group by 1;
--查看表空间大小
select pg_size_pretty(pg_tablespace_size('pg_global'));
--查看表对应的数据文件
select pg_relation_filepath('test_1');
--切换log日志文件到下一个
select pg_rotate_logfile();
--切换日志
select pg_switch_xlog();
checkpoint| 函数名 | 返回类型 | 描述 |
|---|---|---|
| pg_column_size(any) | int | 存储一个指定的数值需要的字节数（可能压缩过） |
| pg_database_size(oid) | bigint | 指定OID的数据库使用的磁盘空间 |
| pg_database_size(name) | bigint | 指定名称的数据库使用的磁盘空间 |
| pg_indexes_size(regclass) | bigint | 关联指定表OID或表名的表索引的使用总磁盘空间 |
| pg_relation_size(relation regclass, fork text) | bigint | 指定OID或名的表或索引，通过指定fork('main', 'fsm' 或'vm')所使用的磁盘空间 |
| pg_relation_size(relation regclass) | bigint | pg_relation_size(..., 'main')的缩写 |
| pg_size_pretty(bigint) | text | Converts a size in bytes expressed as a 64-bit integer into a human-readable format with size units |
| pg_size_pretty(numeric) | text | 把以字节计算的数值转换成一个人类易读的尺寸单位 |
| pg_table_size(regclass) | bigint | 指定表OID或表名的表使用的磁盘空间，除去索引（但是包含TOAST，自由空间映射和可视映射） |
| pg_tablespace_size(oid) | bigint | 指定OID的表空间使用的磁盘空间 |
| pg_tablespace_size(name) | bigint | 指定名称的表空间使用的磁盘空间 |
| pg_total_relation_size(regclass) | bigint | 指定表OID或表名使用的总磁盘空间，包括所有索引和TOAST数据 |
4.1 查询数据库大小
sql
--查询数据库大小
postgres=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
datname | pg_size_pretty
-----------+----------------
postgres | 8033 kB
han_db | 7993 kB
template1 | 7825 kB
template0 | 7825 kB
test | 7993 kB
test01 | 7993 kB
(6 rows)
--或者
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20 ;--查询数据库大小
postgres=# select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;
datname | pg_size_pretty
-----------+----------------
postgres | 8033 kB
han_db | 7993 kB
template1 | 7825 kB
template0 | 7825 kB
test | 7993 kB
test01 | 7993 kB
(6 rows)
--或者
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_database d
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20 ;4.2 查看表大小
sql
--默认只列出当前模式下的表
\d
--列出常用模式
\dn
--列出所有模式
\dnS
--查看表的大小
test01=# select pg_size_pretty(pg_relation_size('table_name'));
pg_size_pretty
----------------
8192 bytes
(1 row)
--或者
test01=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+------------+-------------
public | adg | table | postgres | 8192 bytes |
(1 row)
--info
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname OPERATOR(pg_catalog.~) '^(test)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;--默认只列出当前模式下的表
\d
--列出常用模式
\dn
--列出所有模式
\dnS
--查看表的大小
test01=# select pg_size_pretty(pg_relation_size('table_name'));
pg_size_pretty
----------------
8192 bytes
(1 row)
--或者
test01=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+------------+-------------
public | adg | table | postgres | 8192 bytes |
(1 row)
--info
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','s','')
AND n.nspname !~ '^pg_toast'
AND c.relname OPERATOR(pg_catalog.~) '^(test)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;-- 查看当前库sehcma大小,并按schema大小排序
sql
SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint) as "disk space",
round((sum(table_size) / pg_database_size(current_database())) * 100,2)
as "percent(%)"
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY "percent(%)" desc;SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint) as "disk space",
round((sum(table_size) / pg_database_size(current_database())) * 100,2)
as "percent(%)"
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_total_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace
ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY "percent(%)" desc;-- 查看当前库中所有表大小,并按降序排列
sql
SELECT
table_catalog AS database_name,
table_schema AS schema_name,
table_name,
pg_size_pretty(relation_size) AS table_size
FROM (
SELECT
table_catalog,
table_schema,
table_name,
pg_total_relation_size(('"' || table_schema || '"."' || table_name || '"')) AS relation_size
FROM information_schema.tables
WHERE table_schema not in ('pg_catalog', 'public', 'public_rb', 'topology', 'tiger', 'tiger_data', 'information_schema')
ORDER BY relation_size DESC
)
AS all_tables
WHERE relation_size >= 1073741824;SELECT
table_catalog AS database_name,
table_schema AS schema_name,
table_name,
pg_size_pretty(relation_size) AS table_size
FROM (
SELECT
table_catalog,
table_schema,
table_name,
pg_total_relation_size(('"' || table_schema || '"."' || table_name || '"')) AS relation_size
FROM information_schema.tables
WHERE table_schema not in ('pg_catalog', 'public', 'public_rb', 'topology', 'tiger', 'tiger_data', 'information_schema')
ORDER BY relation_size DESC
)
AS all_tables
WHERE relation_size >= 1073741824;或者
sql
-- 查看当前库中所有表大小
SELECT
table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC ;-- 查看当前库中所有表大小
SELECT
table_schema || '.' || table_name AS table_full_name,
pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC ;4.3 查看模式大小,表，索引
sql
--查看模式大小,表，索引
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='public' group by 1;
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||indexname))/1024/1024) "Size_MB" from pg_indexes where schemaname='public' group by 1;--查看模式大小,表，索引
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='public' group by 1;
select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||indexname))/1024/1024) "Size_MB" from pg_indexes where schemaname='public' group by 1;4.4修改表名字
sql
alter table old_test rename to test_new;alter table old_test rename to test_new;4.5查看oid
sql
han=# select pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/16388/16393
(1 row)han=# select pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/16388/16393
(1 row)5，数据库管理相关
--单用户启动 postgres --single -D /pgdata/10/data postgres
--单用户作用
当多用户模式不接收所有命令时，可以使用单用户连接到数据库
initdb的阶段
修复系统表当多用户模式不接收所有命令时，可以使用单用户连接到数据库
initdb的阶段
修复系统表--相关参数设置
select * from pg_settings;
--相关参数描述,单位
select name,short_desc,unit from pg_settings limit 4;
--参数类别
--internal:这些参数是只读参数，其中有些参数是postgres程序写死的。
--postmaster:改变这些参数值需要重启实例。
--sighup:在postgresql.conf文件中改变这些参数值，无需重启数据库，只需向postmater进程发送SIGHUP信号，让其重启装载配置新的参数值就可以了。
--backend:跟sighup类似，改变生效只适用于新的链接，已有连接中无效.select pg_reload_conf();
--superuser:这类参数可以由超级用户使用set改变，session级别。
--user:同supperuser类似。
--查看参数修改是否需要重启
select name,context from pg_settings where name like 'wal_buffers';
--连接数据库的相关参数
max_connections:默认100，允许和数据库连接的最大并发连接数
superuser_reserved_connections:默认3，防止普通用户消耗掉所有连接，确保超级用户可以连接数据库。
--空闲180秒后尝试连接，每隔10秒连接一次，最多连接三次，尝试失败后关闭连接
tcp_keepalives_idle=180
tcp_keepalives_interval=10
tcp_keepalives_count=3
--内存配置
shared_buffers:通常设置系统内存的25%，max_connections(MB) 数值的两倍
temp_buffers:用于临时表
work_mem:排序、hash
maintenance_work_mem:维护操作中使用的最大内存
synchronous_commit:提交事务是否需要等wal日志写入磁盘再返回。默认on
--日志相关
logging_collector=on --打开日志
log_min_duration_statement --如果如果某个sql运行大于多少毫秒，记录到日志
log_min_error_statement -- sql错误信息可以记录到日志中
log_statement -- 是否记录ddl、dml等操作--相关参数设置
select * from pg_settings;
--相关参数描述,单位
select name,short_desc,unit from pg_settings limit 4;
--参数类别
--internal:这些参数是只读参数，其中有些参数是postgres程序写死的。
--postmaster:改变这些参数值需要重启实例。
--sighup:在postgresql.conf文件中改变这些参数值，无需重启数据库，只需向postmater进程发送SIGHUP信号，让其重启装载配置新的参数值就可以了。
--backend:跟sighup类似，改变生效只适用于新的链接，已有连接中无效.select pg_reload_conf();
--superuser:这类参数可以由超级用户使用set改变，session级别。
--user:同supperuser类似。
--查看参数修改是否需要重启
select name,context from pg_settings where name like 'wal_buffers';
--连接数据库的相关参数
max_connections:默认100，允许和数据库连接的最大并发连接数
superuser_reserved_connections:默认3，防止普通用户消耗掉所有连接，确保超级用户可以连接数据库。
--空闲180秒后尝试连接，每隔10秒连接一次，最多连接三次，尝试失败后关闭连接
tcp_keepalives_idle=180
tcp_keepalives_interval=10
tcp_keepalives_count=3
--内存配置
shared_buffers:通常设置系统内存的25%，max_connections(MB) 数值的两倍
temp_buffers:用于临时表
work_mem:排序、hash
maintenance_work_mem:维护操作中使用的最大内存
synchronous_commit:提交事务是否需要等wal日志写入磁盘再返回。默认on
--日志相关
logging_collector=on --打开日志
log_min_duration_statement --如果如果某个sql运行大于多少毫秒，记录到日志
log_min_error_statement -- sql错误信息可以记录到日志中
log_statement -- 是否记录ddl、dml等操作--常用管理命令
select version();
--数据库启动时间
select pg_postmater_start_time();
--查看最后load配置文件时间
select pg_conf_load_time();
--查看参数配置
select current_settlings('shared_buffers'); show shared_buffers;
--查看当前正在写的wal
select pg_xlogfile_name(pg_current_xlog_location());
--查看当前wal的buffer中有多少字节没有写入到磁盘中
select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
--查看数据库状态
select pg_is_in_recovery();
--查看数据库大小
sql
select pg_size_pretty(pg_relation_size('ipdb2')); --表大小
select pg_size_pretty(pg_total_relation_size('ipdb2')); --总大小select pg_size_pretty(pg_relation_size('ipdb2')); --表大小
select pg_size_pretty(pg_total_relation_size('ipdb2')); --总大小--查看表空间大小
select pg_size_pretty(pg_tablespace_size('pg_global'));
--kill长时间运行sql id
select pid,usename,query_start,query from pg_stat_activity;
select pg_cancel_backend(567);
select pg_terminate_backend(567);select pid,usename,query_start,query from pg_stat_activity;
select pg_cancel_backend(567);
select pg_terminate_backend(567);6,自定义PostgreSQL登录提示符
postgres@postgres=>\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}postgres@postgres=>\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}需要在postgres 用户下，在 postgres 家目录下编辑隐藏文件 .psqlrc
[ptgres@pg01 ~]$ cat .psqlrc
\set PROMPT1 '%n@%/=>'[ptgres@pg01 ~]$ cat .psqlrc
\set PROMPT1 '%n@%/=>'| 参数 | 描述 |
|---|---|
| %n | 当前登陆用户名 |
| %/ | 当前登陆数据库 |
| %M | 服务器主机名称 |
| %> | 数据库监听port |
| %# | 超级用户显示符(#超级用户，> 普通用户) |
| %p | 当前连接的后台进程编号 |
7,锁表
sql
--查询是否锁表了
select oid from pg_class where relname='可能锁表了的表'
select pid from pg_locks where relation='上面查出的oid'
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid)
-- 查看所表信息
select pid,mode,locktype,relation,page,tuple,transactionid from pg_locks where pid <> pg_backend_pid();
或者
postgres@han_db=>select pid,usename,pg_blocking_pids(pid) as blocked_by,query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid))> 0;
pid | usename | blocked_by | blocked_query
-----+---------+------------+---------------
(0 rows)
\i /pathA/xxx.sql --执行外部sql文件--查询是否锁表了
select oid from pg_class where relname='可能锁表了的表'
select pid from pg_locks where relation='上面查出的oid'
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid)
-- 查看所表信息
select pid,mode,locktype,relation,page,tuple,transactionid from pg_locks where pid <> pg_backend_pid();
或者
postgres@han_db=>select pid,usename,pg_blocking_pids(pid) as blocked_by,query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid))> 0;
pid | usename | blocked_by | blocked_query
-----+---------+------------+---------------
(0 rows)
\i /pathA/xxx.sql --执行外部sql文件7.1 查看锁表
sql
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement | current_statement_in_blocking_process
-------------+--------------+--------------+---------------+-------------------+---------------------------------------
(0 rows)SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;
blocked_pid | blocked_user | blocking_pid | blocking_user | blocked_statement | current_statement_in_blocking_process
-------------+--------------+--------------+---------------+-------------------+---------------------------------------
(0 rows)7.2 杀死锁表
sql
#查看锁表
postgres=# select * from pg_locks a join pg_class b on a.relation = b.oid join pg_stat_activity c on a.pid = c.pid where a.mode like '%ExclusiveLock%';
查的是排它锁，也可以精确到行排它锁或者共享锁之类的。
这里有几个重要的column：a.pid是进程id，b.relname是表名、约束名或者索引名，a.mode是锁类型#查看锁表
postgres=# select * from pg_locks a join pg_class b on a.relation = b.oid join pg_stat_activity c on a.pid = c.pid where a.mode like '%ExclusiveLock%';
查的是排它锁，也可以精确到行排它锁或者共享锁之类的。
这里有几个重要的column：a.pid是进程id，b.relname是表名、约束名或者索引名，a.mode是锁类型杀掉指定表指定锁的进程
select pg_cancel_backend(a.pid) from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.pid
where b.relname ilike '表名'
and a.mode like '%ExclusiveLock%';
--或者使用更加霸道的pg_terminate_backend()：
select pg_terminate_backend(a.pid) from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.pid
where b.relname ilike '表名'
and a.mode like '%ExclusiveLock%';
另外需要注意的是，pg_terminate_backend()会把session也关闭，此时sessionId会失效，可能会导致系统账号退出登录，需要清除掉浏览器的缓存cookieselect pg_cancel_backend(a.pid) from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.pid
where b.relname ilike '表名'
and a.mode like '%ExclusiveLock%';
--或者使用更加霸道的pg_terminate_backend()：
select pg_terminate_backend(a.pid) from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.pid
where b.relname ilike '表名'
and a.mode like '%ExclusiveLock%';
另外需要注意的是，pg_terminate_backend()会把session也关闭，此时sessionId会失效，可能会导致系统账号退出登录，需要清除掉浏览器的缓存cookie7.3使用视图查看锁表
- 创建
sql
-- 用一个函数来将锁转换为数字，
create or replace function f_lock_level(i_mode text) returns int as
$$
declare
begin
case i_mode
when 'INVALID' then return 0;
when 'AccessShareLock' then return 1;
when 'RowShareLock' then return 2;
when 'RowExclusiveLock' then return 3;
when 'ShareUpdateExclusiveLock' then return 4;
when 'ShareLock' then return 5;
when 'ShareRowExclusiveLock' then return 6;
when 'ExclusiveLock' then return 7;
when 'AccessExclusiveLock' then return 8;
else return 0;
end case;
end;
$$
language plpgsql strict;-- 用一个函数来将锁转换为数字，
create or replace function f_lock_level(i_mode text) returns int as
$$
declare
begin
case i_mode
when 'INVALID' then return 0;
when 'AccessShareLock' then return 1;
when 'RowShareLock' then return 2;
when 'RowExclusiveLock' then return 3;
when 'ShareUpdateExclusiveLock' then return 4;
when 'ShareLock' then return 5;
when 'ShareRowExclusiveLock' then return 6;
when 'ExclusiveLock' then return 7;
when 'AccessExclusiveLock' then return 8;
else return 0;
end case;
end;
$$
language plpgsql strict;- 查询
sql
-- 修改查询语句，按锁级别排序：
with t_wait as
( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname
from pg_locks a,pg_stat_activity b
where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname
from pg_locks a,pg_stat_activity b
where a.pid=b.pid and a.granted
)
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r
where r.locktype is not distinct from w.locktype
and r.database is not distinct from w.database
and r.relation is not distinct from w.relation
and r.page is not distinct from w.page
and r.tuple is not distinct from w.tuple
and r.classid is not distinct from w.classid
and r.objid is not distinct from w.objid
and r.objsubid is not distinct from w.objsubid
and r.transactionid is not distinct from w.transactionid
and r.pid <> w.pid
order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;-- 修改查询语句，按锁级别排序：
with t_wait as
( select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname
from pg_locks a,pg_stat_activity b
where a.pid=b.pid and not a.granted
),
t_run as
(
select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname
from pg_locks a,pg_stat_activity b
where a.pid=b.pid and a.granted
)
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r
where r.locktype is not distinct from w.locktype
and r.database is not distinct from w.database
and r.relation is not distinct from w.relation
and r.page is not distinct from w.page
and r.tuple is not distinct from w.tuple
and r.classid is not distinct from w.classid
and r.objid is not distinct from w.objid
and r.objsubid is not distinct from w.objsubid
and r.transactionid is not distinct from w.transactionid
and r.pid <> w.pid
order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;
--现在可以排在前面的就是锁级别高的等待，优先干掉这个。
-[ RECORD 1 ]-+---------------------------------------------------------------------
locktype | relation -- 冲突类型
r_mode | ShareUpdateExclusiveLock -- 持锁模式
r_user | postgres -- 持锁用户
r_db | postgres -- 持锁数据库
relation | tbl -- 持锁对象
r_pid | 25656 -- 持锁进程
r_xact_start | 2015-05-10 14:11:16.08318+08 -- 持锁事务开始时间
r_query_start | 2015-05-10 14:11:16.08318+08 -- 持锁SQL开始时间
r_locktime | 00:01:49.460779 -- 持锁时长
r_query | vacuum freeze tbl; -- 持锁SQL,注意不一定是这个SQL带来的锁,也有可能是这个事务在之前执行的SQL加的锁
w_mode | AccessExclusiveLock -- 等待锁模式
w_pid | 26731 -- 等待锁进程
w_xact_start | 2015-05-10 14:11:17.987362+08 -- 等待锁事务开始时间
w_query_start | 2015-05-10 14:11:17.987362+08 -- 等待锁SQL开始时间
w_locktime | 00:01:47.556597 -- 等待锁时长
w_query | truncate tbl; -- 等待锁SQL
-[ RECORD 2 ]-+---------------------------------------------------------------------
locktype | relation
r_mode | ShareUpdateExclusiveLock
r_user | postgres
r_db | postgres
relation | tbl
r_pid | 25656
r_xact_start | 2015-05-10 14:11:16.08318+08
r_query_start | 2015-05-10 14:11:16.08318+08
r_locktime | 00:01:49.460779
r_query | vacuum freeze tbl;
w_mode | RowExclusiveLock
w_pid | 25582
w_xact_start | 2015-05-10 14:11:22.845+08
w_query_start | 2015-05-10 14:11:22.845+08
w_locktime | 00:01:42.698959
w_query | insert into tbl(crt_time) select now() from generate_series(1,1000); -- 这个SQL其实等待的是truncate tbl的锁;--现在可以排在前面的就是锁级别高的等待，优先干掉这个。
-[ RECORD 1 ]-+---------------------------------------------------------------------
locktype | relation -- 冲突类型
r_mode | ShareUpdateExclusiveLock -- 持锁模式
r_user | postgres -- 持锁用户
r_db | postgres -- 持锁数据库
relation | tbl -- 持锁对象
r_pid | 25656 -- 持锁进程
r_xact_start | 2015-05-10 14:11:16.08318+08 -- 持锁事务开始时间
r_query_start | 2015-05-10 14:11:16.08318+08 -- 持锁SQL开始时间
r_locktime | 00:01:49.460779 -- 持锁时长
r_query | vacuum freeze tbl; -- 持锁SQL,注意不一定是这个SQL带来的锁,也有可能是这个事务在之前执行的SQL加的锁
w_mode | AccessExclusiveLock -- 等待锁模式
w_pid | 26731 -- 等待锁进程
w_xact_start | 2015-05-10 14:11:17.987362+08 -- 等待锁事务开始时间
w_query_start | 2015-05-10 14:11:17.987362+08 -- 等待锁SQL开始时间
w_locktime | 00:01:47.556597 -- 等待锁时长
w_query | truncate tbl; -- 等待锁SQL
-[ RECORD 2 ]-+---------------------------------------------------------------------
locktype | relation
r_mode | ShareUpdateExclusiveLock
r_user | postgres
r_db | postgres
relation | tbl
r_pid | 25656
r_xact_start | 2015-05-10 14:11:16.08318+08
r_query_start | 2015-05-10 14:11:16.08318+08
r_locktime | 00:01:49.460779
r_query | vacuum freeze tbl;
w_mode | RowExclusiveLock
w_pid | 25582
w_xact_start | 2015-05-10 14:11:22.845+08
w_query_start | 2015-05-10 14:11:22.845+08
w_locktime | 00:01:42.698959
w_query | insert into tbl(crt_time) select now() from generate_series(1,1000); -- 这个SQL其实等待的是truncate tbl的锁;8,查看进程阻塞
函数pg_blocking_pids,该函数用于获取哪些进程(输出参数)阻塞了某个进程(输入参数)
在执行某些操作时,console可能会挂起没有输出,这时候你没有办法判断是因为执行很慢还是因为被阻塞了,通过pg_blocking_pids可以判断是否存在阻塞
借助中的pg_stat_activity视图
sql
postgres=# select datname,pid,usename,wait_event_type,wait_event,state,query from pg_stat_activity where backend_type = 'client backend'
and pid != pg_backend_pid();
datname | pid | usename | wait_event_type | wait_event | state | query
----------+------+----------+-----------------+------------+---------------------+--------------------------------------
postgres | 3231 | postgres | Client | ClientRead | idle in transaction | alter table t1 add column name text;
postgres | 3386 | postgres | Lock | relation | active | insert into t1(id) values(1);postgres=# select datname,pid,usename,wait_event_type,wait_event,state,query from pg_stat_activity where backend_type = 'client backend'
and pid != pg_backend_pid();
datname | pid | usename | wait_event_type | wait_event | state | query
----------+------+----------+-----------------+------------+---------------------+--------------------------------------
postgres | 3231 | postgres | Client | ClientRead | idle in transaction | alter table t1 add column name text;
postgres | 3386 | postgres | Lock | relation | active | insert into t1(id) values(1);知道PostgreSQL中当前持有或者是授权那些锁的时候，可以查看pg_locks，主要看RowExclusiveLock和AccessExclusiveLock
sql
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 | | |
postgres=# select locktype,database,relation,pid,mode,granted from pg_locks where pid != pg_backend_pid();
locktype | database | relation | pid | mode | granted
---------------+----------+----------+------+---------------------+---------
virtualxid | | | 3386 | ExclusiveLock | t
virtualxid | | | 3231 | ExclusiveLock | t
relation | 13806 | 16434 | 3231 | AccessExclusiveLock | t
transactionid | | | 3231 | ExclusiveLock | t
relation | 13806 | 16434 | 3386 | RowExclusiveLock | f
relation | 13806 | 16439 | 3231 | AccessExclusiveLock | t
relation | 13806 | 16437 | 3231 | ShareLock | t
(7 rows)
-- 直接结果，把pg_locks与pg_database和pg_class，通过加入pid来获取更多的信息
postgres=# select b.locktype,d.datname,c.relname,b.pid,b.mode from pg_locks b,pg_database d,pg_class c where b.pid in (3386,3231) and b.database = d.oid and b.relation = c.oid;
locktype | datname | relname | pid | mode
----------+----------+---------+------+---------------------
relation | postgres | t1 | 3231 | AccessExclusiveLock
relation | postgres | t1 | 3386 | RowExclusiveLock
(2 rows)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 | | |
postgres=# select locktype,database,relation,pid,mode,granted from pg_locks where pid != pg_backend_pid();
locktype | database | relation | pid | mode | granted
---------------+----------+----------+------+---------------------+---------
virtualxid | | | 3386 | ExclusiveLock | t
virtualxid | | | 3231 | ExclusiveLock | t
relation | 13806 | 16434 | 3231 | AccessExclusiveLock | t
transactionid | | | 3231 | ExclusiveLock | t
relation | 13806 | 16434 | 3386 | RowExclusiveLock | f
relation | 13806 | 16439 | 3231 | AccessExclusiveLock | t
relation | 13806 | 16437 | 3231 | ShareLock | t
(7 rows)
-- 直接结果，把pg_locks与pg_database和pg_class，通过加入pid来获取更多的信息
postgres=# select b.locktype,d.datname,c.relname,b.pid,b.mode from pg_locks b,pg_database d,pg_class c where b.pid in (3386,3231) and b.database = d.oid and b.relation = c.oid;
locktype | datname | relname | pid | mode
----------+----------+---------+------+---------------------
relation | postgres | t1 | 3231 | AccessExclusiveLock
relation | postgres | t1 | 3386 | RowExclusiveLock
(2 rows)查看阻塞着
sql
-- 使用pg_blocking_pids函数传递被堵塞的会话,就可以看到阻塞者
postgres=# select pg_blocking_pids(3386);
pg_blocking_pids
------------------
{3231}
(1 row)-- 使用pg_blocking_pids函数传递被堵塞的会话,就可以看到阻塞者
postgres=# select pg_blocking_pids(3386);
pg_blocking_pids
------------------
{3231}
(1 row)kill
sql
postgres=# select pg_terminate_backend(3231);
pg_terminate_backend
----------------------
tpostgres=# select pg_terminate_backend(3231);
pg_terminate_backend
----------------------
t9.忘记密码
PG的超级用户密码忘记
1. PostgreSQL数据库的两种访问方式
- socket方式
- host方式
socket方式比host方式访问效率高，服务器端操作访问推荐采用socket方式
2. socket方式
bash
语法：
psql -h/path/socket_dir -Upostgres -p port
[ptgres@pg01 ~]$ psql -h/tmp/ -Upostgres -p 5532
psql (12.2)
Type "help" for help.
postgres@postgres=>\conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp/" at port "5532".语法：
psql -h/path/socket_dir -Upostgres -p port
[ptgres@pg01 ~]$ psql -h/tmp/ -Upostgres -p 5532
psql (12.2)
Type "help" for help.
postgres@postgres=>\conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp/" at port "5532".3. host方式
bash
[ptgres@pg01 ~]$ psql -h127.0.0.1 -Upostgres -p 5532
psql (12.2)
Type "help" for help.
postgres@postgres=>\conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5532".[ptgres@pg01 ~]$ psql -h127.0.0.1 -Upostgres -p 5532
psql (12.2)
Type "help" for help.
postgres@postgres=>\conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5532".10,PostgreSQL中模板数据库
- template0与template1是否都可以连接
sql
postgres=# \connect template0
致命错误: 数据库 "template0" 当前不接受联接
保留上一次连接
postgres=# \connect template1
您现在已经连接到数据库 "template1",用户 "postgres".postgres=# \connect template0
致命错误: 数据库 "template0" 当前不接受联接
保留上一次连接
postgres=# \connect template1
您现在已经连接到数据库 "template1",用户 "postgres".template0是不能直接连接使用的；
template1则可以，出于安全考虑，一般会删除默认的postgres数据库，但我们想连到一个库，却不知道或不记得数据库名称了，这时可以连接template1，进去后在使用\l查看数据库列表
- template0与template1是否可以修改
template0是一个干净的模板库，不能修改；
template1可以定制修改，比如创建extension或者oracle相关的兼容视图及函数等，后续再创建database时，默认以template1为模板将继承过来
- template0与template1是否可以删除
postgres=# drop database template1;
错误: 无法删除模板数据库
postgres=# alter database template1 is_template false;
ALTER DATABASE
postgres=# drop database template1;
DROP DATABASEpostgres=# drop database template1;
错误: 无法删除模板数据库
postgres=# alter database template1 is_template false;
ALTER DATABASE
postgres=# drop database template1;
DROP DATABASE都可以删除，只要把is_template属性设置为false，即可删除
关于is_template属性：如果设置了这个标志，那么该数据库可以被任何有 CREATEDB权限的用户作为模板库使用（克隆复制）；如果没有被设置，那么只有超级用户和该数据库的拥有者可以克隆复制
- template0与template1是否可以指定新的编码
sql
postgres=# CREATE DATABASE clocaledb
WITH ENCODING 'SQL_ASCII'
LC_COLLATE='C'
LC_CTYPE='C'
TEMPLATE=template1;
错误: 新的编码(SQL_ASCII)与模板数据库(UTF8)的编码不兼容
提示: 在模版数据库中使用同一编码，或者使用template0作为模版.
postgres=# CREATE DATABASE clocaledb
WITH ENCODING 'SQL_ASCII'
LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;
CREATE DATABASE
--其他例子
CREATE DATABASE englishdb
WITH ENCODING 'UTF8'
LC_COLLATE='en_GB.UTF8'
LC_CTYPE='en_GB.UTF8'
TEMPLATE=template0;
--简体中文字符集
CREATE DATABASE chinadb
WITH ENCODING 'EUC_CN'
LC_COLLATE='C'
LC_CTYPE='C'
TEMPLATE=template0;
--简体中文字符集
CREATE DATABASE chinadb2
WITH ENCODING 'EUC_CN'
LC_COLLATE='zh_CN'
LC_CTYPE='zh_CN'
TEMPLATE=template0;postgres=# CREATE DATABASE clocaledb
WITH ENCODING 'SQL_ASCII'
LC_COLLATE='C'
LC_CTYPE='C'
TEMPLATE=template1;
错误: 新的编码(SQL_ASCII)与模板数据库(UTF8)的编码不兼容
提示: 在模版数据库中使用同一编码，或者使用template0作为模版.
postgres=# CREATE DATABASE clocaledb
WITH ENCODING 'SQL_ASCII'
LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;
CREATE DATABASE
--其他例子
CREATE DATABASE englishdb
WITH ENCODING 'UTF8'
LC_COLLATE='en_GB.UTF8'
LC_CTYPE='en_GB.UTF8'
TEMPLATE=template0;
--简体中文字符集
CREATE DATABASE chinadb
WITH ENCODING 'EUC_CN'
LC_COLLATE='C'
LC_CTYPE='C'
TEMPLATE=template0;
--简体中文字符集
CREATE DATABASE chinadb2
WITH ENCODING 'EUC_CN'
LC_COLLATE='zh_CN'
LC_CTYPE='zh_CN'
TEMPLATE=template0;当且仅当用template0作为模板数据库时可指定新的编码（这里指server_encoding） PG被支持的字符集参考http://www.postgres.cn/docs/10/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED
注意：新的编码和区域设置要对应，否则会报错： ERROR: encoding “XXX” does not match locale “XXX”
默认数据库模板
- 默认模板库为 template1
备注：建库时如果不指定 TEMPLATE 属性，默认用的是 template1 模板库.
- 手工指定模板库
postgres=# create database db2 template template0;
CREATE DATABASEpostgres=# create database db2 template template0;
CREATE DATABASE备注：也可以指定模板库为 template1
Template1 和 Template0 的区别
- template1 可以连接并创建对象，template0 不可以连接
postgres@127.0.0.1 ~=#\c template1
You are now connected to database "template1" as user "postgres".
postgres@127.0.0.1 template1=#
postgres@127.0.0.1 template1=#\c template0
FATAL: database "template0" is not currently accepting connections
Previous connection keptpostgres@127.0.0.1 ~=#\c template1
You are now connected to database "template1" as user "postgres".
postgres@127.0.0.1 template1=#
postgres@127.0.0.1 template1=#\c template0
FATAL: database "template0" is not currently accepting connections
Previous connection kept- 使用 template1 模板库建库时不可指定新的 encoding 和 locale，而 template0 可以
template1=# create database db3 TEMPLATE template0 ENCODING 'SQL_ASCII' ;
CREATE DATABASE
template1=# create database db4 TEMPLATE template1 ENCODING 'SQL_ASCII' ;
ERROR: new encoding (SQL_ASCII) is incompatible with the encoding of the template database (UTF8)
HINT: Use the same encoding as in the template database, or use template0 as template.template1=# create database db3 TEMPLATE template0 ENCODING 'SQL_ASCII' ;
CREATE DATABASE
template1=# create database db4 TEMPLATE template1 ENCODING 'SQL_ASCII' ;
ERROR: new encoding (SQL_ASCII) is incompatible with the encoding of the template database (UTF8)
HINT: Use the same encoding as in the template database, or use template0 as template.- Template0、Template1 库都不可删除
备注：当然有方法删除 template1 库，而且这个操作并不危险，需要修改系统表
- 克隆数据库
postgres@127.0.0.1 db2=#create database db4 TEMPLATE db3 ;
CREATE DATABASEpostgres@127.0.0.1 db2=#create database db4 TEMPLATE db3 ;
CREATE DATABASE备注：这种方法在复制数据库时提供了方便, 也可以定制自己的数据库模板， 但是这么操作有个前提，复制时源库不可以连接， 复制过程中也不允许连接源库,
11，conninfo
1. 常见psql连接数据库
$ psql -h127.0.0.1 -Upostgres postgres
psql (12.1)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=#$ psql -h127.0.0.1 -Upostgres postgres
psql (12.1)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=#使用元命令\conninfo可以查看数据库连接信息，包括主机、端口、数据库名称、数据库用户
2.同一用户切换到其他数据库
test=# \connect test
You are now connected to database "test" as user "postgres".test=# \connect test
You are now connected to database "test" as user "postgres".3. 同一库切换登录用户
sql
test=# \connect - test
Password for user test:
You are now connected to database "test" as user "test".test=# \connect - test
Password for user test:
You are now connected to database "test" as user "test".当前数据库为test，数据库不变，登录用户由postgres切换到test 单独切换用户，需要加 “-”
4.当前实例同时切换数据库和登录用户
sql
test=> \conninfo
You are connected to database "test" as user "test" via socket in "/var/run/postgresql" at port "5432".
test=> \connect postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=>test=> \conninfo
You are connected to database "test" as user "test" via socket in "/var/run/postgresql" at port "5432".
test=> \connect postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=>当前数据库为test，登录用户为test，切换数据库为postgres，且切换登录用户为postgres
5.切换主机和数据库
sql
$ psql -Upostgres
psql (12.1)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \connect test - 192.168.99.200
You are now connected to database "test" as user "postgres" on host "192.168.99.200" at port "5432".$ psql -Upostgres
psql (12.1)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \connect test - 192.168.99.200
You are now connected to database "test" as user "postgres" on host "192.168.99.200" at port "5432".主机参数前面需要加 “-”
6.切换主机、数据库和登录用户(端口为默认)
sql
$ psql -Upostgres
psql (12.1)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \connect test test 192.168.99.200
Password for user test:
You are now connected to database "test" as user "test" on host "192.168.99.200" at port "5432".$ psql -Upostgres
psql (12.1)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \connect test test 192.168.99.200
Password for user test:
You are now connected to database "test" as user "test" on host "192.168.99.200" at port "5432".7.切换主机、数据库和登录用户以及端口
sql
$ psql -Upostgres
psql (12.1)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \connect postgres test 192.168.99.200 5432
Password for user test:
You are now connected to database "postgres" as user "test" on host "192.168.99.200" at port "5432".$ psql -Upostgres
psql (12.1)
Type "help" for help.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \connect postgres test 192.168.99.200 5432
Password for user test:
You are now connected to database "postgres" as user "test" on host "192.168.99.200" at port "5432".8. 使用conninfo
sql
$ psql -Upostgres
psql (12.1)
Type "help" for help.
postgres=# \c "hostaddr=127.0.0.1 port=5432 user=test password=123456 dbname=test"
You are now connected to database "test" as user "test" on host "127.0.0.1" at port "5432".
test=>$ psql -Upostgres
psql (12.1)
Type "help" for help.
postgres=# \c "hostaddr=127.0.0.1 port=5432 user=test password=123456 dbname=test"
You are now connected to database "test" as user "test" on host "127.0.0.1" at port "5432".
test=>12.psqlrc
.psqlrc 写好的脚本支持 Tab 补全
psqlrc 文件用来定制 psql 客户端特性的配置文件，定制后对当前客户端全局生效，在 linux 环境下位于 ~/.psqlrc， windows 平台位于 %APPDATA%\postgresql\psqlrc.conf
- 查询连接数
bash
[ptgres@k8s1 ~]$ cat .psqlrc
\set con 'select datname,usename,client_addr,count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc ;'[ptgres@k8s1 ~]$ cat .psqlrc
\set con 'select datname,usename,client_addr,count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc ;'sql
#查看
postgres=# :con
datname | usename | client_addr | count
---------+----------+-------------+-------
| postgres | | 1
| | | 4
(2 rows)#查看
postgres=# :con
datname | usename | client_addr | count
---------+----------+-------------+-------
| postgres | | 1
| | | 4
(2 rows)- 查询用来查询当前活动会话
\set active_session 'select pid, datname, usename, query_start, client_addr, query from pg_stat_activity where state=\'active\' and pid <> pg_backend_pid() order by query;'\set active_session 'select pid, datname, usename, query_start, client_addr, query from pg_stat_activity where state=\'active\' and pid <> pg_backend_pid() order by query;'postgres=# :active_session
pid | datname | usename | query_start | client_addr | query
-----+---------+---------+-------------+-------------+-------
(0 rows)postgres=# :active_session
pid | datname | usename | query_start | client_addr | query
-----+---------+---------+-------------+-------------+-------
(0 rows)- 表空间占用
\set top10_ts_table 'select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb where a.relkind in (\'r\', \'i\') and a.reltablespace=tb.oid and tb.spcname=\':v_spcname\' order by a.relpages desc limit 10;'\set top10_ts_table 'select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb where a.relkind in (\'r\', \'i\') and a.reltablespace=tb.oid and tb.spcname=\':v_spcname\' order by a.relpages desc limit 10;'postgres=# :top10_ts_table
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
(0 rows)postgres=# :top10_ts_table
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
(0 rows)13.Prompting
Prompting 参数以百分号 % 打头，主要有如下：
| Prompting 参数 | 含义 |
|---|---|
| %M | 数据库主机全名，如果通过 UNIX Socket 连接则显示为 [local] |
| %m | 也表示数据库主机名，会截断第一个 . 后的内容 |
| %> | 数据库端口号 |
| %n | 会话的用户名 |
| %/ | 当前数据库名 |
| %# | 如果是超级用户显示为 #，否则显示为 > |
| %R | 在prompt 1中，通常显示 = ，单用户模式显示为 ^，如果会话被断开显示为 !, 等等。 |
备注： 默认的 prompt 的设置为 '%/%R%# '
postgres=# set PROMPT1 '%n@%M %~%R%# '
postgres@192.168.1.36 ~=#postgres=# set PROMPT1 '%n@%M %~%R%# '
postgres@192.168.1.36 ~=#14.索引膨胀评估
sql
SELECT current_database(), nspname AS schemaname, c.relname AS tablename, indexname, bs*(sub.relpages)::bigint AS real_size,
bs*otta::bigint as estimated_size,
bs*(sub.relpages-otta)::bigint AS bloat_size,
bs*(sub.relpages-otta)::bigint * 100 / (bs*(sub.relpages)::bigint) AS bloat_ratio
-- , index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, datawidth, sub.reltuples, sub.relpages -- (DEBUG INFO)
FROM (
SELECT bs, nspname, table_oid, indexname, relpages, coalesce(
ceil((reltuples*(4+nulldatahdrwidth))/(bs-pagehdr::float)) + 1, 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
) AS otta
-- , index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, datawidth, reltuples -- (DEBUG INFO)
FROM (
SELECT maxalign, bs, nspname, relname AS indexname, reltuples, relpages, relam, table_oid,
( index_tuple_hdr_bm +
maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
ELSE index_tuple_hdr_bm%maxalign
END
+ nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
WHEN nulldatawidth = 0 THEN 0
WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
ELSE nulldatawidth::integer%maxalign
END
)::numeric AS nulldatahdrwidth, pagehdr
-- , index_tuple_hdr_bm, nulldatawidth, datawidth -- (DEBUG INFO)
FROM (
SELECT
i.nspname, i.relname, i.reltuples, i.relpages, i.relam, s.starelid, a.attrelid AS table_oid,
CASE cluster_version.v > 7
WHEN true THEN current_setting('block_size')::numeric
ELSE 8192::numeric
END AS bs,
CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
ELSE 4
END AS maxalign,
/* per page header, fixed size: 20 for 7.X, 24 for others */
CASE WHEN cluster_version.v > 7
THEN 24
ELSE 20
END AS pagehdr,
/* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
CASE WHEN max(coalesce(s.stanullfrac,0)) = 0
THEN 2 -- IndexTupleData size
ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
END AS index_tuple_hdr_bm,
/* data len: we remove null values save space using it fractionnal part from stats */
sum( (1-coalesce(s.stanullfrac, 0)) * coalesce(s.stawidth, 1024) ) AS nulldatawidth
-- , sum( s.stawidth ) AS datawidth -- (DEBUG INFO)
FROM pg_attribute AS a
JOIN pg_statistic AS s ON s.starelid=a.attrelid AND s.staattnum = a.attnum
JOIN (
SELECT nspname, relname, reltuples, relpages, indrelid, relam,
string_to_array(pg_catalog.textin(pg_catalog.int2vectorout(indkey)), ' ')::smallint[] AS attnum
FROM pg_index
JOIN pg_class ON pg_class.oid=pg_index.indexrelid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
) AS i ON i.indrelid = a.attrelid AND a.attnum = ANY (i.attnum),
( SELECT substring(current_setting('server_version') FROM '#"[0-9]+#"%' FOR '#')::integer ) AS cluster_version(v)
WHERE a.attnum > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, cluster_version.v
) AS s1
) AS s2
JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
) as sub
JOIN pg_class c ON c.oid=sub.table_oid
WHERE sub.relpages > 2
ORDER BY 2,3,4;SELECT current_database(), nspname AS schemaname, c.relname AS tablename, indexname, bs*(sub.relpages)::bigint AS real_size,
bs*otta::bigint as estimated_size,
bs*(sub.relpages-otta)::bigint AS bloat_size,
bs*(sub.relpages-otta)::bigint * 100 / (bs*(sub.relpages)::bigint) AS bloat_ratio
-- , index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, datawidth, sub.reltuples, sub.relpages -- (DEBUG INFO)
FROM (
SELECT bs, nspname, table_oid, indexname, relpages, coalesce(
ceil((reltuples*(4+nulldatahdrwidth))/(bs-pagehdr::float)) + 1, 0 -- ItemIdData size + computed avg size of a tuple (nulldatahdrwidth)
) AS otta
-- , index_tuple_hdr_bm, maxalign, pagehdr, nulldatawidth, nulldatahdrwidth, datawidth, reltuples -- (DEBUG INFO)
FROM (
SELECT maxalign, bs, nspname, relname AS indexname, reltuples, relpages, relam, table_oid,
( index_tuple_hdr_bm +
maxalign - CASE -- Add padding to the index tuple header to align on MAXALIGN
WHEN index_tuple_hdr_bm%maxalign = 0 THEN maxalign
ELSE index_tuple_hdr_bm%maxalign
END
+ nulldatawidth + maxalign - CASE -- Add padding to the data to align on MAXALIGN
WHEN nulldatawidth = 0 THEN 0
WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
ELSE nulldatawidth::integer%maxalign
END
)::numeric AS nulldatahdrwidth, pagehdr
-- , index_tuple_hdr_bm, nulldatawidth, datawidth -- (DEBUG INFO)
FROM (
SELECT
i.nspname, i.relname, i.reltuples, i.relpages, i.relam, s.starelid, a.attrelid AS table_oid,
CASE cluster_version.v > 7
WHEN true THEN current_setting('block_size')::numeric
ELSE 8192::numeric
END AS bs,
CASE -- MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?)
WHEN version() ~ 'mingw32' OR version() ~ '64-bit|x86_64|ppc64|ia64|amd64' THEN 8
ELSE 4
END AS maxalign,
/* per page header, fixed size: 20 for 7.X, 24 for others */
CASE WHEN cluster_version.v > 7
THEN 24
ELSE 20
END AS pagehdr,
/* per tuple header: add IndexAttributeBitMapData if some cols are null-able */
CASE WHEN max(coalesce(s.stanullfrac,0)) = 0
THEN 2 -- IndexTupleData size
ELSE 2 + (( 32 + 8 - 1 ) / 8) -- IndexTupleData size + IndexAttributeBitMapData size ( max num filed per index + 8 - 1 /8)
END AS index_tuple_hdr_bm,
/* data len: we remove null values save space using it fractionnal part from stats */
sum( (1-coalesce(s.stanullfrac, 0)) * coalesce(s.stawidth, 1024) ) AS nulldatawidth
-- , sum( s.stawidth ) AS datawidth -- (DEBUG INFO)
FROM pg_attribute AS a
JOIN pg_statistic AS s ON s.starelid=a.attrelid AND s.staattnum = a.attnum
JOIN (
SELECT nspname, relname, reltuples, relpages, indrelid, relam,
string_to_array(pg_catalog.textin(pg_catalog.int2vectorout(indkey)), ' ')::smallint[] AS attnum
FROM pg_index
JOIN pg_class ON pg_class.oid=pg_index.indexrelid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
) AS i ON i.indrelid = a.attrelid AND a.attnum = ANY (i.attnum),
( SELECT substring(current_setting('server_version') FROM '#"[0-9]+#"%' FOR '#')::integer ) AS cluster_version(v)
WHERE a.attnum > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, cluster_version.v
) AS s1
) AS s2
JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
) as sub
JOIN pg_class c ON c.oid=sub.table_oid
WHERE sub.relpages > 2
ORDER BY 2,3,4;15.查看编译后参数
[ptgres@k8s1 ~]$ pg_config --configure
16.查询数据库ip来源
sql
select datname,usename ,client_addr,client_port from pg_stat_activity where datname='db2';select datname,usename ,client_addr,client_port from pg_stat_activity where datname='db2';17.免密码登录
1.设置环境变量 PGPASSWORD
postgres@127.0.0.1 ~=#export PGPASSWORD=skytf
#psql -h 192.168.1.25 -p 1921 skytf skytfpostgres@127.0.0.1 ~=#export PGPASSWORD=skytf
#psql -h 192.168.1.25 -p 1921 skytf skytf2.设置 .pgpass 密码文件
修改 /home/postgres/.pgpass 文件，增加以下
hostname:port:database:username:password
Chmod 600 .pgpasshostname:port:database:username:password
Chmod 600 .pgpass3.修改 pg_hba.conf
host skytf skytf 172.16.3.174/32 trusthost skytf skytf 172.16.3.174/32 trust18.修改时区
查看数据库的时区与时间
postgres=# select now();
now
------------------------------
2022-03-18 11:39:04.04916+08postgres=# select now();
now
------------------------------
2022-03-18 11:39:04.04916+08查看时区
postgres=# show time zone;
TimeZone
---------------
RPCpostgres=# show time zone;
TimeZone
---------------
RPC选择的时区
select * from pg_timezone_names;select * from pg_timezone_names;会话级别
postgres=# set time zone "Asia/Shanghai";
SETpostgres=# set time zone "Asia/Shanghai";
SET永久修改
catpostgresql.conf|grep timezone
log_timezone = 'PRC'
timezone = 'PRC'
PRC指：People's Republic of Chinacatpostgresql.conf|grep timezone
log_timezone = 'PRC'
timezone = 'PRC'
PRC指：People's Republic of China数据库级别修改
alter database dbname set timezone='UTC';
pipeline=# select * from pg_db_role_setting ;alter database dbname set timezone='UTC';
pipeline=# select * from pg_db_role_setting ;用户级别
alter role rolname set timezone='UTC';
或者
alter role all set timezone='UTC';
pipeline=# select * from pg_db_role_setting ;alter role rolname set timezone='UTC';
或者
alter role all set timezone='UTC';
pipeline=# select * from pg_db_role_setting ;重新加载数据库
select pg_reload_conf();select pg_reload_conf();19.idle in transaction
https://www.postgresql.org/docs/12/runtime-config-client.html
pg_stat_activity 是一张postgresql的系统视图，它的每一行都表示一个系统进程，显示与当前会话的活动进程的一些信息，比如当前会话的状态和查询等。它的state字段表示当前进程的状态，一共有六种：
1、Active(活动): 进程正在执行某个语句
2、Idle(空闲): 进程正在等待客户端的指令
3、idle in transaction(事务空闲):进程在处理事务的过程中，但当前没有执行任何语句
4、idle in transaction (aborted)(事务空闲-退出):除了事务中声明一个错误外，其余情况与idle in transaction相同
5、fastpath function call(快速通道函数调用): 后台正在执行某个快速通道函数
6、Disabled(禁用): 报告状态被禁用
sql
select p.pid, p.datname, p.usename, p.client_port, p.backend_start, p.query_start, p.state_change,state,query, COUNT(0) over(partition by p.datname order by 1) as cntnum from pg_stat_activity p;
-- *
pid---->连接pid
backend_start---> 客户端连接数据库时间（初始连接建立时间）
query_start ---> 查询开始运行时间
state_change --->状态变化时间
query-----》 当前连接执行的语句
--select p.pid, p.datname, p.usename, p.client_port, p.backend_start, p.query_start, p.state_change,state,query, COUNT(0) over(partition by p.datname order by 1) as cntnum from pg_stat_activity p;
-- *
pid---->连接pid
backend_start---> 客户端连接数据库时间（初始连接建立时间）
query_start ---> 查询开始运行时间
state_change --->状态变化时间
query-----》 当前连接执行的语句
--20.数据库连接数查询
查询当前所有连接的状态
select datname,pid,application_name,state from pg_stat_activity;select datname,pid,application_name,state from pg_stat_activity;关闭当前 state 为 idle 空闲状态的连接
查看数据库剩余连接数：
select max_conn-now_conn as resi_conn from (select setting::int8 as max_conn,(select count(*) from pg_stat_activity) as now_conn from pg_settings where name = 'max_connections') t;select max_conn-now_conn as resi_conn from (select setting::int8 as max_conn,(select count(*) from pg_stat_activity) as now_conn from pg_settings where name = 'max_connections') t;查看为超级用户保留的连接数:
show superuser_reserved_connections;show superuser_reserved_connections;关闭空闲连接
sql
#查询
select datname,pid,application_name,state from pg_stat_activity;
#关闭具体连接
SELECT pg_terminate_backend(pid);
#杀死所有 idle 的进程
select pg_terminate_backend(pid) from pg_stat_activity where state='idle';#查询
select datname,pid,application_name,state from pg_stat_activity;
#关闭具体连接
SELECT pg_terminate_backend(pid);
#杀死所有 idle 的进程
select pg_terminate_backend(pid) from pg_stat_activity where state='idle';21.cpu占满100%性能分析
查看连接数变化
sql
select count( * ) from pg_stat_activity where state not like '%idle';select count( * ) from pg_stat_activity where state not like '%idle';追踪慢SQL
1.第1种方式
通过pg_stat_statements插件来定位慢sql
sql
postgres@postgres=>\c db_name
postgres@postgres=>create extension pg_stat_statements;
postgres@postgres=>select pg_stat_reset();
postgres@postgres=>select pg_stat_statements_reset();
#等待1min左右postgres@postgres=>\c db_name
postgres@postgres=>create extension pg_stat_statements;
postgres@postgres=>select pg_stat_reset();
postgres@postgres=>select pg_stat_statements_reset();
#等待1min左右- 查询最耗时的SQL
sql
select * from pg_stat_statements order by total_time desc limit 5;select * from pg_stat_statements order by total_time desc limit 5;- 查询读取Buffer次数最多的SQL
sql
#查询读取Buffer次数最多的SQL，这些SQL可能由于所查询的数据没有索引，而导致了过多的Buffer读，也同时大量消耗了CPU
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;#查询读取Buffer次数最多的SQL，这些SQL可能由于所查询的数据没有索引，而导致了过多的Buffer读，也同时大量消耗了CPU
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;2.第2种方式
- 通过pg_stat_activity视图
sql
select datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query from (select pgsa.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as query from pg_stat_activity as pgsa where pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 5;select datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query from (select pgsa.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as query from pg_stat_activity as pgsa where pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 5;3.第3种方式
- Table Scan
是从数据表上表扫描（Table Scan）的信息开始查起，查找缺失索引的表。数据表如果缺失索引，大部分热数据又都在内存时（例如内存8G，热数据6G），此时数据库只能使用表扫描，并需要处理已在内存中的大量的无关记录，而耗费大量CPU。特别是对于表记录数超100的表，一次表扫描占用大量CPU（基本把一个CPU占满），多个连接并发（例如上百连接），把所有CPU占满
- 查出使用表扫描最多的表
sql
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;- 查询当前正在运行的访问到上述表的慢查询
sql
select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';select * from pg_stat_activity where query ilike '%<table name>%' and query_start - now() > interval '10 seconds';- 也通过pg_stat_statements插件定位涉及到这些表的查询
sql
select * from pg_stat_statements where query ilike '%<table>%'order by shared_blks_hit+shared_blks_read desc limit 3;select * from pg_stat_statements where query ilike '%<table>%'order by shared_blks_hit+shared_blks_read desc limit 3;4.kill SQL
sql
select pg_cancel_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
#或者
select pg_terminate_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();select pg_cancel_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();
#或者
select pg_terminate_backend(pid) from pg_stat_activity where query like '%<query text>%' and pid != pg_backend_pid();如果这些SQL确实是业务上必需的，则需要对他们做优化。这方面有“三板斧”：
1、对查询涉及的表，执行ANALYZE table或VACUUM ANZLYZE table，更新表的统计信息，使查询计划更准确。注意，为避免对业务影响，最好在业务低峰执行。
2、执行explain (query text)或explain (buffers true, analyze true, verbose true) (query text)命令，查看SQL的执行计划（注意，前者不会实际执行SQL，后者会实际执行而且能得到详细的执行信息），对其中的Table Scan涉及的表，建立索引。
3、重新编写SQL，去除掉不必要的子查询、改写UNION ALL、使用JOIN CLAUSE固定连接顺序等到
22.防止误删库
template1和template0是PostgreSQL的模板数据库。所谓模板数据库就是创建新database时，PostgreSQL会基于模板数据库制作一份副本，其中会包含所有的数据库设置和数据文件。PostgreSQL安装好以后会默认附带两个模板数据库：template0和template1
- 设置模板库属性
pg_database的datistemplate字段可以表明该库是否是模板库
sql
postgres@postgres=>select * from pg_database;
oid | datname | datdba | encoding | datlocprovider | datistemplate | datallowconn | datconnlimit | datfrozenxid | datminmxid | dattablespace | datcollate
| datctype | daticulocale | daticurules | datcollversion | datacl
-----+-----------+--------+----------+----------------+---------------+--------------+--------------+--------------+------------+---------------+------------
+------------+--------------+-------------+----------------+-------------------------------------
5 | postgres | 10 | 6 | c | f | t | -1 | 722 | 1 | 1663 | en_US.UTF8
| en_US.UTF8 | | | 2.26 |
1 | template1 | 10 | 6 | c | t | t | -1 | 722 | 1 | 1663 | en_US.UTF8
| en_US.UTF8 | | | 2.26 | {=c/postgres,postgres=CTc/postgres}
4 | template0 | 10 | 6 | c | t | f | -1 | 722 | 1 | 1663 | en_US.UTF8
| en_US.UTF8 | | | | {=c/postgres,postgres=CTc/postgres}
(3 rows)postgres@postgres=>select * from pg_database;
oid | datname | datdba | encoding | datlocprovider | datistemplate | datallowconn | datconnlimit | datfrozenxid | datminmxid | dattablespace | datcollate
| datctype | daticulocale | daticurules | datcollversion | datacl
-----+-----------+--------+----------+----------------+---------------+--------------+--------------+--------------+------------+---------------+------------
+------------+--------------+-------------+----------------+-------------------------------------
5 | postgres | 10 | 6 | c | f | t | -1 | 722 | 1 | 1663 | en_US.UTF8
| en_US.UTF8 | | | 2.26 |
1 | template1 | 10 | 6 | c | t | t | -1 | 722 | 1 | 1663 | en_US.UTF8
| en_US.UTF8 | | | 2.26 | {=c/postgres,postgres=CTc/postgres}
4 | template0 | 10 | 6 | c | t | f | -1 | 722 | 1 | 1663 | en_US.UTF8
| en_US.UTF8 | | | | {=c/postgres,postgres=CTc/postgres}
(3 rows)- 模板库防止误删
sql
#pg中是无法删除模板数据库的，改一下pg_database的datistemplate字段即可防止误删数据库的情况发生
postgres@postgres=>update pg_database set datistemplate = 'true' where datname = 'mydb';
UPDATE 1
postgres@postgres=>drop database mydb;
ERROR: cannot drop a template database#pg中是无法删除模板数据库的，改一下pg_database的datistemplate字段即可防止误删数据库的情况发生
postgres@postgres=>update pg_database set datistemplate = 'true' where datname = 'mydb';
UPDATE 1
postgres@postgres=>drop database mydb;
ERROR: cannot drop a template database- 误删保护解除
sql
postgres@postgres=>update pg_database set datistemplate = 'false' where datname = 'mydb';
UPDATE 1
postgres@postgres=>drop database mydb;
DROP DATABASEpostgres@postgres=>update pg_database set datistemplate = 'false' where datname = 'mydb';
UPDATE 1
postgres@postgres=>drop database mydb;
DROP DATABASE