PostgreSQL

使用的centos7镜像

常用命令

  1. 连接pg：psql -U username -h 196.128.0.19 -p 5432
  2. 查询配置文件位置：show config_file;
  3. 重新加载配置文件： 用超级用户运行 SELECT pg_reload_conf();
  4. 查询主备库角色：
    • select * from pg_stat_replication;
    • pg_controldata | grep ‘Database cluster state’ 命令，查看Database cluster state的值，如果是”in production”代表主库，”in archive recovery” 代表是备库的意思。
  5. 查询数据库中的表：select tablename from pg_tables where schemaname='public;'
  6. 查询数据库：select * from pg_database;

create user os_admin superuser password 'os_admin@Sugon'; # 内置管理用户

查看用户权限

select * from information_schema.table_privileges where grantee='xjy';

授权数据库及表权限

CREATE USER WITH PASSWORD '1234'; #创建用户

GRANT ALL PRIVILEGES ON DATABASE test TO xiejy3; #授权数据库权限

REVOKE ALL ON DATABASE test FROM xiejy3;

GRANT ALL PRIVILEGES ON ALL tables in schema public TO xiejy3; #授权表权限

REVOKE ALL PRIVILEGES ON ALL tables in schema public FROM xiejy3;

查询用户

select * from pg_user where usename = 'xiejy';

select * from pg_roles;

安装

curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo 更新镜像源

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install PostgreSQL:

sudo yum install -y postgresql11-server

Optionally initialize the database and enable automatic start:

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11

export PATH="$PATH:/usr/pgsql-11/bin"
source /etc/profile

su - postgres
psql
ALTER USER postgres WITH PASSWORD 'postgres'; #设置超级权限用户密码

vim /var/lib/pgsql/11/data/postgresql.conf

fsync = on # 日志是否先写入磁盘
listen_addresses = '*' # 设置为内网IP，允许远程登录主机
wal_level = replica # write ahead log，流复制时为hot_standby hot_standby
hot_standby = on
wal_log_hints = on

下列不建议设置，暂不清楚

max_wal_senders = 10 # 流复制的最大连接数
wal_keep_segments = 16 # 流复制保留的最大xlog数
archive_mode = on #开启wal日志归档
archive_command = 'test ! -f /var/lib/pgsql/11/archive/%f && cp %p /var/lib/pgsql/11/archive/%f' # wal日志归档命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
vim /var/lib/pgsql/11/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

host    all             all             0.0.0.0/0               md5
host    replication     all             0.0.0.0/0               trust

19. 服务器配置

19.1参数设置

19.1.1参数名称和值

所有参数名都是大小写不敏感的。每个参数都可以接受五种类型之一的值： 布尔、字符串、整数、 浮点数或枚举。该类型决定了设置该参数的语法：

  • 布尔: 值可以被写成 on, off, true, false, yes, no, 1, 0 （都是大小写不敏感的）或者这些值的任何无歧义前缀。
  • 字符串: 通常值被包括在单引号内，值内部的任何单引号都需要被双写。不过，如果值是一个简单数字或者 标识符，引号通常可以被省略。
  • 数字（整数和浮点）: 只对浮点参数允许一个小数点。不要使用千位分隔符。不要求引号。
  • 带单位的数字: 一些数字参数具有隐含单位，因为它们描述内存或时间量。单位可能是字节、千字节、块（通常是 8KB）、 毫秒、秒或分钟。这些设置之一的一个未修饰的数字值将使用该设置的默认单位，默认单位可以通 过引用pg_settings.unit来找到。为了方便，也可以 显式地指定一个不同的单位，例如时间值可以是'120 ms'，并且它们将被转换到参数的实际单位。要使用这个特性，注意值必须被写成一个字符 串（带有引号）。单位名称是大小写敏感的，并且在数字值和单位之间可以有空白。
    • 可用的内存单位是B（字节）、kB（千字节）、MB（兆字节）和GB（吉字节）。内存单位的乘数是 1024 而不是 1000。
    • 可用的时间单位是ms（毫秒）、s（秒）、min（分钟）、 h（小时）和d（天）。
  • 枚举: 枚举类型的参数以与字符串参数相同的方式指定，但被限制到一组有限的值。 这样一个参数可用的值可以在pg_settings.enumvals 中找到。枚举参数值是大小写无关的。

19.1.2 通过配置文件影响参数

设置这些参数最基本的方法是编辑postgresql.conf文件， 它通常被保存在数据目录中：/var/lib/pgsql/11/data/postgresql.conf

以这种方式设定的参数为集簇提供了默认值。除非这些设置被覆盖，活动会话看到的就是这些设置。

除postgresql.conf之外，PostgreSQL 数据目录还包含一个文件 postgresql.auto.conf，它具有和postgresql.conf相同的格式但是不应该被手工编辑。这个 文件保存了通过ALTER SYSTEM命令提供的设置。每当postgresql.conf被读 取时这个文件会被自动读取，并且它的设置会以同样的方式生效。 postgresql.auto.conf中的设置会覆盖postgresql.conf 中的设置。

系统视图pg_file_settings 可以有助于对配置文件中的更改进行提前测试，或者在SIGHUP 信号没有达到预期效果时用来诊断问题。

19.1.3. 通过SQL影响参数

PostgreSQL提供了三个SQL命令来建立配置默认值。 已经提到过的ALTER SYSTEM命令提供了一种改变全局默认值的从SQL可 访问的方法；它在功效上等效于编辑postgresql.conf。此外，还有两个命令 可以针对每个数据库或者每个角色设置默认值：

  • ALTER DATABASE命令允许针对一个数据库覆盖其全局设置。
  • ALTER ROLE命令允许用用户指定的值来覆盖全局设置和数据库设置。

只有当开始一个新的数据库会话时，用ALTER DATABASE和 ALTER ROLE设置的值才会被应用。它们会覆盖从配置文件或服务器命令行 获得的值，并且作为该会话后续的默认值。注意某些设置在服务器启动后不能被更改，并且因此 不能被这些命令（或者下文列举的命令）设置。

一旦一个客户端连接到数据库，PostgreSQL会提供两个额外的SQL命令（ 以及等效的函数）用以影响会话本地的配置设置：

  • SHOW命令允许察看所有参数的当前值。对应的函数是 current_setting(setting_name text)。
  • SET命令允许修改对于一个会话可以本地设置的参数的当前值， 它对其他会话没有影响。对应的函数是 set_config(setting_name, new_value, is_local)。

此外，系统视图pg_settings可以被用来查看和改变 会话本地的值：

  • 查询这个视图与使用SHOW ALL相似，但是可以提供更多细节。它也更加灵活， 因为可以为它指定过滤条件或者把它与其他关系进行连接。

  • 在这个视图上使用UPDATE并且指定更新setting 列，其效果等同于发出SET命令。例如，下面的命令

    1
    SET configuration_parameter TO DEFAULT;

    等效于：

    1
    UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter';

实例：

1
2
3
4
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

postgres=# ALTER SYSTEM set synchronous_standby_names=off; #设置参数
postgres=# select pg_reload_conf();		#重载配置

19.5. 预写式日志

19.5.1. 设置

  • wal_level (enum)

wal_level决定多少信息写入到 WAL 中。默认值是replica，它会写入足够的数据以支持WAL归档和复制，包括在后备服务器上运行只读查询。minimal会去掉除从崩溃或者立即关机中进行恢复所需的信息之外的所有记录。最后，logical会增加支持逻辑解码所需的信息。每个层次包括所有更低层次记录的信息。这个参数只能在服务器启动时设置。

在minimal级别中，某些批量操作的 WAL 日志可以被安全地跳过，这可以使那些操作更快（见第 14.4.7 节）。这种优化可以应用的操作包括：

CREATE TABLE AS
CREATE INDEX
CLUSTER
COPY到在同一个事务中被创建或截断的表中

但最少的 WAL 不会包括足够的信息来从基础备份和 WAL 日志中重建数据，因此，要启用 WAL 归档（archive_mode）和流复制，必须使用replica或更高级别。

在logical层，与replica相同的信息会被记录，外加上 允许从 WAL 抽取逻辑修改集所需的信息。使用级别 logical将增加 WAL 容量，特别是如果为了REPLICA IDENTITY FULL配置了很多表并且执行了很多UPDATE和DELETE 语句时。

在 9.6 之前的版本中，这个参数也允许值archive和hot_standby。现在仍然接受这些值，但是它们会被映射到replica。

  • fsync (boolean)

如果打开这个参数，PostgreSQL服务器将尝试确保更新被物理地写入到磁盘，做法是发出fsync()系统调用或者使用多种等价的方法（见wal_sync_method）。这保证了数据库集簇在一次操作系统或者硬件崩溃后能恢复到一个一致的状态。

虽然关闭fsync常常可以得到性能上的收益，但当发生断电或系统崩溃时可能造成不可恢复的数据损坏。因此，只有在能很容易地从外部数据中重建整个数据库时才建议关闭fsync。

能安全关闭fsync的环境的例子包括从一个备份文件中初始加载一个新数据库集簇、使用一个数据库集簇来在数据库被删掉并重建之后处理一批数据，或者一个被经常重建并却不用于失效备援的只读数据库克隆。单独的高质量硬件不足以成为关闭fsync的理由。

当把fsync从关闭改成打开时，为了可靠的恢复，需要强制在内核中的所有被修改的缓冲区进入持久化存储。这可以在多个时机来完成：在集簇被关闭时或在 fsync 因为运行initdb --sync-only而打开时、运行sync时、卸载文件系统时或者重启服务器时。

在很多情况下，为不重要的事务关闭synchronous_commit可以提供很多关闭fsync的潜在性能收益，并不会有的同时， 关闭fsync可以提供很多潜在的性能优势，而不会有伴随着的数据损坏风险。

fsync只能在postgresql.conf文件中或在服务器命令行上设置。如果你关闭这个参数，请也考虑关闭full_page_writes。

  • synchronous_commit (enum)

指定在命令返回“success”指示给客户端之前，一个事务是否需要等待 WAL 记录被写入磁盘。合法的值是on、remote_apply、remote_write、local和off。默认的并且安全的设置是on。当设置为off时，在向客户端报告成功和真正保证事务不会被服务器崩溃威胁之间会有延迟（最大的延迟是wal_writer_delay的三倍）。不同于fsync，将这个参数设置为off不会产生数据库不一致性的风险：一个操作系统或数据库崩溃可能会造成一些最近据说已提交的事务丢失，但数据库状态是一致的，就像这些事务已经被干净地中止。因此，当性能比完全确保事务的持久性更重要时，关闭synchronous_commit可以作为一个有效的代替手段。更多讨论见第 30.3 节。

如果synchronous_standby_names为非空，这个参数也控制事务提交是否将等待它们的 WAL 记录被复制到后备服务器上。当这个参数被设置为on时，直到来自于当前同步的后备服务器的回复指示它们已经收到了事务的提交记录并将其刷入了磁盘，主服务器上的事务才会提交。这保证事务将不会被丢失，除非主服务器和所有同步后备都遭受到了数据库存储损坏的问题。当被设置为remote_apply时，提交将会等待，直到来自当前的同步后备的回复指示它们已经收到了该事务的提交记录并且已经应用了该事务，这样该事务才变得对后备上的查询可见。当这个参数被设置为remote_write时，提交将等待，直到来自当前的同步后备的回复指示它们已经收到了该事务的提交记录并且已经把该记录写出到它们的操作系统，这种设置足以保证数据在后备服务器的PostgreSQL实例崩溃时得以保存，但是不能保证后备服务器遭受操作系统级别崩溃时数据能被保持，因为数据不一定必须要在后备机上达到稳定存储。最后，设置local会导致提交等待本地刷写到磁盘而不是复制完成。在使用同步复制时这通常不是我们想要的效果，但是为了完整性，还是提供了这样一个选项。

如果synchronous_standby_names为空，设置on、remote_apply、remote_write和local都提供了同样的同步级别：事务提交只等待本地刷写磁盘。

这个参数可以随时被修改；任何一个事务的行为由其提交时生效的设置决定。因此，可以同步提交一些事务，同时异步提交其他事务。例如，当默认是相反时，实现一个单一多语句事务的异步提交，在事务中发出SET LOCAL synchronous_commit TO OFF。

  • wal_sync_method (enum)

用来向强制 WAL 更新到磁盘的方法。如果fsync是关闭的，那么这个设置就不相关，因为 WAL 文件更新将根本不会被强制。默认值是fsync，可能的值是：

  • open_datasync（用open()选项O_DSYNC写 WAL 文件）
  • fdatasync（在每次提交时调用fdatasync()）
  • fsync（在每次提交时调用fsync()）
  • fsync_writethrough（在每次提交时调用fsync()，强制任何磁盘写高速缓存的直通写）
  • open_sync（用open()选项O_SYNC写 WAL 文件）

open_* 选项也可以使用O_DIRECT（如果可用）。不是在所有平台上都能使用所有这些选择。默认值是列表中第一个被平台支持的那个， 不过fdatasync是 Linux 中的默认值。默认值不一定是最理想的；有可能需要修改这个设置或系统配置的其他方面来创建一个崩溃-安全的配置，或达到最佳性能。这些方面在第 30.1 节中讨论。这个参数只能在postgresql.conf文件中或在服务器命令行上设置。

  • full_page_writes (boolean)

当这个参数为打开时，PostgreSQL服务器在一个检查点之后的页面的第一次修改期间将每个页面的全部内容写到 WAL 中。这么做是因为在操作系统崩溃期间正在处理的一次页写入可能只有部分完成，从而导致在一个磁盘页面中混合有新旧数据。在崩溃后的恢复期间，通常存储在 WAL 中的行级改变数据不足以完全恢复这样一个页面。存储完整的页面映像可以保证页面被正确存储，但代价是增加了必须被写入 WAL 的数据量（因为 WAL 重放总是从一个检查点开始，所以在检查点后每个页面的第一次改变时这样做就够了。因此，一种减小全页面写开销的方法是增加检查点间隔参数值）。

把这个参数关闭会加快正常操作，但是在系统失败后可能导致不可恢复的数据损坏，或者静默的数据损坏。其风险类似于关闭fsync， 但是风险较小。并且只有在可关闭fsync的情况下才应该关闭它。

关闭这个选项并不影响用于时间点恢复（PITR）的 WAL 归档使用（见第 25.3 节）。

这个参数只能在postgresql.conf文件中或在服务器命令行上设置。默认值是on。

  • wal_log_hints (boolean)

当这个参数为on时，PostgreSQL服务器一个检查点之后页面被第一次修改期间把该磁盘页面的整个内容都写入 WAL，即使对所谓的提示位做非关键修改也会这样做。

如果启用了数据校验和，提示位更新总是会被 WAL 记录并且这个设置会被忽略。你可以使用这个 设置测试如果你的数据库启用了数据校验和，会有多少额外的 WAL 记录发生。

这个参数只能在服务器启动时设置。默认值是off。

  • wal_compression (boolean)

当这个参数为on时，如果full_page_writes 为打开或者处于基础备份期间，PostgreSQL服务器 会压缩写入到 WAL 中的完整页面镜像。压缩页面镜像将在 WAL 重放时 被解压。默认值为off。只有超级用户可以更改这个设置。

打开这个参数可以减小 WAL 所占的空间且无需承受不可恢复的数据损坏风险， 但是代价是需要额外的 CPU 开销以便在 WAL 记录期间进行压缩以及在 WAL 重放时解压。

  • wal_buffers (integer)

用于还未写入磁盘的 WAL 数据的共享内存量。默认值 -1 选择等于shared_buffers的 1/32 的尺寸（大约3%），但是不小于64kB也不大于 WAL 段的尺寸（通常为）。如果自动的选择太大或太小可以手工设置该值，但是任何小于32kB的正值都将被当作32kB。这个参数只能在服务器启动时设置。

在每次事务提交时，WAL 缓冲区的内容被写出到磁盘，因此极大的值不可能提供显著的收益。不过，把这个值设置为几个兆字节可以在一个繁忙的服务器（其中很多客户端会在同一时间提交）上提高写性能。由默认设置 -1 选择的自动调节将在大部分情况下得到合理的结果。

  • wal_writer_delay (integer)

指定 WAL 写入器刷写 WAL 的频繁程度。在刷写 WAL 之后它会睡眠wal_writer_delay毫秒，除非被一个异步提交事务唤醒。假如上一次刷写发生在少于wal_writer_delay毫秒以前并且从上一次刷写发生以来产生了少于wal_writer_flush_after字节的 WAL，则WAL将只被写入到操作系统而不是被刷到磁盘。默认值是 200 毫秒（200ms）。注意在很多系统上，有效的睡眠延迟粒度是 10 毫秒，把wal_writer_delay设置为一个不是 10 的倍数的值，其效果和把它设置为大于该值的下一个 10 的倍数产生的效果相同。这个参数只能在postgresql.conf文件中或者服务器命令行上设置。

  • wal_writer_flush_after (integer)

指定 WAL 写入器刷写 WAL 的频繁程度。如果上一次刷写发生在少于wal_writer_delay毫秒以前并且从上一次刷写发生以来产生了少于wal_writer_flush_after字节的 WAL，则WAL将只被写入到操作系统而不是被刷到磁盘。如果wal_writer_flush_after被设置为0，则WAL数据会被立即刷写。默认是1MB。这个参数只能在postgresql.conf文件中或者服务器命令行上设置。

  • commit_delay (integer)

在一次 WAL 刷写被发起之前，commit_delay增加一个时间延迟，以微妙计。如果系统负载足够高，使得在一个给定间隔内有额外的事务准备好提交，那么通过允许更多事务通过一个单次 WAL 刷写来提交能够提高组提交的吞吐量。但是，它也把每次 WAL 刷写的潜伏期增加到了最多commit_delay微秒。因为如果没有其他事务准备好提交，就会浪费一次延迟，只有在当一次刷写将要被发起时有至少commit_siblings个其他活动事务时，才会执行一次延迟。另外，如果fsync被禁用，则将不会执行任何延迟。默认的commit_delay是零（无延迟）。只有超级用户才能修改这个设置。

在PostgreSQL的 9.3 发布之前，commit_delay的行为不同并且效果更差：它只影响提交，而不是所有 WAL 刷写，并且即使在 WAL 刷写马上就要完成时也会等待一整个配置的延迟。从PostgreSQL 9.3 中开始，第一个准备好刷写的进程会等待配置的间隔，而后续的进程只等到领先者完成刷写操作。

  • commit_siblings (integer)

在执行commit_delay延迟时，要求的并发活动事务的最小数目。大一些的值会导致在延迟间隔期间更可能有至少另外一个事务准备好提交。默认值是五个事务。

常用命令操作

pg_dump

描述

pg_dump是用于备份一种PostgreSQL数据库的工具。即使数据库正在被并发使用，它也能创建一致的备份。pg_dump不阻塞其他用户访问数据库（读取或写入）。

pg_dump只转储单个数据库。要备份一个集簇或者集簇中 对于所有数据库公共的全局对象（例如角色和表空间），应使用 pg_dumpall。

转储可以被输出到脚本或归档文件格式。脚本转储是包含 SQL 命令的纯文本文件，它们可以用来重构数据库到它被转储时的状态。要从这样一个脚本恢复，将它喂给psql。脚本文件甚至可以被用来在其他机器和其他架构上重构数据库。在经过一些修改后，甚至可以在其他 SQL 数据库产品上重构数据库。

另一种可选的归档文件格式必须与pg_restore配合使用来重建数据库。它们允许pg_restore能选择恢复什么，或者甚至在恢复之前对条目重排序。归档文件格式被设计为在架构之间可移植。

当使用归档文件格式之一并与pg_restore组合时，pg_dump提供了一种灵活的归档和传输机制。pg_dump可以被用来备份整个数据库，然后pg_restore可以被用来检查归档并/或选择数据库的哪些部分要被恢复。最灵活的输出文件格式是“自定义”格式（-Fc）和“目录”格式（-Fd）。它们允许选择和重排序所有已归档项、支持并行恢复并且默认是压缩的。“目录”格式是唯一一种支持并行转储的格式。

当运行pg_dump时，我们应该检查输出中有没有任何警告（打印在标准错误上），特别是考虑到下面列出的限制。

参数见：参数选项

诊断

pg_dump在内部执行SELECT语句。如果你运行pg_dump时出现问题，确定你能够从正在使用的数据库中选择信息，例如psql。此外，libpq前端-后端库所使用的任何默认连接设置和环境变量都将适用。

pg_dump的数据库活动会被统计收集器正常地收集。如果不想这样，你可以通过PGOPTIONS或ALTER USER命令设置参数track_counts为假。

注解

如果你的数据库集簇对于template1数据库有任何本地添加，要注意将pg_dump的输出恢复到一个真正的空数据库。否则你很可能由于以增加对象的重复定义而得到错误。要创建一个不带任何本地添加的空数据库，从template0而不是template1复制它，例如：

1
CREATE DATABASE foo WITH TEMPLATE template0;

当一个只含数据的转储被选中并且使用了选项--disable-triggers时，pg_dump在开始插入数据之前会发出命令禁用用户表上的触发器，并且接着在数据被插入之后发出命令重新启用它们。如果恢复中途被停止，系统目录可能会停留在一种错误状态。

pg_dump产生的转储文件不包含优化器用来做出查询计划决定的统计信息。因此，在从一个转储文件恢复后运行ANALYZE来确保最优性能是明智的，详见第 24.1.3 节和第 24.1.6 节。

因为pg_dump被用来传输数据到更新版本的PostgreSQL，pg_dump的输出被认为可以载入到比pg_dump版本更新的PostgreSQL服务器中。pg_dump也能够从比其版本更旧的PostgreSQL服务器中转储（当前支持回退到版本 7.0）。不过，pg_dump无法从比起主版本号更新的PostgreSQL服务器中转储，它甚至将拒绝冒着创建一个非法转储的风险尝试。还有，不保证pg_dump的输出能被载入到一个更旧主版本的服务器 — 即使该转储是从该版本的服务器中被取得也不行。将一个转储文件载入到一个更旧的服务器可能需要手工编辑该转储文件来移除旧服务器无法理解的语法。在跨版本的情况下，推荐使用--quote-all-identifiers选项，因为它可以避免因为不同PostgreSQL版本间的保留词列表变化而发生问题。

在转储逻辑复制订阅时，pg_dump将生成使用connect = false选项的CREATE SUBSCRIPTION命令，这样恢复订阅时不会建立远程连接来创建复制槽或者进行初始的表拷贝。通过这种方式，可以无需到远程服务器的网络访问就能恢复该转储。然后就需要用户以一种合适的方式重新激活订阅。如果涉及到的主机已经改变，连接信息可能也必须被改变。在开启一次新的全表拷贝之前，截断目标表也可能是合适的。

实例

要把一个数据库mydb转储到一个 SQL 脚本文件：

1
$ pg_dump mydb > db.sql

要把这样一个脚本重新载入到一个（新创建的）名为newdb的数据库中：

1
$ psql -d newdb -f db.sql

要转储一个数据库到一个自定义格式归档文件：

1
$ pg_dump -Fc mydb > db.dump

要转储一个数据库到一个目录格式的归档：

1
$ pg_dump -Fd mydb -f dumpdir

要用 5 个行的工作者任务转储一个数据库到一个目录格式的归档：

1
$ pg_dump -Fd mydb -j 5 -f dumpdir

要把一个归档文件重新载入到一个（新创建的）名为newdb的数据库：

1
$ pg_restore -d newdb db.dump

把一个归档文件重新装载到同一个数据库（该归档正是从这个数据库中转储得来）中，丢掉那个数据库中的当前内容：

1
$ pg_restore -d postgres --clean --create db.dump

要转储一个名为mytab的表：

1
$ pg_dump -t mytab mydb > db.sql

要转储detroit模式中名称以emp开始的所有表，排除名为employee_log的表：

1
$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql

要转储名称以east或者west开始并且以gsm结束的所有模式，排除名称包含词test的任何模式：

1
$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql

同样，用正则表达式记号法来合并开关：

1
$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql

要转储除了名称以ts_开头的表之外的所有数据库对象：

1
$ pg_dump -T 'ts_*' mydb > db.sql

要在-t和相关开关中指定一个大写形式或混合大小写形式的名称，你需要双引用该名称，否则它会被折叠到小写形式（见模式（Pattern））。但是双引号对于 shell 是特殊的，所以反过来它们必须被引用。因此，要转储一个有混合大小写名称的表，你需要类似这样的东西：

1
$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql

pg_dumpall

描述

pg_dumpall工具可以一个集簇中所有的PostgreSQL数据库写出到（“转储”）一个脚本文件。该脚本文件包含可以用作psql的输入SQL命令来恢复数据库。它会对集簇中的每个数据库调用pg_dump来完成该工作。pg_dumpall还转储对所有数据库公用的全局对象（pg_dump不保存这些对象），也就是说数据库角色和表空间都会被转储。 目前这包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。

因为pg_dumpall从所有数据库中读取表，所以你很可能需要以一个数据库超级用户的身份连接以便生成完整的转储。同样，你也需要超级用户特权执行保存下来的脚本，这样才能增加角色和组以及创建数据库。

SQL 脚本将被写出到标准输出。使用 [-f|file] 选项或者 shell 操作符可以把它重定向到一个文件。

pg_dumpall需要多次连接到PostgreSQL服务器（每个数据库一次）。如果你使用口令认证，可能每次都会要求口令。这种情况下使用一个~/.pgpass会比较方便。详见第 34.15 节。

参数见：参数选项

注解

因为pg_dumpall在内部调用pg_dump，所以， 一些诊断消息可以参考pg_dump。

即使当用户的目的是把转储脚本恢复到一个空的集簇中，--clean选项也有用武之地。--clean的使用让该脚本删除并且重建内建的postgres和template1数据库，确保这两个数据库保持与源集簇中相同的属性（例如locale和编码）。如果不用这个选项，这两个数据库将保持它们现有的数据库级属性以及任何已有的内容。

一旦恢复，建议在每个数据库上运行ANALYZE，这样优化器就可以得到有用的统计信息。你也可以运行vacuumdb -a -z来分析所有数据库。

不应该预期转储脚本运行到结束都不出错。特别是由于脚本将为源集簇中已有的每一个角色发出CREATE ROLE语句，对于bootstrap超级用户当然会得到一个“role already exists”错误，除非目标集簇用一个不同的bootstrap超级用户名完成的初始化。这种错误是无害的并且应该被忽略。--clean选项的使用很可能会产生额外的有关于不存在对象的无害错误消息，不过可以通过加上--if-exists减少这类错误消息。

pg_dumpall要求所有需要的表空间目录在进行恢复之前就必须存在；否则，数据库创建就会由于在非默认位置创建数据库而失败。

例子

要转储所有数据库：

1
pg_dumpall > db.out

要从这个文件重新载入数据库，你可以使用：

1
psql -f db.out postgres

这里你连接哪一个数据库并不重要，因为由pg_dumpall创建的脚本将包含合适的命令来创建和连接到被保存的数据库。一个例外是，如果指定了--clean，则开始时必须连接到postgres数据库，该脚本将立即尝试删除其他数据库，并且这种动作对于已连接上的这个数据库将会失败。

参见

可能的错误情况请查看pg_dump。

pg_basebackup

描述

pg_basebackup被用于获得一个正在运行的PostgreSQL数据库集簇的基础备份。获得这些备份不会影响连接到该数据库的其他客户端，并且可以被用于时间点恢复（见第 25.3 节）以及用作一个日志传送或流复制后备服务器的开始点（见第 26.2 节）。

pg_basebackup建立数据库集簇文件的一份二进制副本，同时保证系统进入和退出备份模式。备份总是从整个数据库集簇获得，不可能备份单个数据库或数据库对象。关于个体数据库备份，必须使用一个像pg_dump的工具。

备份通过一个常规PostgreSQL连接制作，并且使用复制协议。该连接必须由一个超级用户或者一个具有REPLICATION权限（第 21.2 节）的用户建立，并且pg_hba.conf必须显式地允许该复制连接。该服务器还必须被配置，使max_wal_senders设置得足够高以留出至少一个会话用于备份以及一个用于WAL流（如果使用流）。

在同一时间可以有多个pg_basebackup运行，但是从性能的角度来说最好只做一个备份并且复制结果。

pg_basebackup不仅能从主控机也能从后备机创建一个基础备份。要从后备机获得一个备份，设置后备机让它能接受复制连接（也就是，设置max_wal_senders和hot_standby，并且配置基于主机的认证）。你将也需要在主控机上启用full_page_writes。

注意在来自后备机的在线备份中有一些限制：

  • 不会在被备份的数据库集簇中创建备份历史文件。
  • 如果正在使用-X none，不保证备份所需的所有 WAL 文件在备份结束时被归档。
  • 如果在在线备份期间后备机被提升为主控机，备份会失败。
  • 备份所需的所有 WAL 记录必须包含足够的全页写，这要求你在主控机上启用full_page_writes并且不使用一个类似pg_compresslog的工具以archive_command从 WAL 文件中移除全页写。

选项

下列命令行选项控制输出的位置和格式。

  • -D *directory* --pgdata=*directory*

    将输出写到哪个目录。如果必要，pg_basebackup将创建该目录及任何父目录。该目录可能已经存在，但是如果该目录已经存在并且非空就是一个错误。当备份处于 tar 模式中并且目录被指定为-（破折号）时，tar 文件将被写到stdout。这个选项是必需的。

  • -F *format* --format=*format*

    为输出选择格式。*format*可以是下列之一：p plain把输出写成平面文件，使用和当前数据目录和表空间相同的布局。当集簇没有额外表空间时，整个数据库将被放在目标目录中。如果集簇包含额外的表空间，主数据目录将被放置在目标目录中，但是所有其他表空间将被放在它们位于服务器上的相同的绝对路径中。这是默认格式。t tar将输出写成目标目录中的 tar 文件。主数据目录将被写入到一个名为base.tar的文件中，并且其他表空间将被以其 OID 命名。如果值-（破折号）被指定为目标目录，tar 内容将被写到标准输出，适合于管道输出到其他程序，例如gzip。只有当集簇没有额外表空间并且没有使用WAL流时这才是可能的。

  • -r *rate* --max-rate=*rate*

    从该服务器传输数据的最大传输率。值的单位是千字节每秒。加上一个后缀M表示兆字节每秒。也接受后缀k，但是没有效果。合法的值在 32 千字节每秒到 1024 兆字节每秒之间。其目标是限制在运行服务器上的pg_basebackup产生的影响。这个选项总是会影响数据目录的传输。如果收集方法是fetch时，只有 WAL 文件受到影响。

  • -R --write-recovery-conf

    在输出目录中（或者当使用 tar 格式时再基础归档文件中）写一个最小的recovery.conf来简化设置一个后备服务器。recovery.conf文件将记录连接设置（如果有）以及pg_basebackup所使用的复制槽，这样流复制后面就会使用相同的设置。

  • -T *olddir*=*newdir* --tablespace-mapping=*olddir*=*newdir*

    在备份期间将目录*olddir中的表空间重定位到newdir中。为使之有效，olddir必须正好匹配表空间所在的路径（但如果备份中没有包含olddir中的表空间也不是错误）。olddir和newdir*必须是绝对路径。如果一个路径凑巧包含了一个=符号，可用反斜线对它转义。对于多个表空间可以多次使用这个选项。例子见下文。如果以这种方法重定位一个表空间，主数据目录中的符号链接会被更新成指向新位置。因此新数据目录已经可以被一个所有表空间位于更新后位置的新服务器实例使用。

  • --waldir=*waldir*

    指定用于预写式日志目录的位置。*waldir*必须是绝对路径。只有当备份是平面文件模式时才能指定事务日志目录。

  • -X *method* --wal-method=*method*

    在备份中包括所需的预写式日志文件（WAL文件）。这包括所有在备份期间产生的预写式日志。除非指定了方法none，可以直接在提取出的目录中启动postmaster而无需参考日志归档，所以这样得到的是一种完整的独立备份。支持下列收集预写式日志的方法：n none不要在备份中包括预写式日志。f fetch在备份末尾收集预写式日志文件。因此，有必要把wal_keep_segments参数设置得足够高，这样在备份末尾之前日志不会被移除。如果在要传输日志时它已经被轮转，备份将失败并且是不可用的。如果使用tar格式，预写式日志文件将被写入到base.tar文件。s stream在备份被创建时流传送预写式日志。这将开启一个到服务器的第二连接并且在运行备份时并行开始流传输预写式日志。因此，它将使用最多两个由max_wal_senders参数配置的连接。只要客户端能保持接收预写式日志，使用这种模式不需要在主控机上保存额外的预写式日志。如果使用tar格式，预写式日志文件被写入到一个单独的名为pg_wal.tar的文件（如果服务器的版本超过10，该文件将被命名为pg_wal.tar）。这个值是默认值。

  • -z --gzip

    启用对 tar 文件输出的 gzip 压缩，使用默认的压缩级别。只有使用 tar 格式时压缩才可用，并且会在所有tar文件名后面自动加上后缀.gz。

  • -Z *level* --compress=*level*

    启用对 tar 文件输出的 gzip 压缩，并且制定压缩机别（0 到 9，0 是不压缩，9 是最佳压缩）。只有使用 tar 格式时压缩才可用，并且会在所有tar文件名后面自动加上后缀.gz。

下列命令行选项控制备份的生成和程序的运行。

  • -c *fast|spread* --checkpoint=*fast|spread*

    将检查点模式设置为 fast（立刻）或 spread（默认）（见第 25.3.3 节）。

  • -C --create-slot

    这个选项会导致在开始备份前创建一个由--slot选项指定名称的复制槽。如果槽已经存在则会发生错误。

  • -l *label* --label=*label*

    为备份设置标签。如果没有指定，将使用一个默认值“pg_basebackup base backup”。

  • -n --no-clean

    默认情况下，当pg_basebackup因为一个错误而中止时，它会把它意识到无法完成该工作之前已经创建的目录（例如数据目录和预写式日志目录）都移除。这个选项可以禁止这种清洗，因此可以用于调试。注意不管哪一种方式都不会清除表空间目录。

  • -N --no-sync

    默认情况下，pg_basebackup将等待所有文件被安全地写到磁盘上。这个选项导致pg_basebackup不做这种等待就返回，这样会更快一些，但是也意味着后续发生的操作系统崩溃可能会使得这个基础备份损坏。通常这个选项对测试比较有用，在创建生产安装时不应该使用。

  • -P --progress

    启用进度报告。启用这个选项将在备份期间发表一个大致的进度报告。由于数据库可能在备份期间改变，这仅仅是一种近似并且可能不会刚好在100%结束。特别地，当 WAL 日志被包括在备份中时，总数据量无法预先估计，并且在这种情况中估计的目标尺寸会在它经过不带 WAL 的总估计后增加。当这个选项被启用时，备份开始时会列举整个数据库的尺寸，并且接着回头开始发送实际的内容。这可能使备份需要多花一点点时间，并且它在发送第一个数据之前花费的时间更长。

  • -S *slotname* --slot=*slotname*

    这个选项仅能与-X stream一起使用。它导致WAL流使用指定的复制槽。如果该基础备份的目的是被用作一台使用复制槽的流复制后备，则它应该使用与recovery.conf中相同的复制槽名称。通过这种方式，可以确保服务器不会移除位于该基础备份结束与流复制开始之间产生的任何所需的WAL数据。指定的复制槽必须已经存在，除非同时使用了选项-C。如果这个选项没有被指定并且服务器支持临时复制槽（版本10以后），则会自动使用一个临时复制槽来进行WAL流。

  • -v --verbose

    启用冗长模式。将在启动和关闭期间输出一些额外步骤，并且如果进度报告也被启用，还会显示当前正在被处理的确切文件名。

  • --no-slot

    如果服务器支持临时复制槽，这个选项防止备份期间创建临时复制槽。在使用日志流时，如果没有用选项-S指定槽名称，则默认会创建临时复制槽。这个选项的主要目的是允许在服务器没有空闲复制槽可用时制作基础备份。使用复制槽几乎总是最好的方式，因为它能防止备份期间所需的WAL被删除。

  • --no-verify-checksums

    如果在取基础备份的服务器上启用了校验码验证，则禁用校验码验证。默认情况下，校验码会被验证并且校验码失败将会导致一种非零的退出状态。不过，基础备份在这种情况下将不会被移除，就好像使用了--no-clean选项一样。

下列命令行选项控制数据库连接参数。

  • -d *connstr* --dbname=*connstr*

    以一个连接字符串的形式指定用于连接到服务器的参数。详见第 34.1.1 节。为了和其他客户端应用一致，该选项被称为--dbname。但是因为pg_basebackup并不连接到集簇中的任何特定数据库，连接字符串中的数据库名将被忽略。

  • -h *host* --host=*host*

    指定运行服务器的机器的主机名。如果该值以一个斜线开始，它被用作 Unix 域套接字的目录。默认值取自PGHOST环境变量（如果设置），否则会尝试一个 Unix 域套接字连接。

  • -p *port* --port=*port*

    指定服务器正在监听连接的 TCP 端口或本地 Unix 域套接字文件扩展。默认用PGPORT环境变量中的值（如果设置），或者一个编译在程序中的默认值。

  • -s *interval* --status-interval=*interval*

    指定发送回服务器的状态包之间的秒数。这允许我们更容易地监控服务器的进度。一个零值完全禁用这种周期性的状态更新，不过当服务器需要时还是会有一个更新会被发送来避免超时导致的断开连接。默认值是 10 秒。

  • -U *username* --username=*username*

    要作为哪个用户连接。

  • -w --no-password

    从不发出一个口令提示。如果服务器要求口令认证并且没有其他方式提供口令（例如一个.pgpass文件），那儿连接尝试将失败。这个选项对于批处理任务和脚本有用，因为在其中没有一个用户来输入口令。

  • -W --password

    强制pg_basebackup在连接到一个数据库之前提示要求一个口令。这个选项不是必不可少的，因为如果服务器要求口令认证，pg_basebackup将自动提示要求一个口令。但是，pg_basebackup将浪费一次连接尝试来发现服务器想要一个口令。在某些情况下值得用-W来避免额外的连接尝试。

其他选项也可用：

  • -V --version

    打印pg_basebackup版本并退出。

  • -? --help

    显示有关pg_basebackup命令行参数的帮助并退出。

环境

和大部分其他PostgreSQL工具相似，这个工具也使用libpq（见第 34.14 节）支持的环境变量。

注解

在备份的开始时，需要向从中拿去备份的服务器写一个检查点。尤其在没有使用选项--checkpoint=fast时，这可能需要一点时间，在其间pg_basebackup看起来处于闲置状态。

备份将包括数据目录和表空间中的所有文件，包括配置文件以及由第三方放在该目录中的任何额外文件，不过由PostgreSQL管理的特定临时文件除外。但只有常规文件和目录会被拷贝，但用于表空间的符号链接会被保留。指向PostgreSQL已知的特定目录的符号链接被拷贝为空目录。其他符号链接和特殊设备文件会被跳过。准确的细节请参考第 53.4 节。

表空间默认将以普通格式备份到与它们在服务器上相同的路径中，除非使用了--tablespace-mapping选项。如果没有这个选项并且表空间正在使用，在同一台服务器上进行普通格式的基础备份将无法工作，因为备份必须要写入到与原始表空间相同的目录位置。

在使用 tar 格式模式时，用户应负责在启动 PostgreSQL 服务器前解压每一个 tar 文件。如果有额外的表空间，用于它们的 tar 文件需要被解压到正确的位置。在这种情况下，服务器将根据包含在base.tar文件中的tablespace_map文件的内容为那些表空间创建符号链接。

pg_basebackup可以和具有相同或较低主版本的服务器一起工作，最低是 9.1。但是，WAL 流模式（-X 流）只能和版本为 9.3 及以上版本的服务器一起工作。当前版本的 tar 格式模式（--format=tar）只能用于版本 9.5 及以上的服务器。

如果在源集簇上启用了组权限，在plain以及tar模式中pg_basebackup将保留组权限。

例子

要创建服务器mydbserver的一个基础备份并将它存储在本地目录/usr/local/pgsql/data中：

1
$ pg_basebackup -h mydbserver -D /usr/local/pgsql/data

要创建本地服务器的一个备份，为其中每一个表空间产生一个压缩过的 tar 文件，并且将它存储在目录backup中，在运行期间显示一个进度报告：

1
$ pg_basebackup -D backup -Ft -z -P

要创建一个单一表空间本地数据库的备份并且使用bzip2压缩它：

1
$ pg_basebackup -D - -Ft -X fetch | bzip2 > backup.tar.bz2

（如果在该数据库中有多个表空间，这个命令将失败）。

要创建一个本地数据库的备份，其中/opt/ts中的表空间被重定位到./backup/ts：

1
$ pg_basebackup -D backup/data -T /opt/ts=$(pwd)/backup/ts
1
pg_basebackup -h /var/run/postgresql -U os_admin -p 5432 -D backup/  -l %s -F t -z -X f

pg_rewind

pg_rewind — 把一个PostgreSQL数据目录与另一个从该目录中复制出来的数据目录同步

大纲

pg_rewind [option…] { -D | --target-pgdata } directory { --source-pgdata=*directory* | --source-server=*connstr* }

简介

pg_rewind是用于在集簇的时间线分叉以后，同步一个 PostgreSQL 集簇和同一集簇的另一份拷贝的工具。一种典型的场景是在失效后让一个旧的主服务器重新上线，同时有一个后备机跟随着新的主机。

其结果等效于把目标数据目录替换成源数据目录。关系文件中只有更改过的块才会被拷贝，所有其他的文件会被整个拷贝，包括配置文件。pg_rewind比起做一个新的基础备份或者rsync等工具的优势在于，pg_rewind不要求通读集簇中未更改的块。这使得它在数据库很大并且在集簇间只有小部分块不同时速度很快。

pg_rewind检查源集簇和目标集簇的时间线历史来判断它们在哪一点分叉，并且期望在目标集簇的pg_wal目录中找到 WAL 来返回到分叉点。分叉点可能会在目标时间线、源时间线或者它们的共同祖先上找到。在典型的失效场景中，目标集簇在分叉后很快就被关闭，这不是问题，但是如果目标集簇在分叉后已经运行了很长时间，旧的 WAL 文件可能已经不存在了。在这样的情况下，它们可以被手工从 WAL 归档复制到pg_wal目录，或者通过配置recovery.conf在启动时取得。pg_rewind的使用并不限于失效的场景，例如一个后备服务器可能被提升、运行一些写事务，然后被倒回再次成为一个后备。

当目标服务器在运行了pg_rewind之后第一次启动时，它将进入到恢复模式并且重放源服务器在分叉点之后产生的所有 WAL。当pg_rewind被运行时有某些 WAL 在源服务器上不可用，并且因此无法被pg_rewind会话所复制，则在目标服务器被启动时必须让这些 WAL 可用。这可以通过在目标数据目录中创建一个recovery.conf文件并且在其中使用一个适当的restore_command来实现。

pg_rewind要求目标服务器在postgresql.conf中启用了wal_log_hints选项，或者在用initdb初始化集簇时启用了数据校验。目前默认情况下这两者都没有被打开。full_page_writes也必须被设置为on，这是默认的。

警告

如果在处理时pg_rewind失败，则目标的数据目录很可能不在可恢复的状态。在这种情况下，推荐创建一个新的备份。

如果pg_rewind发现它无法直接写入的文件，它将立刻失败。例如当源服务器和目标服务器为只读的SSL密钥及证书使用相同的文件映射，就会发生这种情况。如果在目标服务器上存在这样的文件，推荐在运行pg_rewind之前移除它们。在做了rewind之后，一些那样的文件可能已经被从源服务器拷贝，这样就有必要移除已经拷贝的数据并且恢复到rewind之前使用的链接集合。

选项

pg_rewind接受下列命令行参数：

  • -D *directory* --target-pgdata=*directory*

    这个选项指定要与源数据目录同步的目标数据目录。在运行pg_rewind之前目标服务器必须被干净地关闭。

  • --source-pgdata=*directory*

    指定要和目标服务器同步的源服务器的数据目录的文件系统路径。这个选项要求源服务器必须被干净地关闭。

  • --source-server=*connstr*

    指定一个 libpq 连接串用于连接要与目标服务器同步的源PostgreSQL服务器。该连接必须是一个具有超级用户访问权限的普通（非复制）连接。这个选项要求源服务器正在运行且不处于恢复模式。

  • -n --dry-run

    做除了实际修改目标目录之外的其他所有事情。

  • -P --progress

    启用进度报告。在从源集簇拷贝数据时，打开这个选项将会发送一个近似的进度报告。

  • --debug

    打印冗长的调试输出，这主要对于调试pg_rewind的开发者有用。

  • -V --version

    显示版本信息然后退出。

  • -? --help

    显示帮助然后退出。

环境

在使用--source-server选项时，pg_rewind也使用libpq支持的环境变量（见第 34.14 节）。

注解

如何工作

其基本思想是从源集簇拷贝所有文件系统级别的改变到目标集簇：

  1. 以源集簇的时间线历史从目标集簇分叉出来的点之前的最后一个检查点为起点，扫描目标集簇的 WAL 日志。对于每一个 WAL 记录，读取每一个被动过的数据块。这会得到在目标集簇中从源集簇被分支出去以后所有被更改过的数据块列表。
  2. 使用直接的文件系统访问（--source-pgdata）或者 SQL （--source-server），把所有那些更改过的块从源集簇拷贝到目标集簇。
  3. 把所有其他诸如pg_xact和配置文件（除了关系文件之外所有的东西）从源集簇拷贝到目标集簇。与基础备份类似，在从源集簇拷贝的数据中，目录pg_dynshmem/、pg_notify/、pg_replslot/、pg_serial/、pg_snapshots/、pg_stat_tmp/以及pg_subtrans/的内容会被忽略。任何以pgsql_tmp开始的文件或目录都会被忽略，backup_label、tablespace_map、pg_internal.init、postmaster.opts以及postmaster.pid也是这样。
  4. 从源集簇应用 WAL，从失效处创建的检查点开始（严格来说，pg_rewind并不应用 WAL，它只是创建一个备份标签文件，该文件让PostgreSQL从那个检查点开始向前重放所有 WAL）。

wal日志

WAL：Write-Ahead Logging 预写式日志

什么是WAL

持久性是指，事务提交后，对系统的影响必须是永久的，即使系统意外宕机，也必须确保事务提交时的修改已真正永久写入到永久存储中。最简单的实现方法，当然是在事务提交后立即刷新事务修改后的数据到磁盘。但是磁盘和内存之间的IO操作是最影响数据库系统影响时间的，一有事务提交就去刷新磁盘，会对数据库性能产生不好影响。

WAL机制的引入，即保证了事务持久性和数据完整性，又尽量地避免了频繁IO对性能的影响。

WAL的概念就是对数据文件的改变（包括表和索引）必须先写入日志，即日志记录刷新到永久储存之后，才能被写。遵循这个过程，就不需要在每个事务提交时都刷新数据页到磁盘，因为在宕机时可以用日志来恢复数据库：任何没有应用到数据页上的改动都可以根据日志记录重做。

WAL工作流程

WAL机制实际是在这个写数据的过程中加入了对应的写WAL log的过程，步骤一样是先到Buffer，再刷新到Disk。

Change发生时：

  • 先将变更后内容记入WAL Buffer
  • 再将更新后的数据写入Data Buffer

Commit发生时：

  • WAL Buffer刷新到Disk
  • Data Buffer写磁盘推迟

Checkpoint发生时：

  • 将所有Data Buffer刷新到磁盘

WAL的好处

使用WAL可以显著地减少写磁盘的次数，因为只需要把日志文件刷新到磁盘就可以保证事务被提交，而不需要把事务改动过的每一个数据文件都刷新到磁盘。日志文件是连续写的，所以同步log的花销远小于刷新数据页的花销。特别是服务器要处理涉及数据存储不同部分的大量小事务时更是这样。另外，当服务器在处理大量并行小事务时，log文件一次fsync就可以提交多个事务。

WAL还使得在线备份和时间点恢复成为可能。通过归档WAL数据，我们可以恢复到WAL数据覆盖范围内的任何时间点：只需install一份数据库的物理备份，并恢复WAL日志到所需时间即可。更重要的是，这个物理备份并不必须是一个数据库状态的瞬时快照—如果一段时间的快照，那把WAL日志也恢复成那一段时间的即可。

checkpoint

checkpoint又名检查点，一般checkpoint会将某个时间点之前的脏数据全部刷新到磁盘，以实现数据的一致性与完整性。目前各个流行的关系型数据库都具备checkpoint功能，其主要目的是为了缩短崩溃恢复时间，以Oracle为例，在进行数据恢复时，会以最近的checkpoint为参考点执行事务前滚。而在WAL机制的浅析中，也提过PostgreSQL在崩溃恢复时会以最近的checkpoint为基础，不断应用这之后的WAL日志。

配置参数

  • fsync：该参数直接控制日志是否先写入磁盘。默认值是ON(先写入)。开启该值时表明，更新数据写入磁盘时系统必须等待WAL的写入完成。可以配置该参数为OFF，更新数据写入磁盘完全不用等待WAL的写入完成，没有了等待的时间，显然接下来的工作能够更早的去做，节省了时间，提高了性能。其直接隐患是无法保证在系统崩溃时最近的事务能够得到恢复，也就无法保证相关数据的真实与正确性。
  • synchronous_commit：参数表明是否等待WAL完成后才返回给用户事务的状态信息。默认值是ON，表明必须等待WAL完成后才返回事务状态信息。配置OFF值能够更快的反馈回事务状态。因参数只是控制事务的状态反馈，因此对于数据的一致性不存在风险。但事务的状态信息影响着数据库的整个状态。该参数可以灵活的配置，对于业务没有严谨要求的事务可以配置为OFF，能够为系统的性能带来不小的提升。
  • wal_sync_method：WAL写入磁盘的控制方式，默认值是fsync。可选用值：open_datasync,fdatasync,fsync_writethrough,fsync,open_sync。
  • full_page_writes：表明是否将整个page写入WAL。
  • wal_buffers：用于存放WAL数据的内存空间。系统默认值是64K，执行一个大的事务肯定受到影响，应该适当的增大该参数。类似oracle中的log buffer。该参数还受以下几个参数影响。
  • wal_writer_delay：WAL writer进程的间歇时间。默认值是200ms。准确的配置应该根据自身系统的运行状况。如果时间过长可能造成WAL buffer的内存不足；反之过小将会引起WAL的不断的写入，对磁盘的IO也是很大考验。
  • commit_delay：表示了一个已经提交的数据在WAL buffer中存放的时间，单位ms，默认值是0，不用延迟。非0值表示可能存在多个事务的WAL同时写入磁盘。如果设置为非0，表明了某个事务执行commit后不会立即写入WAL中，而仍存放在WAL buffer中，这样对于后面的事务申请WAL buffer时非常不利，尤其是提交事务较多的高峰期，可能引起WAL buffer内存不足。如果内存足够大，可以尽量延长该参数值，能够使数据集中写入这样降低了系统的IO，提高了性能。同样如果此时崩溃数据面临着丢失的危险。个人建议采用默认值，同时将WAL文件存放在IO性能好的磁盘上。
  • commit_siblings：该参数非常有意思，该参数还决定了commit_delay的有效性。系统默认值是5。表示当一个事务发出提交请求，此时数据库中正在执行的事务数量大于5，则该事务将等待一段时间（commit_delay的值），反之，该事务则直接写入WAL。
  • checkpoint_timeout：
    自动 WAL 检查点之间的最长时间，以秒计。合理的范围在 30 秒到 1 天之间。默认是 5 分钟(5min)。增加这个参数的值会增加崩溃恢复所需的时间。
  • max_wal_size：
    在自动 WAL检查点之间允许WAL 增长到的最大尺寸。这是一个软限制，在特殊的情况 下 WAL 尺寸可能会超过max_wal_size， 例如在重度负荷下、archive_command失败或者高的 wal_keep_segments设置。默认为 1 GB。增加这个参数可能导致崩溃恢复所需的时间。

  • min_wal_size：
    只要 WAL 磁盘用量保持在这个设置之下，在检查点时旧的 WAL文件总是被回收以便未来使用，而不是直接被删除。 pg中wal日志是动态切换，从pg9.6开始采用这种模式。pg中这种动态wal切换步骤是这样的：单个wal日志写满(默认大小16MB，编译数据库时指定)继续写下一个wal日志，直到磁盘剩余空间不足min_wal_size时才会将旧的 WAL文件回收以便继续使用。

    这种模式有一个弊端就是如果在checkpoint之前产生了大量的wal日志就会导致发生checkpoint时对性能的影响巨大，因此pg中还有一个参数checkpoint_completion_target来进行调整。

  • checkpoint_completion_target：
    指定检查点完成的目标，作为检查点之间总时间的一部分。默认是 0.5。
    什么意思呢，假如我的checkpoint_timeout设置是30分钟，而wal生成了10G，那么设置成0.5就允许我在15分钟内完成checkpoint，调大这个值就可以降低checkpoint对性能的影响，但是万一数据库出现故障，那么这个值设置越大数据就越危险。

高可用

异步流复制

PostgreSQL流复制热备

​ 流复制允许备服务器比使用基于文件的日志传送更能保持为最新的状态。备服务器连接到主服务器，主服务器则在 WAL 记录产生时将它们以流式传送给后备服务器而不必等到 WAL 文件被填充。（实时传输wal文件）

​ 默认情况下流复制是异步的（见第 26.2.8 节），当主服务器上提交一个事务与该变化时，直到备服务器上变得可见时存在短暂的延迟。不过这种延迟比基于文件的日志传送方式中要小得多，在备服务器的能力足以跟得上负载的前提下延迟通常低于一秒。在流复制中，不需要archive_timeout来缩减数据丢失窗口。

wal日志归档

配置归档需要开启如下参数：

  • wal_level = replica （pg13默认已经开启replica）
    该参数的可选的值有minimal，replica和logical，wal的级别依次增高，在wal的信息也越多。由于minimal这一级别的wal不包含从基础的备份和wal日志重建数据的足够信息，在该模式下，无法开启wal日志归档
  • archive_mode = on
    上述参数为on，表示打开归档备份,可选的参数为on,off,always 默认值为off，所以要手动打开
  • archive_command = ‘test ! -f /var/lib/pgsql/11/archive/%f && cp %p /var/lib/pgsql/11/archive/%f’
    该参数的默认值是一个空字符串，他的值可以是一条shell命令或者一个复杂的shell脚本。在shell脚本或命令中可以用 “%p” 表示将要归档的wal文件包含完整路径的信息的文件名，用“%f” 代表不包含路径信息的wal文件的文件名

注意：wal_level和archive_mode参数修改都需要重新启动数据库才可以生效。而修改archive_command则不需要。所以一般配置新系统时，无论当时是否需要归档，这要建议将这两个参数开启

主库
1
2
3
4
5
6
7
8
9
10
11
vim /var/lib/pgsql/11/data/postgresql.conf

fsync = on									# 日志是否先写入磁盘
listen_addresses = '*'       # 设置为内网IP，允许远程登录主机
wal_level = replica     # write ahead log，流复制时为hot_standby
hot_standby = on
max_wal_senders = 2         # 流复制的最大连接数
wal_keep_segments = 16      # 流复制保留的最大xlog数
archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/11/archive/%f && cp %p /var/lib/pgsql/11/archive/%f'
  wal_log_hints = on
1
2
3
4
vim /var/lib/pgsql/11/data/pg_hba.conf

host    all             all             0.0.0.0/0               md5
host    replication     all             0.0.0.0/0               trust
备库
1
2
3
4
5
6
7
8
9
10
11
12
13
rm -rf /var/lib/pgsql/11/data/

vim /var/lib/pgsql/.pgpass

   *:*:*:postgres:postgres

chmod 600 .pgpass

pg_basebackup -h 10.1.2.195 -U postgres -Xs -R -D /var/lib/pgsql/11/data/

vim /var/lib/pgsql/11/data/recovery.conf
	standby_mode = 'on'
  primary_conninfo = 'user=postgres password=postfres host=10.1.2.195 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any application_name=backup'

同步流复制

PostgreSQL Replication之第五章 设置同步复制

配置

vim /var/lib/pgsql/11/data/postgresql.conf

synchronous_standby_names = 'backup'

一旦流复制已经被配置，配置同步复制就只需要一个额外的配置步骤：synchronous_standby_names必须被设置为一个非空值。synchronous_commit也必须被设置为on，但由于这是默认值，通常不需要改变（见第 19.5.1 节和第 19.6.2 节）。这样的配置将导致每一次提交都等待确认消息，以保证后备服务器已经将提交记录写入到持久化存储中。synchronous_commit可以由个体用户设置，因此它可以在配置文件中配置、可以为特定用户或数据库配置或者由应用动态配置，这样可以在一种每事务基础上控制持久性保证。

synchronous_standby_names = “*”

“synchronous_commit = on”

同步提交参数, 控制事务提交后返回客户端是否成功的策略
可选值为:on, remote_write, local, off

on

1 为on且没有开启同步备库的时候,会当wal日志真正刷新到磁盘永久存储后才会返回客户端事务已提交成功,
2 当为on且开启了同步备库的时候(设置了synchronous_standby_names),必须要等事务日志刷新到本地磁盘,并且还要等远程备库也提交到磁盘才能返回客户端已经提交.

off

写到缓存中就会向客户端返回提交成功，但也不是一直不刷到磁盘，延迟写入磁盘,延迟的时间为最大3倍的wal_writer_delay参数的(默认200ms)的时间,所有如果即使关闭synchronous_commit,也只会造成最多600ms的事务丢失,此事务甚至包括已经提交的事务（会丢数据）,但数据库确可以安全启动,不会发生块折断,只是丢失了部分数据,但对高并发的小事务系统来说,性能来说提升较大。

remote_write

当事务提交时,不仅要把wal刷新到磁盘,还需要等wal日志发送到备库操作系统(但不用等备库刷新到磁盘),因此如果备库此时发生实例中断不会有数据丢失,因为数据还在操作系统上,而如果操作系统故障,则此部分wal日志还没有来得及写入磁盘就会丢失,备库启动后还需要想主库索取wal日志。

local

当事务提交时,仅写入本地磁盘即可返回客户端事务提交成功,而不管是否有同步备库

主备切换

查询主备库角色

  • select * from pg_stat_replication;
  • pg_controldata | grep ‘Database cluster state’ 命令，查看Database cluster state的值，如果是”in production”代表主库，”in archive recovery” 代表是备库的意思。

备升级为主

  • 备库配置文件 recovery.conf 中有配置项 trigger_file ，它是激活从库的触发文件，当它存在就会激活从库。

如果备库配置文件 recovery.conf 配置项 trigger_file 不为空，例如：

vim /var/lib/pgsql/11/data/recovery.conf

1
trigger_file = '/var/lib/pgsql/10/data/trigger_standby'

​ 创建文件/var/lib/pgsql/10/data/trigger_standby，就可以激活备库。

1
touch /var/lib/pgsql/10/data/trigger_standby
  • 在备库上执行 pg_ctl promote -D /var/lib/pgsql/11/data/ 命令激活。
1
pg_rewind --target-pgdata /var/lib/pgsql/11/data --source-server='host=12.5.0.27 port=5432 user=os_admin password=os_admin@Sugon dbname=postgres' -P

1、停止主库

2、在备库上执行pg_ctl promote，使升级为新的主库。

3、在旧主库上配置recovery.conf 文件，启动

success

  1. postgresql.conf配置文件

    #主备
    synchronous_commit = on
    synchronous_standby_names = ‘backup’

    fsync = on # 日志是否先写入磁盘
    listen_addresses = ‘*’ # 设置为内网IP，允许远程登录主机
    wal_level = replica

    hot_standby = on

  2. recovery.conf/recovery.done配置文件

    #主备

    1
    2
    3
    4
    recovery_target_timeline = 'latest'

    standby_mode = on
    primary_conninfo = 'host=10.1.2.195 port=5432 user=postgres password=postgres application_name=backup'

    #只读

    1
    2
    3
    4
    5
    arecovery_target_timeline = 'latest'

    standby_mode = on

    primary_conninfo = 'host=10.1.2.195 port=5432 user=postgres password=postgres application_name=slave'

  3. 启动
    #测试，检测
    #同步线程查看
    ps -ef |grep wal
    #节点状态，in archive recovery备只读，in production主
    pg_controldata -D /var/lib/pgsql/11/data |grep “Database cluster state”

  4. 主备切换，备升级为主，停主
    pg_ctl promote -D /var/lib/pgsql/11/data

  5. 配置新的同步
    #旧主和只读修改，recovery.conf中primary_conninfo参数的host信息

克隆盘

安装步骤

  1. curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo 更新镜像源

  2. sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

  3. Install PostgreSQL:

    sudo yum install -y postgresql11-server

  4. 主节点：复制数据到新的备节点：

    scp -r data/ root@10.1.2.9:/var/lib/pgsql/11/

  5. 新节点(备)：添加免密：

    vim /var/lib/pgsql/11/.pgpass 写入:*:*:postgres:postgres保存

    chmod 600 .pgpass

  6. 新节点(备)：修改连接的主节点信息：

    mv /var/lib/pgsql/11/data/recovery.done /var/lib/pgsql/11/data/recovery.conf

    修改host为主节点ip；

    修改application_name=slave4

  7. 主节点：设置新节点的流复制为同步：

    vim /var/lig/pgsql/11/data/postgresql.conf

    修改synchronous_standby_names = 'slave3,slave4'，

  8. 新节点

    systemctl restart postgresql-11

设置agent服务自启动

vim /usr/lib/systemd/system/sugoncloud-pgsql-agent.service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[Unit]
Description=Pgsql Agent Service
BindsTo=dev-virtio\x2dports-org.qemu.guest_agent.1.device dev-virtio\x2dports-org.qemu.guest_agent.2.device
After=dev-virtio\x2dports-org.qemu.guest_agent.1.device dev-virtio\x2dports-org.qemu.guest_agent.2.device
IgnoreOnIsolate=True

[Service]
Type=forking
User=root
ExecStart=/etc/agent/agent-start.sh
Restart=always
RestartSec=0

[Install]
WantedBy=multi-user.target

#重载
systemctl daemon-reload
#设置自启动
systemctl enable sugoncloud-pgsql-agent

vim /etc/agent/agent-start.sh 

1
2
3
4
5
#!/bin/sh
export JAVA_HOME=/usr/local/java/jdk1.8.0_60
export PATH=$PATH:$​​​{JAVA_HOME}/bin
cd /etc/agent/
java -jar /etc/agent/sugoncloud-pgsql-agent.jar > /dev/null 2>&1 &

#改变权限
chmod 777 /etc/agent/agent-start.sh

java安装

mkdir /usr/local/java
tar xzf jdk-8u60-linux-x64.tar.gz -C /usr/local/java/

#环境变量
vim /etc/profile

1
2
3
export JAVA_HOME=/usr/local/java/jdk1.8.0_60
export CLASSPATH=.:${JAVA_HOME}/jre/lib/rt.jar:${JAVA_HOME}/lib/dt.jar:${JAVA_HOME}/lib/tools.jar
export PATH=$​​PATH:${JAVA_HOME}/bin

#生效
source /etc/profile