postgresql pg_hba免密登录 pg数据库免密登录

常用命令行命令

1, 连接数据库

# 需要输入密码
psql -h host -U dbuser -d dbname

免密登录方法:

• 方法一: 设定环境变量 PGPASSWORD
• 方法二：配置 .pgpass

touch ~/.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;