PostgreSQL-常用命令

1. psql常用命令

登录pg数据库：
psql postgres postgres 
psql -h ip -p port daname username 
这些连接参数也可以通过环境变量指定： 
export PGDATABASE=postgres 
export PGHOST=xxx.xxx.xxx.xxx
export PGPORT=5432
export PGUSER=postgres 

\c 显示连接信息 
\conninfo 显示当前连接的相关信息 

1.同一用户切换到其他数据库 
\c testdb 

2.同一库切换登录用户 
\c ‐ user1 
单独切换用户，需要加 “-” 

3.当前实例同时切换数据库和登录用户 
\c postgres postgres

4.切换主机和数据库 
\c testdb ‐ xxx.xxx.xxx.xxx
主机参数前面需要加 “-” 

5.切换主机、数据库和登录用户(端口为默认) 
\c testdb user1 xxx.xxx.xxx.xxx
 
6.切换主机、数据库和登录用户以及端口 
\c postgres user1 xxx.xxx.xxx.xxx

断开连接 
ctrl + d 
\q 
exit 
\encoding utf8 指定客户端字符集 

常用快捷命令
\l 查看数据库 
\db 查看表空间 
\d 查看表 
\dt 查看表
\di 查看索引 
\ds 查看序列 
\dv 查看视图 
\df 查看函数 
\d+查看表详细信息 
\d t1 查看表定义 
\dt+查看表大小 
\di+查看索引大小 
\sf 查看函数代码 
\dn 查看模式 
\du or \dg 查看用户 
\dp or \z 查看表权限

格式化输出命令-\pset
\pset border 0： 表示输出内容无边框。 
\pset border 1： 表示输出内容只有内边框。也是默认输出格式。 
\pset border 2： 表示输出内容内外都有边框。 
\pset format unaligned 以分隔符分隔输出结果，默认分隔符是“|” 
\pset fieldsep '\t'设置 Tab 分隔符 
\t 只显示记录，不显示列字段
\x 切换扩展输出模式 

输入\输出
执行 sql 脚本
vi test.sql 
create table test(id int); 
insert into test values(1); 
insert into test values(2); 
insert into test values(3); 

psql dbname username -f /home/test.sql 
\i test.sql 

将查询结果写入文件
\o test.sql 

将 SQL 命令输出到文件中 
\o test.txt 
select * from t1; 
其他命令

psql -E dbname username 查看快捷命令对应的实际 SQL 
\set ECHO_HIDDEN on|off
\timing 记时 
\watch 反复执行命令

2.用户管理

语法格式：

create user name 【【with】option [......]】

option 选项：

 SUPERUSER|NOSUPERUSER： 表示创建出来的用户是否为超级用户。

 CREATEDB|NOCREATEDB： 表示创建出来的用户是否具有 CREATE DATABASE 的

权限。

 CREATEROLE|NOCREATEROLE： 表示创建出来的用户是否具有创建其他角色的权

限。

 INHERIT|NOINHERIT： 如果创建的用户拥有某个或某几个角色， 这时若指定

INHERIT， 则表示用户自动拥有相应角色的权限， 否则该用户没有相应角色的权限。

 LOGIN|NOLOGIN： 表示创建出来的用户是否具有 LOGIN 权限。

 REPLICATION | NOREPLICATION：创建出来的用户是否具有复制权限。

 BYPASSRLS | NOBYPASSRLS：决定是否一个用户可以绕过每一条行级安全性（RLS）

策略。 默认是 NOBYPASSRLS。

 CONNECTION LIMIT connlimit： 指明该用户可以使用的并发连接的数量。 默认值

是“-1”， 表示没有限制。

 [ENCRYPTED|UNENCRYPTED]PASSWORD 'password'： 设置密码。

 VALID UNTIL 'timestamp'： 密码失效时间， 如果不指定该子句， 那么口令将永远

有效。

 IN ROLE role_name [,...]： 指定用户成为哪些角色的成员。

 IN GROUP role_name [,...]： 与 IN ROLE 相同， 是已过时的语法。

 ROLE role_name [,...]： role_name 将成为这个新建的角色的成员。

 ADMIN role_name [,...]： role_name 将有这个新建角色的 WITH ADMIN OPTION

权限。

 USER role_name [,...]： 与 ROLE 子句相同， 但已过时。

 SYSID uid： 此子句主要是为了 SQL 向下兼容， 实际没有什么用途。

 2.1创建没有密码的用户

create user test;

后续添加密码：

alter user test password 'test';

2.2创建带密的用户

create user test password 'test';

2.3创建有时间限制的用户

create user test password 'test' valid until '2024-1-1 00:00:00';

2.4创建具有创建数据库和管理角色权限的用户

create user test password 'test' CREATEDB CREATEROLE;

2.5创建具有超级权限的用户

create user test password 'test'  SUPERUSER;

2.6创建复制账号

create user repl REPLICATION password 'repl';

2.7修改用户密码

alter user test password 'xxxx';

2.8移除用户密码

alter user test password null;

2.9更改密码失效日期

alter user test VALID UNTIL  '2025-1-1:00:00:00';

2.10 让密码永久生效

alter user user2 VALID UNTIL 'infinity';

2.11修改用户系统权限，让其用户createdb和createrole的权限

alter user test createdb createrole;

2.12删除用户

drop user test;

3.角色管理

语法格式：

CREATE ROLE name 【with option 【...】】

3.1创建角色

create role role1 login createdb createrole;

3.2将角色授权给用户

grant all on t1 to role1;

grant role1 to user1;

3.3修改角色权限

alter role role1 superuser;

3.4删除角色

drop role role1；

4.权限管理

grant 语法：

grant {权限} on {对象} to {角色}

revoke 语法格式：

revoke  {权限} on {对象}  from {角色}

示例：

grant all on t1 to role1;

revoke all on t1  from  role1;

5.查看权限

5.1查看表权限：

5.1.1方式1：\dp

示例，user1=r*w/postgres 指明 user1 是被授权者，r(select)权限、*（with grant option）、w(update)，postgres 授权者

例如，授权 t1 的查询权限给所有人:

GRANT SELECT ON t1 TO PUBLIC;

注意：public 表示所有用户

GRANT SELECT, UPDATE, INSERT,DELETE ON t1 TO user2;

GRANT SELECT (name), UPDATE (age) ON t1 TO user2;

5.1.2方式二：

select * from information_schema.table_privileges where table_name='t1';

5.2查看用户权限

select usename,usecreatedb,usesuper,userepl,usebypassrls,valuntil  from pg_user;