主页 / dba / 问题 / 276612
Accepted
Lennart - Slava Ukraini
Asked: 2020-10-07 05:02:37 +0800 CST

pg_restore 卡在桌子上

  • 772

我正在为一个在 docker 容器内运行的小型 postgres 12.2 数据库创建一个备份/恢复脚本。备份看起来像：

docker exec -e PGPASSWORD=${passwd} ${container} \
   pg_dump -h ${host} -U ${username} ${db} \
   -Fc > ${backupdir}${db}_$(date +"%Y-%m-%d-%H.%M.%S.%N").pgdump

然后我创建一个新数据库，并尝试将其还原为：

docker exec -i ${container} pg_restore -Fc --verbose --clean \
   --no-acl --no-owner -U ${username} \
   -d ${testdb} < ${backupdir}${db}_ ...

这似乎适用于许多表，然后每次都冻结在一个特定的表上。尽管 Postgres 正在使用 80% 的 CPU，但似乎什么也没发生。我让它运行了 10 个小时而没有完成。

有问题的表包含约 1E6 行，一个 3 倍大的表在大约 10 秒内恢复。该表包含一个生成的列：

CREATE TABLE MIND.FILER
( DOKUMENT_UUID UUID NOT NULL   -- document_uuid
, SIDNUMMER INTEGER NOT NULL    -- page number
, FILTYP TEXT NOT NULL          -- file type
, TILLSTAND SMALLINT NOT NULL   -- state 
, FILNAMN TEXT NOT NULL         -- name of file
, FULLSTANDIGT_FILNAMN TEXT GENERATED ALWAYS AS -- complete file name including path 
    (mind.uuid_2_path(dokument_uuid) || rtrim(filnamn)) STORED
,   CONSTRAINT PK_FILER PRIMARY KEY (DOKUMENT_UUID, SIDNUMMER, FILTYP)
,   CONSTRAINT FK_SIDOR
        FOREIGN KEY (DOKUMENT_UUID, SIDNUMMER)
            REFERENCES MIND.SIDOR (DOKUMENT_UUID, SIDNUMMER)
                ON DELETE CASCADE
                ON UPDATE CASCADE
,   CONSTRAINT FK_FILTYPER
        FOREIGN KEY (FILTYP)
            REFERENCES MIND.FILTYPER (FILTYP)
                ON DELETE CASCADE
                ON UPDATE CASCADE
);

但我的印象是，这不应该影响恢复，至少不会影响到这个程度。

函数 MIND.UUID_2_PATH 根据使用的 UUID 版本定位文件系统中的挂载点。

CREATE OR REPLACE FUNCTION MIND.UUID_2_PATH(DUID UUID)
    RETURNS TEXT AS $$
    DECLARE s text;
    DECLARE ss text;
    DECLARE uuid_v int;
BEGIN
    SELECT substr(DUID::text,15,1) into uuid_v;
    IF uuid_v = 4 THEN
        SELECT REPLACE(CAST(DUID AS TEXT),'-','') INTO s;
        SELECT monteringspunkt
                  ||SUBSTR(s,1,4)||'/'
                  ||SUBSTR(s,5,4)||'/'
                  ||SUBSTR(s,9,4)||'/'
                  ||SUBSTR(s,13,4)||'/'
                  ||SUBSTR(s,17,4)||'/'
                  ||SUBSTR(s,21,4)||'/'
                  ||SUBSTR(s,25,4)||'/'
                  ||SUBSTR(s,29,4)||'/' INTO ss
        FROM mind.filsystemsmonteringar
        WHERE uuid_version = 4;
    ELSE -- uuid_v = 3
        SELECT lpad(dokument_id::text, 10,'0') into s FROM MIND.DOKUMENT where dokument_uuid = DUID;
        SELECT monteringspunkt
                  ||SUBSTR(s,1,3)||'/'
                  ||SUBSTR(s,4,3)||'/'
                  ||SUBSTR(s,7,2)||'/'
                  ||s||'/' INTO ss
        FROM mind.filsystemsmonteringar
        WHERE uuid_version = 3;
    END IF;
    RETURN ss;
end;
$$
LANGUAGE plpgsql
IMMUTABLE
RETURNS NULL ON NULL INPUT;

这可能不是最有效的方法，但使用 \copy 将数百万行加载到表中执行得不错。

时不时有类似的条目：

2020-10-06 12:36:17.078 UTC [27] LOGG:  checkpoint starting: time
2020-10-06 12:38:47.123 UTC [27] LOGG:  checkpoint complete: wrote 689 buffers (4.2%); 0 WAL file(s) added, 0 removed, 10 recycled; write=149.943 s, sync=0.000 s, total=150.045 s; sync files=79, longest=0.000 s, average=0.000 s; distance=163338 kB, estimate=163338 kB
2020-10-06 12:41:17.223 UTC [27] LOGG:  checkpoint starting: time
2020-10-06 12:41:17.743 UTC [27] LOGG:  checkpoint complete: wrote 5 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.503 s, sync=0.013 s, total=0.519 s; sync files=4, longest=0.012 s, average=0.003 s; distance=369 kB, estimate=147041 kB

在日志中。据我所知，Postgres 配置并没有做太多事情。我不得不增加 shm，但除此之外，它是普通的香草。我不确定哪些内存区域/配置可以提高 pg_restore 的性能。正如你所知道的，我是 postgres 的新手（在 docker 上做一个白痴似乎也无济于事 ;-)。因此，任何有关可能导致此“冻结”的原因或在哪里寻找更多信息的提示将不胜感激。

编辑：在@laurenz-albe 提供的帮助下，我可以取消较旧的恢复，瞧，剩余的恢复在几分钟内完成。

然后我删除了数据库，再次创建它并检查了以下查询，结果为 0 行：

select pid, application_name, backend_start, state_change, backend_type 
from pg_stat_activity 
where datname = 'testdb';

然后我像以前一样开始新的恢复并再次检查查询。结果是 1 个客户端后端和 2 个并行工作人员，工作人员似乎不时重启，但恢复似乎卡住了。

查看查询：

select backend_type, query 
from pg_stat_activity 
where datname = 'testdb';

表明：

client backend  | COPY mind.filer (dokument_uuid, sidnummer, filtyp, tillstand, filnamn) FROM stdin;         +
                 | 
 parallel worker | SELECT lpad(dokument_id::text, 10,'0')        FROM MIND.DOKUMENT where dokument_uuid = DUID
 parallel worker | SELECT lpad(dokument_id::text, 10,'0')        FROM MIND.DOKUMENT where dokument_uuid = DUID

因此工作人员正在从生成的列中使用的函数运行查询。pg_restore 是否有可能由于对另一个表的依赖而锁定自己？

是否可以在 \copy 期间让 pg_restore 转为生成的表达式？在转储中已经存在所有值之后，我得到的印象是这是默认值。

杀死工人后：

postgres=# select pg_cancel_backend(pid) 
    from pg_stat_activity 
    where datname = 'testdb' and backend_type = 'parallel worker';
 pg_cancel_backend 
-------------------
 t
 t
(2 rader)

恢复继续，但恢复后表为空

postgresql

1 个回答

  1. Best Answer

    您被长期运行的事务持有的锁所困。检查pg_stat_activity以查找长期运行的事务并查看pg_locks有关谁锁定了什么的详细信息。

    如果您找不到更好的方法，请使用该pg_cancel_backend功能终止阻塞事务。

    我认为这个问题的核心是函数 thst 生成列，该列从另一个表中选择。这看起来很奇怪——这不是说生成的列在filsystemsmonteringar修改时会出错吗？pg_restore无论如何，如果一个人从一个表中选择另一个ALTERs ，我可以很容易地想象工作之间的死锁。

    • 1

相关问题