postgresql常用命令

查看当前用户

\du

或  select * from pg_roles;

postgres的退出
postgres-# \q

查看用户权限

select * from information_schema.table_privileges where grantee='zjy';

创建
CREATE ROLE rolename;
CREATE USER username;
CREATE USER和CREATE ROLE的区别在于，CREATE USER指令创建的用户默认是有登录权限的，而CREATE ROLE没有。

创建用户时设定用户属性

基本语法格式:  CREATE ROLE role_name WITH optional_permissions;

示例:在创建用户时设定登录权限:  CREATE ROLE username WITH LOGIN;

可以通过  \h CREATE ROLE  指令查看全部可设置的管理权限

创建该用户后，还不能直接登录。需要修改 pg_hba.conf 文件（后面会对该文件进行说明），加入：

①：本地登陆：local   all    all    trust
②：远程登陆：host   all    all    192.168.163.132/32     trust

　　参考链接：https://www.cnblogs.com/zhoujinyi/p/10939715.html

修改用户属性
修改权限的命令格式
ALTER ROLE username WITH attribute_options;
例如:可通过以下方式禁止用户登录
ALTER ROLE username WITH NOLOGIN;

设置访问权限
语法格式如下:
GRANT permission_type ON table_name TO role_name;

示例
GRANT UPDATE ON demo TO demo_role; --赋予demo_role demo表的update权限
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role; --赋予demo_role所有表的SELECT权限

特殊符号:ALL代表所访问权限，PUBLIC代表所有用户
GRANT ALL ON demo TO demo_role; --赋给用户所有权限
GRANT SELECT ON demo TO PUBLIC; --将SELECT权限赋给所有用户

\z或\dp指令显示用户访问权限。
\h GRANT显示所有可设置的访问权限

撤销用户访问权限
语法格式如下:
REVOKE permission_type ON table_name FROM user_name;
其中permission_type和table_name含义与GRANT指令中相同。

 

用户组
在postgres中用户实际上是role，同时组也是role。 包含其他role的role就是组。

创建组示例:
CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
GRANT temporary_users TO test_user;

INHERIT权限：该属性使组成员拥有组的所有权限
ALTER ROLE test_user INHERIT;

切换ROLE
SET ROLE role_name; --切换到role_name用户
RESET ROLE; --切换回最初的role

删除用户和组

DROP ROLE role_name;
DROP ROLE IF EXISTS role_name;

删除用户 先revoke 再DROP

revoke all on database yqxc_archive from xiaoming;

DROP user xiaoming; #区分大小写的

应用

create user zabbix;

create database zabbix owner zabbix;

alter user zabbix password zabbix;

grant all on DATABASE zabbix to zabbix;

参考链接：https://www.cnblogs.com/zhangeamon/p/8990232.html

create database wanglibao;   #创建数据库

grant all on database wanglibao to postgres;   #赋权限

创建用户并授权

create user xiaoming with password '8Uy';
grant all on DATABASE yve to xiaoming;
\c mydb
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xiaoming;   #必须执行这一步，要不然没有权限。

参考链接：https://blog.csdn.net/fwhezfwhez/article/details/88854470

 

删除数据库

drop database wlmr;

导出数据库
先进入容器，然后执行pg_dump命令进行导出
docker exec -it 101768619d0c bash
root@101768619d0c:/# pg_dump -U ueiu dev > /var/lib/postgresql/data/dev.sql #dev是要到导出的数据库名

格式：docker exec -it 容器名 pg_dump -U 用户名 -s -t table_name db_name > sql文件保存位置
-s  选项用来只导出表结构，而不会导出表中的数据
-t 选项用来指定要导出的数据库表

如果是远程连接，添加 -h -p参数
docker exec -it group-postgres pg_dump -h host -p port -U leaniot -s -t quake_info gis > ./t.sql

导入数据库
先进入容器，然后执行psql命令
root@101768619d0c:/# psql -U ueiu dev < /var/lib/postgresql/data/dev.sql #导入时的路径还是要填Destination的路径

将数据库文件拷贝至容器卷目录下，下面 . 代表的就是容器卷目录
[root@localhost _data]# cp /data/soft/test_ceshi.20201228.sql .
docker exec 容器名称 sh -c "exec psql -U 用户名 -d 数据库名（没有要新建） < /var/lib/postgresql/data/test_ceshi.20201228.sql(该路径为容器内路径)"
[root@localhost _data]# docker exec postgres9.5 sh -c "exec psql -U postgres -d test < /var/lib/postgresql/data/test_ceshi.20201228.sql"

 

参考链接：

https://www.dazhuanlan.com/2019/12/09/5dee33827926e/
https://www.cnblogs.com/zhzhlong/p/11466464.html

使用账号密码登录
psql -U postgres -W （默认账号postgres 密码:启动容器时设置）-W 密码

切换数据库,相当于mysql的use dbname
\c dbname
列举数据库，相当于mysql的show databases
\l
列举表，相当于mysql的show tables
\dt
查看表结构，相当于desc tblname,show columns from tbname
\d tblname

\di 查看索引 

查看数据库版本

SELECT version();

查看数据库大小：

SELECT pg_size_pretty(pg_database_size('数据库名')) As fulldbsize;

查看所有的数据库的大小：

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

查看各数据库数据创建时间：

select datname,(pg_stat_file(format('%s/%s/PG_VERSION',
case
when spcname='pg_default' then 'base'
else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/'
end,
t1.oid))).*
from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;

PostgreSQL 9.5版本数据目录下的目录。需要注意的是各版本并不完全相同。  参考链接：https://zhidao.baidu.com/question/653204219422276045.html

base： 该目录包含数据库用户所创建的各个du数据库，同时也包括postgres、template0和template1的pg_defaulttablespace    

pg_xlog：该目录包含wal日志。    

hgdb_log： 该目录包含数据库日志。(目录名可自定义)    

global：  该目录包含集群范围的各个表和相关视图。 （ pg_database、 pg_tablespace ）    

pg_clog：  该目录包含事务提交状态数据。    

pg_multixact：  该目录包含多事务状态数据（等待锁定的并发事务）    

pg_notify ：该目录包含LISTEN/NOTIFY状态数据。    

pg_serial：该目录包含了已经提交的序列化事务的有关信息。    

pg_snapshots：该目录包含导出的快照。    

pg_stat_tmp：该目录包含统计子系统的临时文件。    

pg_subtrans：该目录包含子事务状态数据。    

pg_tblspc：该目录包含表空间的符号链接。    

pg_twophase：该目录包含预备事务的状态文件。    

pg_commit_ts：该目录包含已提交事务的时间。    

pg_dynshmem：该目录包含动态共享内存子系统使用的文件。    

pg_logical：该目录包含逻辑解码的状态数据。   

pg_replslot：该目录包含复制槽数据。    

pg_stat：该目录包含统计子系统的永久文件。