| PostgreSQL常用命令 | 您所在的位置：网站首页 › 创建数据库对象的命令有哪些 › PostgreSQL常用命令 |
PostgreSQL常用命令
|
目录
序言1、创建 PostgreSQL 的数据库命令tips: 使用双引号区分英文大小写名字，单引号隔开SQL命令和内容，SQL 命令英文大小写都可以
2、创建用户命令3、修改用户密码4、 创建多模式5、修改多模式的拥有者6、分配用户访问其他模式的权限或在其他模式建表的权限6.1 撤销权限命令多用户多模式权限分配步骤
7、切换数据库并切换用户8、删除数据库和删除用户8.1、删除数据库时，报错无法删除数据库的问题，会话存在
9、导入/导出 sql 文件命令9.1 导入sql 文件命令9.2 导出 sql 文件命令
10、活用 set search_path总结
11、创建表（or 其他模式的表）11.1 模式的使用与创建模式内的表：11.2 模式的更改：
参考链接
序言
下面都以 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 模式内部的表。 赋予在某模式下拥有可以修改表和创建表的权限效果演示：
这里的 public 代指所有的用户（public代表了all users），指的是所有用户都无法访问 my_schema 模式或在 my_schema 模式内创建数据库对象（比如建表或修改表访问表等操作） 也可以修改为特定的用户名：（如 admin 用户） revoke all privileges on schema my_schema from admin撤销了特定用户的的权限效果演示：
在 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;权限理解触发截图：
如下命令，意义是，切换当前数据库，相当于 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 pidpg_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 指定模式名创建 默认是 public 模式，所以是在 public 模式内，创建表 testtable1如果是指定模式，自然就不是在 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如何导出数据库文件 |
【本文地址】