PostgreSQL常⽤SQL语句
PostgreSQL 简介[1]
PostgreSQL 可以说是⽬前功能最强⼤、特性最丰富和结构最复杂的开源数据库管理系统，其中有些特性甚⾄连商业数据库都不具备。这个起源于加州⼤学伯克利分校的数据库，现已成为⼀项国际开发项⽬，并且拥有⼴泛的⽤户群，尤其是在海外，⽬前国内使⽤者也越来越多。
PostgreSQL 基本上算是见证了整个数据库理论和技术的发展历程，由 UCB 计算机教授 Michael Stonebraker 于 1986 年创建。在此之前，Stonebraker 教授主导了关系数据库 Ingres 研究项⽬，88 年，提出了 Postgres 的第⼀个原型设计。
MySQL 号称是使⽤最⼴泛的开源数据库，⽽ PG 则被称为功能最强⼤的开源数据库。
创建新的⽤户
白血球低吃什么补得快
创建⼀个新的⽤户
CREATE USER <urname> WITH ENCRYPTED PASSWORD '<your_own_password>';
⽤户授权
授予 CONNECT 访问权限
GRANT CONNECT ON DATABASE databa_name TO urname;
然后授予模式使⽤
GRANT USAGE ON SCHEMA schema_name TO urname;
为特定表授予 SELECT 权限
GRANT SELECT ON table_name TO urname;
将 SELECT 授予多个表
# 执⾏格式
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO urname;
# 例⼦
grant lect on all tables in schema public to ur1;
如果您希望将来⾃动授予对新表的访问权限，则必须更改默认值
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO urname;
创建数据库
创建以 utf-8 字符的数据库，并且以 template0 为模版创建
上行文CREATE DATABASE dbname WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8';
给指定⽤户授指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE dbname to urname;
在执⾏登陆操作后提⽰ FATAL：role 'root' is not permitted to log in.
alter ur "root" login;
数据库备份与恢复
备份所有数据库
pg_dumpall > db.out
恢复所有数据库
# 执⾏这个命令的时候连接到哪个数据库⽆关紧要，因为pg_dumpall 创建的脚本将会包含恰当的创建和连接数据库的命令psql -f db.out postgres
双十一素材备份单个数据库新版绝代双骄
pg_dump -h localhost -U postgres(⽤户名) 数据库名(缺省时同⽤户名) > /data/dum.sql
恢复单个数据库
psql -U postgres(⽤户名) 数据库名(缺省时同⽤户名) < /data/dum.sql
备份单个数据库并压缩
pg_dump -h localhost -U postgres(⽤户名) 数据库名(缺省时同⽤户名) | gzip > /data/
恢复单个压缩数据库备份
gunzip < /data/ | psql -h localhost -U postgres(⽤户名) 数据库名(缺省时同⽤户名)
备份单表操作
pg_dump -U postgres -h localhost -p 5432 -t staff -f staff.sql yjl（表⽰数据库名称）
-U 表⽰⽤户
-h 表⽰主机
-p 表⽰端⼝号
-t 表⽰表名
-f 表⽰备份后的sql⽂件的名字
-d 表⽰要恢复数据库名称
恢复数据单表操作
psql -U postgres -h localhost -p 5432 -d product -f staff.sql
查询当前链接
查询当前连接数
# 统计当前所有连接数
lect count(1) from pg_stat_activity;
# 查询当前连接数详细信息
lect * from pg_stat_activity;
查询最⼤连接数
show max_connections;
# 最⼤连接数也可以在pg配置⽂件中配置：
# 在 f 中设置：
max_connections = 500
统计数据库占⽤磁盘⼤⼩
统计各数据库占⽤磁盘⼤⼩
SELECT d.datname AS Name, pg_catalog.pg_get_urbyid(d.datdba) AS Owner, CASE WHEN pg_catalog.has_databa_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_size_pretty(pg_catalog.pg_databa_size(d.datname)) ELSE 'No Access'
END AS SIZE
FROM pg_catalog.pg_databa d
ORDER BY
调研计划怎么写CASE WHEN pg_catalog.has_databa_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_databa_size(d.datname)
ELSE NULL
END DESC -- nulls first
LIMIT 20
统计数据库中各表占⽤磁盘⼤⼩
# 只显⽰表名和占⽤磁盘⼤⼩
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 *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a ORDER BY total_bytes desc;
查看 PostgreSQL 正在执⾏的 SQL
SELECT
procpid,
start,
四边形now() - start AS lap,
current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idt() AS backendid) AS S
) AS S
WHERE
current_query <> '<IDLE>'
ORDER BY
lap DESC;
# 参数解释
国有大型企业procpid：进程id
start：进程开始时间
lap：经过时间
current_query：执⾏中的sql短的英语怎么读
# 通过命令：
=# lect pg_cancel_backend(线程id);
来kill掉指定的SQL语句。（这个函数只能 kill Select 查询，⽽updae,delete DML不⽣效）
# 使⽤
=# lect pg_terminate_backend(pid int)
可以kill 各种DML(SELECT,UPDATE,DELETE,DROP)操作
虽然可以使⽤ kill -9 来强制删除⽤户进程，但是不建议这么去做。
因为：对于执⾏ update 的语句来说，kill掉进程，可能会导致 Postgres 进⼊到 recovery mode ⽽在 recovery mode 下，会锁表，不允许链接数据库。
参考链接
往期精彩⽂章
您的关注是⼩站的动⼒
