主页 / dba / 问题

问题[postgresql-11](dba)

Martin Hope
Thompson Sanjoto
Asked: 2022-07-08 09:55:03 +0800 CST
  • 1

我有一个如下所示的表格。

CREATE TABLE tbl (sl_no int, username text, petname1 text, petname2 text);
    
INSERT INTO tbl VALUES
  (1, 'A', 'XYZ', 'ABC')
, (2, 'B', 'RTS', 'DEF')
, (3, 'C', 'QWE', 'GHI')
, (4, 'D', 'HGD', 'JKL')
;

如何将单列转换为多行条目？进入这个结果：

1, 'A', 'XYZ'
1, 'A', 'ABC'
2, 'B', 'RTS'
2, 'B', 'DEF'
3, 'C', 'QWE'
3, 'C', 'GHI'
4, 'D', 'HGD'
4, 'D', 'JKL'

如果可能，使用 postgresql 11.3

postgresql postgresql-11
Martin Hope
Aldar
Asked: 2021-07-30 08:27:21 +0800 CST
  • 0

我的 PostgreSQL 11 服务器之一抛出了一个奇怪的错误，并在基本备份后拒绝正确启动流复制：

致命：由于管理员命令而终止 walreceiver 进程

我尝试启用最高的日志详细度（Debug5），但这并没有提供更多关于它为什么一直死的洞察力：

2021-07-29 15:33:30.862 UTC [39315] DEBUG:  postgres: PostmasterMain: initial environment dump:
2021-07-29 15:33:30.862 UTC [39315] DEBUG:  -----------------------------------------
2021-07-29 15:33:30.862 UTC [39315] DEBUG:      PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
2021-07-29 15:33:30.862 UTC [39315] DEBUG:      PG_GRANDPARENT_PID=39310
2021-07-29 15:33:30.862 UTC [39315] DEBUG:      PGLOCALEDIR=/usr/share/locale
2021-07-29 15:33:30.862 UTC [39315] DEBUG:      PGSYSCONFDIR=/etc/postgresql-common
2021-07-29 15:33:30.862 UTC [39315] DEBUG:      LANG=en_US.UTF-8
2021-07-29 15:33:30.862 UTC [39315] DEBUG:      PWD=/
2021-07-29 15:33:30.863 UTC [39315] DEBUG:      PGDATA=/var/lib/postgresql/11/replica
2021-07-29 15:33:30.863 UTC [39315] DEBUG:      LC_COLLATE=en_US.UTF-8
2021-07-29 15:33:30.863 UTC [39315] DEBUG:      LC_CTYPE=en_US.UTF-8
2021-07-29 15:33:30.863 UTC [39315] DEBUG:      LC_MESSAGES=en_US.UTF-8
2021-07-29 15:33:30.863 UTC [39315] DEBUG:      LC_MONETARY=C
2021-07-29 15:33:30.863 UTC [39315] DEBUG:      LC_NUMERIC=C
2021-07-29 15:33:30.863 UTC [39315] DEBUG:      LC_TIME=C
2021-07-29 15:33:30.863 UTC [39315] DEBUG:  -----------------------------------------
2021-07-29 15:33:30.867 UTC [39315] DEBUG:  registering background worker "logical replication launcher"
2021-07-29 15:33:30.868 UTC [39315] LOG:  listening on IPv6 address "::1", port 5433
2021-07-29 15:33:30.868 UTC [39315] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-07-29 15:33:30.868 UTC [39315] LOG:  listening on IPv4 address "*snip*", port 5433
2021-07-29 15:33:30.868 UTC [39315] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-07-29 15:33:30.869 UTC [39315] DEBUG:  invoking IpcMemoryCreate(size=156459008)
2021-07-29 15:33:30.869 UTC [39315] DEBUG:  mmap(157286400) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
2021-07-29 15:33:30.885 UTC [39315] DEBUG:  SlruScanDirectory invoking callback on pg_notify/0000
2021-07-29 15:33:30.885 UTC [39315] DEBUG:  removing file "pg_notify/0000"
2021-07-29 15:33:30.885 UTC [39315] DEBUG:  dynamic shared memory system will support 588 segments
2021-07-29 15:33:30.885 UTC [39315] DEBUG:  created dynamic shared memory control segment 1597797971 (14128 bytes)
2021-07-29 15:33:30.887 UTC [39315] DEBUG:  max_safe_fds = 982, usable_fds = 1000, already_open = 8
2021-07-29 15:33:30.889 UTC [39316] LOG:  database system was shut down in recovery at 2021-07-29 15:31:23 UTC
2021-07-29 15:33:30.890 UTC [39316] DEBUG:  standby_mode = 'on'
2021-07-29 15:33:30.890 UTC [39316] DEBUG:  primary_conninfo = '*snip*'
2021-07-29 15:33:30.890 UTC [39316] DEBUG:  recovery_target_timeline = latest
2021-07-29 15:33:30.890 UTC [39316] LOG:  entering standby mode
2021-07-29 15:33:30.890 UTC [39316] LOG:  invalid resource manager ID 172 at 235/450000D0
2021-07-29 15:33:30.890 UTC [39316] DEBUG:  switched WAL source from archive to stream after failure
2021-07-29 15:33:30.891 UTC [39317] DEBUG:  find_in_dynamic_libpath: trying "/usr/lib/postgresql/11/lib/libpqwalreceiver"
2021-07-29 15:33:30.905 UTC [39317] DEBUG:  find_in_dynamic_libpath: trying "/usr/lib/postgresql/11/lib/libpqwalreceiver.so"
2021-07-29 15:33:30.918 UTC [39317] LOG:  started streaming WAL from primary at 235/45000000 on timeline 1
2021-07-29 15:33:30.920 UTC [39317] DEBUG:  sendtime 2021-07-29 15:33:30.917704+00 receipttime 2021-07-29 15:33:30.920718+00 replication apply delay (N/A) transfer latency 3 ms
2021-07-29 15:33:30.920 UTC [39317] DEBUG:  sending write 235/45020000 flush 0/0 apply 0/0
2021-07-29 15:33:30.921 UTC [39317] DEBUG:  sending write 235/45020000 flush 235/45020000 apply 0/0
2021-07-29 15:33:30.921 UTC [39316] LOG:  invalid resource manager ID 172 at 235/450000D0
2021-07-29 15:33:30.921 UTC [39317] FATAL:  terminating walreceiver process due to administrator command
2021-07-29 15:33:30.921 UTC [39317] DEBUG:  shmem_exit(1): 1 before_shmem_exit callbacks to make
2021-07-29 15:33:30.921 UTC [39317] DEBUG:  shmem_exit(1): 5 on_shmem_exit callbacks to make
2021-07-29 15:33:30.921 UTC [39317] DEBUG:  proc_exit(1): 2 callbacks to make
2021-07-29 15:33:30.921 UTC [39317] DEBUG:  exit(1)
2021-07-29 15:33:30.921 UTC [39316] DEBUG:  switched WAL source from stream to archive after failure

唯一值得关注的是 LOG 级别的消息，例如

日志：235/450000D0 处的资源管理器 ID 172 无效

然而，事实证明，这些只是 Postgres 的说法“到达有效 WAL 结构的末尾”，并且可以安全地忽略 LOG 级别的消息。

我尝试删除现有的 WAL 日志（来自datadir/pg_wal/），认为可能是文件损坏，服务器仍然不会启动复制。唯一的解决方案是制作一个全新的基本备份。

我的问题 - 当 Postgres 的terminating xyz process due to administrator command一个进程以非标准方式死亡时，Postgres 是否会给出默认消息？

在这种情况下会有更多的调试选项吗？即使是最高的日志记录详细程度也没有提供太多有用的信息。

postgresql postgresql-11
Martin Hope
user1068636
Asked: 2021-04-30 16:46:22 +0800 CST
  • 1

我成功地按照此处找到的 Centos 7 上的 Postgres 11 安装说明进行操作。我能够成功地创建模式/表并通过端口 5432 将数据插入到数据库中，没有任何问题。

现在我想将默认端口更改为 5332 （或其他，没关系），我做了以下事情：

 vi /var/lib/pgsql/11/data/postgresql.conf

更改了行：

#port = 5432                            # (change requires restart)

看起来像：

port = 5332                            # (change requires restart)

然后通过执行以下操作重新启动 postgres 服务：

systemctl restart postgresql-11.service

然后我更改为 postgres 用户：

su - postgres
psql

我收到以下错误消息：

sql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

所以显然在某处还有另一个端口 5432 的引用，但我找不到它。

仅供参考，如果我撤消上面所做的更改（在文件 /var/lib/pgsql/11/data/postgresql.conf 中），那么一切都会在默认端口 5432 上再次完美运行。

我还需要做什么才能让 postgres 在不同的端口上运行？

postgresql-11 centos-7
Martin Hope
Dimitrios Desyllas
Asked: 2021-02-27 07:13:31 +0800 CST
  • 0

Ι 有以下看法：

CREATE OR REPLACE VIEW public.my_view as 
SELECT
  col1,
  col11
from
  table_a JOIN table_b on table_a.id=table_b.a_id
  join
   (
     select id,col56 from table_c where col56 > 3
   ) as filtered_c on table_a.id=filtered_c.a_id;

table_a 是：

id: SERIAL PK
col1: INTEGER

table_b 是：

id: SERIAL PK
col11: INTEGER
a_id FK table_a

table_c 是：

id: SERIAL PK
col56 INTEGER
a_id FK table_a

正如我在 postgresql wiki上看到的那样，我可以将值插入到与相应表对应的视图中。但就我而言，如果我插入：

INSERT INTO my_view VALUES (7,88);

将在哪个表中插入值？据我所知，视图是只读表。

postgresql postgresql-11
Martin Hope
Chessbrain
Asked: 2021-01-21 03:10:13 +0800 CST
  • 1

查看 PostgreSQL 的文档，我在timenow(). 但是，如果我调用该函数，它就可以工作。

那么now()和 和有什么不一样timenow()？

我猜这now()是基于事务，而timenow()服务器操作系统时间还是我完全关闭？

以下是 DataGrip 的自动生成定义。所有系统功能（例如sum）都具有missing source code并且您可以看到它是pg_catalog模式的一部分。

在此处输入图像描述

postgresql postgresql-11
Martin Hope
user219430
Asked: 2020-12-16 11:00:10 +0800 CST
  • 0

标题讲述了这个故事，但我如何追踪 Postgres 11.10 中内存峰值的原因？正如您从下面的屏幕截图中看到的那样，内存峰值一直高达 32 GB，这导致 OOM 启动并终止了一堆连接。根据/var/log/postgresql/postgresql-11-main.log数据库系统同时进入恢复模式，我们也丢失了相当多的数据。

我怀疑连接数加上非常大的插入语句可能与它有关，但需要一种证明（或反驳）假设的方法。

内存峰值的屏幕截图

postgresql postgresql-11
Martin Hope
Christophe
Asked: 2020-10-24 05:54:18 +0800 CST
  • 5

我有一个表，其中每个条目都是一个节点，并且该表包含每个节点与其他节点的直接连接。我希望为每个节点创建一个包含链中所有节点的列的视图，而不仅仅是节点本身连接到的节点。

一个示例是从下表的前两列生成“链中的节点”列：

CREATE TABLE example
(
    node text,
    connections text[],
    nodes_in_chain text[]
)

INSERT INTO example VALUES
('a', ARRAY['a','b'],         null),
('b', ARRAY['a','b','c','d'], null),
('c', ARRAY['b','c'],         null),
('d', ARRAY['b','d'],         null),
('e', ARRAY['e','f'],         null),
('f', ARRAY['e','f'],         null);

Node   Connections    Nodes in Chain
"a"    "{a,b}"        "{a,b,c,d}"
"b"    "{a,b,c,d}"    "{a,b,c,d}"
"c"    "{b,c}"        "{a,b,c,d}"
"d"    "{b,d}"        "{a,b,c,d}"
"e"    "{e,f}"        "{e,f}"
"f"    "{e,f}"        "{e,f}"

这是真正问题的一个小简化版本。如果我能解决这个例子，全表应该没问题。

该表的数据可以通过以下方式可视化：

数据

我研究了几种不同的方法来解决这个问题。我研究了递归 CTE，但我没有设法让它们工作。

每个节点都连接到当前在数据库中的自身。如果有必要，在数据库中删除与自身的连接不是问题。

问题可能不必要的背景：

这个问题的根源来自试图识别交通中的车辆。原始数据库包含给定区域内每个时间步 t 的车辆位置和速度。目标是确定在红绿灯处花费的时间。为了解决这个问题，确定了交通信号灯的停车区域。该区域内每辆速度低于某个阈值的车辆都被认为正在等待红绿灯。然而，由于排长队，车辆可能在该区域外排队。因此，一条交通线（“节点链”）由所有彼此相距一定距离内且速度较低的车辆组成。从识别的队列区域内的车辆开始。这个问题是对飞机滑行时间的科学研究的一部分。

我首先使用 Python 和 pandas 执行计算以识别区域内的车辆。然而，代码的运行时间要长 10 倍，这使得该项目望而却步。该代码非常简单，无需手动引入循环，因此无法加速（我相信）。我还将比较在 Python 和 PostgreSQL 中执行排队算法的速​​度。

postgresql postgresql-11
Martin Hope
Theron Luhn
Asked: 2020-08-04 16:41:48 +0800 CST
  • 1

我正在运行 PostgreSQL 11.5。我有一个简单的寄存器表。

create table register(
  id serial primary key,
  name text not null
);

该表很小（约 6000 行），读取相对较重（100 秒查询/秒），几乎没有写入。我想添加一个自引用外键：

alter table register
  add column leader_id integer references register(id);

鉴于桌子的大小，我认为这将是一个相当平凡的变化。它在本地和登台服务器上运行良好。但是，当我针对生产运行它时，表被锁定了。日志显示 ALTER 和各种 SELECT 语句需要大约 10m 才能完成。

2020-08-04 00:01:15 UTC:10.0.2.101(59588):postgres@db:[21609]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: unexpected EOF on client connection with an open transaction
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: duration: 456741.453 ms execute <unnamed>: alter table register
add column leader_id integer references register(id);

2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: could not send data to client: Broken pipe
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: duration: 456635.839 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 123 AND register.account_id = '22781BD1-F37A-4ACE-9A3D-CBF3464AFB43'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: duration: 456656.956 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 234 AND register.account_id = 'A6D8395C-63E8-40A8-A0AE-4F19B1DA5509'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:FATAL: connection to client lost

这里发生了什么导致表锁定？如何安全地使用自引用外键？

postgresql postgresql-11
Martin Hope
Slava Rozhnev
Asked: 2020-06-05 00:12:55 +0800 CST
  • 0

根据PostgreSQL 文档，我可以使用 pg_waldump 来显示 PostgreSQL 数据库集群的预写日志的人类可读渲染。

我在 Ubuntu 18.04 上全新安装了 PostgreSQL-11 服务器，但我没有这个命令：

pg_waldump：找不到命令

下面列出了现有的 pg 实用程序：

pg_archivecleanup    pg_conftool          pg_dumpall           pg_recvlogical       pg_virtualenv
pg_basebackup        pg_createcluster     pg_isready           pg_renamecluster     
pg_buildext          pg_ctlcluster        pg_lsclusters        pg_restore           
pg_config            pg_dropcluster       pg_receivewal        pg_updatedicts       
pg_config.libpq-dev  pg_dump              pg_receivexlog       pg_upgradecluster

我需要做什么才能在我的服务器上使用这个命令

postgresql postgresql-11
Martin Hope
Laurence Gonsalves
Asked: 2020-03-25 21:48:53 +0800 CST
  • 0

我有一张桌子，我们称它为parent：

CREATE TABLE parent (
    id          TEXT PRIMARY KEY,
    name        TEXT,
    description TEXT,
    -- other parent attributes
    created_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at  TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

它有许多子表，比如说child_1，child_2，... child_n。它们看起来都像：

CREATE TABLE child_n (
    id        BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parent_id TEXT NOT NULL REFERENCES parent (id) ON DELETE CASCADE,
    -- other columns of child_n attributes
);

其中一些孩子甚至可能有自己的子表。

当我更新父行中的一行时，我经常还想替换它的所有后代（即：它的子代、他们的子代等递归），因为它们只与该父行的旧版本相关。

我的计划是依靠它REFERENCES parent (id) ON DELETE CASCADE，但这只有在我真正删除父级时才有效。我知道我可以在事务中删除和插入，但这使得保留列（如created_at）的值更加尴尬。

有没有办法在不实际删除父行的情况下触发子级的级联删除？

postgresql postgresql-11