第 2 章 数据库管理
2.1 配置文件
postgresql.conf
该文件包含一些通用设置，比如内存分配、新建 database 的默认存储位置、PostgreSQL 服务器的 IP 地址、日志的位置以及许多其他设置。
pg_hba.conf
该文件用于控制 PostgreSQL 服务器的访问权限，具体包括：允许哪些用户连接到哪个数据库，允许哪些 IP 地址连接到本服务器，以及指定连接时使用的身份验证模式。
pg_ident.conf
如果该文件存在，则系统会基于文件内容将当前登录的操作系统用户映射为一个 PostgreSQL 数据库内部用户的身份来登录。有些人会把操作系统的 root 用户映射为 PostgreSQL 的 postgres 超级用户账号。
说明：角色即表示用户，又表示一组权限的集合。
- 创建单个用户等价于创建一个同名的角色。可以有登录权限。
- 角色也可以表示一组权限的集合。集合中可以包含多个用户。
以超级用户身份连接到任何一个数据库上查找配置文件的位置。(pg13版本 系统表信息更详细)
SELECT name, setting FROM pg_settings WHERE category = 'File Locations';
name | setting
--------------------+------------------------------------------
config_file | /etc/postgresql/9.6/main/postgresql.conf
data_directory | /var/lib/postgresql/9.6/main
external_pid_file | /var/run/postgresql/9.6-main.pid
hba_file | /etc/postgresql/9.6/main/pg_hba.conf
ident_file | /etc/postgresql/9.6/main/pg_ident.conf
(5 rows)
2.1.1 让配置文件生效
有些配置项修改后需要重启 PostgreSQL 服务实例才能生效，重启时会关闭所有客户端连接。有的配置项只需重新加载一下配置文件即可生效，此后连接上来的新用户都会自动读取到新的配置。重新加载配置文件时，原来已连接的用户会话不会受到影响。如果你不确定修改了某个配置后是否需要重启，请查看下该配置项的 context 属性（在系统表pg_settings中），如果是 postmaster，那么需要重启；如果是 user，那么重新加载配置文件即可。
01. 重新加载配置文件
控制台窗口执行以下命令
pg_ctl reload -D 你的数据目录
或，以服务的形式安装了 PostgreSQL，那么执行以下命令即可
service postgresql-9.5 reload
或，以超级用户身份登录到任何数据库并执行以下 SQL
SELECT pg_reload_conf();
02. 重启PostgreSQL运行实例
PostgreSQL 运行实例无法通过执行 PostgreSQL 本身的命令行来实现重启，只能通过操作系统的命令来实现。
service postgresql-9.6 restart
或
pg_ctl restart -D 你的数据目录
2.1.2 postgresql.conf
postgresql.conf 文件包含了 PostgreSQL 服务正常运行所必需的基础设置。你可以在数据库级、用户级、会话级甚至是函数级重载这些设置。
pg9.4版本中引入了一个新的名为 postgresql.auto.conf 的配置文件（可能会频繁修改）。主要目的是为了保护postgresql.conf 的配置文件。在postgres里这两个文件同时生效，仅仅是优先级不同，换句话说 auto.conf 配置文件优先级高于 .conf 文件 。值得注意的是 auto.conf（二进制）这个文件必须在 psql 中使用 alter system 来修改，而.conf（可读写）可以直接在文本编辑器中修改。如果auto.conf文件不存在，重启数据库或重新加载利用.conf文件可以生成auto.conf文件。例如，你使用很多alter system命令后搞的一团糟，那么你只需要删除auto.conf文件，再执行pg_ctl reload加载生成即可。如果auto.conf文件存在，重启数据库或重新加载.conf文件会覆盖auto.conf文件中的同名参数值。
01. 查看postgresql.conf中的配置
通过查询 pg_settings 视图即可查看所有配置项内容，无须打开配置文件。
SELECT
name,
context ➊,
unit ➋,
setting, boot_val, reset_val ➌
FROM pg_settings
WHERE name IN ( 'listen_addresses','deadlock_timeout','shared_buffers',
'effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY context, name;
name | context | unit | setting | boot_val | reset_val
------------------------+---------------+-------+-----------+-----------+-----------
listen_addresses | postmaster | | * | localhost | *
shared_buffers | postmaster | 8kB | 131584 | 1024 | 131584
deadlock_timeout | superuser | ms | 1000 | 1000 |
effective_cache_size | user | 8kB | 16384 | 16384 | 16384
maintenance_work_mem | user | kB | 16384 | 16384 | 16384
work_mem | user | kB | 5120 | 1024 | 5120
➊ context 字段代表配置项的作用范围。各配置项的作用范围大小不一，具体取决于 context字段的内容。
-
context 值为 user 表示是用户级配置项，它可以被每个用户单独修改，也就是说该配置项针对每个用户都可以有不同的值，用户修改后会在自己的所有会话中生效。如果是超级用户修改了一个user 级的配置项，那么所有此后连接上来的用户都会将这个修改过的值作为默认值。
-
context 值为 superuser 表示是超级用户级配置项，只能由超级用户来修改，修改后会在所有用户会话中生效。非超级用户不能在自己的会话中修改覆盖这个值。
-
context 值为 postmaster 表示是整个服务实例级配置项，更改后需要重启 PostgreSQL 服务才能生效。
-
context 为 user 和 superuser 的配置项可以在 database 级、用户级、会话级和函数级分别设置。比如对于一个能写出非常烧脑的 SQL 的专家用户来说，work_mem 参数应该设置得大一些。又比如，如果有一个函数里面会进行密集的排序操作，那么可以仅针对此函数调大 work_mem 的值。database 级、用户级、会话级和函数级的参数设置不需要执行重加载操作。数据库级的参数设置会在用户下次连接到该数据库时生效。会话和函数级的参数设置立即生效。
➋ 请注意内存相关参数所使用的单位。例如：执行 SHOW shared_buffers; 显示的结果是 1028MB（而非前面查出来的 131 584 个 8KB）。类似地，运行 SHOW deadlock_timeout; 返回的是 1s（而非前面查出来的 1000 ms）。如果你想以合适的单位查看所有参数，请执行 SHOW ALL。
➌ setting 是指当前设置；boot_val 是指默认设置；reset_val 是指重新启动服务器或重新加载设置之后的新设置。修改了设置后，一定要记得查看一下 setting 和 reset_val 并确保二者是一致的，否则说明设置并未生效，需要重新启动服务器或者重新加载设置。
9.5 版中引入了一个新的 pg_file_settings 视图，通过该视图也可以进行配置信息查询。查询该视图会列出每个配置项所属的配置文件。其中 applied 字段表示该配置项是否已经生效，如果值为 f，表示需要重启服务器或者重加载配置文件。如果 postgresql.conf 和postgresql.auto.conf 中存在同名配置，那么后者会覆盖前者，前者在 pg_file_settings 中对应的条目会显示 applied 字段为 f。
SELECT name, sourcefile, sourceline, setting, applied
FROM pg_file_settings
WHERE name IN ('listen_addresses','deadlock_timeout','shared_buffers',
'effective_cache_size','work_mem','maintenance_work_mem')
ORDER BY name;
name | sourcefile | sourceline | setting | applied
------------------------+-------------------------------+---------------+---------------+--------
effective_cache_size | E:/data96/postgresql.auto.conf| 11 | 8GB | t
listen_addresses | E:/data96/postgresql.conf | 59 | * | t
maintenance_work_mem | E:/data96/postgresql.auto.conf| 3 | 16MB | t
shared_buffers | E:/data96/postgresql.conf | 115 | 128MB | f
shared_buffers | E:/data96/postgresql.auto.conf| 5 | 131584 | t
请特别注意 postgresql.conf 或者 postgresql.auto.conf 中的以下网络设置，如果设得不对会导致客户端无法连接，修改这些值是一定要重新启动数据库服务的。
listen_addresses
表示 PostgreSQL 服务使用的 IP 地址，一般设置为 localhost，表示本机的 IPV6 或者IPV4 地址。但也有很多人设置为 *，表示使用任意本机 IP 地址均可连接到 PostgreSQL 服务。
port
PostgreSQL 服务的侦听端口，默认值为 5432。这个端口广为人知，因此建议修改为别的端口，这样可以降低受攻击的概率。当然，如果你在同一台机器上启动了多个 PostgreSQL 服务实例，那么每个服务实例的侦听端口都不能重复，此时也需要修改该配置。
max_connections
系统允许的最大并发连接数。
log_destination
这个配置项的名字可能有点误导，它实际指的是日志文件的输出格式而非输出的物理位置。默认值是 stderr。如果你希望保存日志内容以做进一步分析，建议把它修改为 csvlog，这样就更容易将日志导入第三方日志分析工具中使用。注意，如果希望记日志的话，请一定要将logging_collection 配置项设为 on。
介绍的几个配置项会影响整体系统性能，其默认值一般都不是最优的。建议用户对它们有所了解后尽快根据实际情况做优化调整。
shared_buffers
此设置定义了用于缓存最近访问过的数据页的内存区大小，所有用户会话均可共享此缓存区。此设置对查询速度有着重大影响，一般来说设置得越大越好，至少应该达到系统总内存的 25%，但不宜超过 8GB，因为超过后会出现“边际收益递减”效应，即消耗的内存很多，但得到的速度提升却很少，得不偿失。修改此设置需要重启 PostgreSQL 服务。
effective_cache_size
该配置项是一个估算值，表示操作系统分配多少内存给 PostgreSQL 专用。系统并不会根据这个值来真实地分配这么多内存，但是规划器会根据这个值来判断系统能否提供查询执行过程中所需的内存。如果将此设置设得过小，远远小于系统的真实可用内存量，那么可能会给规划器造成误导，让规划器认为系统可用内存有限，从而选择不使用索引而是执行全表扫描（因为使用索引虽然速度快，但需要占用更多的中间内存）。在一台专用于运行 PostgreSQL 数据库服务的服务器上，建议将 effective_cache_size 的值设为系统总内存的一半或者更多。此设置的更改可动态生效，执行重新加载即可。
work_mem
此设置指定了用于执行排序、散列关联、表扫描等操作的最大内存大小。要得到此设置的最优值需要考虑以下因素：数据库的使用方式，需要预留多少内存给除数据库系统外的程序，以及服务器是否专用于运行 PostgreSQL 服务等问题。如果使用场景仅仅是有很多用户并发执行简单查询，那么这个值可以设得小一点，这样每个用户都得以较为公平地使用内存，否则第一个用户就可能把内存占光。这个值该设多大同样取决于你的机器上总共有多少内存可用。此设置的更改可动态生效，执行重新加载即可。
maintenance_work_mem
此设置指定了可用于 vaccum（即清空已标记为“被删除”状态的记录）这类系统内部维护操作的内存总量。其值不应大于 1GB。此设置的更改可动态生效，执行重新加载即可。
max_parallel_workers_per_gather
这是 9.6 版中新引入的一个配置项，用于控制语句执行的并行度。该配置项决定了执行计划的每个 gather 节点中最多允许启动多少个 worker 进程并行工作。默认值为 0，表示不启用并行功能。如果你的 PostgreSQL 服务器是多核的，那么建议评估后尝试开启此参数。并行处理是 9.6 版开始支持的新特性，因此你需要做一些测试来验证到底将并行度设为多大时效果最好。另外，注意该配置项的值应该小于 max_worker_processes 的值（默认为 8），因为用于并行处理的后台worker 进程是系统总体后台 worker 进程的一部分。
版本 10 中引入了一个新的参数叫作 max_parallel_workers，用于控制所有后台 worker进程中有多少可用于并行。
02. 修改postgresql.conf中配置项的值
例如，如果要设置一个全局生效的 work_mem，执行以下命令即可，该命令不会直接修改 postgresql.conf 文件本身，而是会去修改 postgresql.auto.conf 文件。
ALTER SYSTEM SET work_mem = '500MB';
有的更改后必须重启数据库服务才能生效，有的只要重新加载一次就可以了，下面这个命令可以实现设置重新加载。
SELECT pg_reload_conf();
如果你需要时常修改很多配置项，那么可以尝试将它们分门别类存放到多个配置文件中，然后通过在postgresql.conf 中使用 include 或者 include_if_exists 前缀来引入这些配置文件。配置文件名可以是绝对路径也可以是相对路径，相对路径的起始位置就是 postgresql.conf 文件本身所在的位置。
include '配置文件名'
03. “我修改了postgresql.conf文件，结果数据库服务无法启动了，该怎么办？”
定位这种问题最简单的方法是查看日志文件。最常见的错误是把 shared_buffers 设得太大了。还有一个常见问题是由于上次系统异常关闭导致遗留了一个没来得及删除的 postmaster.pid 文件，该文件就位于数据文件夹下，你可以手动删除该文件并重新启动 PostgreSQL。（postmaster.pid是postgresql服务自动产生的，记录服务进程号的，不要人为去改动它。服务停止时，会自动删除。如果有遗留下，那就手工删除掉）
2.1.3 pg_hba.conf
pg_hba.conf 文件指定了哪些 IP 地址和哪些用户可以连接到 PostgreSQL 数据库，同时还规定了用户必须使用何种身份验证方式登录。针对该文件的修改可动态生效，执行一次配置重加载即可。
# TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 ident ➊
host all all ::1/128 trust ➋
host all all 192.168.54.0/24 md5 ➌
hostssl all all 0.0.0.0/0 md5 ➍
# TYPE DATABASE USER ADDRESS METHOD
# Allow replication connections from localhost,
# by a user with replication privilege. ➎
#host replication postgres 127.0.0.1/32 trust
#host replication postgres ::1/128 trust
说明：常见固定ip的掩码 /32，网段ip的掩码 /24 。
❶ 身份验证模式。一般有以下几种常用选项：ident、trust、md5、peer 以及 password。
❷ 用于定义 IPv6 网段。只有服务器支持 IPv6 时才可以配置该项，如果在非 IPv6 网络环境下配置了这样的条目，会导致 pg_hba.conf 文件无法加载，从而进一步导致任何客户端都无法连接。
❸ 用于定义 IPv4 网段。第一部分是网络地址，后面跟着的是子网掩码，比如 192.168.54.0/24。这样定义的效果是该子网中的任何客户端都可以连到本 PostgreSQL 实例。
❹ 这是针对 SSL 连接的规则。任何能使用 SSL 方式连接的客户端都可以连接到本 PostgreSQL实例。SSL 相关配置都在 postgresql.conf 和 postgresql.auto.conf 中，包含以下几项：ssl、ssl_cert_file、ssl_key_file 。
➎ 这是允许与本节点构成复制关系的其他 PostgreSQL 服务器节点的 IP 网段。
postgres 服务会按照 pg_hba.conf 文件中记录的规则条目自上而下进行检查。当匹配到第一条允许此请求接入的规则时，就不再往下检查，系统将允许该连接请求。类似地，如果匹配到一条拒绝此连接请求的规则，也不再继续检查，并拒绝该连接请求。如果一直搜索到文件的末尾都没能找到匹配项，那么该连接请求会被拒绝。大家常犯的一个错误是把规则的顺序放错。
PostgreSQL 10 中引入了一个新的名为 pg_hba_file_rules 的视图，通过查询该视图可以直接看到pg_hba.conf 中的内容。
01. “我修改了pg_hba.conf文件，结果服务器崩溃了，该怎么办？”
这一般是因拼写错误或增加了一种不支持的身份验证模式导致的。如果 postgres 服务无法正确地解析 pg_hba.conf 文件，那么为确保系统安全，它会禁止所有的连接请求甚至是禁止系统启动。查看日志是最有效的方式。如果你经常会笔误，改错东西，那么请一定记得在修改配置文件之前做个备份。
02. 身份验证方法
大多数人只会用到其中几种最常见的：trust、peer、ident、md5 和 password。还有一种 reject模式，其作用是拒绝所有请求。不过别忘了 pg_hba.conf 中还可以定义其他不同层面的身份验证模式（比如 gss、sspi、ldap、radius、cert、pam 等），务必牢记 PostgreSQL 对于 pg_hba.conf 中的规则的查找顺序是从上到下，第一条匹配到的规则就是系统使用的规则。当然，在通过了这一层外部安全控制并成功建立连接以后，连上来的用户仍需遵守角色权限和数据库访问限制等内部约束规则。
trust
这是最不安全的身份验证模式，用户无须提供密码就可以连接到数据库。在单用户的桌面环境下，这却是最常用的身份验证模式，因为一般这种场景下系统的安全性根本不是问题。
md5
该模式很常用，要求连接发起者携带用 md5 算法加密的密码。
password
该模式要求连接发起者携带明文密码进行身份验证。
ident
该身份验证模式下，系统会将请求发起者的操作系统用户映射为 PostgreSQL 数据库内部用户，并以该内部用户的权限登录，且此时无须提供登录密码。Windows 上不支持 ident 验证方式。
peer
该模式下系统会直接从操作系统内核获取当前连接发起者的操作系统用户名，如果与其请求连接的 PostgreSQL 用户名一致，即可连接成功。该模式仅可用于 Linux、BSD、Mac OS X 和 Solaris，并且仅可用于本地服务器发起的连接。
cert
该模式要求客户端必须使用 SSL 方式进行连接。连接发起者必须提供一个合法的 SSL 证书。该模式使用一个身份认证文件（比如 pg_ident）来将 SSL 证书映射为 PostgreSQL 数据库的内部用户，该模式在所有支持 SSL 连接的平台上都可用。另外还有一些不常见的验证方式，比如 gss、radius、ldap 和 pam。有的可能不会默认安装。
2.2 连接管理
在客户端应用程序中，首先应该采取措施防止或者想办法解决 SQL 语句出现失控（耗时长或者占资源多）的情况，然后再考虑通过在后台直接杀掉的方式处理。
(1) 查出活动连接列表及其进程 ID。
SELECT * FROM pg_stat_activity;
(2) 取消连接（假设对应的进程号是 1234）上的活动查询。该操作不会终止连接本身。
SELECT pg_cancel_backend(1234);
(3) 终止该连接。
SELECT pg_terminate_backend(1234);
如果仅仅是终止了正在执行的语句而没有彻底杀掉连接，客户端是可以立即重新执行刚刚被终止掉的语句的，这又会导致系统陷入之前的状态。为了避免此种情况的发生，可以采用直接终止连接的方式。如果你未停止某个连接上正在执行的语句就直接终止该连接，那么这些语句也会被停止掉。
例如，如果你希望一次性终止某个用户的所有连接，那么可以执行以下语句。
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'some_role';
PostgreSQL 有一些语句参数可以用来控制语句的运行状态，一旦语句运行期间的某些状态值超过了这些运行参数所限定的范围，该语句会被系统自动杀掉。这些参数可以在服务实例级、database 级、用户级、会话级和函数级设置。参数值设为 0 代表禁用。
deadlock_timeout
该参数表示在进行死锁检测之前需要等待多久。（死锁检测是很昂贵的操作，因此系统不会每次发生锁等待时都做死锁检测）。我们更建议在 UPDATE 语句中加 NOWAIT 子句来避免死锁，例如：SELECT FOR UPDATE NOWAIT…。该语句会在发生死锁时自动终止执行。用户还有另一个选择：SELECT FOR UPDATE SKIP LOCKED，该语法可
以跳过已经被别的用户锁定的记录。
statement_timeout
该参数可以控制一个语句能够执行的最长时间，超出限定的时间后该语句会被自动终止。该参数的默认值是 0，即无限制。如果你需要对一个运行可能耗时很久的函数加上最长运行时间限制，那么最好不要把这个参数设置为全局级别，仅在要控制的函数的定义中针对该函数自身设置一下即可。
lock_timeout
该参数控制锁等待的最长时间，超出限定时间后等待锁的语句就会被自动终止。对于执行数据更新的语句来说，该参数有较大价值，因为每次更新数据之前都必须先获取待修改的记录上的排他锁，所以更新语句之间是最容易发生锁等待的。该参数的默认值为 0，表示不限制锁等待的时间。一般会在函数级或者会话级设置该参数。lock_timeout 的值应该设得比 statement_timeout 小，否则总会是语句先超时，这样 lock_timeout 就毫无意义了。
idle_in_transaction_session_timeout
该参数表示一个事务可以处于 idle 状态的最长时间，超过限定的时间后该事务会被自动回滚。该参数的默认值为 0，表示事务可以永久处于 idle 状态。该参数是 9.6 版引入的，可以起到两个作用：防止一个空闲事务占着记录锁一直不释放从而阻塞别的事务继续运行，还可以防止一个数据库连接被一个空闲事务永远占用。
查看被阻塞语句的情况
pg_stat_activity 视图发生了较大变化。9.6 版中，waiting 字段被取消，替代它的是两个名为wait_event_type 和 wait_event 的字段，其中记录了当前会话上的语句在等待什么资源。因此在9.6 版之前，可以使用 waiting = true 过滤条件查出所有被别人阻塞的语句；9.6 版之后，应更改为使用 wait_event IS NOT NULL 过滤条件来查找被阻塞的语句。
2.3 角色
拥有登录数据库权限的角色称为可登录角色。一个角色可以继承其他角色的权限从而成为其成员角色；拥有成员角色的角色称为组角色。（一个组角色可以是另一个组角色的成员角色，并且这种角色间的继承关系可以有无限多层，但除非你非常有把握能搞定这种多层嵌套关系，否则别这么干，因为你最后一定会把自己搞糊涂。）拥有登录权限的组角色称为可登录的组角色。然而，基于安全性的考虑，数据库管理员一般不会为组角色授予登录权限，因为设计组角色的本意是将其作为一个“权限集合”使用，而不是将其作为一个真正需要登录权限的用户角色来使用。一个角色可被授予超级用户（SUPERUSER）权限，拥有此权限的角色可以彻底地控制 PostgreSQL 服务，因此授予这种权限时一定要慎重。
为保持前向兼容，CREATE USER 和 CREATE GROUP 这两个命令在当前版本中也是支持的，但最好不要使用它们，请使用 CREATE ROLE。
2.3.1 创建可登录角色
在 PostgreSQL 安装过程中的数据初始化阶段，系统会默认创建一个名为 postgres 的可登录角色（也会创建一个名为 postgres 的 database）。
创建具备登录权限的角色。VALID 子句是可选的，其功能是为此角色的权限设定有效期，如果不写则该角色永久有效。CREATEDB 子句表明为此角色赋予了创建新数据库的权限。
CREATE ROLE leo LOGIN PASSWORD 'king' VALID UNTIL 'infinity' CREATEDB;
要想创建一个超级用户，创建者自身也必须是一个超级用户。如果需要创建不可登录的角色，省略掉 LOGIN PASSWORD 子句即可。
CREATE ROLE regina LOGIN PASSWORD 'queen' VALID UNTIL '2020-1-1 00:00' SUPERUSER;
2.3.2 创建组角色
一般不应授予组角色登录权限，因为它是作为其他角色的容器而存在的。
请注意关键字 INHERIT 的用法。它表示组角色 royalty 的任何一个成员角色都将自动继承其除“超级用户权限”外的所有权限。出于安全考虑，PostgreSQL 不允许超级用户权限通过继承的方式传递。如果不写 INHERIT，默认也会有 INHERIT 的效果。
CREATE ROLE royalty INHERIT;
如果希望禁止组角色将其权限授予成员角色，可以加上 NOINHERIT 关键字。
GRANT royalty TO leo;
GRANT royalty TO regina;
有些权限是无法被继承的，例如前面提过的 SUPERUSER 超级用户权限，然而成员角色可以通过 SET ROLE 命令来实现“冒名顶替”其组角色的身份，从而获得其父角色所拥有的 SUPERUSER 权限，当然这种冒名顶替的状态是有期限的，仅限于当前会话存续期间有效。（越权操作）
授予 royalty 组角色超级用户权限。
ALTER ROLE royalty SUPERUSER;
尽管 leo 是 royalty 的成员角色，也继承了其绝大多数的权限，但 leo 登录后依然不具备SUPERUSER 权限。但执行以下语句即可获取 SUPERUSER 权限。
SET ROLE royalty;
有一个更强大的命令：SET SESSION AUTHORIZATION，该命令只允许具备SUPERUSER 权限的用户执行。为了便于理解，我们首先介绍 PostgreSQL 中的两个全局变量：current_user 和 session_user 。
SELECT session_user, current_user;
首次登录成功后，这两个变量的值相同。执行 SET ROLE 会修改 current_user 的值，执行 SET SESSION AUTHORIZATION 会同时改变 current_user 和 session_user 的值。
SET ROLE 命令的主要特点
- SET ROLE 无须 SUPERUSER 权限即可执行。
- SET ROLE 会修改 current_user 变量的值，但不修改 session_user 的值。
- 一个具备 SUPERUSER 权限的 session_user 同名角色可以通过 SET ROLE 设置为任何用户。
- 非超级用户可以通过 SET ROLE 设置为 session_user 同名角色或者其所属的组用户。
- SET ROLE 命令可以让执行角色获取到所“扮演”角色的全部权限，SET SESSION
AUTHORIZATION 和 SET ROLE 权限除外（不能嵌套使用）。
SET SESSION AUTHORIZATION 命令的主要特点
- 只有超级用户才可以执行 SET SESSION AUTHORIZATION。
- SET SESSION AUTHORIZATION 权限在整个会话存续期间都是有效的，也就是说即使超级用户通过 SET SESSION AUTHORIZATION 来“扮演”了一个非超级用户，只要会话未中断，都可以在上面再次执行 SET SESSION AUTHORIZATION 命令。
- SET SESSION AUTHORIZATION 会将 current_user 和 session_user 修改为要“扮演”的角色。
- 具备超级用户权限的 session_user 同名角色可以通过 SET ROLE 来“扮演”任何其他角色。
SELECT session_user, current_user;
session_user | current_user
----------------+--------------
leo | leo
(1 row)
-- 普通用户没有权限
SET SESSION AUTHORIZATION regina;
ERROR: permission denied to set session authorization
-- 普通用户，不属于这个组角色不能，同组内可以
SET ROLE regina;
ERROR: permission denied to set role "regina"
-- 必须超级用户授予管理员权限
ALTER ROLE leo SUPERUSER;
ERROR: must be superuser to alter superusers
-- 扮演同组内组角色成功，越权操作，leo 具有了royalty 的超级 superusers 权限
SET ROLE royalty;
SELECT session_user, current_user;
session_user | current_user
----------------+--------------
leo | royalty
(1 row)
-- 扮演角色后，不属于这个组角色不能，同组内可以
SET ROLE regina;
ERROR: permission denied to set role "regina"
-- 扮演角色后，授予超级权限，可以扮演任何组角色权限
ALTER ROLE leo SUPERUSER;
SET ROLE regina;
SELECT session_user, current_user;
session_user | current_user
----------------+--------------
leo | regina
(1 row)
-- 扮演后，SET SESSION AUTHORIZATION 和 SET ROLE 权限除外。
SET SESSION AUTHORIZATION regina;
ERROR: permission denied to set session authorization
-- 退出此会话然后重新以leo身份登录
SELECT session_user, current_user;
session_user | current_user
----------------+--------------
leo | leo
(1 row)
-- 上一个会话授予了leo超级权限，可以任何操作。
SET SESSION AUTHORIZATION regina;
SELECT session_user, current_user;
session_user | current_user
--------------+--------------
regina | regina
(1 row)
2.4 创建database
该命令会以 template1 库为模板生成一份副本并将此副本作为新 database。任何一个拥有 CREATEDB 权限的角色都能够创建新的 database。
CREATE DATABASE mydb;
2.4.1 模板数据库
模板数据库就是创建新 database 时所使用的骨架。创建新 database 时，PostgreSQL 会基于模板数据库制作一份副本，其中会包含所有的数据库设置和数据文件。PostgreSQL 安装好以后默认附带两个模板数据库：template0 和 template1。如果创建新库时未指定使用哪个模板，那么系统默认会使用 template1 库作为新库的模板。
注意：
- 切记，任何时候都不要对 template0 模板数据库做任何修改，因为这是原始的干净模板，如果其他模板数据库被搞坏了，基于这个数据库做一个副本就可以了。
- 对基于 template1 或你自建的模板数据库创建出来的数据库来说，你不能修改其字符集编码和排序规则。如果你想这么做，那么请基于 template0 模板来创建新数据库。
- 对于这种被标记为模板的数据库，PostgreSQL 会禁止对其进行编辑或者删除。
基于某个模板来创建新数据库
CREATE DATABASE my_db TEMPLATE my_template_db;
以超级用户身份运行以下 SQL 可使任何数据库成为模板数据库
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'mydb';
如果你想修改或者删除被标记为模板的数据库，请先将上述语句中的 datistemplate 字段值改为 FALSE，这样就可以放开编辑限制。
2.4.2 schema的使用
schema 可以对 database 中的对象进行逻辑分组管理。同一个 schema 中的对象名不允许重复，但同一个 database 的不同 schema 中的对象是可以重名的。
另外一种常见的管理 schema 的方法是基于角色的管理。当系统拥有多个客户端并且每个客户端的数据必须完全隔离时，这种方法特别合适。，PostgreSQL 是怎么知道要查的是哪个 schema 中的表呢？这个问题最简单的解决办法是在表名前加上所属 schema 的名称，比如 SELECT * FROM customer1.dogs；另一种方法是通过设置 search_path 变量来解决，比如可以设定为 customer1, public。当执行查询语句时，规划器会先到 customer1 schema 中查找 dogs，找不到再到 public schema 中寻找，再找不到就结束。
PostgreSQL 有一个罕为人知的系统变量叫作 user，它代表了当前登录用户的名字。执行 SELECT user 就能看到其值。系统变量 user 和 current_user 完全相同，用哪个都一样。接下来在postgresql.conf 中将 search_path 变量设成下面这样（用户名和schema名相同时）。
search_path = "$user", public;
我们强烈推荐为每一个扩展包创建一个单独的 schema 来容纳其对象。安装一个新的扩展包时，会在数据库服务器上创建大量的表、函数、数据类型以及其他对象。安装扩展包时，记得在 CREATE EXTENSION 语句中将你为其创建的新 schema 声明为其归属 schema。对于现有连接来说，ALTER DATABASE … SET search_path 命令执行后是不能直接生效的，你需要断开此连接并重连才可以。
CREATE SCHEMA my_extensions;
ALTER DATABASE mydb SET search_path='$user', public, my_extensions;
2.5 权限管理
PostgreSQL 的权限控制可以精确到字段和记录级别。
2.5.1 权限的类型
PostgreSQL 有几十种权限，其中的一些基本不会用到。常见的几种权限包括 SELECT、INSERT、UPDATE、ALTER、EXECUTE 以及 TRUNCATE。
大多数权限需要上下文，也就是需要绑定一个特定的数据库对象才有意义。有些权限无须绑定数据库对象，比如 CREATEDB 和 CREATE ROLE 就是这样。
2.5.2 入门介绍
(1) PostgreSQL 在安装阶段会默认创建一个超级用户角色以及一个 database，二者的名称都是postgres。请以 postgres 身份登录服务器。
(2) 在创建你自己的首个 database 之前，需要先创建一个角色作为此 database 的所有者，所有者可以登录该库。语法如下：
CREATE ROLE mydb_admin LOGIN PASSWORD 'something';
(3) 创建 database 并设定其所有者：
CREATE DATABASE mydb WITH owner = mydb_admin;
(4) 然后用 mydb_admin 身份登录并创建 schema 和表。
2.5.3 GRANT
GRANT 命令是授予权限的基本手段。基本用法如下。
GRANT some_privilege TO some_role;
- 很显然，只有权限的拥有者才能将权限授予别人，并且拥有者自身还得有 GRANT 操作的权限。这一点是不言而喻的，因为自己没有的东西当然给不了别人。
- 有些权限只有对象的所有者才能拥有，任何情况下都不能授予别人。这类权限包括 DROP 和 ALTER。
- 对象的所有者天然拥有此对象的所有权限，不需要再次授予。然而请特别注意：一个对象的所有者并不天然拥有此对象的子对象。例如，虽然你是某个 database 的属主，但这并不意味着你必然是该 database 下所有 schema 的属主。
- 授权时可以加上 WITH GRANT 子句，这意味着被授权者可以将得到的权限再次授予别人，从而实现权限传递。示例如下。
GRANT ALL ON ALL TABLES IN SCHEMA public TO mydb_admin WITH GRANT OPTION;
- 如果希望一次性将某一类对象的所有权限都授予某人，可以使用 ALL 关键字来指代所有对象，而不需要针对每一个对象都操作一遍。请注意，此处 ALL TABLES 涵盖了所有的普通表、外部表以及视图。
GRANT SELECT, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA my_schema TO PUBLIC;
- 如果希望将权限授予所有人，可以用 PUBLIC 关键字来指代所有角色。
GRANT USAGE ON SCHEMA my_schema TO PUBLIC;
- 系统会默认将某些权限授予 PUBLIC（即所有人）。这些权限包括：CONNECT CREATE TEMP TABLE（针对 database）、EXECUTE（针对函数）。有些情况下出于安全考虑，你可能希望取消一些默认权限，那么可以使用 REVOKE 命令：
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema FROM PUBLIC;
2.5.4 默认权限
默认权限可以简化权限管理工作，该机制允许用户在数据库对象创建之前就对其设置权限。
GRANT USAGE ON SCHEMA my_schema TO PUBLIC; ➊
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT SELECT, REFERENCES ON TABLES TO PUBLIC; ➋
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT ALL ON TABLES TO mydb_admin WITH GRANT OPTION; ➌
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema ➍
GRANT SELECT, UPDATE ON SEQUENCES TO public;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema ➎
GRANT ALL ON FUNCTIONS TO mydb_admin WITH GRANT OPTION;
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema ➏
GRANT USAGE ON TYPES TO PUBLIC;
❶ 允许所有能够连接到此 database 的用户在 my_schema 中访问和创建对象，同时该用户需要已经具备访问此 schema 中所有对象的权限。为用户授予某个 schema 的 USAGE 权限是允许该用户访问 schema 中所有对象的前提条件。如果用户拥有访问 schema 中某张表的查询权限，却没有该 schema 的 USAGE 权限，则该用户不能访问这张表。
❷ 为所有具备此 schema 的 USAGE 权限的用户授予该 schema 中后续创建的所有表的查询（SELECT）和引用（REFERENCE）权限（引用权限指的是针对该表的某些字段建立外键约束的权限）。
❸ 把该 schema 中所有后续创建的表的所有权限都授予 mydb_admin 角色。同时还允许 mydb_admin 组的所有成员将本 schema 中所有后续创建的表的部分或者全部权限授予其他用户。所有权限具体包括：插入记录、更新记录、删除记录、截断表、创建触发器、创建约束等。
➍➎➏ 针对本 schema 中后续创建的序列号生成器、函数、数据类型授予权限。
2.5.5 PostgreSQL权限体系中一些与众不同的特点
PostgreSQL 中一个 database 的所有者并不天然对此库中的所有对象拥有完全的控制权。比如另一个角色可以在你的库中创建一张表，你虽然身为此库的所有者却无权访问这张表，然而此时你却有权把整个库都删掉。
在对 schema 中的表和函数做了授权操作后，一定不要忘了授予 schema 本身的 USAGE 权限。
2.6 扩展包机制
扩展包（extension）是一种用于扩展 PostgreSQL 系统功能的插件机制，该机制的前身被称为“contrib” 。
对于一台 PostgreSQL 服务器来说，并不是其中每个 database 都要安装全部的扩展包，只有当某个database 的确需要此扩展包提供的功能时才应安装。建议定期检查并卸载不再需要的扩展包以避免系统过于臃肿。
要想查看某个 database 中已经安装了哪些扩展包。
SELECT name, default_version, installed_version, left(comment,30) As comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
ORDER BY name;
name | default_version | installed_version | comment
----------------+-------------------+-----------------------+--------------------------------
btree_gist | 1.5 | 1.5 | support for indexing common da
fuzzystrmatch | 1.1 | 1.1 | determine similarities and dis
hstore | 1.4 | 1.4 | data type for storing sets of
ogr_fdw | 1.0 | 1.0 | foreign-data wrapper for GIS d
pgrouting | 2.4.1 | 2.4.1 | pgRouting Extension
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
plv8 | 1.4.10 | 1.4.10 | PL/JavaScript (v8) trusted pro
postgis | 2.4.0dev | 2.4.0dev | PostGIS geometry, geography, a
(8 rows)
如果你想查看一个 PostgreSQL 服务实例中所有 database 安装的所有扩展包，请把上面查询语句中的 WHERE installed_version IS NOT NULL 删掉。如果想要了解某个 database 中已安装的扩展包的更多详细内容，请在 psql 中执行类似以下的命令。
\dx+ fuzzystrmatch
或
SELECT pg_describe_object(D.classid,D.objid,0) AS description
FROM pg_catalog.pg_depend AS D INNER JOIN pg_catalog.pg_extension AS E
ON D.refobjid = E.oid
WHERE
D.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND
deptype = 'e' AND
E.extname = 'fuzzystrmatch';
查询结果显示了该扩展包中包含了哪些内容
description
----------------------------------------------------------------------------
function dmetaphone_alt(text)
function dmetaphone(text)
function difference(text,text)
function text_soundex(text)
function soundex(text)
function metaphone(text,integer)
function levenshtein_less_equal(text,text,integer,integer,integer,integer)
function levenshtein_less_equal(text,text,integer)
function levenshtein(text,text,integer,integer,integer)
function levenshtein(text,text)
2.6.1 扩展包的安装
01. 步骤一：将扩展包安装到数据库服务器
总的来说就是先下载该扩展包的安装文件以及所依赖的库文件，然后将它们分别复制到操作系统的 bin 和 lib 文件夹，同时把 SQL 脚本文件复制到 share/extension 文件夹（9.1 版及之后版本）或者 share/contrib 文件夹（9.1 版之前的版本）。通常可以从你下载 PostgreSQL的站点下载到完整的安装包（包括依赖包）。
如果想了解当前服务器上有哪些扩展包可用，请执行以下命令
SELECT * FROM pg_available_extensions;
02. 步骤二：将扩展包安装到指定的database中
使用 CREATE EXTENSION 命令即可将扩展包安装到指定的 database 中。可以通过 DROP EXTENSION 命令方便地卸载扩展包。GitHub 站点上也有很多 PostgreSQL 扩展包，只需搜索“postgresql extension”关键字就能找到。
CREATE EXTENSION fuzzystrmatch;
或
psql -p 5432 -d mydb -c "CREATE EXTENSION fuzzystrmatch;"
强烈建议你创建专门的 schema 来安装扩展包，以确保扩展包数据与业务数据隔离。
CREATE EXTENSION fuzzystrmatch SCHEMA my_extensions;
基于 C 语言的扩展包必须由具备超级用户权限的角色来安装。大多数扩展包都是基于 C 语言的。
03. 升级PostgreSQL版本以支持新的extension扩展包机制
系统升级到了 9.1 版，那么可以执行以下命令来升级插件，这样就实现了从 contrib 到 extension 的扩展包格式升级。（9.1之前叫contrib，9.1之后叫extension）
CREATE EXTENSION tablefunc SCHEMA contrib FROM unpackaged;
2.6.2 通用扩展包
通用扩展包是指那些因功能比较基本和通用而在 PostgreSQL 安装包中默认附带的一些扩展包，但它们不一定会被默认安装，具体视其功能而定。
01. 比较常用的扩展包介绍
btree_gist
该扩展包实现了基于 B-树索引算法的 GiST 索引运算符类，适用于 B-树索引支持的所有数据类型，其具体效果与标准的 B-树索引类似。
btree_gin
该扩展包实现了基于 B-树索引算法的 GIN 索引运算符类，适用于 B-树索引支持的所有数据类型，其具体效果与标准的 B-树索引类似。
postgis
该扩展包将 PostgreSQL 变成了一个业界最先进的空间数据库，PostGIS 是扩展包界的“巨无霸”，它包含 800 多个函数、自定义数据类型以及空间索引等对象。
fuzzystrmatch
这是一个用于字符串模糊匹配的轻量级扩展包，包含了诸如 soundex、levenshtein 和metaphone 等算法。
hstore
该扩展包为 PostgreSQL 添加了对键值数据库的支持，同时也支持索引，非常适用于存储非结构化数据。如果你正在寻找一种介于关系型数据库和 NoSQL 数据库之间的产品，可以尝试一下hstore。很多原先适用 hstore 的场景已经可以用内置 jsonb 类型来替代，因此这个扩展包已经不像以前那么流行。
pg_trgm（trigram）
该扩展包提供了另外一种字符串模糊搜索算法库，可与 fuzzystrmatch 配合使用。该扩展包包含一种运算符类，使得基于 ILIKE 的搜索操作能用上索引。该扩展还能够让形如 LIKE ‘%something%’ 的通配符查询或者是形如 somefield ~ ‘(foo|bar)’ 的正则表达式查询用上索引。
dblink
该扩展包支持从一台 PostgreSQL 服务器远程访问另一台 PostgreSQL 服务器上的数据。在 9.3版中引入对外部数据源的支持之前，dblink 是唯一能够实现跨数据库交互的机制。PostgreSQL 9.6 中，你可以预先声明远端服务器上已安装某扩展包，那么就可以在本地调用远端PostgreSQL 服务器上安装的扩展包中的函数了，例如 postgres_fdw、mysql_fdw、oracle_fdw、pg_cron、pg_rman 等等。
pgcrypto
该扩展包提供了一系列的加密工具，包括使用广泛的 PGP 算法。使用该扩展包提供的功能来对数据库中存储的信用卡号码或其他顶级机密信息进行加密是非常方便的。
02. 经典扩展包介绍
之所以称之为“曾经的扩展包”，是因为它们使用非常广泛，并因此被 PostgreSQL 官方接纳而成为了系统内核功能的一部分。
tsearch
该扩展包封装了一系列用于强化全文搜索功能的索引、运算符、自定义词典和函数。
xml
该扩展包提供了对 XML 数据类型的支持以及相关的函数和运算符。
2.7 备份与恢复
PostgreSQL 自身附带了三个备份工具：pg_dump、pg_dumpall 和 pg_basebackup，三者均位于bin 文件夹下。pg_dump 用于备份一个指定的 database，而 pg_dumpall 可一次性备份所有 database 的数据以及系统全局数据。由于 pg_dumpall 需要能够访问系统中的所有 database，因此必须由具备 SUPERUSER权限的角色来执行。pg_basebackup 可以针对所有 database 实现系统级的磁盘备份（数据库量非常大时）。
一些常用的第三方工具来实现 PostgreSQL 的备份和恢复。两个用得比较多的开源工具是 pgBackRest 和 Barman 。相比官方原生的工具，它们多了定时备份、多服务器备份以及
快捷恢复等功能。
pg_dump 和 pg_dumpall 工具不支持在命令行选项中设定登录密码，因此为了便于执行自动任务，你需要在 postgres 操作系统账户的 home 文件夹下创建一个密码文件 .pgpass 来存储密码；或者也可以用 PGPASSWORD 环境变量来设定密码。
2.7.1 使用pg_dump进行有选择性的备份
pg_dump 支持精确指定要备份的表、schema 和 database，pg_dumpall 不支持。
pg_dump 可以将数据备份为 SQL 文本文件格式，也支持备份为压缩格式、TAR 包格式或者目录格式。在用 pg_restore 进行数据恢复时，前述三种格式的备份文件都可以实现并行恢复。用 pg_dump 进行备份时，选择目录格式可以实现并行备份，因此如果要备份的数据库非常大，可以考虑使用目录格式。
备份某个 database，备份结果以自定义压缩格式输出：
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.backup mydb
备份某个 database，备份结果以 SQL 文本方式输出，命令中带 -C 选项，输出结果中包含 CREATE DATABASE 语句：
pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb
备份某个 database 中所有名称以“pay”开头的表，备份结果以压缩格式输出：
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *.pay* -f pay.backup mydb
备份某个 database 中 hr 和 payroll 这两个 schema 中的所有数据，备份结果以压缩格式输出：
pg_dump -h localhost -p 5432 -U someuser -F c -b -v \
-n hr -n payroll -f hr.backup mydb
备份某个 database 中除了 public schema 中的数据以外的所有数据，备份结果以压缩格式输出：
pg_dump -h localhost -p 5432 -U someuser -F c -b -v -N public \
-f all_sch_except_pub.backup mydb
将数据备份为 SQL 文本文件，且生成的 INSERT 语句是带有字段名列表的标准格式，该文件可用于将数据导入到低于当前版本的 PostgreSQL 或者其他支持 SQL 的非 PostgreSQL 数据库中。
pg_dump -h localhost -p 5432 -U someuser -F p --column-inserts \
-f select_tables.backup mydb
使用目录格式备份会将每个表备份为某个文件夹下的一个单独的文件，这样就解决了以其他备份格式备份时可能存在的单个文件大小超出操作系统限制的问题。备份时会先创建一个新目录，然后为每个表生成一个 gzip 格式的压缩文件，另外目录下还会生成一个描述所有备份对象之间层级关系的文件。如果备份开始时发现指定的目录已存在，那么该命令会报错并退出。
pg_dump -h localhost -p 5432 -U someuser -F d -f /somepath/a_directory mydb
只有在按目录格式进行备份时，才支持并行备份选项--jobs 或 -j，因为只有选择目录格式时才能并行生成多个备份文件。
pg_dump -h localhost -p 5432 -U someuser -j 3 -Fd -f /somepath/a_directory mydb
2.7.2 使用pg_dumpall进行全局备份
pg_dumpall 工具可以将当前 PostgreSQL 服务实例中所有 database 的数据都导出为 SQL 文本（请注意：pg_dumpall 不支持导出 SQL 文本以外的其他格式），这种庞大的 SQL 文本备份来进行全库级别的数据恢复是极其耗时的。备份结果也包含了表空间定义和角色等全局对象。
实现仅备份角色和表空间定义等全局对象：
pg_dumpall -h localhost -U postgres --port=5432 -f myglobals.sql --globals-only
实现仅需备份角色定义而无须备份表空间：
pg_dumpall -h localhost -U postgres --port=5432 -f myroles.sql --roles-only
2.7.3 数据恢复
PostgreSQL 可以使用以下两种方法来恢复 pg_dump 和 pg_dumpall 备份的数据：
- 使用 psql 来恢复 pg_dump 或者 pg_dumpall 工具生成的 SQL 文本格式的数据备份；
- 使用 pg_restore 工具来恢复由 pg_dump 工具生成的压缩格式、TAR 包格式或者目录格式备份。
恢复一个 SQL 备份文件并忽略过程中可能发生的所有错误：
psql -U postgres -f myglobals.sql
恢复一个 SQL 备份文件，如遇任何错误则立即停止恢复：
psql -U postgres --set ON_ERROR_STOP=on -f myglobals.sql
将 SQL 文本中的数据恢复到某个指定的 database：
psql -U postgres -d mydb -f select_objects.sql
pg_restore 支持并行恢复，多个恢复线程可以并行处理，每个线程处理一张表。该模式可以显著提高恢复速度。使用 pg_restore 扫描备份文件来生成一张备份内容列表，通过该列表可以确认备份中包含了哪些内容。你还可以通过编辑该内容列表来控制恢复哪些内容。pg_restore 支持选择性地局部恢复部分对象。
在使用 pg_restore 执行恢复动作之前，请先创建目标数据库：
CREATE DATABASE mydb;
然后执行恢复：
pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup
如果备份和恢复时使用的 database 同名，则可以通过加 --create 选项省去单独建库的过程，命令如下：
pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup
如果指定了 --create 选项，那么恢复出来的数据库名就会默认采用备份时的数据库名，不允许改名。如果还同时指定了 --dbname 选项，那么此时连接的数据库名一定不能是待恢复的数据库名，因为恢复数据库之前必然要建数据库，而建数据库之前必然要先连到某个已存在的数据库，–dbname 选项指定的就是建立被恢复的数据库之前先连到哪个数据库，所以必然不能与待恢复的数据库重名，我们一般指定为先连到 postgres 数据库。
如果你的目标 database 和其中的各类对象均已存在，并且还有一些数据在里面，你只是想用备份中的数据来替代现有 database中的数据，那么执行 pg_resotre 时使用 --clean 选项即可达到目标。该选项的效果是先把目标 database 中的对象删除掉，然后在恢复过程中一一重建。--section 选项，加上该选项后可以实现仅恢复表结构而不恢复表数据。
pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup
2.8 基于表空间机制进行存储管理
PostgreSQL 使用“表空间”这一概念来将逻辑存储空间映射到磁盘上的物理存储空间。
PostgreSQL 在安装阶段会自动生成两个表空间：一个是 pg_default，用于存储所有的用户级数据；另一个是 pg_global，用于存储所有的系统级数据。这两个表空间就位于默认的数据文件夹下。你可以不受限地创建表空间并将其物理存储位置设定到任何一块物理磁盘上。你也可以为 database 设定默认表空间，这样该 database 中创建的任何新对象都会存储到此表空间上。你也可以将现存的数据库对象迁移到新的表空间中。
2.8.1 表空间的创建
创建表空间需要先为其取一个逻辑名称并指定某个物理文件夹作为其存储位置，注意要确保 postgres操作系统账户对此文件夹有完全的访问权限。
CREATE TABLESPACE secondary LOCATION '/usr/data/pgdata94_secondary';
2.8.2 在表空间之间迁移对象
如果希望将一个 database 的所有对象都移动到另一个表空间中，可以执行以下命令：
ALTER DATABASE mydb SET TABLESPACE secondary;
如果只希望移动一张表，命令如下：
ALTER TABLE mytable SET TABLESPACE secondary;
PostgreSQL 9.4 中引入了一个新功能：一次性把一个表空间的多个对象迁移到另一个表空间。如果命令执行者是超级用户，那么源表空间所有的对象都会被迁移过去；否则仅会迁移执行者所拥有的对象。
将 pg_default 默认表空间中的所有对象迁移到 secondary 表空间，所需的命令行如下，在迁移过程中所涉及的 database 和表会被锁定。
ALTER TABLESPACE pg_default MOVE ALL TO secondary;
2.9 禁止的行为
查看系统日志，从中可以找到解决问题的线索。日志文件位于数据文件夹的根目录或者其中的 pg_log 子文件夹下。也有可能系统在记下日志之前即已崩溃，那么显然这种情况下查日志是没用的。如果你的 PostgreSQL 服务启动失败，请尝试执行以下操作系统命令。
path/to/your/bin/pg_ctl -D your_postgresql_data_folder
2.9.1 切记不要删除PostgreSQL系统文件
比如：pg_log、pg_xlog 和 pg_clog。这里面的确有些文件是可以安全删除的，但你需要准确地知道哪些能删哪些不能删，否则很容易导致数据库被破坏。
pg_log 文件夹一般在 data 文件夹下，其体积可能增长得很快，尤其是当打开了日志开关的时候。这个文件夹下的文件任何时候都可以安全删除，事实上很多人会设置一个定时任务来定期清除这些日志文件。
除了 pg_xlog 文件夹下的文件可以有条件地删除外，其他 PostgreSQL 系统文件夹中的文件都不能删，即使有的文件夹名称中带有 log 字样因而看起来像是某种日志，也绝对不能删，比如 pg_clog 文件夹中存储的是活跃事务提交日志，除非你想删库跑路，否则千万不要碰。
pg_xlog 文件夹用于存储事务日志。我们见过有的系统中会在 pg_xlog 文件夹下建一个子文件夹 archive，专门用于存放归档的事务日志。一般来说，你的系统总会需要创建一个专门的文件夹（该文件夹不一定要放在 pg_xlog 下）用于日志归档，因为如果这是一个同步复制环境，那么需要持续地进行事务日志归档；需要归档文件夹的另一个理由是得把这些日志存下来以备不时之需，因为我们有可能需要将系统数据恢复到过去的某个时间点。将 pg_xlog 文件夹下的所有文件都删除会导致 PostgreSQL 后台进程崩溃。但仅删除归档文件夹下的日志却没这么严重，最多会导致无法恢复到过去的某个时点，或者是在同步环境下可能导致从属服务器无法进行数据同步，因为还未来得及同步的一些日志文件可能已被删掉了。如果以上场景在你的系统中都不涉及，那么可以放心地删除归档文件夹下的日志文件。
在 PostgreSQL 10 中，pg_xlog 目录被改名为 pg_wal，pg_clog 被改名为 pg_xact，以防止用户认为这些目录中放的都是日志，想删就删而不会造成任何后果。
2.9.2 不要把操作系统管理员权限授予PostgreSQL的系统账号
postgres 账号应该就是一个普通用户账号，只要能够访问 data 文件夹以及其他表空间文件夹即可。大多数 PostgreSQL 安装包会自动为 postgres 账号设定够用的权限，请不要画蛇添足。在 SQL 注入攻击中，那些不必要的权限会为攻击者们提供可乘之机。
2.9.3 不要把shared_buffers缓存区设置得过大
禁止将 shared_buffers 设置得和系统当前内存总容量一样大，否则很可能会导致操作系统崩溃或者是启动失败。在有些 Linux 系统上，不能将 shared_buffers 设置得大于 SHMMAX 变量，而一般来说 SHMMAX（OS的内核资源变量） 的值是比较小的，所以这就给 shared_buffers 的设置带来了一些限制。
2.9.4 不要将PostgreSQL服务器的侦听端口设为一个已被其他程序占用的端口
如果启动 PostgreSQL 时系统发现侦听端口已被占用，那么会在 pg_log 中记录类似这样的错误日志：make sure PostgreSQL is not already running。以下是可能导致该问题的常见原因。
- postgres 服务实例已经启动好了，而你正试图再启动一遍。
- PostgreSQL 服务侦听端口已被其他程序占用。
- postgres 服务之前发生过异常关闭或者崩溃，在 data 文件夹下遗留了一个postgresql.pid 文件。请直接删除该文件并再次尝试启动。
- 有一个孤立的 PostgreSQL 进程还在运行。如果前述方法都试过了但问题仍未解决，请终止所有还在运行的 PostgreSQL 进程，然后再次尝试启动。
4969
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
