PostgreSql 主备同步 数据库备份 pgsql
转 https://blog.csdn.net/Gjw_java/article/details/123402180
前言
本文中使用到的系统及软件版本
PostgreSQL 10
centos 8
一次性SQL转储
单数据库
PostgreSQL 提供了 pg_dump 实用程序来简化备份单个数据库的过程。
t i p : \color{red}{tip:}tip: 必须以对要备份的数据库具有读取权限的用户身份运行此命令。
以 postgres 用户身份登录：
su - postgres
1
通过运行以下命令将数据库的内容转储到文件中。
t i p : \color{red}{tip:}tip: 替换 dbname 为要备份的数据库的名称。
pg_dump dbname > dbname.bak
1
生成的备份文件 dbname.bak 可以使用 scp 传输到另一台主机，也可以存储在本地以供以后使用。
要演示恢复丢失的数据，请删除示例数据库并在其位置创建一个空数据库
dropdb dbname
createdb dbname
1
2
使用 psql 恢复数据库
psql dbname < dumpfile
1
备份格式有几种选择：
*.bak：压缩二进制格式
*.sql：明文转储
*.tar：tarball
t i p : \color{red}{tip:}tip: 默认情况下，PostgreSQL 将忽略备份过程中发生的任何错误。这可能导致备份不完整。要防止这种情况，您可以使用 -1 选项运行 pg_dump 命令。这会将整个备份过程视为单个事务，这将在发生错误时阻止部分备份。
pg_dump -1 dbname > dbname.bak
1
远程数据库
正如 psql 允许您连接到远程主机一样，可以从客户端计算机运行 pg_dump 以备份远程服务器上的数据。 使用 -h 标志指定服务器的 IP 地址(本文使用 198.51.100.0 )，使用 -p 标识 PostgreSQL 正在侦听的端口：
pg_dump -h 198.51.100.0 -p 5432 dbname > dbname.bak
1
所有数据库
由于 pg_dump 一次只创建一个数据库的备份，因此它不会存储有关数据库角色或其他群集范围配置的信息。要存储此信息并同时备份所有数据库，可以使用 pg_dumpall
创建备份文件：
pg_dumpall > pg_backup.bak
1
从备份还原所有数据库：
psql -f pg_backup.bak postgres
1
处理大型数据库
某些操作系统具有最大文件大小限制，这些限制会在创建大型 pg_dump 输出文件时导致问题。幸运的是，pg_dump 可以写入标准输出，因此您可以使用标准 Unix 工具来解决这个潜在的问题。有几种可能的方法：
使用压缩转储。 您可以使用自己喜欢的压缩程序，例如 gzip：
pg_dump dbname | gzip > filename.gz
1
重新加载：
gunzip -c filename.gz | psql dbname
# 或者
cat filename.gz | gunzip | psql dbname
1
2
3
使用拆分 该命令允许您将输出拆分为较小的文件，这些文件的大小对于底层文件系统是可以接受的。 例如，制作 2 GB 的块：split
pg_dump dbname | split -b 2G - filename
# 如果使用 GNU split，可以将它和 gzip 一起使用：它可以使用 .zcat 恢复
pg_dump dbname | split -b 2G --filter='gzip > $FILE.gz'
1
2
3
重新加载：
cat filename* | psql dbname
1
使用 pg_dump 的自定义转储格式。 如果 PostgreSQL 是在安装了 zlib 压缩库的系统上构建的，则自定义转储格式将在将数据写入输出文件时对其进行压缩。 这将产生类似于 using 的转储文件大小，但它具有可以选择性地恢复表的额外优势。 以下命令使用自定义转储格式转储数据库：gzip
pg_dump -Fc dbname > filename
1
自定义格式转储不是 psql 的脚本，而是必须使用 pg_restore 恢复，例如：
pg_restore -d dbname filename
1
使用 pg_dump 的并行转储功能。 要加快大型数据库的转储，可以使用 pg_dump 的并行模式。 这将同时转储多个表。 您可以通过参数控制并行度。 仅“目录”归档格式支持并行转储。-j
pg_dump -j num -F d -f out.dir dbname
1
您可以使用并行恢复转储。 这适用于“自定义”或“目录”存档模式的任何存档，无论它是否已使用 .pg_restore -j pg_dump -j 创建
使用 Cron 任务自动执行备份
您可能希望设置一个 cron 作业，以便定期自动备份数据库。 本节中的步骤将设置一个 cron 任务，每周运行一次 pg_dump。
确保以 postgres 用户身份登录：
su - postgres
1
创建存储自动备份的目录：
mkdir -p ~/postgres/backups
1
编辑 crontab 以创建新的 cron 任务：
crontab -e
1
将以下行添加到 crontab 的末尾：
0 * * * 0 pg_dump -U postgres dbname > ~/postgres/backups/dbname.bak
1
保存并退出编辑器。您的数据库将在每个星期日的半夜12点进行备份。
文件系统级别的备份
另一种备份策略是直接复制 PostgreSQL 用于在数据库中存储数据的文件；可以使用您喜欢的任何方法进行文件系统备份； 例如：
tar -cf backup.tar /usr/local/pgsql/data
1
然而，有两个限制使得这种方法不切实际，或者至少不如 pg_dump 方法：
必须关闭数据库服务器才能获得可用的备份。 诸如禁止所有连接之类的中途措施将不起作用（部分原因是 tar 和类似工具不会对文件系统的状态进行原子快照，但也因为服务器内部的内部缓冲）。有关停止服务器的信息可以在第 18.5 节中找到。不用说，您还需要在恢复数据之前关闭服务器。
如果您深入了解了数据库文件系统布局的详细信息，您可能会尝试从它们各自的文件或目录中仅备份或恢复某些单独的表或数据库。这将不起作用，因为如果没有提交日志文件 pg_xact/*，这些文件中包含的信息将无法使用，其中包含所有事务的提交状态。表格文件仅可用于此信息。当然，也不可能只恢复一个表和相关的 pg_xact 数据，因为这会使数据库集群中的所有其他表无用。所以文件系统备份只适用于整个数据库集群的完整备份和恢复。
t i p : \color{red}{tip:}tip: 文件系统备份通常比 SQL 转储大。（例如，pg_dump 不需要转储索引的内容，只需要重新创建它们的命令。）但是，进行文件系统备份可能会更快。
连续存档和时间点恢复 (Continuous Archiving and Point-in-Time Recovery 简称 PITR)
PostgreSQL 在任何时候都在集群数据目录的 pg_wal/ 子目录中维护一个预写日志 (write ahead log 简称 WAL)。日志记录对数据库数据文件所做的每一次更改。此日志的存在主要是为了防崩溃：如果系统崩溃，可以通过"重播"自上次检查点以来创建的日志条目来将数据库还原从而保证一致性。于是，日志的存在使得使用第三种备份数据库的策略成为可能：我们可以将文件系统级别的备份与 WAL 文件的备份结合起来。如果需要恢复，我们会恢复文件系统备份，然后从备份的 WAL 文件中重播，以使系统恢复到当前状态。这种方法比以前的任何一种方法管理起来都更复杂，但它有一些显著的好处：
我们不需要一个完全一致的文件系统备份作为起点。 备份中的任何内部不一致都将通过日志重放来纠正（这与崩溃恢复期间发生的情况没有显著不同）。所以我们不需要文件系统快照功能，只需要 tar 或类似的归档工具。
由于我们可以组合无限长的 WAL 文件序列进行回放，因此只需继续归档 WAL 文件即可实现连续备份。这对于大型数据库特别有价值，因为在这些数据库中经常进行完整备份可能不方便。
没有必要一直重播 WAL 条目。 我们可以在任何时候停止重播，并获得数据库当时的一致快照。因此，该技术支持时间点恢复：可以在进行基本备份后的任何时间将数据库恢复到其状态。
如果我们不断地将一系列 WAL 文件提供给另一台已经加载了相同基本备份文件的机器，我们就有了一个热备用系统：在任何时候我们都可以启动第二台机器，它会有一个几乎最新的副本数据库。
t i p : \color{red}{tip:}tip: pg_dump 和 pg_dumpall 不生成文件系统级备份，并且不能用作连续归档解决方案的一部分。因为 dump 生成的备份文件不包含 WAL 重放使用的足够信息。
与普通文件系统备份技术一样，这种方法只能支持整个数据库集群的恢复，而不是一个子集。 此外，它需要大量的存档存储：基本备份可能很庞大，并且繁忙的系统会产生许多兆字节的 WAL 流量需要存档。 尽管如此，在许多需要高可靠性的情况下，它仍然是首选的备份技术。
设置 WAL(预写日志) 归档
在抽象意义上，一个正在运行的 PostgreSQL 系统会产生一个无限长的 WAL 记录序列。系统在物理上将此序列划分为 WAL 段文件，通常每个文件为 16MB（尽管在构建 PostgreSQL 时可以更改段大小）。段文件被赋予反映它们在抽象 WAL 序列中的位置的数字名称。当不使用 WAL 归档时，系统通常只创建几个段文件，然后通过将不再需要的段文件重命名为更高的段号来“回收”它们。假设内容在 checkpoint-before-last 之前的段文件不再感兴趣并且可以回收。
在归档 WAL 数据时，我们需要在每个段文件填充后捕获它的内容，并将该数据保存在某处，然后再将段文件回收以供重复使用。根据应用程序和可用的硬件，可能有许多不同的“将数据保存在某处”的方法：我们可以将段文件复制到另一台机器上的 NFS 挂载目录，将它们写入磁带驱动器（确保您有一种识别每个文件的原始名称的方法），或者将它们批处理在一起并将它们刻录到 CD 上，或者完全是其他东西。为了给数据库管理员提供灵活性，PostgreSQL 尽量不对归档的完成方式做任何假设。相反，PostgreSQL 允许管理员指定要执行的 shell 命令，以将完成的段文件复制到它需要去的任何地方。该命令可以像 cp 一样简单，也可以调用复杂的 shell 脚本——这完全取决于您。
要启用 WAL 归档，请将 wal_level 配置参数设置为 replica 或更高，将 archive_mode 设置为 on，并在 archive_command 配置参数中指定要使用的 shell 命令。在实践中，这些设置总是放在 postgresql.conf 文件中。在 archive_command 中，%p 替换为要归档的文件的路径名，而 %f 仅替换为文件名。 （路径名相对于当前工作目录，即集群的数据目录。）如果需要在命令中嵌入实际的 % 字符，请使用 %%。最简单有用的命令是这样的：
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' # Unix
archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"' # Windows
1
2
这会将可归档的 WAL 段复制到目录 /mnt/server/archivedir。
t i p : \color{red}{tip:}tip: 这是一个示例，并非建议，并且可能不适用于所有平台。
替换 %p 和 %f 参数后，实际执行的命令可能如下所示：
test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_wal/00000001000000A900000065 /mnt/server/archivedir/00000001000000A900000065
1
归档命令将在运行 PostgreSQL 服务器的同一用户的所有权下执行。 由于正在归档的一系列 WAL 文件实际上包含了数据库中的所有内容，因此您需要确保归档的数据不会被窥探； 例如，归档到没有组或全局读取访问权限的目录中。
当且仅当归档命令成功时，归档命令返回零退出状态是很重要的。 得到零结果后，PostgreSQL 将假定文件已成功归档，并将删除或回收它。 然而，一个非零状态告诉 PostgreSQL 该文件没有被归档； 它会定期重试，直到成功。
归档命令通常应设计为拒绝覆盖任何预先存在的归档文件。 这是一项重要的安全功能，可在出现管理员错误（例如将两个不同服务器的输出发送到同一存档目录）时保持存档的完整性。
在设计归档设置时，请考虑如果归档命令由于某些方面需要操作员干预或归档空间不足而反复失败会发生什么。例如，如果您在没有自动转换器的情况下写入磁带，就会发生这种情况；当磁带装满时，在更换磁带之前，不能再存档任何内容。您应确保适当地报告任何错误情况或对人工操作员的请求，以便可以合理快速地解决问题。pg_wal/ 目录将继续填充 WAL 段文件，直到情况得到解决。
t i p : \color{red}{tip:}tip: 如果包含 pg_wal/ 的文件系统已满，PostgreSQL 将执行 PANIC 关闭。不会丢失任何已提交的事务，但数据库将保持离线状态，直到您释放一些空间。
归档命令的速度并不重要，只要它能够跟上服务器生成 WAL 数据的平均速率即可。即使归档过程稍有滞后，正常操作也会继续。如果归档明显落后，这将增加在发生灾难时丢失的数据量。这也意味着 pg_wal/ 目录将包含大量尚未归档的段文件，最终可能会超出可用磁盘空间。建议您监控存档过程，以确保它按您的预期工作。
在编写归档命令时，您应该假设要归档的文件名最长为 64 个字符，并且可以包含 ASCII 字母、数字和点的任意组合。不必保留原始相对路径 (%p)，但必须保留文件名 (%f)。
t i p : \color{red}{tip:}tip: 请注意，尽管 WAL 归档允许您恢复对 PostgreSQL 数据库中的数据所做的任何修改，但它不会恢复对配置文件（即 postgresql.conf、pg_hba.conf 和 pg_ident.conf）所做的更改，因为这些是手动编辑，而不是通过 SQL 操作。
归档命令仅在已完成的 WAL 段上调用。因此，如果您的服务器只产生很少的 WAL 流量（或者在它这样做的时候有松弛期），那么在事务完成和它在存档存储中的安全记录之间可能会有很长的延迟。要限制未归档数据的陈旧时间，您可以设置 archive_timeout 以强制服务器至少经常切换到新的 WAL 段文件。请注意，由于强制切换而提前归档的归档文件的长度仍与完全完整的文件相同。因此，设置一个非常短的 archive_timeout 是不明智的——它会使你的存档存储膨胀。 archive_timeout 一分钟左右的设置通常是合理的。
此外，如果您想确保尽快归档刚刚完成的事务，您可以使用 pg_switch_wal 手动强制进行段切换。
当 wal_level 最小时，一些 SQL 命令被优化以避免 WAL 日志记录，如第 14.4.7 节所述。如果在执行这些语句之一期间打开了归档或流复制，WAL 将不会包含足够的信息来进行归档恢复。（崩溃恢复不受影响。）因此，wal_level 只能在服务器启动时更改。但是，archive_command 可以通过重新加载配置文件来更改。如果您希望暂时停止归档，一种方法是将 archive_command 设置为空字符串 (‘’)。这将导致 WAL 文件在 pg_wal/ 中累积，直到重新建立工作的 archive_command。
进行基本备份
执行基本备份的最简单方法是使用 pg_basebackup 工具。 它可以将基本备份创建为常规文件或 tar 存档。 如果需要比 pg_basebackup 更大的灵活性，您还可以使用低级 API 进行基本备份。
不必担心进行基本备份所需的时间。但是，如果您通常在禁用 full_page_writes 的情况下运行服务器，您可能会注意到在备份运行时性能下降，因为 full_page_writes 在备份模式下被有效地强制打开。
要使用备份，您需要保留在文件系统备份期间和之后生成的所有 WAL 段文件。为了帮助您执行此操作，基本备份过程会创建一个备份历史文件，该文件会立即存储到 WAL 存档区域中。该文件以文件系统备份所需的第一个 WAL 段文件命名。例如，如果起始 WAL 文件是 0000000100001234000055CD，则备份历史文件将命名为 0000000100001234000055CD.007C9330.backup。 （文件名的第二部分代表 WAL 文件中的确切位置，通常可以忽略。）一旦您安全地归档了文件系统备份和备份期间使用的 WAL 段文件（如备份历史记录中指定的那样）文件），则不再需要名称数字较小的所有存档 WAL 段来恢复文件系统备份，并且可以将其删除。但是，您应该考虑保留几个备份集，以绝对确定您可以恢复数据。
备份历史文件只是一个小文本文件。它包含你给 pg_basebackup 的标签字符串，以及备份的开始和结束时间和 WAL 段。如果您使用标签来识别关联的转储文件，那么归档的历史文件就足以告诉您要恢复哪个转储文件。
由于您必须将所有已归档的 WAL 文件保留到您上次的基本备份，因此基本备份之间的间隔通常应根据您希望在已归档的 WAL 文件上花费多少存储空间来选择。您还应该考虑准备花多长时间进行恢复，如果需要恢复 - 系统将不得不重播所有这些 WAL 段，如果自上次基本备份以来已经很长时间，这可能需要一段时间。
使用低级 API 进行基本备份
使用低级 API 进行基本备份的过程比 pg_basebackup 方法包含更多步骤，但相对简单。这些步骤按顺序执行是非常重要的，并且在进行下一步之前验证一个步骤是否成功。
可以以非独占或独占方式进行低级别基础备份。推荐使用非独占方法，独占方法已弃用，最终将被删除。
进行非排他性低级备份
非独占低级别备份是允许其他并发备份运行的备份：
确保 WAL 归档已启用并正常工作。
以有权运行 pg_start_backup 的用户（超级用户或已被授予 EXECUTE 功能的用户）连接到服务器（哪个数据库无关紧要）并发出命令：
SELECT pg_start_backup('label', false, false);
1
其中 label 是您要用于唯一标识此备份操作的任何字符串。调用 pg_start_backup 的连接必须保持到备份结束，否则备份将自动中止。
默认情况下，pg_start_backup 可能需要很长时间才能完成。这是因为它执行一个检查点，并且检查点所需的 I/O 将分散在很长一段时间内，默认情况下是检查点间间隔的一半（请参阅配置参数 checkpoint_completion_target）。这通常是您想要的，因为它最大限度地减少了对查询处理的影响。如果您想尽快开始备份，请将第二个参数更改为 true，这将使用尽可能多的 I/O 发出即时检查点。
第三个参数为 false 告诉 pg_start_backup 启动一个非独占的基本备份。
使用任何方便的文件系统备份工具执行备份，例如 tar 或 cpio（不是 pg_dump 或 pg_dumpall）。在执行此操作时停止数据库的正常操作既没有必要也不可取。
在与以前相同的连接中，发出命令：
SELECT * FROM pg_stop_backup(false, true);
1
这将终止备份模式。在主节点上，它还会自动切换到下一个 WAL 段。在备用服务器上，无法自动切换 WAL 段，因此您可能希望在主服务器上运行 pg_switch_wal 以执行手动切换。切换的原因是为了安排在备份间隔期间写入的最后一个 WAL 段文件准备归档。
pg_stop_backup 将返回一行包含三个值。这些字段中的第二个应写入备份根目录中名为 backup_label 的文件。第三个字段应写入名为 tablespace_map 的文件，除非该字段为空。这些文件对备份工作至关重要，必须逐字节写入而不进行修改，这可能需要以二进制模式打开文件。
一旦备份期间活动的 WAL 段文件被归档，您就完成了。由 pg_stop_backup 的第一个返回值标识的文件是形成一组完整备份文件所需的最后一个段。在主节点上，如果启用了 archive_mode 并且 wait_for_archive 参数为 true，则 pg_stop_backup 直到最后一个段被归档后才会返回。在备用服务器上，archive_mode 必须始终为 pg_stop_backup 等待。由于您已经配置了archive_command，这些文件的归档会自动进行。在大多数情况下，这会很快发生，但建议您监控存档系统以确保没有延迟。如果归档进程由于归档命令失败而落后，它将继续重试，直到归档成功并且备份完成。如果您希望对 pg_stop_backup 的执行设置时间限制，请设置适当的 statement_timeout 值，但请注意，如果 pg_stop_backup 因此终止，您的备份可能无效。
如果备份进程监控并确保备份所需的所有 WAL 段文件都已成功归档，则可以将 wait_for_archive 参数（默认为 true）设置为 false，以便在停止备份记录写入 WAL 后立即返回 pg_stop_backup .默认情况下，pg_stop_backup 将等到所有 WAL 都已归档，这可能需要一些时间。必须谨慎使用此选项：如果未正确监控 WAL 归档，则备份可能不包括所有 WAL 文件，因此将不完整且无法恢复。
进行排他性低级备份
独占备份的过程与非独占备份的过程基本相同，但在几个关键步骤上有所不同。这种类型的备份只能在主节点上进行，并且不允许并发备份。在 PostgreSQL 9.6 之前，这是唯一可用的低级方法，但现在建议所有用户升级他们的脚本以尽可能使用非独占备份。
确保 WAL 归档已启用并正常工作。
以有权运行 pg_start_backup 的用户（超级用户或已被授予 EXECUTE 功能的用户）连接到服务器（哪个数据库无关紧要）并发出命令：
SELECT pg_start_backup('label');
1
其中 label 是您要用于唯一标识此备份操作的任何字符串。 pg_start_backup 在集群目录中创建一个名为backup_label 的备份标签文件，其中包含有关您的备份的信息，包括开始时间和标签字符串。该函数还在集群目录中创建一个名为 tablespace_map 的表空间映射文件，其中包含有关 pg_tblspc/ 中的表空间符号链接的信息（如果存在一个或多个此类链接）。如果您需要从中恢复，这两个文件对于备份的完整性都至关重要。
默认情况下，pg_start_backup 可能需要很长时间才能完成。这是因为它执行一个检查点，并且检查点所需的 I/O 将分散在很长一段时间内，默认情况下是检查点间间隔的一半（请参阅配置参数 checkpoint_completion_target）。这通常是您想要的，因为它最大限度地减少了对查询处理的影响。如果您想尽快开始备份，请使用：
SELECT pg_start_backup('label', true);
1
这迫使检查点尽快完成。
使用任何方便的文件系统备份工具执行备份，例如 tar 或 cpio（不是 pg_dump 或 pg_dumpall）。在执行此操作时停止数据库的正常操作既没有必要也不可取。
t i p : \color{red}{tip:}tip: 如果服务器在备份期间崩溃，则可能无法重新启动，直到从 PGDATA 目录中手动删除 backup_label 文件。
再次以有权运行 pg_stop_backup 的用户（超级用户，或已被授予 EXECUTE 功能的用户）连接到数据库，并发出命令：
SELECT pg_stop_backup();
1
此函数终止备份模式并执行自动切换到下一个 WAL 段。切换的原因是为了安排在备份间隔期间写入的最后一个 WAL 段准备好归档。
一旦备份期间活动的 WAL 段文件被归档，您就完成了。由 pg_stop_backup 的结果标识的文件是形成完整备份文件集所需的最后一个段。如果启用 archive_mode，pg_stop_backup 直到最后一个段被归档后才会返回。由于您已经配置了archive_command，这些文件的归档会自动进行。在大多数情况下，这会很快发生，但建议您监控存档系统以确保没有延迟。如果归档进程由于归档命令失败而落后，它将继续重试，直到归档成功并且备份完成。如果您希望对 pg_stop_backup 的执行设置时间限制，请设置适当的 statement_timeout 值，但请注意，如果 pg_stop_backup 因此终止，您的备份可能无效。
备份数据目录
如果在复制过程中尝试复制的文件发生更改，某些文件系统备份工具会发出警告或错误。在对活动数据库进行基本备份时，这种情况是正常的，而不是错误。但是，您需要确保能够将此类投诉与真正的错误区分开来。例如，某些版本的 rsync 为“消失的源文件”返回一个单独的退出代码，您可以编写一个驱动程序脚本来接受这个退出代码作为非错误情况。此外，如果在 tar 复制文件时文件被截断，某些版本的 GNU tar 返回的错误代码与致命错误无法区分。幸运的是，如果在备份期间更改了文件，则 GNU tar 版本 1.16 和更高版本会以 1 退出，而对于其他错误则以 2 退出。使用 GNU tar 1.23 及更高版本，您可以使用警告选项 --warning=no-file-changed --warning=no-file-removed 来隐藏相关的警告消息。
确保您的备份包括数据库集群目录下的所有文件（例如，/usr/local/pgsql/data）。如果您正在使用不在此目录下的表空间，请注意将它们也包括在内（并确保您的备份将符号链接存档为链接，否则恢复将损坏您的表空间）。
但是，您应该从备份中省略集群的 pg_wal/ 子目录中的文件。这种轻微的调整是值得的，因为它降低了恢复时出错的风险。如果 pg_wal/ 是指向集群目录外部某个位置的符号链接，这很容易安排，无论如何出于性能原因，这是一个常见的设置。您可能还想排除 postmaster.pid 和 postmaster.opts，它们记录有关正在运行的 postmaster 的信息，而不是有关最终将使用此备份的 postmaster 的信息。 （这些文件可能会混淆 pg_ctl。）
从备份中省略集群的 pg_replslot/ 目录中的文件通常是一个好主意，这样主服务器上存在的复制槽就不会成为备份的一部分。否则，后续使用备份创建备用可能会导致备用上无限期保留 WAL 文件，如果启用热备用反馈，可能会导致主上膨胀，因为使用这些复制槽的客户端仍将连接并更新主设备上的插槽，而不是备用设备。即使备份仅用于创建新主服务器，复制复制槽也不会特别有用，因为到新主服务器上线时，这些槽的内容可能已经严重过时.
可以从备份中省略目录 pg_dynshmem/、pg_notify/、pg_serial/、pg_snapshots/、pg_stat_tmp/ 和 pg_subtrans/（但不是目录本身）的内容，因为它们将在 postmaster 启动时被初始化。如果设置了 stats_temp_directory 并且位于数据目录下，则该目录的内容也可以省略。
任何以 pgsql_tmp 开头的文件或目录都可以从备份中省略。这些文件在 postmaster 启动时被删除，并且将根据需要重新创建目录。
备份标签文件包括您提供给 pg_start_backup 的标签字符串，以及运行 pg_start_backup 的时间，以及起始 WAL 文件的名称。因此，如果出现混淆，可以查看备份文件内部并准确确定转储文件来自哪个备份会话。表空间映射文件包括存在于目录 pg_tblspc/ 中的符号链接名称以及每个符号链接的完整路径。这些文件不仅供您参考；它们的存在和内容对于系统恢复过程的正常运行至关重要。
也可以在服务器停止时进行备份。在这种情况下，您显然不能使用 pg_start_backup 或 pg_stop_backup，因此您将留给自己的设备来跟踪哪个备份是哪个备份以及关联的 WAL 文件回溯多远。通常最好遵循上面的连续归档过程。
使用连续存档备份进行恢复
好的，最糟糕的情况已经发生，您需要从备份中恢复。这是程序：
停止服务器，如果它正在运行。
如果您有空间这样做，请将整个集群数据目录和任何表空间复制到一个临时位置，以备日后需要它们时使用。请注意，此预防措施将要求您的系统上有足够的可用空间来保存现有数据库的两个副本。如果您没有足够的空间，您至少应该保存集群的 pg_wal 子目录的内容，因为它可能包含在系统关闭之前未归档的日志。
删除集群数据目录下以及您正在使用的任何表空间的根目录下的所有现有文件和子目录。
从文件系统备份中恢复数据库文件。确保以正确的所有权（数据库系统用户，而不是 root！）和正确的权限恢复它们。如果您正在使用表空间，您应该验证 pg_tblspc/ 中的符号链接是否已正确恢复。
删除 pg_wal/ 中存在的所有文件；这些来自文件系统备份，因此可能已过时而不是当前。如果您根本没有存档 pg_wal/，则使用适当的权限重新创建它，如果您之前以这种方式设置它，请注意确保您将其重新建立为符号链接。
如果您在步骤 2 中保存了未归档的 WAL 段文件，请将它们复制到 pg_wal/. （最好是复制它们，而不是移动它们，这样如果出现问题，你仍然有未修改的文件，你必须重新开始。）
在集群数据目录中创建一个恢复命令文件 recovery.conf。您可能还想临时修改 pg_hba.conf 以防止普通用户连接，直到您确定恢复成功。
启动服务器。服务器将进入恢复模式并继续读取它需要的存档 WAL 文件。如果由于外部错误而终止恢复，则可以简单地重新启动服务器并继续恢复。恢复过程完成后，服务器会将 recovery.conf 重命名为 recovery.done（以防止以后意外重新进入恢复模式），然后开始正常的数据库操作。
检查数据库的内容以确保您已恢复到所需的状态。如果没有，请返回步骤 1。如果一切正常，请通过将 pg_hba.conf 恢复为正常来允许您的用户连接。
所有这一切的关键部分是设置一个恢复配置文件，该文件描述您希望如何恢复以及恢复应该运行多远。您可以使用 recovery.conf.sample（通常位于安装的 share/ 目录中）作为原型。你必须在 recovery.conf 中指定的一件事是 restore_command，它告诉 PostgreSQL 如何检索归档的 WAL 文件段。与 archive_command 一样，这是一个shell 命令字符串。它可以包含 %f（替换为所需日志文件的名称）和 %p（替换为要将日志文件复制到的路径名）。 （路径名相对于当前工作目录，即集群的数据目录。）如果需要在命令中嵌入实际的 % 字符，请写入 %%。最简单有用的命令是这样的：
restore_command = 'cp /mnt/server/archivedir/%f %p'
1
这将从目录 /mnt/server/archivedir 复制以前存档的 WAL 段。当然，您可以使用更复杂的东西，甚至可以使用要求操作员安装适当磁带的 shell 脚本。
重要的是该命令在失败时返回非零退出状态。该命令将被称为请求存档中不存在的文件；当被问到时，它必须返回非零。这不是错误情况。一个例外是，如果命令被信号（SIGTERM 除外，它用作数据库服务器关闭的一部分）或 shell 错误（例如找不到命令）终止，则恢复将中止，服务器将不启动。
并非所有请求的文件都是 WAL 段文件；您还应该期望对带有 .history 后缀的文件的请求。另请注意，%p 路径的基本名称将与 %f 不同；不要指望它们可以互换。
无法在存档中找到的 WAL 段将在 pg_wal/ 中查找；这允许使用最近未归档的段。但是，存档中可用的段将优先于 pg_wal/ 中的文件使用。
通常，恢复将通过所有可用的 WAL 段进行，从而将数据库恢复到当前时间点（或尽可能接近可用的 WAL 段）。因此，正常恢复将以“找不到文件”消息结束，错误消息的确切文本取决于您选择的 restore_command。对于名为 00000001.history 的文件，您可能还会在恢复开始时看到一条错误消息。这也是正常的，在简单的恢复情况下并不表示有问题；
如果您想恢复到以前的某个时间点（例如，就在初级 DBA 删除您的主事务表之前），只需在 recovery.conf 中指定所需的停止点。您可以通过日期/时间、命名的还原点或特定事务 ID 的完成来指定称为“恢复目标”的停止点。在撰写本文时，只有日期/时间和命名的还原点选项非常有用，因为没有工具可以帮助您准确识别要使用的事务 ID。
t i p : \color{red}{tip:}tip: 停止点必须在基础备份的结束时间之后，即 pg_stop_backup 的结束时间。您不能使用基本备份来恢复到该备份正在进行的时间。 （要恢复到这样的时间，您必须回到以前的基本备份并从那里前滚。）
如果恢复发现损坏的 WAL 数据，恢复将在该点停止并且服务器将不会启动。在这种情况下，恢复过程可以从头开始重新运行，在损坏点之前指定“恢复目标”，以便恢复可以正常完成。如果由于外部原因恢复失败，例如系统崩溃或 WAL 存档变得不可访问，则可以简单地重新启动恢复，并且它将几乎从失败的地方重新启动。恢复重启的工作方式很像正常操作中的检查点：服务器定期强制其所有状态到磁盘，然后更新 pg_control 文件以指示不需要再次扫描已经处理的 WAL 数据。
Timelines
将数据库恢复到以前的时间点的能力会产生一些类似于关于时间旅行和平行宇宙的科幻故事的复杂性。例如，在数据库的原始历史记录中，假设您在周二晚上 5:15 PM 删除了一个关键表，但直到周三中午才意识到自己的错误。毫不担心，您取出备份，恢复到周二晚上 5:14 PM 的时间点，然后开始运行。在这个数据库世界的历史中，您从未删除过该表。但是假设您后来意识到这不是一个好主意，并想回到原始历史中的某个星期三早上。如果在您的数据库启动并运行时，它会覆盖一些 WAL 段文件，这些文件会导致您现在希望可以返回的时间，那么您将无法做到这一点。因此，为避免这种情况，您需要将在执行时间点恢复后生成的一系列 WAL 记录与在原始数据库历史记录中生成的记录区分开来。
为了解决这个问题，PostgreSQL 有一个时间线的概念。每当归档恢复完成时，都会创建一个新的时间线来识别在该恢复之后生成的一系列 WAL 记录。时间线 ID 号是 WAL 段文件名的一部分，因此新时间线不会覆盖先前时间线生成的 WAL 数据。事实上，可以归档许多不同的时间线。虽然这似乎是一个无用的功能，但它通常是救命稻草。考虑一下您不太确定要恢复到哪个时间点的情况，因此必须通过反复试验进行多次时间点恢复，直到找到从旧历史分支的最佳位置。如果没有时间表，这个过程很快就会产生无法控制的混乱局面。使用时间线，您可以恢复到任何先前的状态，包括您之前放弃的时间线分支中的状态。
每次创建新的时间线时，PostgreSQL 都会创建一个“时间线历史”文件，显示它从哪个时间线分支以及何时分支。当从包含多个时间线的存档中恢复时，这些历史文件是允许系统选择正确的 WAL 段文件所必需的。因此，它们就像 WAL 段文件一样被归档到 WAL 归档区。历史文件只是小文本文件，因此将它们无限期地保存起来既便宜又合适（与大的段文件不同）。如果愿意，您可以在历史文件中添加评论，以记录您自己关于如何以及为何创建此特定时间线的注释。当您通过实验获得大量不同的时间线时，此类评论将特别有价值。
恢复的默认行为是沿进行基本备份时的当前时间线进行恢复。如果您希望恢复到某个子时间线（即，您希望返回到恢复尝试后自身生成的某个状态），您需要在 recovery.conf 中指定目标时间线 ID。您无法恢复到早于基本备份分支的时间线。
Tips and Examples
此处提供了一些配置连续归档的技巧。
Standalone Hot Backups
可以使用 PostgreSQL 的备份工具来生成独立的热备份。这些备份不能用于时间点恢复，但通常比 pg_dump 转储快得多。（它们也比 pg_dump 转储大得多，因此在某些情况下速度优势可能会被抵消。）
与基本备份一样，生成独立热备份的最简单方法是使用 pg_basebackup 工具。如果调用时包含 -X 参数，则使用备份所需的所有预写日志将自动包含在备份中，无需特殊操作即可恢复备份。
如果需要更灵活地复制备份文件，也可以将较低级别的进程用于独立热备份。要准备低级别的独立热备份，请确保将 wal_level 设置为副本或更高，将 archive_mode 设置为 on，并设置仅当存在切换文件时才执行归档的 archive_command。例如：
archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/archive/%f && cp %p /var/lib/pgsql/archive/%f)'
1
当 /var/lib/pgsql/backup_in_progress 存在时，此命令将执行归档，否则会静默返回零退出状态（允许 PostgreSQL 回收不需要的 WAL 文件）。
通过这种准备，可以使用如下脚本进行备份：
touch /var/lib/pgsql/backup_in_progress
psql -c "select pg_start_backup('hot_backup');"
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
psql -c "select pg_stop_backup();"
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
1
2
3
4
5
6
切换文件 /var/lib/pgsql/backup_in_progress 首先被创建，使完成的 WAL 文件能够归档。备份后，交换机文件被删除。然后将存档的 WAL 文件添加到备份中，以便基本备份和所有必需的 WAL 文件都是同一个 tar 文件的一部分。请记住在备份脚本中添加错误处理。
Compressed Archive Logs
如果归档存储大小是一个问题，您可以使用 gzip 压缩归档文件：
archive_command = 'gzip < %p > /var/lib/pgsql/archive/%f'
1
然后，您将需要在恢复期间使用 gunzip：
restore_command = 'gunzip < /mnt/server/archivedir/%f > %p'
1
Archive_command Scripts
许多人选择使用脚本来定义他们的 archive_command，这样他们的postgresql.conf 条目看起来就很简单：
archive_command = 'local_backup_script.sh "%p" "%f"'
1
每当您想在归档过程中使用多个命令时，建议使用单独的脚本文件。这允许在脚本中管理所有复杂性，脚本可以用流行的脚本语言（如 bash 或 perl）编写。
可能在脚本中解决的需求示例包括：
复制数据以保护异地数据存储
批处理 WAL 文件，以便每三个小时传输一次，而不是一次传输一个
与其他备份和恢复软件的接口
与监控软件接口以报告错误
t i p : \color{red}{tip:}tip: 使用 archive_command 脚本时，最好启用 logging_collector。然后，从脚本写入 stderr 的任何消息都将出现在数据库服务器日志中，如果复杂配置失败，则可以轻松诊断它们。
警告
在本文中，连续存档技术有几个局限性。这些问题可能会在未来的版本中得到修复：
如果在进行基本备份时执行 CREATE DATABASE 命令，然后在基本备份仍在进行时修改 CREATE DATABASE 复制的模板数据库，则恢复可能会导致这些修改传播到也创建了数据库。这当然是不可取的。为避免这种风险，最好不要在进行基本备份时修改任何模板数据库。
CREATE TABLESPACE 命令使用文字绝对路径进行 WAL 日志记录，因此将作为具有相同绝对路径的表空间创建重放。 如果在另一台机器上重放日志，这可能是不可取的。 即使日志在同一台机器上重放，但进入一个新的数据目录也可能很危险：重放仍然会覆盖原始表空间的内容。 为避免此类潜在问题，最佳实践是在创建或删除表空间后进行新的基本备份。
还应该注意的是，默认的 WAL 格式相当庞大，因为它包含许多磁盘页面快照。 这些页面快照旨在支持崩溃恢复，因为我们可能需要修复部分写入的磁盘页面。 根据您的系统硬件和软件，部分写入的风险可能小到可以忽略，在这种情况下，您可以通过使用 full_page_writes 参数关闭页面快照来显着减少归档日志的总量。 （在这样做之前，请阅读第 30 章中的注释和警告。）关闭页面快照不会阻止将日志用于 PITR 操作。 未来发展的一个领域是通过删除不必要的页面副本来压缩存档的 WAL 数据，即使 full_page_writes 处于打开状态。 同时，管理员可能希望通过尽可能增加检查点间隔参数来减少 WAL 中包含的页面快照的数量。
————————————————
版权声明：本文为CSDN博主「高建伟-joe」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Gjw_java/article/details/123402180
数据库备份和恢复 (Backup and Restore) 参考上面View Code
数据主备搭建
psql (PostgreSQL) 9.2.24
PGDATA=/var/lib/pgsql/data
systemctl start postgresql
systemctl enable postgresql
You can now start the database server using:
/usr/bin/postgres -D /var/lib/pgsql/data
or
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
#admin用户创建用户
-bash-4.2$ psql
psql (9.2.24)
Type "help" for help.
postgres=# create user replica login replication encrypted password 'replica';
CREATE ROLE
#ambari登录再查
psql -U ambari
select * from pg_user;
修改主配置文件postgresql.conf
修改主配置文件pg_hba.conf
systemctl restart postgresql.service
su - postgres
psql -U ambari
Password for user ambari:ambari
#查当前已连接的数据库
\c ambari
#给ambari授权
grant all privileges on database ambari to ambari;
#列出所有表
\d
select * from users;(ambari界面登录的用户)
hosts 主机列表
备库192.168.159.12
安装好数据库，不要启动
rm -rf /var/lib/pgsql/data/*
主库192.168.159.11
root
vi postgresql.conf
[root@hdp1 data]# cat postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytes Time units: ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
# Note: In RHEL/Fedora installations, you can't set the port number here;
# adjust it in the service file instead.
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 0 # amount of data between renegotiations
#ssl_cert_file = 'server.crt' # (change requires restart)
#ssl_key_file = 'server.key' # (change requires restart)
#ssl_ca_file = '' # (change requires restart)
#ssl_crl_file = '' # (change requires restart)
#password_encryption = on
#db_user_namespace = off
# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres' # (Kerberos only)
#krb_caseins_users = off
# - TCP Keepalives -
# see "man 7 tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 32MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Disk -
#temp_file_limit = -1 # limits per-session temp file space
# in kB, or -1 for no limit
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
wal_level = hot_standby # minimal, archive, or hot_standby
#wal_level = minimal # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # synchronization level;
# off, local, remote_write, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
#archive_command = 'cp %p /var/lib/postgresql/data/pg_bak/%f' # command to use to archive a logfile segment
archive_command = 'cp %p /var/lib/pgsql/data/pg_bak/%f'
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
# Set these on the master and on any standby that will send replication data.
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
#replication_timeout = 60s # in milliseconds; 0 disables
# - Master Server -
# These settings are ignored on a standby server.
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
# - Standby Servers -
# These settings are ignored on a master server.
#hot_standby = off # "on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 128MB
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0
# - Other Planner Options -
#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
#log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# This is only relevant when logging to eventlog (win32):
# (change requires restart)
#event_source = 'PostgreSQL'
# - When to Log -
#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#log_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_min_error_statement = error # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages
#log_hostname = off
#log_line_prefix = '' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'PRC'
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
#gin_fuzzy_search_limit = 0
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'PRC'
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia (historical usage)
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#standard_conforming_strings = on
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off # terminate session on any error?
#restart_after_crash = on # reinitialize after backend crash?
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
wal_level = hot_standby
max_wal_senders = 10
archive_mode=on
archive_command = 'cp %p /var/lib/pgsql/data/pg_bak/%f'
vi pg_hba.conf
[root@hdp1 data]# cat pg_hba.conf # PostgreSQL Client Authentication Configuration File # =================================================== # # Refer to the "Client Authentication" section in the PostgreSQL # documentation for a complete description of this file. A short # synopsis follows. # # This file controls: which hosts are allowed to connect, how clients # are authenticated, which PostgreSQL user names they can use, which # databases they can access. Records take one of these forms: # # local DATABASE USER METHOD [OPTIONS] # host DATABASE USER ADDRESS METHOD [OPTIONS] # hostssl DATABASE USER ADDRESS METHOD [OPTIONS] # hostnossl DATABASE USER ADDRESS METHOD [OPTIONS] # # (The uppercase items must be replaced by actual values.) # # The first field is the connection type: "local" is a Unix-domain # socket, "host" is either a plain or SSL-encrypted TCP/IP socket, # "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a # plain TCP/IP socket. # # DATABASE can be "all", "sameuser", "samerole", "replication", a # database name, or a comma-separated list thereof. The "all" # keyword does not match "replication". Access to replication # must be enabled in a separate record (see example below). # # USER can be "all", a user name, a group name prefixed with "+", or a # comma-separated list thereof. In both the DATABASE and USER fields # you can also write a file name prefixed with "@" to include names # from a separate file. # # ADDRESS specifies the set of hosts the record matches. It can be a # host name, or it is made up of an IP address and a CIDR mask that is # an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that # specifies the number of significant bits in the mask. A host name # that starts with a dot (.) matches a suffix of the actual host name. # Alternatively, you can write an IP address and netmask in separate # columns to specify the set of hosts. Instead of a CIDR-address, you # can write "samehost" to match any of the server's own IP addresses, # or "samenet" to match any address in any subnet that the server is # directly connected to. # # METHOD can be "trust", "reject", "md5", "password", "gss", "sspi", # "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert". Note that # "password" sends passwords in clear text; "md5" is preferred since # it sends encrypted passwords. # # OPTIONS are a set of options for the authentication in the format # NAME=VALUE. The available options depend on the different # authentication methods -- refer to the "Client Authentication" # section in the documentation for a list of which options are # available for which authentication methods. # # Database and user names containing spaces, commas, quotes and other # special characters must be quoted. Quoting one of the keywords # "all", "sameuser", "samerole" or "replication" makes the name lose # its special character, and just match a database or username with # that name. # # This file is read on server startup and when the postmaster receives # a SIGHUP signal. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect. You can # use "pg_ctl reload" to do that. # Put your actual configuration here # ---------------------------------- # # If you want to allow non-local connections, you need to add more # "host" records. In that case you will also need to make PostgreSQL # listen on a non-local interface via the listen_addresses # configuration parameter, or via the -i or -h command line switches. # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres peer # IPv4 local connections: host all postgres 127.0.0.1/32 ident # IPv6 local connections: host all postgres ::1/128 ident # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 ident #host replication postgres ::1/128 ident local all ambari,mapred md5 host all ambari,mapred 0.0.0.0/0 md5 host all ambari,mapred ::/0 md5 #host all all 0.0.0.0/0 md5 host all all 192.168.159.11/32 trust host replication all 192.168.159.12/32 trust host replication replica 192.168.159.12/32 md5 host replication replica 192.168.159.11/32 trust
host all all 192.168.159.11/32 trust
host replication all 192.168.159.12/32 trust
host replication replica 192.168.159.12/32 md5
host replication replica 192.168.159.11/32 trust
systemctl restart postgresql.service
su - postgres
-bash-4.2$ psql
postgres=# create user replica login replication encrypted password 'replica';
postgres=# ALTER USER replica with encrypted password '123456';
mkdir -pv /tmp/bak1111
pg_basebackup -h 192.168.159.11 -p 5432 -U replica -F p -x -P -D /tmp/bak1111
scp -r /tmp/bak1111/* 192.168.159.12:/var/lib/pgsql/data
备库192.168.159.12
chown -R postgres: /var/lib/pgsql/data
vi postgresql.conf 参数修改 (hot_standby = on 其他参数同主库，不用改)
[root@hdp2 data]# cat postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytes Time units: ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
# Note: In RHEL/Fedora installations, you can't set the port number here;
# adjust it in the service file instead.
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/var/run/postgresql, /tmp' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 0 # amount of data between renegotiations
#ssl_cert_file = 'server.crt' # (change requires restart)
#ssl_key_file = 'server.key' # (change requires restart)
#ssl_ca_file = '' # (change requires restart)
#ssl_crl_file = '' # (change requires restart)
#password_encryption = on
#db_user_namespace = off
# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres' # (Kerberos only)
#krb_caseins_users = off
# - TCP Keepalives -
# see "man 7 tcp" for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects the system default
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 32MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Disk -
#temp_file_limit = -1 # limits per-session temp file space
# in kB, or -1 for no limit
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)
# - Cost-Based Vacuum Delay -
#vacuum_cost_delay = 0ms # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
wal_level = hot_standby # minimal, archive, or hot_standby
#wal_level = minimal # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # synchronization level;
# off, local, remote_write, or on
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
#archive_command = 'cp %p /var/lib/postgresql/data/pg_bak/%f' # command to use to archive a logfile segment
archive_command = 'cp %p /var/lib/pgsql/data/pg_bak/%f'
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
# Set these on the master and on any standby that will send replication data.
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
#replication_timeout = 60s # in milliseconds; 0 disables
# - Master Server -
# These settings are ignored on a standby server.
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
# - Standby Servers -
# These settings are ignored on a master server.
hot_standby = on # on" allows queries during recovery
# (change requires restart)
#max_standby_archive_delay = 30s # max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent
# query conflicts
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 128MB
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_seed = 0.0 # range 0.0-1.0
# - Other Planner Options -
#default_statistics_target = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
#log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
#log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# This is only relevant when logging to eventlog (win32):
# (change requires restart)
#event_source = 'PostgreSQL'
# - When to Log -
#client_min_messages = notice # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
#log_min_messages = warning # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
#log_min_error_statement = error # values in order of decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default # terse, default, or verbose messages
#log_hostname = off
#log_line_prefix = '' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'PRC'
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_io_timing = off
#track_functions = none # none, pl, all
#track_activity_query_size = 1024 # (change requires restart)
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
# (change requires restart)
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use
# vacuum_cost_limit
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '"$user",public' # schema names
#default_tablespace = '' # a tablespace name, '' uses the default
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex' # hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
#gin_fuzzy_search_limit = 0
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'PRC'
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia (historical usage)
# India
# You can create your own file in
# share/timezonesets/.
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii # actually, defaults to database
# encoding
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
#max_pred_locks_per_transaction = 64 # min 10
# (change requires restart)
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#standard_conforming_strings = on
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off # terminate session on any error?
#restart_after_crash = on # reinitialize after backend crash?
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
hot_standby = on
cp /usr/share/pgsql/recovery.conf.sample /var/lib/pgsql/data/
cp /usr/share/pgsql/recovery.conf.sample /var/lib/pgsql/data/recovery.conf
vi /var/lib/pgsql/data/recovery.conf
[root@hdp2 data]# cat /var/lib/pgsql/data/recovery.conf|grep -v ^# standby_mode = on primary_conninfo = 'host=192.168.159.11 user=replica port=5432 password=123456'
standby_mode = on
primary_conninfo = 'host=192.168.159.11 user=replica password=123456'
主库进程walsender，参考输出
[root@hdp1 data]# ps -ef | grep "wal" | grep -v "grep"
postgres 57233 57228 0 14:31 ? 00:00:00 postgres: wal writer process
select pg_is_in_recovery();
# 如果返回t 说明是备库，返回f 说明是主库。
#配置重载
[root@hdp1 ~]# su - postgres
Last login: Sun Nov 19 14:52:14 CST 2023 on pts/1
-bash-4.2$ pg_ctl reload -D $PGDATA
server signaled
users
hosts
参考 https://www.cnblogs.com/52shaidan/p/17198309.html
https://blog.csdn.net/weixin_33860147/article/details/92944588
https://www.cnblogs.com/warmingsun/p/6738840.html
https://www.modb.pro/db/647647
https://codeantenna.com/a/vyfdgucZqP
https://blog.51cto.com/u_16175447/6799619
https://www.cnblogs.com/ezgod/p/15843221.html
PostgreSQL中 drop table指令出现ERROR: cannot drop table userinfo because
other objects depend on it
DROP TABLE hosts CASCADE;
