PostgreSQL常用命令-创建数据库、用户、多模式schema、修改用户密码、访问其他模式或在其他模式建表的权限、切换用户、无法删除数据库、导入导出sql、活用set search_path、建表

序言

下面都以 test 数据库，my_schema 模式名为例，主用户是 postgres ，新建用户是 admin

测试环境：

Windows 10

1、创建 PostgreSQL 的数据库命令

tips: 使用双引号区分英文大小写名字，单引号隔开SQL命令和内容，SQL 命令英文大小写都可以

小写 t

create database test owner postgres;

大写 T

create database "Test" owner postgres;

test 和 Test 这两个是不同的数据库

owner 参数可以指定拥有者（所有者），但如果不指定 owner ，那么就会默认当前用户

查看数据库命令：（注意是英文小写的 l）

\l

postgres=# \l
                                                        数据库列表
   名称    |  拥有者  | 字元编码 |            校对规则            |             Ctype              |       存取权限     
-----------+----------+----------+--------------------------------+--------------------------------+-----------------------
 Test      | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |

2、创建用户命令

小写

create user admin with password 'mypassword';

大写

create user "Admin" with password 'mypassword';

查看用户命令：

\du

postgres=# \du
                             角色列表
 角色名称 |                    属性                    | 成员属于
----------+--------------------------------------------+----------
 Admin    |                                            | {}
 admin    |                                            | {}
 postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}

3、修改用户密码

alter user admin with password 'mypassword';

4、 创建多模式

create schema my_schema;

查看当前的多模式现状命令：

简略信息

\dn

postgres=# \dn
   架构模式列表
  名称  |  拥有者
--------+----------
 admin  | postgres
 public | postgres
(2 行记录)

详细信息的加 + 号

\dn+

postgres=# \dn+
                           架构模式列表
  名称  |  拥有者  |       存取权限       |          描述
--------+----------+----------------------+------------------------
 admin  | postgres | postgres=UC/postgres+|
        |          | admin=U/postgres     |
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(2 行记录)

5、修改多模式的拥有者

ALTER SCHEMA my_schema OWNER TO postgres;

6、分配用户访问其他模式的权限或在其他模式建表的权限

比如模式拥有者是 postgres 那么需要分配给其他用户用于访问用户 postgres 的 my_schema 模式内的表，例如用户 admin 。

# 重点输入如下命令，修改用户admin权限可以访问 schema 模式 admin
# 1
grant all privileges on database "test" to admin;
# 2
# 在 my_schema 模式内拥有访问修改权限 U
grant usage on schema my_schema to admin;
# 在 my_schema 模式内拥有创建表权限 C
grant create  on schema my_schema to admin;
# 3
grant all privileges on all tables in schema my_schema to admin;
 

一般来说，只需要配置上面四个个命令，就可以让某个用户拥有访问和某个数据库内的其他模式的权限，以及在其他模式内创建表的权限，比如上面的例子意义是，首先，用户 admin 第一步需要拥有访问数据库 test 的权限，才能进入数据库 test 内部的模式进行访问，然后在赋予用户 admin 拥有访问数据库内 my_schema 模式的权限，进入数据库 test 内部存在的 my_schema 模式后，还要赋予用户 admin 拥有访问 my_schema 模式内部的表。

赋予在某模式下拥有可以修改表和创建表的权限效果演示：

6.1 撤销权限命令

revoke all privileges on schema my_schema from public

这里的 public 代指所有的用户（public代表了all users），指的是所有用户都无法访问 my_schema 模式或在 my_schema 模式内创建数据库对象（比如建表或修改表访问表等操作）

也可以修改为特定的用户名：（如 admin 用户）

revoke all privileges on schema my_schema from admin

撤销了特定用户的的权限效果演示：

多用户多模式权限分配步骤

1. 用户 admin 第一步需要拥有访问数据库 test 的权限（当然这里是包含了在这个数据库内拥有创建、更新‘’删除表等等的权限，访问权限只是其中之一）；
2. 赋予用户 admin 拥有访问数据库内 my_schema 模式的权限；
3. 赋予用户 admin 拥有访问 my_schema 模式内部的表。

在 PostgreSQL 中也是一样。您需要模式 usage 权限和对象权限才能对对象执行操作，例如表中的 SELECT。类似查询语句的权限，比如 Linux 系统访问目录是需要 rwx 中的 x 权限，usage 差不多就是 x 权限的意思。

下面的命令笔者感觉不需要，感觉有点重复，如果今后需要用到如下的三条命令，再说。笔者会再次补充。

# 上面3个是重点。
grant all privileges on all sequences in schema my_schema to admin;
 
grant select,insert,update,delete on all tables in schema my_schema to admin;
 
grant all on schema my_schema to admin;

权限理解触发截图：

7、切换数据库并切换用户

如下命令，意义是，切换当前数据库，相当于 MySQL 数据库的 use "Test" 切换数据库命令，然后后面可以跟着用户名，相当于是切换当前用户，从 postgres 用户切换为用户 admin

\c "Test" admin

先查看当前用户：select current_user;

postgres=# select current_user;
 current_user
--------------
 postgres
(1 行记录)

postgres=# \c "Test" admin;
用户 admin 的口令：
您现在已经连接到数据库 "Test",用户 "admin".
Test=> select current_user;
 current_user
--------------
 admin
(1 行记录)

8、删除数据库和删除用户

删除数据库

drop databse test;

删除用户

drop use admin;

8.1、删除数据库时，报错无法删除数据库的问题，会话存在

Postgresql删除数据库失败解决方法

“ERROR: database "dbname" is being accessed by other users
DETAIL: There are 2 other sessions using the database.”

需断开数据库所有连接执行

输入如下命令，断开 test 数据库的所有连接，注意 datname ='删除的数据库名'

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='test' AND pid<>pg_backend_pid();

如果还是无法删除数据库，请看如下链接：

关于 PostgreSQL 删除数据库 - 命令行删除，报错数据库不存在，pgadmin 报错存在会话链接 导致无法删除数据库问题

9、导入/导出 sql 文件命令

9.1 导入sql 文件命令

psql -h 127.0.0.1 -p 5432 -U postgres -d test -f initData.sql

-h 主机ip

-p 主机 postgresql 数据库监听的端口号

-U 主机 postgresql 数据库的验证用户

-d 指定访问的数据库名

-f 要导入的sql 文件绝对路径或相对路径，看当前位置。

9.2 导出 sql 文件命令

pg_dump -U postgres -c test -f /home/test.sql

10、活用 set search_path

如果要在 postgresql 数据库中使用这个 set search_path 命令，需求是为了查看当前数据库内其他模式内的表。那么首先就得知道 \d 或 \d+ （查看表命令）

可以查看当前的 search_path 的内容

postgres=# show search_path;
   search_path
-----------------
 "$user", public
(1 行记录)

第一个值指定一个与当前用户同名的schema被搜索。如果不存在这样的schema，该条目将被忽略。第二个元素指的是我们已经看到的public schema。

search_path中存在的第一个schema是创建新对象的默认位置。这就是默认情况下，对象是在public schema中创建的原因。当对象在没有schema限定的情况下被引用时（表修改、数据修改或查询命令），search_path被遍历，直到找到一个匹配的对象。因此，在默认配置中，任何未经限定的访问又只能引用public schema。

设置参数
为了把我们的新schema放在路径中，我们使用：

SET search_path TO myschema,public;

我们在这里省略了$user，因为我们没有立即需要它，然后我们可以在没有schema限定的情况下访问该表。

DROP TABLE mytable

另外，由于myschema是路径中的第一个元素，新的对象将默认在其中创建。

我们也可以这样写。

SET search_path TO myschema

然后我们就不再能够在没有明确限定的情况下访问public schema了。除了默认存在之外，public schema并没有什么特别之处。它也可以被放弃。

参数理解：

"$user" :这是指名称为当前用户的模式。

'public': 这是指所有用户都可以访问的默认模式。

总结

总之，就是说假如除了默认的模式 public ，还存在一个新的模式，叫做 my_schema，那么按照原来的命令 \d 或 \d+ 命令，是无法查询其他模式下的表的，所以需要配置 set search_path 将查询指向另外的一个新模式，上面的说法，有些繁琐，直接上例子。

演示：

database：test
schema：public、my_schema

默认情况：

postgres=# show search_path;
   search_path
-----------------
 "$user", public
(1 行记录)

修改后情况：set search_path to my_schema,public;

postgres=# set search_path to my_schema,public;
SET
postgres=# show search_path;
  search_path
---------------
 my_schema, public
(1 行记录)

然后就可以使用命令 \d 或 \d+ 查询到新模式 my_schema 和 public 模式内的表情况了

11、创建表（or 其他模式的表）

11.1 模式的使用与创建模式内的表：

创建表时在表明前面添加模式名，若不指定模式名，默认使用search_path第一个模式创建数据表，命令：
create table 模式名.表名 (列名 数据类型);

eg：创建两个数据表，testtable1使用默认模式名创建，testtable2 指定模式名创建

1. 默认是 public 模式，所以是在 public 模式内，创建表 testtable1
2. 如果是指定模式，自然就不是在 public 模式下创建表 testtable2

11.2 模式的更改：

a)重命名：alter schema 旧名字 to 新名字;
b)修改模式的归属用户：alter schema 模式名 to 新用户;
模式的删除：drop schema 模式名;

---

参考链接

1. 在PostgreSQL中使用SET search_path有什么用？

2. PostgreSQL_通过schema控制用户权限

3. postgres中schema访问权限设置

4. postgres 基于Schema 权限访问探讨

5. postgresql数据库中多个Schemas互相访问

6. Postgres ： 创建schema、创建表空间与指定用户权限

7. 为什么POSTGRESQL的\ DT只显示公共模式表？

8. 第三节——模式

9. PostgreSQL中search_path参数解密

10. 关于数据库：SCHANT上的GRANT USAGE到底能做什么？

11. linux查看文件权限命令 查看文件权限 linux

12. PostgreSQL-表空间、数据库、模式、角色实践

13. postgreSQL数据库基本操作和导入导出数据-命令行

14. PostgreSQL如何导出数据库文件