【Postgresql学习笔记】

Postgresql学习笔记

提示：这里可以添加系列文章的所有文章的目录，目录需要自己手动添加

前言

包含自己整理和收集的顶级Postgresql的文章。

零、常用文档链接

PostgreDSQL 14.2

一、psql的常用命令

psql -h -p [dbname] [username]
psql -E postgres 显示"" 命令的实际SQL语句， “\set ECHO_HIDDEN on||off"来开关
“\?” 查询psql的快捷命令, 最有用的命令
\h 查询数据库的SQL语法, 最有用的命令:\h create user
\l[+] 显示数据库列表
\db[+] 表空间列表
\d [pattern] 显示每个匹配"pattern”(表,视图,索引,系列)的信息
\d 列出当前数据库的所有表,d+ 更详细的信息
\d {table_name | index_name | 通配符 “*” | “?” } 只显示知道对象的信息
\dt,\di.\ds,\dv,\df 只显示表,索引,序列,视图,函数
\timing on 只显示执行SQL的时间
\dn 列出所有schema,\db 列出所有的表空间,\du \dg 列出所有的角色或用户
\dp \z 显示表的权限分配情况
\x 按列展示,相当于MySQL的\G
\i <文件名> 执行sql文件,相当于psql -f
\set AUTOCOMMIT off(后面没有分号；） 或"begin;" 开启事物的手动提交";默认为事物为自动提交
\d 两次Tab键,命令自动补全或给出提示
(S = 显示系统对象,+ = 额外详细信息): \db[+] [pattern] 列出表空间，\d[S+] [pattern]描述表视图索引
\set PROMPT1 '%n@%m %~%R%# ’

二、好的文章

PostgreSQL 逻辑结构 和 权限体系 介绍

ACL（Access Control List 访问控制列表）

List databases user has privilege to connect

FUNCTIONS-INFO-ACCESS-TABLE

三、表空间 tablepsace

Creation of the tablespace itself must be done as a database superuser, but after that you can allow ordinary database users to use it. To do that, grant them the CREATE privilege on it.

ALTER DATABASE name SET TABLESPACE new_tablespace

The fourth form changes the default tablespace of the database. Only
the database owner or a superuser can do this; you must also have
create privilege for the new tablespace. This command physically moves
any tables or indexes in the database’s old default tablespace to the
new tablespace. The new default tablespace must be empty for this
database, and no one can be connected to the database. Tables and
indexes in non-default tablespaces are unaffected.
ALTER SYSTEM SET default_tablespace=tablespace_name;
This will change the default tablespace for all databases, so it should not be used if there is different default tablespace per-database.

四、 PUBLIC schema

Note that by default, everyone has CREATE and USAGE privileges on the schema public. This allows all users that are able to connect to a given database to create objects in its public schema.If you do not want to allow that, you can revoke that privilege:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(The first “public” is the schema, the second “public” means “every user”.

五、 备份

1.例子

代码如下（示例）：

总结：不积跬步无以至千里。