主页 / server / 问题

问题[postgresql](server)

Martin Hope
GuteShel
Asked: 2024-10-17 13:42:05 +0800 CST
  • 5

在 PostgreSQL 中，我有以下配置pg_hba.conf：

local   all     sec_eng                 scram-sha-256
host    all     sec_eng                 scram-sha-256
hostssl all     app_server  192.168.1.0/32      scram-sha-256   clientcert=verify-full

没有其他配置行。

当我尝试使用 连接到 PostgreSQL 时sudo psql -U sec_eng，客户端将我验证为peer，并且不提示我输入任何密码，因此身份验证失败。

如果我尝试sudo psql -U sec_eng -h localhost以 的身份进行身份验证Ident，则不会提示输入密码，因此身份验证失败。

我在这里做错了什么？如何使用我的用户名和密码通过 Unix 套接字连接到 PostgreSQL？

postgresql
Martin Hope
mattbasta
Asked: 2024-07-23 01:30:41 +0800 CST
  • 7

在工作中，我们遇到了一种神秘的问题。每隔几个小时，我们的一个表（tasks）就会出现查询延迟增长（由 CPU 使用率引起），随后出现爆发式增长LWLock:BufferContent，然后又下降到正常水平。以下是 RDS 性能洞察的屏幕截图：

图表显示 CPU 使用率增加，随后 BufferContent 锁定并返回稳定状态

我们做了大量工作来尝试缓解这种情况，这些工作已经导致事故发生的频率（据传闻）降低，严重程度也降低。然而，问题并没有消失。

首先，我们注意到有些查询没有命中索引。我花了一些时间确保针对此表的所有查询都执行索引和仅索引扫描。查询通常如下所示：

SELECT COUNT(1) FROM tasks WHERE status = 'PENDING' AND NOT deleted

status并且我们在条件上有相应的索引status = 'PENDING' AND deleted = FALSE。EXPLAIN ANALYZE表明索引正在被正确使用。

我们确实发现，当用户有大量记录时，一些在我们的列上指定条件的查询userId会有一个糟糕的查询计划。这个问题通过修复查询和索引改进得到了解决。据我们所知，没有任何查询对任何值都有糟糕的查询计划（但问题仍然存在）。

在峰值期间，传入负载没有增加。在事件发生之前、期间和之后，受影响的查询在 RDS 性能洞察中显示相同的“调用次数/秒”，并且我们的客户端应用程序显示稳定的 QPS，因此这不是由查询增加引起的。

我确实观察到，EXPLAIN ANALYZE这些查询的结果确实表明在这些峰值期间堆提取有所增加，即使对于仅索引扫描也是如此。我读到在索引扫描期间堆仍然会受到影响，因为可见性图可能表明包含索引引用的元组的页面可能不可见。这似乎是一个强烈的信号：PENDING任务（如上面的查询所示）通常是接收许多更新的“热”行，因此包含这些行的页面的可见性图需要提取是有道理的。

为了弥补这一缺陷，我们调整了自动清理设置，使清理频率大大提高。现在我们每隔几个小时就会看到一次自动清理，但问题并没有消失。我看到这些峰值出现在表的自动清理完成 20 分钟后。此外，EXPLAIN ANALYZE峰值期间报告的堆提取次数可能比任务记录数高出 1-2 个数量级PENDING：总数很少超过 200，我们可以看到在仅包含PENDING记录的索引的索引扫描中存在数千次堆提取。

在调试过程中，我注意到查询规划器确实倾向于随着时间的推移调整查询的索引，即使负载保持不变。它有时会选择允许索引扫描但不允许仅索引扫描的索引。例如，它可能会选择 上的索引，而忽略上的条件status索引，即使后者完全满足查询。我将和设置调整为 而不是 Postgres 默认值（分别为 1 和 4），这应该会指示 Postgres 优先使用索引扫描。不幸的是，问题仍然存在（尽管可能频率较低）。上面的屏幕截图是在设置调整之后的。statusstatus = 'PENDING' and not deletedseq_page_costrandom_page_cost1.0

一些附加信息：

  1. 该问题仅影响此一张表（或者我应该说，此一张表的查询）。
  2. 峰值期间表上的所有查询都会受到影响，而不是单个查询。

到目前为止，我几乎没有主意了。作为参考，我们在 Postgres 14.10 上运行db.m5.16xlargeGP2 SSD 卷。我怀疑我们的 Postgres 版本只需要更新（计划中的工作），但这不是一个令人满意的解决方案。

我有点困惑的是，为什么造成延迟的资源使用是 CPU。您可以在上面的屏幕截图中看到绿色（CPU）的增加。基本上每个查询都应该扫描很少的记录。所有查询都是计数聚合，查询的条件都是简单的相等。这是没有峰值时受影响的查询输出之一：

Aggregate  (cost=46.02..46.03 rows=1 width=8) (actual time=0.362..0.362 rows=1 loops=1)
"  ->  Index Only Scan using ""tasks_globalPending"" on tasks  (cost=0.25..37.54 rows=3393 width=0) (actual time=0.239..0.353 rows=128 loops=1)"
        Heap Fetches: 178
Planning Time: 0.161 ms
Execution Time: 0.377 ms

非常快！可以说是非常高效（尽管我更喜欢零堆提取）。对于COUNT()~never 返回值大于 300 的程序来说，令人费解的是，它如何从每个查询 0.4 毫秒增加到 10-20 毫秒，并将这段时间浪费在 CPU 使用上。它在做什么？

postgresql
Martin Hope
QGuy
Asked: 2024-07-22 21:08:44 +0800 CST
  • 5

macOS Sonoma 14.2.1 (23C71)，刚刚更新至 14.5 (23F79)

故事

周末，我有一个 Python 脚本 (norgis ALKIS-Import) 将大量数据导入我的 Postgres 数据库。今天我回来时，数据库仍在运行，但我无法访问它。我收到此错误消息："base/5/2703": No such file or directory。重新启动 Mac 后，它不再启动。

备份

我有一个带有 RAID 10 的直接附加存储，我使用 rsync shell 脚本和 LaunchAgents 进行每日和每月备份。它只是将 var-16 文件夹复制到大存储。我尝试启动每一个每日备份 (7) 和每月备份，但它们都出现相同的错误。我还有一个几周前的备份，但我遇到了同样的错误。我简直无法解释为什么我的所有备份都在我未触碰任何备份的情况下被损坏了。

我尝试过的方法

  • 我在同一台 Mac 上创建了一个新的 Postgres 服务器并且它运行良好。
  • 我在另一台 Mac 上运行了服务器（它存储在外部 SSD 上），但仍然收到相同的错误消息。

这是启动服务器后，postgresql.log文件中的日志输出。

2024-07-22 14:29:58.728 CEST [9382] LOG:  listening on IPv6 address "::", port 5432
2024-07-22 14:29:58.728 CEST [9382] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-07-22 14:29:58.729 CEST [9382] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-07-22 14:29:58.744 CEST [9382] LOG:  could not open directory "pg_tblspc/.DS_Store/PG_16_202307071/pgsql_tmp": Not a directory
2024-07-22 14:29:58.744 CEST [9382] LOG:  could not open directory "pg_tblspc/.DS_Store/PG_16_202307071": Not a directory
2024-07-22 14:29:58.745 CEST [9385] LOG:  database system was interrupted while in recovery at 2024-07-22 14:23:35 CEST
2024-07-22 14:29:58.745 CEST [9385] HINT:  This probably means that some data is corrupted and you will have to use the last backup for recovery.
2024-07-22 14:30:03.952 CEST [9385] LOG:  database system was not properly shut down; automatic recovery in progress
2024-07-22 14:30:03.966 CEST [9385] FATAL:  could not open directory "pg_tblspc/.DS_Store/PG_16_202307071": Not a directory
2024-07-22 14:30:03.967 CEST [9382] LOG:  startup process (PID 9385) exited with exit code 1
2024-07-22 14:30:03.967 CEST [9382] LOG:  aborting startup due to startup process failure
2024-07-22 14:30:03.967 CEST [9382] LOG:  database system is shut down

我也不明白为什么它要在 .DS_Store 目录中执行某些操作。这些不是由 macOS 制作和使用的吗？

编辑

如果有人知道为什么我的所有备份也都损坏了，请回答。.DS_Stores 来自哪里，为什么 postgres 会尝试对 .DS_Store 文件执行某些操作？谢谢！

postgresql
Martin Hope
HubbaBubba2000
Asked: 2024-07-15 01:15:05 +0800 CST
  • 6

我是 PostgreSQL 的完全初学者，我正在按照数据库简介设置本地 PostgreSQL 数据库并连接到它。我正在运行 Debian 15，因此我执行以下操作：

# apt update
# apt install postgresql

安装成功。然后我执行以下操作：

sudo -u postgres psql
psql (15.7 (Debian 15.7-0+deb12u1))
Type "help" for help.

postgres=# \l
postgres=# createdb prismatest
postgres-# \l
postgres-# \c prismatest
connection to server on socket "/var/run/postgresql/.s.PGSQL.5433" failed: FATAL:  database "prismatest" does not exist
Previous connection kept

因此，数据库列表没有显示任何内容，无法连接到据称新创建的数据库。我的用户拥有以下权限：

postgres-# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

有人能帮我弄清楚发生了什么吗？谢谢...

postgresql
Martin Hope
red888
Asked: 2023-06-07 00:35:16 +0800 CST
  • 5

这行不通

CREATE ROLE "role-one" LOGIN;
CREATE ROLE "other_role" LOGIN WITH "role-one";

我收到这个错误

ERROR:  unrecognized role option "role-one"
LINE 1: CREATE ROLE "other_role" WITH "role-one";

我究竟做错了什么？我无法在网上找到可以让我清楚语法是什么的示例。据我所见，这应该可行。

如何使用 LOGIN 创建一个新角色并将其添加到另一个名为 role-one 的角色？

postgresql
Martin Hope
folow
Asked: 2023-06-02 19:55:30 +0800 CST
  • 5
  1. 我的环境：
  • Red Hat 8.8 上的 MIT Kerberos 服务器
  • Red Hat 8.8 上的 PostgreSQL 服务器 v15.3
  • Ubuntu 22.04 桌面上的 PostgreSQL 客户端 v15.3

  1. 我已将 PostgreSQL 服务器配置为使用 Kerberos。在我安装了 PostgreSQL 客户端的 Ubuntu 桌面上，我可以使用 psql 客户端成功建立与 PostgreSQL 服务器的 Kerberos 连接。

  2. 在 Ubuntu 桌面上，我按照说明使用 apache2 服务器安装了 pgadmin4：https://computingforgeeks.com/how-to-install-pgadmin-4-on-ubuntu/我可以使用内部 pgadmin4 连接成功登录 pgadmin4，并且可以建立 PostgreSQL与不使用 Kerberos 的 PostgreSQL 服务器的连接。我认为 pgadmin4 设置正确。

  3. 现在我想使用 pgadmin4 建立 Kerberos 身份验证到 PostgreSQL Kerberos 配置的服务器。我已关注官方文档：https://www.pgadmin.org/docs/pgadmin4/latest/kerberos.html还观看了 youtube 视频：https://www.youtube.com/watch?v =EDg6I21kCak - 我可以关注并配置文档和视频中的所有内容，但最后当我打开 Firefox 113 浏览器时，输入 URL： http: //desktop.example.com/pgadmin4/browser/和 pgadmin4 网页加载，但在右上角有错误显示消息：“Kerberos 身份验证失败。找不到 kerberos 票证。”

  4. 我已经使用“klist”命令检查了票证，票证在那里并且仍然有效（记住我可以使用 psql 工具成功建立 Kerberos 连接）。桌面的 keytab 文件也存在于 Ubuntu 中。

  5. 在 Ubuntu 上，我检查了 /var/log/pgadmin/ 哪里是一个空文件。还检查了 /var/log/apache2/ 文件夹，其中唯一的消息（在连接建立期间）在 access.log 中：

192.168.100.31 - - [02/Jun/2023:13:41:49 +0200] "GET /pgadmin4/browser/ HTTP/1.1" 302 930 "-" "Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv: 109.0) 壁虎/20100101 Firefox/113.0"

192.168.100.31 - - [02/Jun/2023:13:42:04 +0200] "GET /pgadmin4/login?next=%2Fpgadmin4%2Fbrowser%2F HTTP/1.1" 401 5435 "-" "Mozilla/5.0 (X11 ; Ubuntu; Linux x86_64; rv:109.0) Gecko/20100101 Firefox/113.0"

  1. 我在日志文件中看不到任何错误。我在网上搜索过，但找不到任何有用的东西。

知道我可以调查什么吗？

postgresql
Martin Hope
Dustin Oprea
Asked: 2023-05-05 05:11:35 +0800 CST
  • 7

从任何其他环境连接到它都没有问题（本地工作正常，就像从云端一样），并且安全组目前是完全开放的。托管在 AWS 中的实际PostgreSQL实例没有问题。这只是极光的事情。我创建了几个具有不同配置的实例。SSL 身份验证已关闭，并且 SSL 本身配置为可选。

“抱歉，我们遇到错误，无法完成您的请求。”

破碎的极光支持

postgresql
Martin Hope
keramat
Asked: 2023-04-23 18:51:08 +0800 CST
  • 3

我在 Windows Server 2022 Standard 上运行 Postgres 14。当数据库操作正在运行时，例如插入，我的服务器重新启动，现在 Postgres 没有启动。我查看了配置文件，最后几行如下：

2023-04-23 01:43:36.913 PDT [123528] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

    
2023-04-23 01:52:04.222 PDT [105764] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

    
2023-04-23 01:52:04.223 PDT [118464] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

    
2023-04-23 01:54:38.568 PDT [121492] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

    
2023-04-23 01:54:38.569 PDT [123696] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

    
2023-04-23 01:59:51.982 PDT [124252] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

    
2023-04-23 01:59:51.983 PDT [122440] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

    
2023-04-23 01:59:51.983 PDT [116252] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

    
2023-04-23 02:00:52.468 PDT [4988] LOG:  received fast shutdown request
2023-04-23 02:00:52.572 PDT [4988] LOG:  aborting any active transactions
2023-04-23 02:00:52.611 PDT [6172] FATAL:  terminating connection due to administrator command
2023-04-23 02:00:52.683 PDT [5984] ERROR:  canceling statement due to user request
2023-04-23 02:00:52.716 PDT [4988] LOG:  background worker "logical replication launcher" (PID 5984) exited with exit code 1
2023-04-23 02:00:52.903 PDT [5916] LOG:  shutting down
2023-04-23 02:00:55.161 PDT [4988] LOG:  database system is shut down

另外，正如其他人所建议的，我检查事件查看器并查看：

2023-04-23 03:33:39.610 PDT [5804] FATAL:  configuration file "C:/Program Files/PostgreSQL/14/data/postgresql.conf" contains errors

由于我使用 psycopg 来处理 db，因此出现以下错误：

File "C:\Python310\lib\site-packages\psycopg2\__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5432 failed: Connection refused (0x0000274D/10061)      
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused (0x0000274D/10061)
        Is the server running on that host and accepting TCP/IP connections?

配置文件

对于大小限制，我删除了所有评论：

listen_addresses = '*'      # what IP address(es) to listen on;
port = 5432         # (change requires restart)
max_connections = 100           # (change requires restart)
shared_buffers = 4096MB         # min 128kB
work_mem = 2000MB               # min 64kB
maintenance_work_mem = 3000MB       # min 1MB
dynamic_shared_memory_type = windows    # the default is the first option
max_wal_size = 1GB
min_wal_size = 80MB
effective_cache_size = 4GB
log_destination = 'stderr'
logging_collector = on      # Enable capturing of stderr and csvlog
log_timezone = 'America/Los_Angeles'
datestyle = 'iso, mdy'
timezone = 'America/Los_Angeles'
lc_messages = 'English_United States.1252'          # locale for system error message
                    # strings
lc_monetary = 'English_United States.1252'          # locale for monetary formatting
lc_numeric = 'English_United States.1252'           # locale for number formatting
lc_time = 'English_United States.1252'              # locale for time formatting
default_text_search_config = 'pg_catalog.english'

我没有最近的数据库备份。我应该如何克服这个问题？

postgresql
Martin Hope
user15397083
Asked: 2023-03-17 22:54:17 +0800 CST
  • 6

我做了

pgsqld stop

命令行就像

Stopping PostgreSql:

我已经等了几秒钟，然后按 ctrl+C 取消它，因为我只是觉得出了什么问题。

我又试了一次，然后我看到了这样的信息

pg_ctl: server does not shut down

我检查了状态

pgsqld status
// pg_ctl: server is running (PID: XXXXX)

简而言之，数据库似乎还在运行，我无法关闭，但我的服务现在已经死了……请帮我关闭并重新启动。

++ 当我尝试登录到 pgsql 服务器时

psql -U postgres
// FATAL: the database system is shutting down...
postgresql
Martin Hope
user15397083
Asked: 2023-03-07 12:22:05 +0800 CST
  • 5

我正在使用 postgres 设置复制数据库。似乎有很多关于如何设置的信息，但我仍然找不到如何在应用程序中操作它。

我想要的是仅从 repl 数据库中“选择”，并从源数据库中“插入/更新/删除”。在这种情况下，我是否应该在每次选择时都连接到 repl db，而在其他情况下“在应用程序级别”连接到 source db？或者有什么办法可以让这些全部自动化？

我的意思是，似乎必须有办法让它自动化，因为如果真的有很多 repl db（超过 2 个），它一定是这样工作的。

请帮助我，谢谢大家。

postgresql