常用命令行命令
1, 连接数据库
# 需要输入密码
psql -h host -U dbuser -d dbname
免密登录方法:
-
方法一: 设定环境变量
PGPASSWORD -
方法二：配置
.pgpasstouch ~/.pgpass chmod 600 ~/.pgpass # 文件内容内容一条一行，格式如下 hostname:port:database:username:password
2, 重载配置文件
pg_ctl reload -D /var/lib/pgsql/9.6/data/
常用数据库命令
# 数据库切换
\c dbname
# 退出
\q
# 列出数据库
\l
# 列出表
\dt
# 列出索引
\di
# 列出除系统模式外的所有模式和所有者
\dn
# 列出所有模式
\dnS+
# 查看指定表结构
\d TableName
# 查看指定模式
\dn SchemaName
# 查看指定模式的详细信息
\dnS+ SchemaName
数据库状态查看命令
1, 查看连接状态
-- 通过视图pg_stat_activity
-- 查看所有连接的信息
select * from pg_stat_activity;
-- 查看所有连接数
select count(*) from pg_stat_activity;
-- 查询所有连接状态
select datname,pid,application_name,state from pg_stat_activity;
-- 查看各个用户连接数
select count(*), usename from pg_stat_activity group by usename;
-- 查看剩余数据库连接
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 max_connections;
2, 查看数据库磁盘占用
-- 查看指定库大小，单位：字节（B）
select pg_database_size('<DB_NAME>');
-- 查看指定库大小，带单位
select pg_size_pretty(pg_database_size('postgres'));
-- 查看每个库的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;
-- 查看所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;
-- 统计各个库大小和所有者
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
3, 慢查询
select pid,
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.pid as pid,
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;
常用用户操作
1, 密码修改
- 数据库命令方式
# 使用指定用户进入数据库
\password
- SQL方式
ALTER USER UserName PASSWORD 'password';
2, 赋权
设置模式权限
GRANT CREATE, USAGE ON SCHEMA s_1 TO rd23;
设置表权限
grant select on all tables in schema public to username;
grant select on all tables in schema public to dev;
GRANT SELECT,INSERT,DELETE,UPDATE ON ALL TABLES IN SCHEMA public to normal;
# 设置超级用户
ALTER ROLE username SUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN NOREPLICATION NOBYPASSRLS;
设置默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA s_1 grant select on tables to dev;
ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to dev;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,DELETE,UPDATE ON tables to rd23;
3, 批量修改表所有者
-- 生成执行语句
SELECT
'alter table ' || nsp.nspname || '.' || cls.relname || ' owner to cms;' || chr ( 13 )
FROM
pg_catalog.pg_class cls,
pg_catalog.pg_namespace nsp
WHERE
nsp.nspname IN ( 'public' )
AND cls.relnamespace = nsp.oid
AND cls.relkind = 'r'
ORDER BY
nsp.nspname,
cls.relname;
-- 将结果复制出来执行
数据库备份和恢复
1, 常用备份命令
# 只保存数据，不保存权限
pg_dump -h host -U username -p port -d database -ax -f bak.sql
# 只保存结构，不保存权限
pg_dump -h host -U username -p port -d database -sx -f bak.sql
# 备份指定库的SQL脚本
pg_dump -h host -U username -p port -d database -cC --if-exists -f bak.sql
# tar备份
pg_dump -h host -U username -p port -d database -cC --if-exists -Ft -f bak.tar
# dump备份
pg_dump -h host -U username -p port -d database -cC --if-exists -Fc -f bak.dump
2, 常用恢复命令
# 从SQL脚本恢复
psql -h host -U username -p port -v ON_ERROR_STOP=ON -f bak.sql
psql -h host -U username -p port -v ON_ERROR_STOP=ON < bak.sql
# 从tar恢复
pg_restore -h host -U username -d database bak.tar
# 从dump恢复
pg_restore -h host -U username -d database bak.dump
使用查询结果生成新表
通过命令 CREATE TABLE AS 通过查询结果生产一个新表
# 创建一张正常的表
CREATE TABLE new_table AS SELECT * FROM old_table WHERE id=1;
# 创建一张临时表,只在当前会话存在的表，其他会话无法访问且会话结束就消失
CREATE TEMP TABLE new_table AS SELECT * FROM old_table WHERE id=1;