本文已参与「新人创作礼亅活动，一起开启掘金创作之路。

postgresql数据库 DBA 运维 经常使用的命令

@[TOC] （持续更新） 最近一直在维护postgresql数据库 ，整理一下经常会用到的命令，sql语句。 postgresql11 常用命令

一.死锁

因为postgresql的本身，常常会导致一部分表被锁

1.查询锁

select w1.pid as 等待进程,
w1.mode as 等待锁模式,
w2.usename as 等待用户,
w2.query as 等待会话,
b1.pid as 锁的进程,
b1.mode 锁的锁模式,
b2.usename as 锁的用户,
b2.query as 锁的会话,
b2.application_name 锁的应用,
b2.client_addr 锁的IP地址,
b2.query_start 锁的语句执行时间
from pg_locks w1
join pg_stat_activity w2 on w1.pid=w2.pid
join pg_locks b1 on w1.transactionid=b1.transactionid and w1.pid!=b1.pid
join pg_stat_activity b2 on b1.pid=b2.pid
where not w1.granted;

2.杀死死锁进程

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid='62560'

如果仍然不能杀死会话，可以在操作系统层面，直接kill 掉

二.查看正在运行的进程

用于服务器监控，可查询进程，时间消耗与锁相关

SELECT 
C.relname 对象名称,
l.locktype 可锁对象的类型,
l.pid 进程id,
l.MODE 持有的锁模式,
l.GRANTED 是否已经对锁进行授权,
l.fastpath,
psa.datname 数据库名称,
psa.usesysid 用户id,
psa.usename 用户名称,
psa.application_name 应用程序名称,
psa.client_addr 连接的IP地址,
psa.client_port 连接使用的TCP端口号,
psa.backend_start 进程开始时间,
psa.xact_start 事务开始时间,
psa.query_start 事务执行此语句时间,
psa.state_change 事务状态改变时间,
psa.wait_event_type 等待事件类型,
psa.wait_event 等待事件,
psa.STATE 查询状态,
backend_xid 事务是否有写入操作,
backend_xmin 是否执事务快照,
psa.query 执行语句,
now( ) - query_start 持续时间
FROM
pg_locks l
INNER JOIN pg_stat_activity psa ON ( psa.pid = l.pid )
LEFT OUTER JOIN pg_class C ON ( l.relation = C.oid )
-- where l.relation = 'tb_base_apparatus'::regclass
where relkind ='r'
ORDER BY query_start asc

三 PostgreSql 查询数据库，索引，表，表空间大小

-- 查询表空间大小

select pg_size_pretty(pg_tablespace_size('pg_default'));

-- 查询所有数据库大小

 select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;

-- 查询指定索引大小

select pg_size_pretty(pg_relation_size('dotime'));

-- 查询指定表所有索引大小

select pg_size_pretty(pg_indexes_size('tb_sys_loginfo'));

-- 查询指定schema 里所有的索引大小，按从大到小的顺序排列。

 select relname,indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;

-- 查询指定表大小（仅仅指定表数据）

 select pg_size_pretty(pg_relation_size('tb_sys_loginfo'));

-- 查询指定表总大小（包括表数据与索引）

select pg_size_pretty(pg_total_relation_size('tb_sys_loginfo'));

-- 查询指定schema里面所有表大小（仅仅指定表数据，从大到小）

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

--查询指定schema里面所有表数据大小，总索引大小，总大小，行数

SELECT
    table_size.relname 表名,
    pg_size_pretty ( pg_relation_size ( relid ) ) 表数据大小,
    pg_size_pretty ( pg_indexes_size ( relid ) ) 表总索引大小,
    pg_size_pretty ( pg_total_relation_size ( relid ) ) 表总大小,
    表行数
FROM
pg_stat_user_tables table_size
    LEFT JOIN (
        SELECT
            relname,
            reltuples :: DECIMAL ( 19, 0 ) 表行数
        FROM
        pg_class r
        JOIN pg_namespace n ON ( relnamespace = n.oid )
        WHERE
            relkind = 'r'
            AND n.nspname = 'public'
        ) table_num ON table_num.relname = table_size.relname
WHERE
    schemaname = 'public'
ORDER BY
    pg_relation_size ( relid ) DESC;

四 查询外部server

select * from pg_foreign_server;

五 杀掉所有数据库连接

SELECT
    pg_terminate_backend(pid)
FROM
    pg_stat_activity
WHERE
    -- don't kill my own connection
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = '库名'
    ;

六 重新启动服务

--启动服务：
systemctl start postgresql-11
--停止服务：
systemctl stop postgresql-11
--重启服务：
systemctl restart postgresql-11
--查看pgagent_11服务
systemctl enable pgagent_11
systemctl status pgagent_11
systemctl start pgagent_11

七 备份与还原

1. 备份数据库

pg_dump --file "/home/back/pgsql-all-"$(date +%F+%T)".backup" --host "0.0.0.11" --port "5432" --username "postgres" --dbname "postgres" --verbose --role "postgres" --format=c --blobs --encoding "UTF8"

2.还原数据库

pg_restore --username "postgres" --no-password --role "postgres" --dbname "hrmwv2" --verbose /home/back/pgsql-all-postgres-2020-07-22+11:03:28.backup

八 重新加载数据库配置的方法有三种：

1. 用超级用户运行

postgres=# SELECT pg_reload_conf();

2. 用UNIX的kill手动发起HUP信号

$kill -HUP PID

3.使用pg_ctl命令触发SIGHUP信号

$pg_ctl reload