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:该目录包含统计子系统的永久文件。