一.基本使用
1.登录数据库
Pgsql 登录时，必须使用 postgres 用户，登录后的命令提示符为“postgres=#postgres 表示你当前所在的库
2.数据库操作
2.1列出库
常用的三种方法如下:
方法一
在 PostgreSQL 的交互式终端 psql 中，”开头的命令称为元命令(类似MySQL 的 SHOW 语句)，用于快速管理数据库
方法二
1+的输出比\l多了Size,Tablespace 和 Description 列
+:扩展输出，显示更多字段或详细信息
方法三
使用 SOL 命今
pg_database 是系统表:它存储了 PostgreSQL 实例中所有数据库的元信息(如数据库名称、所有者、编码等)。属于系统目录(System Catalog):类似 MySQL的 information schema，但 PostgreSQl 的系统目录更底层且直接存储在pg_catalog 模式中。
pg_database 是系统目录表，所以无论当前连接到哪个数据库，该表始终可见系统表默认属于pg_catalog 模式，而pg_catalog 始终位于搜索路径(searchpath)的首位。因此，查询时无需显式指定模式(如pg catalog.pg database)。
2.2创建库
2.3删除库
2.4切换库
2.5查看库大小
函数以字节为单位返回数据库的大小
pg_size_pretty()函数将字节转为更易于阅读值
3.数据表操作
3.1列出表
列出表的常用方法:
pg_tables 是视图:属于pg_catalog 模式，但它是基于 pg_class 和pg namespace的逻辑视图，并非物理表。无需切换数据库，直接查询pg catalog.pg tables 即可获取当前数据库的表信息
3.2创建表
PostgreSQL 支持标准的 SQL 类型 int、smallint、real、double precision、char(N)、varchar(N)、date、time、timestamp 和 interval，还支持其他的通用功能的类型和丰富的几何类型。PostgreSqL 中可以定制任意数量的用户定义数据类型。因而类型名并不是语法关键字，除了SL,标准要求支持的特例外。
3.3复制表
要将已有的 table_name 表复制为新表 new_table，包括表结构和数据
3.4删除表
3.5查看表结构
4.模式操作命令
在 PostgreSQL 中，模式(Schema)是一个逻辑容器，用于组织和管理数据库对象(如表、视图、函数、索引等)它类似于文件系统中的文件夹，帮助你在同一个数据库中分类存储不同的对象，避免命名冲突，并实现权限隔离
4.1创建模式
4.2 默认模式
PostgreSQL 每个数据库都有一个默认模式 public。
如果创建对象(表、视图等)时不指定模式，默认会放在public 模式中。
通过 search path 参数可以设置模式的搜索优先级(类似 PATH 环境变量):
search path用于控制对象解析顺序，避免每次查询都要写模式名$user，public 表示优先查找当前用户同名模式，再找public 模式。
4.3删除模式
4.4 查看所有模式
SQL 查询，列出当前库中所有模式
4.5在指定模式中创建表
4.6切换当前模式
切换模式也就是调整search path 的搜索范围
4.7查看当前所在schema
4.8查看搜索路径(SearchPath)
4.9 PostgresQl 的模式隔离性
PostgreSQL 的模式是数据库内的逻辑分组，不同模式可以存在同名表。这也是和 mysql 的不同之处
跨模式查询需显式指定模式名(如 schemal.users),或通过 search path 设置默认模式。
无需切换数据库连接，所有操作在同一数据库内完成。
步骤1:创建一个数据库
创建数据库 mydb
步骤2:在数据库中创建两个模式
创建模式 schemal 和 schema2
步骤3:在每个模式中创建同名表，并插入数据
在 schemal 中创建 users 表
在 schema2 中创建同名 users 表
步骤 4:跨模式查询
查询 schemal.users 和 schema2.users(需显式指定模式名)
设置 search path 切换默认模式(不需显式指定模式名)
5.数据操作
5.1添加数据
在postgres库，新建表 test
5.2查询数据
5.3修改数据
5.4删除数据
6.备份与恢复
PostgreSqL 数据库应当被定期地备份。虽然过程相当简单，但清晰地理解其底层技术和假设是非常重要的。
有三种不同的基本方法来备份 PostgreSQL 数据:
SQL 转储上
文件系统级备份
连续归档了
每一种都有其优缺点，我们主要以 SQL 转储为主。
6.1 SQL 转储
SQL 转储方法的思想是创建一个由 SQL,命令组成的文件，当把这个文件回馈给服务器时，服务器将利用其中的SQL命令重建与转储时状态一样的数据库。 PostgreSQ 为此提供了工具 pg_dump。这个工具的基本用法是:
pg_dump dbname >dumpfile
正如你所见，pg_dump把结果输出到标准输出。我们后面将看到这样做有什么用处。 尽管上述命令会创建一个文本文件，pg_dump 可以用其他格式创建文件以支持并行 和细粒度的对象恢复控制。
pg_dump 是一个普通的 PostgreSQL, 客户端应用(尽管是个 相当聪明的东西)这就意味着你可以在任何可以访问该数据库的远端主机上进行备份工作。但是请记住 pg_dump不会以任何特殊权限运行。具体说来，就是它必须要有你想备份的表的读 权限，因此为了备份整个数据库你几乎总是必须以一个数据库超级用
户来运行它(如果你没有足够的特权来备份整个数据库，你仍然可以使用诸如schema 或-t table选项来备份该数据库中你能够访问的部分)。-h
要声明pg_dump连接哪个数据库服务器，使用命令行选项-hhost者和 -pport。默认主机是本地主机或你的 PGHOST 环境变量指定的主机。似地,默认端口是环境变量 PGPORT 或(如果 PGPORT 不存在)内建的默认值。(服务器通常有相同的默认值，所以还算方便。)
和任何其他 PostgreSQL 客户端应用一样，pg_dump 默认使用与当前操作系统用户名同名的数据库用户名进行连接。要使用其他名字,要么声明-U选项，要么设置环境变量 PGUSER。请注意 pg_dump 的连接也要通过客户认证机制。
pg dump 对于其他备份方法的一个重要优势是，pgdump的输出可以很容易地在新版本的 PostgreSqL中载入，而文件级备份和连续归档都是极度的服务器版本限定的。pg_dump 也是唯一可以将一个数据库传送到一个不同机器架构上的方法，例如从一个32位服务器到一个 64 位服务器.
由 pg_dump 创建的备份在内部是一致的，也就是说,转储表现了 pg_dump开始运行时刻的数据库快照,且在 pg dump 运行过程中发生的更新将不会被转储pg_dump 工作的时候并不阻塞其他的对数据库的操作。(但是会阻塞那些需要排它锁的操作，比如大部分形式的 ALTER TABLE)
6.2从转储中恢复
pg_dump 生成的文本文件可以由 psq1 程序读取。 从转储中恢复的常用命令是:
psql dbname< dumpfile
其中 dumpfile 就是 pg_dump 命令的输出文件。这条命令不会创建数据库dbname，你必须在执行 psql 前自己从 template0 创建(例如，用命令 createdb-T template0 dbname)。psql 支持类似 pg_dump 的选项用以指定要连接的数据库服务器和要使用的用户名。参阅 psql的手册获取更多信息。非文本文件转储可以使用 pg restore 工具来恢复。
在开始恢复之前，转储库中对象的拥有者以及在其上被授予了权限的用户必须已经存在。如果它们不存在，那么恢复过程将无法将对象创建成具有原来的所属关系以及权限(有时候这就是你所需要的，但通常不是)
默认情况下，psq1 脚本在遇到一个 SQL 错误后会继续执行。你也许希望在遇到一个 SQL 错误后让 psq1 退出,那么可以设置 ON ERROR STOP 变量来运行 psql,这将使 psq1 在遇到 SQL 错误后退出并返回状态 3:
psql --set ON ERROR STOP=on dbname< infile
不管怎样，你将只能得到一个部分恢复的数据库。作为另一种选择，你可以指定让整个恢复作为一个单独的事务运行,这样恢复要么完全完成要么完全回滚这种模式可以通过向 psql 传递-1 或–single-transaction 命令行选项来指定。在使用这种模式时,注意即使是很小的一个错误也会导致运行了数小时的恢复被回滚。但是，这仍然比在一个部分恢复后手工清理复杂的数据库要更好。
pg_dump 和 psql 读写管道的能力使得直接从一个服务器转储一个数据库到另一个服务器成为可能，例如:
pg_dump -h hostl dbnamepsql -h host2 dbname
注意:
pg_dump 产生的转储是相对于 template0。这意味着在 template1 中加入的任何语言、过程等都会被 pg dump 转储。结果是，如果在恢复时使用的是一个自定义的 template1，你必须从 template0 创建一个空的数据库，正如上面的例子所示。
一旦完成恢复，在每个数据库上运行ANALYZE是明智的举动，这样优化器就有有用的统计数据了。
6.3 使用 pg dumpall
pg_dump每次只转储一个数据库，而且它不会转储关于角色或表空间(因为它们是集簇范围的)的信息。为了支持方便地转储一个数据库集的全部内容，提供了 pg dumpal1 程序。pg dumpal1 备份一个给定集簇中的每一个数据库，并且也保留了集簇范围的数据，如角色和表空间定义。该命令的基本用法是:
pg dumpall >dumpfile
转储的结果可以使用 psq1 恢复:
psql -f dumpfile postgres
(实际上，你可以指定恢复到任何已有数据库名，但是如果你正在将转储载入到一个空集簇中则通常要用(postgres)。在恢复一个pg dumpall 转储时常常需要具有数据库超级用户访问权限，因为它需要恢复角色和表空间信息。如果你在使用表空间，请确保转储中的表空间路径适合于新的安装。
pg dumpal1 工作时会发出命令重新创建角色、表空间和空数据库，接着为每一个数据库 pgdump。这意味着每个数据库自身是一致的，但是不同数据库的快照并不同步。
集簇范围的数据可以使用 pg_dumpall 的globals-only选项来单独转储。如果在单个数据库上运行 pg_dump 命令，上述做法对于完全备份整个集簇是必需的。
7.远程连接
7.1修改 PostgreSQL 监听地址
默认 PostgreSQl 监听的地址是 127.0.0.1,别的机器无法远程连接上，所以需要调整，修改 postgresql.conf 文件。
更改第 60行，取消注释并把localhost 改成*
重启服务
7.2配置访问权限
默认是只能本地访问 PostgreSQ的，我们需要在 pg hba.conf 里面配置找到 IPv4 local connections 这一行，在这一行下面添加
host:这指定了连接类型。host 表示该规则适用于通过 TCP/IP 进行的远程连接。如果是本地连接，通常会使用 1ocal。
a11:这定义了哪些数据库可以接受这个规则。a11 表示这个规则适用于所有数据库。你也可以指定特定的数据库名，例如mydatabase。
a11:这定义了哪些用户可以接受这个规则。all 表示这个规则适用于所有用户。你也可以指定特定的用户名，例如myuser。
0.0.0.0/0:这定义了哪些客户端IP地址或IP地址范围可以接受这个规则。0.0.0.0/0 是一个特殊的 CIDR 表示法,它表示任何 IP 地址(即没有 IP 地址限制)。你也可以指定具体的IP地址，如192.168.1.100，或者 IP 地址范围，如 192.168.1.0/24。
trust:这定义了认证方法。trust 表示不需要密码或其他任何形式的认证，客户端可以直接连接。这通常只在本地或受信任的网络环境中使用，因为它允许任何人无需认证即可访问数据库。请注意,使用 trust 认证方法允许任何 IP 地址连接到你的数据库，而不需要任何认证，这是非常不安全的。这通常只在开发或测试环境中使用,并且应该始终确保数据库服务器不暴露在不受信任的网络中在生产环境中，你应该使用更安全的认证方法
7.3重启服务
7.4验证远程连接
使用其它主机远程连接本机数据库，设置的是trust，无密码，可直接登录
8.重置密码
8.1备份配置文件
对 pg_hba.conf 文件，进行备份
8.2修改配置文件
8.3重启服务
8.4修改密码
8.5恢复 pg hba.conf 配置文件。
将 postgresql.confbak 文件的内容覆盖 pg_hba.conf，重启 PostgreSQl 数据库服务器，重新登陆时，如果提示输入密码，则输入刚才修改的密码即可