主页 / dba / 问题

问题[postgresql](dba)

Martin Hope
swe
Asked: 2025-04-25 14:45:34 +0800 CST
  • 7

如果我在 postgresql.conf 中设置random_page_cost = 1.1，它不会被设置，重启后也不会，之后也不会select pg_reload_conf();。
但是，如果我在创建新集群之前将它放入配置文件中，它就会被设置。

如果我通过更新它ALTER SYSTEM SET random_page_cost = 1.1; select pg_reload_conf();，它设置正确。

有人可以指导我如何找出原因吗？

（我已经开始-d 5，希望在启动日志中找到一些有趣的东西，为什么该值被忽略，但我什么也没找到。

提前致谢。

问候，
斯蒂芬

postgresql
Martin Hope
Primordial_Hamster
Asked: 2025-04-19 00:50:12 +0800 CST
  • 8

我可能需要更改 Postgresql 配置中 lc_messages、lc_monetary、lc_numeric 和 lc_time 的区域设置。但是，在执行此操作之前，我需要了解它们具体会影响哪些内容。Postgresql 文档列出了受影响的内容，但仍然留下了很多问题。例如，文档中说upper和lower函数会受到影响。这是所有设置都受影响吗？还是只有部分设置受影响？文档中还说 to_char() 会受到影响。具体是如何影响的？

我想提前知道此更改是否需要更改应用程序代码，那么是否有好的资源可以帮助我生成要测试的功能/场景列表？

postgresql
Martin Hope
Sig
Asked: 2025-04-14 17:41:11 +0800 CST
  • 7

我感觉这是一个相对简单的问题，但半个小时后，我仍然不知道如何解决它。

在 PostgreSQL 查询WHERE子句中，我需要引用一个值而不是直接使用它。该查询将在 Ruby 脚本中执行，我无法直接从我的环境中插入需要获取的值。

我的查询是

CREATE MATERIALIZED VIEW my_mat_view AS
SELECT  ...
...
WHERE occurred_at BETWEEN NOW() - INTERVAL '?? HOURS' AND NOW()

用于计算间隔的小时数在INTERVAL_HOURS环境变量中定义。如上所述，我可以??用插值替换，因为它在单引号内。

我的想法是使用变量（或产生相同结果的东西）将插值移到单引号之外。

我发现最接近解决方案的选项是

DO $$
DECLARE myVar INT;
BEGIN
myVar := #{ENV['INTERVAL_HOURS'};
CREATE MATERIALIZED VIEW my_mat_view AS
SELECT  ...
...
WHERE occurred_at BETWEEN NOW() - INTERVAL 'myVar HOURS' AND NOW()
END $$;

应该可以正确插值，但会产生

Query 1 ERROR at Line 1: : ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

我尝试SELECT用替换PERFORM，但视图未创建（并且未返回任何错误）。

我这里漏掉了什么？我可以采取其他方法吗（例如，WITH子句）？

postgresql
Martin Hope
ConanTheGerbil
Asked: 2025-04-11 02:14:17 +0800 CST
  • 7

配置文件postgresql.conf包含的设置max_connections。

我有许多相关问题...

  1. 在这个价值观的背景下，什么才构成了“联系”？
  2. 最好的计算方法是什么？
  3. 需要增加数据库时可能会出现哪些症状/错误消息max_connections？

我见过几篇关于如何计算当前连接数的帖子......

1)  SELECT count(*) from pg_stat_activity;

2)  SELECT count(distinct(numbackends)) FROM pg_stat_database;

3)  SELECT sum(numbackends) FROM pg_stat_database WHERE datname is not null; 

4)  SELECT count(*) FROM pg_stat_activity WHERE datname is not null;

5)  select sum(numbackends) from pg_stat_database;

它们通常会产生不同的结果，并且需要不同的时间来执行。

哪个查询应该给出最准确的结果，我如何知道是否需要增加max_connections？

postgresql
Martin Hope
Luboš Suk
Asked: 2025-04-09 15:29:29 +0800 CST
  • 5

我正在使用 postgresql 数据库，目前正在试验一些真空/自动真空/完全真空的东西。

据我所知，表中已更新/删除的行仍然占用空间（并且仅被标记为隐藏），直到调用vacuum操作。调用vacuum操作后，这些死元组将被标记为可用空间，这些空间可以被同一个表（或者可能是不同的表？我不确定）重新使用，但空间不会返回给操作系统/文件系统 (OS/FS)，而是仍然由数据库保留，直到执行vacuum full操作。

我的问题来了。我能确定表占用了多少磁盘空间吗？

SELECT pg_size_pretty(pg_relation_size('fluffy_table')) AS data_only,
       pg_size_pretty(pg_total_relation_size('fluffy_table')) AS with_indexes;

这应该是空间，即为表“保留”的空间，执行 VACUUM FULL 后，这个值可能会改变（如果有一些被删除的行）。

但是这里有个选项，如何在执行 VACUUM FULL 之前获取 VACUUM FULL 之后该表的大小？或者用百分比来表示使用情况？所以我可以说这个表有 100MB 大，但实际数据只使用了 60%（60MB），所以我可以再添加 40% 的行，然后它才会再次增长？

使用原生 postgresql（不带扩展）可以实现吗？

我考虑过将存活元组和死亡元组求和，计算平均行大小，然后以此值计算多个存活/死亡元组。但我已经调用了 VACUUM 操作，所以现在没有关于死亡元组的信息了。

postgresql
Martin Hope
machineghost
Asked: 2025-04-09 02:14:35 +0800 CST
  • 5

我正在尝试在一台新的（Linux Mint）电脑上安装 PostgreSQL。一切正常，直到我尝试访问数据库。当我以自己的用户身份访问数据库时，出现了以下（预期的）错误：

$ psql 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "me" does not exist

这很合理，因为 Linux 上有一个单独的postgres用户拥有数据库访问权限。我尝试使用它们：

$ sudo su postgres
[sudo] password for me:
$ psql
Password for user postgres:  psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied

这时我有点困惑：postgres用户应该可以无密码访问所有 PostgreSQL 数据库。此外，我尝试设置pg_hba.conf文件以允许所有用户本地访问：

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

但是，即使在我重新启动 PostgreSQL （使用/etc/init.d/postgresql reload）之后，系统仍然提示我输入用户的数据库密码postgres。

谁能帮助我理解：

  1. postgres当我尝试以用户身份（全新安装）访问数据库时，为什么会提示我输入密码？
  2. 为什么即使将 IPv4/IPv6 本地连接设置为 后仍然会收到提示trust？
  3. 我怎样才能使我的postgres用户像其他 Linux 系统一样（即没有任何数据库密码，但仍然能够访问所有数据库）
postgresql
Martin Hope
Dolphin
Asked: 2025-04-08 18:14:48 +0800 CST
  • 6

我想在 PostgreSQL 选择块中选择多行。然后我尝试使用 row 函数在 PostgreSQL 15 中执行查询，如下所示：

select c.a from ( select row(1 as a, 2 as b) as c)

显示错误ERROR: syntax error at or near "as，我也尝试过：

select (c).a from ( select row(1 , 2 ) as c(a,b))

我是不是漏掉了什么？或者如何获取行值？我发现这个方法很好用：

select * from ( select row(1 , 2 ) ) a

如何得到 1 或 2？我试过：

select a[0] from ( select row(1 , 2 ) ) a

似乎没有起作用。

postgresql
Martin Hope
raphael
Asked: 2025-04-08 02:17:40 +0800 CST
  • 5

我们正在尝试根据最近的地理距离将事件与线路进行匹配

--pseudo code
LATERAL JOIN (
    SELECT line_id, geom 
    FROM lines 
    ORDER by distance(lines.geom, events.geom) 
    LIMIT 1) nearest_line

有时，值得注意的是，会出现平局。我们希望确保如果使用相同的数据集再次运行我们的 ETL 管道，返回的结果会相同。一个想法是将其包含line_id在 中ORDER BY，但这可能会使我们的匹配结果偏向更低的line_id。如果我们的 ETL 必须在相同的数据集上重新运行，有没有办法随机打破平局，从而产生相同的输出？

我正在考虑类似的方法，但要根据和 的random()组合来设置种子。像在 ID 上使用 这样的方法可以吗？event_idline_idmod()

postgresql
Martin Hope
Jukurrpa
Asked: 2025-04-07 23:16:01 +0800 CST
  • 5

在两个仅行数不同的表（约 7.8M vs 约 1.4M）上运行相同的查询，会得到两个不同的计划，这听起来很合理。但是在较小的表上执行速度要慢 4 到 5 倍，我想知道原因。

表格定义如下：

   Column   |           Type           | Collation | Nullable | Default 
------------+--------------------------+-----------+----------+---------
 image_id   | bigint                   |           | not null | 
 h3_cell    | h3index                  |           | not null | 
 created_at | timestamp with time zone |           | not null | 
 location   | geometry(PointZ,4326)    |           | not null | 
Indexes:
    "images_a_pkey" PRIMARY KEY, btree (image_id)
    "images_a_created_at_idx" btree (created_at)
    "images_a_h3_cell_idx" btree (h3_cell)

查询如下

h3_cells AS (
    SELECT UNNEST(h3_linestring_to_cells(:line_string, 13, 1)) AS cell
)
SELECT COUNT(*)
FROM images
JOIN h3_cells hc ON images.h3_cell = hc.cell

该h3_linestring_to_cells()函数返回一个数组，h3index其大小在某些情况下可能高达数万个值。在下面的示例中，它返回的值约为 50,000 个。

在具有 7.8M 行的表中，计划和执行条目如下（为简洁起见，删除了数组值）：

Aggregate  (cost=347404.47..347404.48 rows=1 width=8) (actual time=74.311..74.312 rows=1 loops=1)
  Buffers: shared hit=154681 read=328
  I/O Timings: shared read=1.362
  ->  Nested Loop  (cost=0.43..346724.23 rows=272093 width=0) (actual time=0.051..74.246 rows=833 loops=1)
        Buffers: shared hit=154681 read=328
        I/O Timings: shared read=1.362
        ->  ProjectSet  (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..4.113 rows=51377 loops=1)
              ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
        ->  Index Only Scan using images_a_h3_cell_idx on images_a  (cost=0.43..6.68 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=51377)
              Index Cond: (h3_cell = (unnest('{...}'::h3index[])))
              Heap Fetches: 354
              Buffers: shared hit=154681 read=328
              I/O Timings: shared read=1.362
Planning Time: 139.421 ms
Execution Time: 74.345 ms

而在较小的 1.4M 行表上，计划和执行如下：

Aggregate  (cost=105040.78..105040.79 rows=1 width=8) (actual time=327.586..327.587 rows=1 loops=1)
  Buffers: shared hit=148358 read=6315 written=41
  I/O Timings: shared read=26.521 write=0.327
  ->  Merge Join  (cost=4791.05..104802.14 rows=95455 width=0) (actual time=321.174..327.575 rows=118 loops=1)
        Merge Cond: (ptilmi.h3_cell = (unnest('{...}'::h3index[])))
        Buffers: shared hit=148358 read=6315 written=41
        I/O Timings: shared read=26.521 write=0.327
        ->  Index Only Scan using images_b_h3_cell_idx on images_b ptilmi  (cost=0.43..95041.10 rows=1415438 width=8) (actual time=0.026..245.897 rows=964987 loops=1)
              Heap Fetches: 469832
              Buffers: shared hit=148358 read=6315 written=41
              I/O Timings: shared read=26.521 write=0.327
        ->  Sort  (cost=4790.62..4919.07 rows=51377 width=8) (actual time=11.181..13.551 rows=51390 loops=1)
              Sort Key: (unnest('{...}'::h3index[]))
              Sort Method: quicksort  Memory: 1537kB
              ->  ProjectSet  (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..3.716 rows=51377 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
Planning Time: 146.617 ms
Execution Time: 327.626 ms

对于较小的源数组（例如大小为 25,000），较小表上的计划更改为第一个（嵌套循环），并且其执行时间变得更符合预期（比较大的表更快）。

我不明白是什么促使计划改变为效率更低的计划。

请注意，我使用的是 CTE+JOIN 而不是 eg WHERE h3_cell = ANY(h3_linestring_to_cells(:line_string, 13, 1))，因为生成的数组通常很大，而且我发现在这种情况下前者通常更高效。有趣的是，对于包含 50,000 个条目的数组，这种= ANY()方法在较小的表上速度更快，而对于包含 25,000 个条目的数组，这种方法速度较慢。

postgresql