最近在浏览 guc_table.c 时注意到 PostgreSQL 有很多与超时相关的参数可供用户设置，在这些参数中，我想介绍三个我认为最常用的参数: statement_timeout: Sets the maximum allowed duration of any statement. idle_in_transaction_session_timeout: Sets the maximum allowed idle time between queries, when in a transaction. idle_session_timeout: Sets the maximum allowed idle time between queries, when not in a transaction. statement_timeout PostgreSQL 中长事务往往会带来一些问题，比如 table bloat（由于旧版本记录不能及时回收）、占用资源（锁、内存）等，因此有些实例会设置一个合理的 statement_timeout 来自动杀死运行时间过长的查询。 postgres=# set session statement_timeout = '10s'; SET postgres=# select pg_sleep(1000); ERROR: canceling statement due to statement timeout postgres=# 但这个参数对于 idle in transaction 的 session 不起作用，比如:...

在一个典型的 PostgreSQL master-slave 部署中，如何能快速识别谁是主谁是备？本文介绍几种常见的方法。 这些方法可以粗略归为两类: 通过客户端连接到实例上 能够登录到实例运行的主机上 先看第一类: 1.1 pg_is_in_recovery() pg_is_in_recovery 是一个 PostgreSQL 内置的 UDF，用于确定当前数据库实例是否处于恢复模式。在 PostgreSQL 中，恢复模式是指数据库实例正在进行故障恢复或流复制时的工作状态。 既然能连到实例去执行 SQL，证明肯定不处于故障恢复状态，因此，如果查询返回 true，则表示数据库实例处于恢复模式，如果是 false，则表示数据库实例是主服务器。 postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) 当然，如果备机压根没有开启 hot_standby，通过连接时的报错即可知道它为备机: ➜ psql -p 5433 postgres psql: error: connection to server on socket "/tmp/.s.PGSQL.5433" failed: FATAL: the database system is not accepting connections DETAIL: Hot standby mode is disabled. 1.2 in_hot_standby in_hot_standby 是 pg14 引入的一个 INTERNAL 参数（不能被用户更改，只能由进程内部逻辑进行设置）。当实例处于 hot_standby 时该参数查询为 on，当实例被 promote 后，参数值为 off。...

PostgreSQL 实例在启动的时候会创建一个 postmaster.pid 文件，通过查看该文件的状态可以知道实例启动的时间，另外 pg_stat_activity 记录了辅助进程的状态，因此我们可以通过 select pg_stat_file('postmaster.pid') AS start_time;、 select MIN(backend_start) as start_time from pg_stat_activity; 或者 select pg_postmaster_start_time(); 来获取实例启动的时间（或者 ），但如何知道实例的创建时间呢？ PostgreSQL 创建实例（initdb）的时候会写一个 global/pg_control 文件，这其中有一个字段 Database system identifier，在实例 bootstrap 的时候初始化该字段，且在实例之后的生命周期中不再变更。 void BootStrapXLOG(void) { ... gettimeofday(&tv, NULL); sysidentifier = ((uint64) tv.tv_sec) << 32; sysidentifier |= ((uint64) tv.tv_usec) << 12; sysidentifier |= getpid() & 0xFFF; ... /* Now create pg_control */ InitControlFile(sysidentifier); ... } 因此我们可以读取该字段，解析其高 32 位即可获取实例创建的时间： ➜ postgres_data pg_controldata . | head pg_control version number: 1300 Catalog version number: 202308241 Database system identifier: 7271454612807718361 Database cluster state: in production pg_control last modified: Mon Oct 2 12:56:26 2023 Latest checkpoint location: 0/2C0163A0 Latest checkpoint's REDO location: 0/2C016368 Latest checkpoint's REDO WAL file: 00000001000000000000002C Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 postgres=# select to_hex(7271454612807718361); to_hex ------------------ 64e96571ccc795d9 (1 row) postgres=# select '0x64e96571'::int; int4 ------------ 1693017457 (1 row) postgres=# select to_timestamp(1693017457) as cluster_init; to_timestamp ------------------------ 2023-08-26 10:37:37+08 (1 row) 或者用一条语句获取实例创建时间:...

国庆节前一个生产环境的备机 A2 在进行 pg_basebackup 时未加 --write-recovery-conf 且未创建 recovery.conf 文件后，手动将实例进行了启动（该环境本身由 patroni 控制 HA，但 patroni 一直未能将环境恢复，因此 DBA 进行了手动操作），导致 A2 和它原来的主 A1 以同样的 timeline 对外提供服务。这种情况下，我们还能将 A1 和 A2 恢复成主备关系吗？ 首先由于 A1 和 A2 处于同一个 timeline，pg_rewind 会直接退出。一个同事提出：postgres 不是有 PITR 特性吗，能不能用 PITR 把 A2 恢复到上一个 timeline，然后再用 pg_rewind 将 A1 和 A2 恢复为主备关系？ PITR 原理 PITR（Point-in-Time Recovery）是 PostgreSQL 数据库的一项重要功能，它允许你在数据库发生故障或数据丢失时恢复到特定的时间点。PITR 的工作原理是通过使用 WAL 日志来记录数据库的所有变更操作，从而实现对数据库状态的恢复。PITR 的第一步是创建数据库的基本备份，通常使用 PostgreSQL 的 pg_basebackup 工具来完成。这个基本备份是数据库的初始状态，用于在恢复过程中提供一个起点。 图片来源: http://www.interdb.jp/pg/pgsql10.html 如上图所示，PITR 需要以基本备份记录的 REDO point 为起点，回放之后的 WAL 日志。...

PostgreSQL 是一个关系型数据库，无论通过 libpq 还是 jdbc 去进行查询，其返回的数据格式都以行（Row/Record）为单位。不过现在出现了一种新的可能，2023/09/13，Sutou Kouhei 发布了 Apache Arrow Flight SQL adapter for PostgreSQL 0.1.0 版本，这意味着今后我们可以用 arrow flight 来访问保存在 PostgreSQL 中的数据，而且返回的是 Arrow 定义的列存格式！ 编译安装 git clone https://github.com/apache/arrow-flight-sql-postgresql.git cd arrow-flight-sql-postgresql mkdir build && cd build meson setup -Dexample=true .. ninja sudo meson install 上面操作结束后会把生成的 arrow_flight_sql.so 拷贝到 pg_config --libdir 指定的目录，然后修改 postgresql.conf 加上下面的语句并重启实例。 shared_preload_libraries = 'arrow_flight_sql' 功能测试 该项目提供了几个客户端程序样例，上面的编译选项 -Dexample=true 把样例程序也编译了，核心代码逻辑如下，这里执行的 SQL 语句被我替换成了带 join 的查询: // Start query arrow::Status run() { arrow::flight::FlightCallOptions call_options; ARROW_ASSIGN_OR_RAISE(auto sql_client, connect(call_options)); ARROW_ASSIGN_OR_RAISE( auto info, sql_client->Execute(call_options, "select * from t1, t2;")); for (const auto& endpoint : info->endpoints()) { ARROW_ASSIGN_OR_RAISE(auto reader, sql_client->DoGet(call_options, endpoint....

在 PostGIS 扩展的加持下，PostgreSQL 成为了一个强大的时空数据库。本文介绍一个用 PostGIS 生成等高线的应用示例。 PostGIS 发展历程 2001 年 5 月，Refractions Research 发布了 PostGIS 0.1。最初的 PostGIS 版本具备对象、索引和一些函数的功能，适合于存储和检索空间数据，但是缺乏分析能力。随着函数数量的增加，对一个组织来规范接口的需求变得逐渐明朗，开放地理空间联盟（Open Geospatial Consortium）应运而生，并提出了 “Simple Features for SQL”（SFSQL）规范。 接下来的几年，PostGIS 的函数数量不断增加，但其功能仍然有限。其中一些有趣的函数（如 ST_Intersects()、ST_Buffer()、ST_Union()）很难编写，从头开始实现它们需要耗费非常多的时间。幸运的是出现了“Geometry Engine, Open Source” (GEOS) 这个项目。GEOS 提供了实现 SFSQL 规范所需的算法。PostGIS 0.8 版本通过链接 GEOS 库完全支持了 SFSQL。 随着 PostGIS 数据量的增长，出现了另一个问题: 用于存储几何数据的表示形式比较低效。对于点和短线等小对象，元数据开销高达300%。为了提高性能，PostGIS 通过缩小元数据头部和所需维度，降低了开销。在 PostGIS 1.0 中，这种更快速、更轻量的表示形式成为默认选项。 另外，PostGIS 还依赖: Proj: 是一个用于地理空间坐标转换的库。它提供各种投影方法和坐标系统的定义，用于在不同的地理空间参考框架之间进行转换。PostGIS 使用 Proj 库来支持空间坐标的转换和投影。 GDAL: Geospatial Data Abstraction Library 是一个用于读取和写入地理空间数据格式的库，包括栅格数据和矢量数据。PostGIS 使用 GDAL 库来支持与各种地理空间数据格式的交互，如 GeoTIFF、Shapefile 等。 JSON-C: 是一个用于处理 JSON 数据的 C 语言库。它在 PostGIS 中用于处理和解析 GeoJSON 空间数据。 … 准备数据 在对 PostGIS 简单了解之后，我们准备下用于生成等高线的数据，理想情况下如果有一个区域的坐标海拔数据当然再好不过了，但是我没有找到这样的数据集。不过幸运的是，我找到了一个美国 1987~2022 年 National Lightning Detection Network 公布的闪电数据信息，给的是 0....

PostgreSQL 中的数据膨胀（bloat）是指数据表或者索引中无用数据过多占用空间的情况。PostgreSQL 通过元组中的 xmin/xmax 以及可见性判断逻辑来实现 MVCC，当更新或删除一条元组的时候，会更新它的 xmax 来标记删除而非真正进行物理删除，因此如果大量死元组未及时清理，会出现数据膨胀，占用额外存储空间且影响查询性能。本文我们讨论数据膨胀的主要原因以及如何检查并解决的方法。 膨胀原因 PostgreSQL 中的 autovacuum launcher 在满足条件时会启动 autovacuum worker 去清理垃圾数据，但当出现以下情况时，依旧会出现膨胀的现象: 设置了 autovauum_enabled = false，防止 autovacuum 进程清理该表（但如果表 age 过大，autovacuum 依然会处理该表以防止 transaction ID wraparound 问题出现） 长事务，如果一个事务长时间未结束，会阻止 vacuum 进程清理该事务 id 之后修改或删除的元组 更新/删除频繁的业务负载 autovacuum 参数配置不合理 检查是否存在膨胀 检查数据膨胀的方法有很多，常用于监控或异常时手动检查，下面列举几种检查的方法。 pg_stat_user_tables pg_stat_user_tables 提供了用户表各种运行状态信息，其中 n_live_tup 和 n_dead_tup 表示一张表中活元组和死元组近似数值，以下查询可以检测数据膨胀情况: SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM pg_stat_user_tables ORDER BY 2, 3 DESC; 上述查询还会输出一些跟 vacuum 相关的字段以检测 vacuum 在该表上运行的情况。pg_stat_user_tables 还可以查看 seq_scan 和 idx_scan 的统计信息以帮助用户更好地优化索引的创建。...