标签 Postgres 下的文章

数据库管理员（DBA）的职责是什么？

在依赖 Postgres 作为主要数据库管理系统的现代 IT 组织中，Postgres DevOps DBA 发挥着关键作用。Postgres DevOps DBA 的角色涉及许多职责、技能和任务。其中一些包括：管理数据库设计和架构、基础设施管理、确保高可用性、安全性以及执行日常维护任务（调整、备份和恢复以及监控）。

本文总结了当今企业环境中 Postgres DevOps DBA 的常见职责和技能。

数据库设计和架构

Postgres DevOps DBA 的两个主要职责是数据库设计和架构。该角色必须对应用的数据存储要求和涉及的业务逻辑有更深入的了解。这些知识包括设计和创建数据库模式和表。它还意味着配置索引和其他数据库对象以优化查询性能，并选择使用正确的 Postgres 版本。该角色必须确保数据库的设计具有可扩展性和可维护性，同时考虑到未来的增长和数据保留需求。

性能调优

另一个关键的职责是性能调优。Postgres DevOps DBA 必须能够通过监控数据库性能指标和分析查询性能来识别和解决性能问题。该角色还必须对数据库有深入的了解，并能够对其进行配置以获得最佳性能，包括优化查询和索引、调整内存设置以及识别和解决性能瓶颈。

备份与恢复

备份和恢复也是职责的关键。DBA 必须对备份和恢复解决方案有深入的了解，并且必须设计和实施备份策略，以确保在数据丢失的情况下始终可以恢复数据。他们还必须验证恢复过程并实施高可用性和灾难恢复解决方案，以最大限度地减少停机时间和数据丢失。

安全

安全是另一个重要的职责。DBA 通过实施访问控制、加密和其他安全措施来保护数据，从而确保数据库安全。他们还必须了解最新的安全趋势和最佳实践，并加以实施以防范潜在威胁。

基础设施管理

基础设施管理也是一项重要职责。这些 DBA 必须管理硬件、网络和存储基础设施，并提供基础设施以支持 Postgres。他们还必须针对性能和可用性配置基础架构，并根据需要扩展基础架构以适应数据增长。

自动化和脚本

该角色必须能够使用 Ansible、Terraform 和 Kubernetes 等工具自动执行重复性任务，例如备份、监控和修补。他们还必须熟悉自动化最佳实践，以确保高效且有效地自动化任务。自动化减少了人为错误的可能性，提高了效率，并允许 DBA 专注于更复杂的任务。

监控和配置警报

监控数据库和基础设施并设置警报以通知他们问题非常重要。该角色还必须采取主动措施来防止停机和数据丢失，使用 Nagios、Zabbix 和 Prometheus 等监控工具来检测潜在问题。

合作

除了这些技术职责外，PostgreSQL DevOps DBA 还必须与其他 IT 团队（例如开发人员、运维人员和安全人员）协作，以将数据库集成到更大的 IT 生态系统中。DBA 还必须记录他们的工作，并及时了解 Postgres 和 DevOps 的最新趋势和最佳实践。这涉及与利益相关者合作以收集需求、确定优先级并使数据库与组织的更广泛目标保持一致。

总结

总之，Postgres DevOps DBA 在依赖 Postgres 作为主要数据库管理系统的现代 IT 组织中发挥着关键作用。你当前的技能和期望如何匹配此列表？作为现代数据库环境中的 DBA，你是否走在正确的道路上？

via: https://opensource.com/article/23/3/postgres-devops-dba

作者：Doug Ortiz 选题：lkxed 译者：geekpi 校对：校对者ID

本文由 LCTT 原创编译，Linux中国 荣誉推出

在处理庞大的数据库时，请尝试这些方便的解决方案，以解决常见的问题。

关系型数据库 PostgreSQL（也被称为 Postgres）已经越来越流行，全球各地的企业和公共部门都在使用它。随着这种广泛的采用，数据库已经变得比以前更大了。在 Crunchy Data，我们经常与 20TB 以上的数据库打交道，而且我们现有的数据库还在继续增长。我的同事 David Christensen 和我收集了一些关于管理拥有巨大表的数据库的技巧。

大表

生产数据库通常由许多具有不同数据、大小和模式的表组成。常见的情况是，最终有一个巨大的、无序的数据库表，远远大于你数据库中的任何其他表。这个表经常存储活动日志或有时间戳的事件，而且对你的应用或用户来说是必要的。

真正的大表会因为很多原因造成挑战，但一个常见的原因是锁。对表的定期维护往往需要锁，但对大表的锁可能会使你的应用瘫痪，或导致堵塞和许多令人头痛的问题。我有一些做基本维护的技巧，比如添加列或索引，同时避免长期运行的锁。

添加索引的问题：在创建索引的过程中锁住表。如果你有一个庞大的表，这可能需要几个小时。

CREATE INDEX ON customers (last_name)

方案：使用 CREATE INDEX CONCURRENTLY 功能。这种方法将索引创建分成两部分，一部分是短暂的锁定，以创建索引，立即开始跟踪变化，但尽量减少应用阻塞，然后是完全建立该索引，之后查询可以开始使用它。

CREATE INDEX CONCURRENTLY ON customers (last_name)

添加列

在数据库的使用过程中，添加列是一个常见的请求，但是对于一个巨大的表来说，这可能是很棘手的，同样是由于锁的问题。

问题：当你添加一个新的默认值为一个函数的列时，Postgres 需要重写表。对于大表，这可能需要几个小时。

方案：将操作拆分为多条基本语句，总效果一致，但控制锁的时间。

添加列：

ALTER TABLE all_my_exes ADD COLUMN location text

添加默认值：

ALTER TABLE all_my_exes ALTER COLUMN location SET DEFAULT texas()

使用 UPDATE 来添加默认值：

UPDATE all_my_exes SET location = DEFAULT

添加约束条件

问题: 你想添加一个用于数据验证的检查约束。但是如果你使用直接的方法来添加约束，它将锁定表，同时验证表中的所有现有数据。另外，如果在验证的任何时候出现错误，它将回滚。

ALTER TABLE favorite_bands ADD CONSTRAINT name_check CHECK (name = 'Led Zeppelin')

方案：告诉 Postgres 这个约束，但不要验证它。在第二步中进行验证。这将在第一步中进行短暂的锁定，确保所有新的/修改过的行都符合约束条件，然后在另一步骤中进行验证，以确认所有现有的数据都通过约束条件。

告诉 Postgres 这个约束，但不要强制执行它：

ALTER TABLE favorite_bands ADD CONSTRAINT name_check CHECK (name = 'Led Zeppelin') NOT VALID

然后在创建后验证它：

ALTER TABLE favorite_bands VALIDATE CONSTRAINT name_check

想了解更多？

David Christensen 和我将在 3 月 9 号到 10 到在加州帕萨迪纳参加 SCaLE 的 Postgres Days。很多来自 Postgres 社区的优秀人士也会在那里。加入我们吧！

via: https://opensource.com/article/23/2/manage-large-postgres-databases

作者：Elizabeth Garrett Christensen 选题：lkxed 译者：geekpi 校对：wxy

本文由 LCTT 原创编译，Linux中国 荣誉推出

在 Citus 公司，为让事情做的更好，我们与客户一起在数据建模、优化查询、和增加 索引上花费了许多时间。我的目标是为客户的需求提供更好的服务，从而创造成功。我们所做的其中一部分工作是持续为你的 Citus 集群保持良好的优化和 高性能；另外一部分是帮你了解关于 Postgres 和 Citus 你所需要知道的一切。毕竟，一个健康和高性能的数据库意味着 app 执行的更快，并且谁不愿意这样呢？ 今天，我们简化一些内容，与客户分享一些关于 Postgres 索引的信息。

Postgres 有几种索引类型， 并且每个新版本都似乎增加一些新的索引类型。每个索引类型都是有用的，但是具体使用哪种类型取决于（1）数据类型，有时是（2）表中的底层数据和（3）执行的查找类型。接下来的内容我们将介绍在 Postgres 中你可以使用的索引类型，以及你何时该使用何种索引类型。在开始之前，这里有一个我们将带你亲历的索引类型列表：

  • B-Tree
  • 倒排索引 Generalized Inverted Index (GIN)
  • 倒排搜索树 Generalized Inverted Seach Tree (GiST)
  • 空间分区的 Space partitioned GiST (SP-GiST)
  • 块范围索引 Block Range Index (BRIN)
  • Hash

现在开始介绍索引。

在 Postgres 中，B-Tree 索引是你使用的最普遍的索引

如果你有一个计算机科学的学位，那么 B-Tree 索引可能是你学会的第一个索引。B-tree 索引 会创建一个始终保持自身平衡的一棵树。当它根据索引去查找某个东西时，它会遍历这棵树去找到键，然后返回你要查找的数据。使用索引是大大快于顺序扫描的，因为相对于顺序扫描成千上万的记录，它可以仅需要读几个 页 (当你仅返回几个记录时)。

如果你运行一个标准的 CREATE INDEX 语句，它将为你创建一个 B-tree 索引。 B-tree 索引在大多数的数据类型上是很有价值的，比如文本、数字和时间戳。如果你刚开始在你的数据库中使用索引，并且不在你的数据库上使用太多的 Postgres 的高级特性，使用标准的 B-Tree 索引可能是你最好的选择。

GIN 索引，用于多值列

倒排索引 Generalized Inverted Index ，一般称为 GIN，大多适用于当单个列中包含多个值的数据类型。

据 Postgres 文档：

“GIN 设计用于处理被索引的条目是复合值的情况，并且由索引处理的查询需要搜索在复合条目中出现的值。例如，这个条目可能是文档，查询可以搜索文档中包含的指定字符。”

包含在这个范围内的最常见的数据类型有：

关于 GIN 索引中最让人满意的一件事是，它们能够理解存储在复合值中的数据。但是，因为一个 GIN 索引需要有每个被添加的单独类型的数据结构的特定知识，因此，GIN 索引并不是支持所有的数据类型。

GiST 索引， 用于有重叠值的行

倒排搜索树 Generalized Inverted Seach Tree （GiST）索引多适用于当你的数据与同一列的其它行数据重叠时。GiST 索引最好的用处是：如果你声明一个几何数据类型，并且你希望知道两个多边型是否包含一些点时。在一种情况中一个特定的点可能被包含在一个盒子中，而与此同时，其它的点仅存在于一个多边形中。使用 GiST 索引的常见数据类型有：

  • 几何类型
  • 需要进行全文搜索的文本类型

GiST 索引在大小上有很多的固定限制，否则，GiST 索引可能会变的特别大。作为其代价，GiST 索引是有损的（不精确的）。

据官方文档：

“GiST 索引是有损的，这意味着索引可能产生虚假匹配，所以需要去检查真实的表行去消除虚假匹配。 (当需要时 PostgreSQL 会自动执行这个动作)”

这并不意味着你会得到一个错误结果，它只是说明了在 Postgres 给你返回数据之前，会做了一个很小的额外工作来过滤这些虚假结果。

特别提示：同一个数据类型上 GIN 和 GiST 索引往往都可以使用。通常一个有很好的性能表现，但会占用很大的磁盘空间，反之亦然。说到 GIN 与 GiST 的比较，并没有某个完美的方案可以适用所有情况，但是，以上规则应用于大部分常见情况。

SP-GiST 索引，用于更大的数据

空间分区 GiST （SP-GiST）索引采用来自 Purdue 研究的空间分区树。 SP-GiST 索引经常用于当你的数据有一个天然的聚集因素，并且不是一个平衡树的时候。 电话号码是一个非常好的例子 (至少 US 的电话号码是)。 它们有如下的格式：

  • 3 位数字的区域号
  • 3 位数字的前缀号 (与以前的电话交换机有关)
  • 4 位的线路号

这意味着第一组前三位处有一个天然的聚集因素，接着是第二组三位，然后的数字才是一个均匀的分布。但是，在电话号码的一些区域号中，存在一个比其它区域号更高的饱合状态。结果可能导致树非常的不平衡。因为前面有一个天然的聚集因素，并且数据不对等分布，像电话号码一样的数据可能会是 SP-GiST 的一个很好的案例。

BRIN 索引， 用于更大的数据

块范围索引（BRIN）专注于一些类似 SP-GiST 的情形，它们最好用在当数据有一些自然排序，并且往往数据量很大时。如果有一个以时间为序的 10 亿条的记录，BRIN 也许就能派上用场。如果你正在查询一组很大的有自然分组的数据，如有几个邮编的数据，BRIN 能帮你确保相近的邮编存储在磁盘上相近的地方。

当你有一个非常大的比如以日期或邮编排序的数据库， BRIN 索引可以让你非常快的跳过或排除一些不需要的数据。此外，与整体数据量大小相比，BRIN 索引相对较小，因此，当你有一个大的数据集时，BRIN 索引就可以表现出较好的性能。

Hash 索引， 总算不怕崩溃了

Hash 索引在 Postgres 中已经存在多年了，但是，在 Postgres 10 发布之前，对它们的使用一直有个巨大的警告，它不是 WAL-logged 的。这意味着如果你的服务器崩溃，并且你无法使用如 wal-g 故障转移到备机或从存档中恢复，那么你将丢失那个索引，直到你重建它。 随着 Postgres 10 发布，它们现在是 WAL-logged 的，因此，你可以再次考虑使用它们 ，但是，真正的问题是，你应该这样做吗?

Hash 索引有时会提供比 B-Tree 索引更快的查找，并且创建也很快。最大的问题是它们被限制仅用于“相等”的比较操作，因此你只能用于精确匹配的查找。这使得 hash 索引的灵活性远不及通常使用的 B-Tree 索引，并且，你不能把它看成是一种替代品，而是一种用于特殊情况的索引。

你该使用哪个？

我们刚才介绍了很多，如果你有点被吓到，也很正常。 如果在你知道这些之前， CREATE INDEX 将始终为你创建使用 B-Tree 的索引，并且有一个好消息是，对于大多数的数据库， Postgres 的性能都很好或非常好。 :) 如果你考虑使用更多的 Postgres 特性，下面是一个当你使用其它 Postgres 索引类型的备忘清单：

  • B-Tree - 适用于大多数的数据类型和查询
  • GIN - 适用于 JSONB/hstore/arrays
  • GiST - 适用于全文搜索和几何数据类型
  • SP-GiST - 适用于有天然的聚集因素但是分布不均匀的大数据集
  • BRIN - 适用于有顺序排列的真正的大数据集
  • Hash - 适用于相等操作，而且，通常情况下 B-Tree 索引仍然是你所需要的。

如果你有关于这篇文章的任何问题或反馈，欢迎加入我们的 slack channel。

via: https://www.citusdata.com/blog/2017/10/17/tour-of-postgres-index-types/

作者：Craig Kerstiens 译者：qhwdw 校对：wxy

本文由 LCTT 原创编译，Linux中国 荣誉推出