共计 3478 个字符，预计需要花费 9 分钟才能阅读完成。
这期内容当中丸趣 TV 小编将会给大家带来有关 PostgreSQL 中有哪些常用的 SQL 语句，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。
创建新的用户
创建一个新的用户
CREATE USER WITH ENCRYPTED PASSWORD
用户授权
授予 CONNECT 访问权限
GRANT CONNECT ON DATABASE database_name TO username;
然后授予模式使用
GRANT USAGE ON SCHEMA schema_name TO username;
为特定表授予 SELECT 权限
GRANT SELECT ON table_name TO username;
将 SELECT 授予多个表
# 执行格式
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;
# 例子
grant select on all tables in schema public to user1;
如果您希望将来自动授予对新表的访问权限，则必须更改默认值
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO username;
创建数据库
创建以 utf-8 字符的数据库，并且以 template0 为模版创建
CREATE DATABASE dbname WITH OWNER = postgres TEMPLATE = template0 ENCODING = UTF8
给指定用户授指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE dbname to username;
在执行登陆操作后提示 FATAL：role root is not permitted to log in.
alter user 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/dum.sql.gz
恢复单个压缩数据库备份
gunzip /data/dum.sql.gz | 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
查询当前链接
查询当前连接数
# 统计当前所有连接数
select count(1) from pg_stat_activity;
# 查询当前连接数详细信息
select * from pg_stat_activity;
查询最大连接数
show max_connections;
# 最大连接数也可以在 pg 配置文件中配置：
# 在 postgresql.conf 中设置：
max_connections = 500
统计数据库占用磁盘大小
统计各数据库占用磁盘大小
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
统计数据库中各表占用磁盘大小
# 只显示表名和占用磁盘大小
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_idset() AS backendid) AS S
) AS S
WHERE
current_query
ORDER BY
lap DESC;
# 参数解释
procpid：进程 id
start：进程开始时间
lap：经过时间
current_query：执行中的 sql
# 通过命令：
=# select pg_cancel_backend(线程 id);
来 kill 掉指定的 SQL 语句。(这个函数只能 kill Select 查询，而 updae,delete DML 不生效)。
# 使用
=# select pg_terminate_backend(pid int)
可以 kill 各种 DML(SELECT,UPDATE,DELETE,DROP) 操作。
虽然可以使用 kill -9 来强制删除用户进程，但是不建议这么去做。
因为：对于执行 update 的语句来说，kill 掉进程，可能会导致 Postgres 进入到 recovery mode；而在 recovery mode 下，会锁表，不允许链接数据库。
上述就是丸趣 TV 小编为大家分享的 PostgreSQL 中有哪些常用的 SQL 语句了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注丸趣 TV 行业资讯频道。
