主页 / dba / 问题

问题[postgresql-12](dba)

Martin Hope
padjee
Asked: 2022-09-14 05:56:56 +0800 CST
  • 0

所以我继承了这个数据库。一开始，pg_wal.

然后，几周后，我计算了一下SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';，结果是：8182个文件。

查看目录上文件的时间戳pg_wal，我看到正在创建一些新文件，但文件数量保持不变。我怀疑发生了轮换。

然后我观察pg_stat_archiver：archived_count = **2**; failed_count = 0

大约 3 小时后，我再次数数SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';结果：8183。显然会创建一个新文件。

然后我做pg_stat_archiver：archived_count = **3**; failed_count = 0

archived_count last_archived_wal           last_archived_time   failed_count    last_failed_wal  last_failed_time 
3              000000010000009B00000083    22-09-13 13:14:20.   0
stats_reset                          
2022-09-13 08:14:19

这个数据库很低，一点也不忙。有时没有人访问它。但是有逻辑复制，每天一次将大约 300 条记录同步到另一台服务器。

这是我的设置：

max_wal_size : 1024 Mb
min_wal_size : 2048 Mb
wal_keep_segment : 0
wal_rotation : ON
wal_level : logical
wal_compression : ON
archive_on : ON
archive_mode : test ! -f /Archive/%f && cp %p /Archive/%f
archive_timeout : 900

问题 ：

  1. 为什么第二个计数pg_wal增加？我预计它会减少，因为存档数量会增加。
  2. 如果wal_keep_segment = 0，为什么 WAL 的数量pg_wal从不为 0？在文档中，它说：

如果 wal_keep_segments 为零（默认值），系统不会为备用目的保留任何额外的段，

  1. 为什么在低谷期间 WAL 的最小数量不= min_wal_size+ wal_keep_segment？

请赐教。

太感谢了

postgresql postgresql-12
Martin Hope
padjee
Asked: 2022-09-09 20:55:35 +0800 CST
  • 0

发出后pg_basebackup -D /var/lib/postgresql/basebackup -l "backup main $(hostname) on $(date)" -P，我看到目录中有 2 个“.backup”文件pg_wal。看到它这里。这就是pg_archive目录的样子这里。问题：

  1. 为什么有2个文件？
  2. 在第一个“.backup”文件（**96.00000028 之上的所有内容）之前或第二个文件（**98.00000028 之上的所有内容）之前，我可以安全删除哪些 WAL 文件？
  3. 还是我必须删除 wal 文件（00028 以上的所有文件）pg_archive？

谢谢

postgresql postgresql-12
Martin Hope
awestover89
Asked: 2022-07-06 04:47:26 +0800 CST
  • 0

我们有一个 PostgreSQL 数据库，它的大小最近显着增长，在过去的几个月里从大约 340GB 增加到 571GB，我们没有跟踪在那段时间用户行为的任何重大变化。我们的主要 DBA 提出了一些建议，他的主要建议是导出整个数据库，然后重新导入它，根据他在从我们的主数据库克隆的第二台服务器上的测试，这需要大约 3 小时的停机时间，并获得大小降到只有 300GB。

我关心的两个主要领域是找出这种显着增长的来源（使用 du -h 我至少可以看到它在 /data 目录中，表空间或 pg_wal 没有显着增长），并了解如何导入和导出该数据库可以为我们带来将近 300GB 的空间恢复，而不会实际丢失任何生产数据。

postgresql postgresql-12
Martin Hope
Rich_F
Asked: 2022-02-21 07:11:45 +0800 CST
  • 1

我正在Mac使用Monterey. 当我第一次开始在这台机器上工作时，它的安装Mac M1有点问题。我搬到了似乎只是底层证券的前沿，所以我认为没有太大变化。homebrewpostgresGUI postgres.apppostgres

无论如何，数据库 ( Postgres 12) 现在正在运行，我发现尝试创建新数据库时出现问题。第三方客户端以及psql.

rich=# create database sresearch
rich-# \l
                                   List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rich         | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sresearch    | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
 weather      | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

rich-# \c sresearch
FATAL:  no such database: sresearch

由于某种原因无法创建数据库，即使它报告已经这样做了。我不知道为什么会这样，但我想解决它。

我想我可以进行备份并尝试通过删除两个版本来解决此问题，然后返回以homebrew重新开始Postgres 12。我不确定如何找出这两个略有不同的版本之间的区别，它们本质上是相同的。

我怎样才能解决这个问题？显然我需要能够制作新的数据库。

更新：我刚刚重新安装了homebrew版本并尝试导入我的替代postgres.app导出。它出于某种原因挂断了，\connect <dbname>但这很容易手动解决。在尝试创建新数据库时，此安装也给了我同样的错误。因此，即使从导出中，两个版本都显示相同。

我不知道该尝试什么。

rich=# \c sres
FATAL:  no such database: sres             # Indeed there is though
Previous connection kept
rich=#

更新：更多信息 ==>

rich=# \l
                                List of databases
     Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
--------------+-------+----------+-------------+-------------+-------------------
 postgres     | rich  | UTF8     | C           | C           |
 rich         | rich  | UTF8     | C           | C           |
 sres         | rich  | UTF8     | C           | C           |
 sresearch    | rich  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
 template1    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
(6 rows)

rich=# \c sres;
FATAL:  no such database: sres
Previous connection kept
rich=# \s sresearch;
Wrote history to file "sresearch".
rich=#

以下是来自的反馈Sequel：

来自 Ruby 的命令

这是Ruby的结果

在此处输入图像描述

更新。甚至更多。==>

rich=# CREATE DATABASE alpha;
CREATE DATABASE
rich=# \c alpha;
FATAL:  no such database: alpha
Previous connection kept
rich=# \l
                                List of databases
     Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
--------------+-------+----------+-------------+-------------+-------------------
 alpha        | rich  | UTF8     | C           | C           |
 postgres     | rich  | UTF8     | C           | C           |
 rich         | rich  | UTF8     | C           | C           |
 template0    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
 template1    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
(5 rows)

rich=# DROP DATABASE alpha;
DROP DATABASE
rich=# \l
                                List of databases
     Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
--------------+-------+----------+-------------+-------------+-------------------
 postgres     | rich  | UTF8     | C           | C           |
 rich         | rich  | UTF8     | C           | C           |
 template0    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
 template1    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
(4 rows)

rich=# CREATE DATABASE alpha;
CREATE DATABASE
rich=# \l
                                List of databases
     Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
--------------+-------+----------+-------------+-------------+-------------------
 alpha        | rich  | UTF8     | C           | C           |
 postgres     | rich  | UTF8     | C           | C           |
 rich         | rich  | UTF8     | C           | C           |
 template0    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
 template1    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
(5 rows)

rich=# \c alpha;
FATAL:  no such database: alpha
Previous connection kept
rich=#
postgresql-12 macos
Martin Hope
Tom
Asked: 2021-11-24 18:07:47 +0800 CST
  • 1

阅读 pg_cron文档，我看到这些示例仅在调度任务时执行命令。

在 StackOverflow的一篇文章中，我看到一个用户在调度任务时尝试运行多个 VACUUM，但出现错误。

有没有办法使用 pg_cron 在多个表上按顺序运行 VACUUM？在总共 155 个表中，大约有 112 个必须清空，因此为每个表安排一个任务不是很实用。

或者例如，从表中删除旧记录并在进程结束时立即在选定的表上运行 cron？

postgresql postgresql-12
Martin Hope
Beekay
Asked: 2021-10-01 13:31:20 +0800 CST
  • 0

一个新手在这里寻求你的帮助。我做了 WAL LEVEL = replica Archiving Mode = ON 的归档设置，提到了备份的位置：/home/beekay/archive 用于 archive_command 的在线备份。

不知道为什么我现在收到此错误（线程中的屏幕截图）。请帮忙！

在此处输入图像描述

postgresql-12
Martin Hope
Joe
Asked: 2021-09-23 02:29:38 +0800 CST
  • 0

我目前正在实现 2 层安全性，这意味着我们在应用程序代码和数据库中都有 orm 查询过滤。在数据库中，我使用的是 Postgre 行级安全性。

在理想情况下，应用程序代码中构建的所有过滤查询都是完美的，并且行级安全策略永远不会删除条目。但是，如果它确实过滤掉了一个结果，这意味着我们在过滤应用程序代码时有一个错误，必须修复。我希望能够记录下来。

是否可以配置 Postgresql 以通知我/记录这些事件？

postgresql postgresql-12
Martin Hope
pinoyyid
Asked: 2021-05-31 08:11:21 +0800 CST
  • 2

我有一个应用程序完全依赖PostgreSQL OID 进行操作。它目前在 PostgreSQL 9.6.1，但我希望升级到 13。是否有推荐/标准机制（使用触发器？）在较新版本中重新创建 OID 功能？

作为背景，应用程序使用 OID 的原因是因为应用程序对数据库架构一无所知，并且旨在用于任何随机数据库架构，使用逆向工程进行导航。出于这个原因，“只添加一个主键”的建议对我不起作用。目前，该应用程序针对 6 个独立的数据库运行，每个数据库都有自己的架构。

附加背景

实际上，每个应用程序都了解其数据库模式，例如工资单应用程序了解员工表，该表的主键可能为emp_id.

我的应用程序不同。

我的应用程序会自动为它指向的任何随机 PostgreSQL 数据库生成一个 CRUD 应用程序。因此，它不对主键、表名、列名等做任何假设。它所做的唯一假设是它所指向的数据库对于每个表中的每一行都有一个唯一的 OID。就目前的情况而言，我将这个应用程序用于五个独立且不同的数据库，并且自 2007 年左右以来一直这样做。

所以我明白了，在没有系统生成的 OID 的情况下，我可能需要创建一个名为 OID 的用户列，定义为一个序列。该问题的目的是询问是否有适用于使用 pre-v12 OID 的应用程序的规范解决方案。假设应用程序无法更改，因此任何解决方案都必须完全在数据库中实现。

postgresql postgresql-12
Martin Hope
JPM
Asked: 2021-03-17 01:07:14 +0800 CST
  • -1

我有一个像这样的 timescaledb 超表：

create table logs
(
    time         timestamp not null,
    partitionkey text      not null,
    ip           inet,
    raw          text,
    transformed  double precision
);

和索引如下：

create index logs_time_idx
    on logs (time desc);

create unique index logs_partitionkey_time_uindex
    on logs (partitionkey asc, time desc);

当我运行此查询时，需要 20 分钟才能完成：

SELECT * FROM data.logs 
WHERE partitionkey LIKE '%m.60.05482730' 
AND time > NOW() - INTERVAL '3 days'

但是当我运行这个时，它需要 2 秒：

SELECT * FROM data.logs 
WHERE partitionkey LIKE '865617033605366.m.60.05482730'
AND time > NOW() - INTERVAL '3 days'

我尝试仅索引分区键以帮助通配符查询找到匹配值，但这没有效果。

-- created this index later to try and fix the slow wildcard query
create index logs_partitionkey_index
    on logs (partitionkey);

解释通配符查询的计划：

Gather  (cost=1000.57..525711.89 rows=1219 width=81)
  Workers Planned: 2
  ->  Parallel Custom Scan (ChunkAppend) on logs  (cost=0.57..524589.99 rows=509 width=82)
        Chunks excluded during startup: 2
        ->  Parallel Index Scan using _hyper_2_10_chunk_logs_time_idx on _hyper_2_10_chunk  (cost=0.57..263956.91 rows=255 width=81)
              Index Cond: ("time" > (now() - '3 days'::interval))
              Filter: (partitionkey ~~ '%m.60.05482730'::text)
        ->  Parallel Index Scan using _hyper_2_9_chunk_logs_time_idx on _hyper_2_9_chunk  (cost=0.57..260629.72 rows=252 width=83)
              Index Cond: ("time" > (now() - '3 days'::interval))
              Filter: (partitionkey ~~ '%m.60.05482730'::text)
JIT:
  Functions: 8
  Options: Inlining true, Optimization true, Expressions true, Deforming true

解释具体的partionkey值：

Custom Scan (ChunkAppend) on logs  (cost=0.44..903.08 rows=790 width=82)
  Chunks excluded during startup: 2
  ->  Index Scan using _hyper_2_9_chunk_logs_partitionkey_time_uindex on _hyper_2_9_chunk  (cost=0.57..447.44 rows=392 width=83)
        Index Cond: ((partitionkey = '865617033605366.m.60.05482730'::text) AND ("time" > (now() - '3 days'::interval)))
        Filter: (partitionkey ~~ '865617033605366.m.60.05482730'::text)
  ->  Index Scan using _hyper_2_10_chunk_logs_partitionkey_time_uindex on _hyper_2_10_chunk  (cost=0.57..452.27 rows=396 width=81)
        Index Cond: ((partitionkey = '865617033605366.m.60.05482730'::text) AND ("time" > (now() - '3 days'::interval)))
        Filter: (partitionkey ~~ '865617033605366.m.60.05482730'::text)

TimescaleDB 是否无法执行通配符 (%) 查询，还是我错过了索引？

postgresql-12 timescaledb
Martin Hope
JPM
Asked: 2021-03-07 02:27:04 +0800 CST
  • 0

我有一个包含列的表：id、天线 ID、纬度、经度。(antenna_id, latitude) 和 (antenna_id, longitude) 上有两个复合索引。当我为特定的天线 id 做一个 max(latitude) 时，速度是可以接受的，但是同时对纬度和经度做一个 min 和 max 是非常慢的。

使用 PostgreSQL 12.3

询问 

EXPLAIN (analyze, buffers, format text) 
SELECT max(latitude) 
FROM packets 
WHERE antenna_id IN (1,2)

Finalize Aggregate  (cost=443017.21..443017.22 rows=1 width=32) (actual time=4373.679..4373.679 rows=1 loops=1)
  Buffers: shared hit=10812 read=16887
  ->  Gather  (cost=443017.10..443017.21 rows=1 width=32) (actual time=4373.412..4389.032 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        Buffers: shared hit=10812 read=16887
        ->  Partial Aggregate  (cost=442017.10..442017.11 rows=1 width=32) (actual time=4313.576..4313.577 rows=1 loops=2)
              Buffers: shared hit=10809 read=16887
              ->  Parallel Index Only Scan using idx_packets_antenna_id_latitude on packets  (cost=0.57..433527.51 rows=3395835 width=7) (actual time=0.375..3435.488 rows=2201866 loops=2)
                    Index Cond: (antenna_id = ANY ('{1,2}'::integer[]))
                    Heap Fetches: 0
                    Buffers: shared hit=10809 read=16887
Planning Time: 5.992 ms
JIT:
  Functions: 8
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 6.236 ms, Inlining 0.000 ms, Optimization 1.549 ms, Emission 32.058 ms, Total 39.842 ms
Execution Time: 4706.406 ms

对 max(longitude)、min(latitude) 和 min(longitude) 的解释看起来几乎相同。速度可以接受。

但是当我结合查询时 
SELECT max(latitude), max(longitude), min(latitude), min(longitude) 
FROM packets 
WHERE antenna_id IN (1,2)
期间

[2021-03-06 09:28:30] 1 row retrieved starting from 1 in 5 m 35 s 907 ms (execution: 5 m 35 s 869 ms, fetching: 38 ms)

解释 
Finalize Aggregate  (cost=3677020.18..3677020.19 rows=1 width=128)
  ->  Gather  (cost=3677020.06..3677020.17 rows=1 width=128)
        Workers Planned: 1
        ->  Partial Aggregate  (cost=3676020.06..3676020.07 rows=1 width=128)
              ->  Parallel Seq Scan on packets  (cost=0.00..3642080.76 rows=3393930 width=14)
                    Filter: (antenna_id = ANY ('{1,2}'::integer[]))
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN (analyze, buffers, format text) 
SELECT max(latitude), max(longitude), min(latitude), min(longitude) 
FROM packets 
WHERE antenna_id IN (1,2)

已经运行了 24 小时，还没有完成

索引

create index idx_packets_antenna_id_time
    on packets (antenna_id, time);

create index idx_packets_antenna_id_longitude
    on packets (antenna_id, longitude);

create index idx_packets_device_id_time
    on packets (device_id, time);

create index idx_packets_antenna_id_latitude
    on packets (antenna_id, latitude);

数据统计

select count(*) from packets
136758098

select count(distinct (antenna_id)) from packets
17558

select antenna_id, count(*) as records 
from packets 
where antenna_id in (1,2) 
group by antenna_id 
order by records desc

1,4361049
2,42683

问题

为什么在纬度和经度字段上执行最小值和最大值的第二个查询不使用索引？以及如何重写查询以使其更快？

postgresql postgresql-12