search

DIYgod / RSSHub

🧡 Everything is RSSible
https://docs.rsshub.app
MIT License
32.81k stars 7.31k forks source link

hexo的next主题docker部署异常，手动部署正常 #10635

Closed sincatter closed 1 year ago

sincatter commented 2 years ago

路由地址

/hexo/next/:url

完整路由地址

/hexo/next/blog.japinli.top

相关文档

https://docs.rsshub.app/blog.html#hexo

预期是什么？

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<rss xmlns:atom="http://www.w3.org/2005/Atom" version="2.0">
<channel>
<title>
<![CDATA[ Japin ]]>
</title>
<link>http://blog.japinli.top</link>
<atom:link href="http://127.0.0.1:1200/hexo/next/blog.japinli.top" rel="self" type="application/rss+xml"/>
<description>
<![CDATA[ Japin - Made with love by RSSHub(https://github.com/DIYgod/RSSHub) ]]>
</description>
<generator>RSSHub</generator>
<webMaster>i@diygod.me (DIYgod)</webMaster>
<language>zh-cn</language>
<lastBuildDate>Sun, 28 Aug 2022 01:53:12 GMT</lastBuildDate>
<ttl>5</ttl>
<item>
<title>
<![CDATA[ PostgreSQL 文本搜索字典导致奔溃 ]]>
</title>
<description>
<![CDATA[ <p>今天朋友在群里发了一个关于文本搜索字典导致崩溃的问题，如下所示：</p> <figure class="highlight sql"><table><tbody><tr><td class="gutter"><pre><span
........

实际发生了什么？

Looks like something went wrong
Route requested: /hexo/next/blog.japinli.top
Error message: key.startsWith is not a function
Helpful Information to provide when opening issue:
Path: /hexo/next/blog.japinli.top
Node version: v16.17.0
Git Hash: f806d0c

部署

RSSHub 演示 (https://rsshub.app)

部署相关信息

docker-compose version 1.29.2, build unknown 5.15.0-46-generic #49-Ubuntu SMP Thu Aug 4 18:03:25 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

额外信息

docker-compose部署/rsshub.app均返回报错
但是用git仓库手动部署则正常返回结果，且查看仓库js文件解析无异常

这不是重复的 issue

github-actions[bot] commented 2 years ago
Searching for maintainers:

To maintainers: if you are not willing to be disturbed, list your username in scripts/workflow/test-issue/call-maintainer.js. In this way, your username will be wrapped in an inline code block when tagged so you will not be notified.

如果有任何路由无法匹配，issue 将会被自动关闭。如果 issue 和路由无关，请使用 NOROUTE 关键词，或者留下评论。我们会重新审核。 If there is any route not found, the issue will be closed automatically. Please use NOROUTE for a route-irrelevant issue or leave a comment if it is a mistake.

TonyRL commented 1 year ago

/test

/hexo/next/blog.japinli.top
github-actions[bot] commented 1 year ago

Successfully generated as following:

http://localhost:1200/hexo/next/blog.japinli.top - Success ✔️ ```rss <![CDATA[Japin]]> http://blog.japinli.top RSSHub i@diygod.me (DIYgod) zh-cn Wed, 27 Sep 2023 18:14:07 GMT 5 <![CDATA[PostgreSQL ICU 回归测试失败]]> 最近遇到一个比较有意思的问题，我在运行 PostgreSQL 的回归测试时发现 collate.icu.utf8 这个测试用例在 REL_14_STABLE 分支始终无法跑过，但是在 REL_15_STABLE 却可以正常运行。同时由于 collate.icu.utf8 失败还将导致 foreign_data 测试用例也失败了。

下面是详细的错误信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
diff -U3 /home/japin/Codes/postgres/build/../src/test/regress/expected/collate.icu.utf8.out /home/japin/Codes/postgres/build/src/test/regress/results/collate.icu.utf8.out
--- /home/japin/Codes/postgres/build/../src/test/regress/expected/collate.icu.utf8.out2023-08-14 17:37:31.960448245 +0800
+++ /home/japin/Codes/postgres/build/src/test/regress/results/collate.icu.utf8.out2023-08-14 21:30:44.335214886 +0800
@@ -1035,6 +1035,9 @@
           quote_literal(current_setting('lc_ctype')) || ');';
 END
 $$;
+ERROR:  collations with different collate and ctype values are not supported by ICU
+CONTEXT:  SQL statement "CREATE COLLATION test1 (provider = icu, lc_collate = 'C.UTF-8', lc_ctype = 'en_US.UTF-8');"
+PL/pgSQL function inline_code_block line 3 at EXECUTE
 CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, need lc_ctype
 ERROR:  parameter "lc_ctype" must be specified
 CREATE COLLATION testx (provider = icu, locale = 'nonsense'); /* never fails with ICU */  DROP COLLATION testx;
@@ -1045,13 +1048,12 @@
  collname
 ----------
  test0
- test1
  test5
-(3 rows)
+(2 rows)

 ALTER COLLATION test1 RENAME TO test11;
+ERROR:  collation "test1" for encoding "UTF8" does not exist
 ALTER COLLATION test0 RENAME TO test11; -- fail
-ERROR:  collation "test11" already exists in schema "collate_tests"
 ALTER COLLATION test1 RENAME TO test22; -- fail
 ERROR:  collation "test1" for encoding "UTF8" does not exist
 ALTER COLLATION test11 OWNER TO regress_test_role;
@@ -1059,18 +1061,19 @@
 ERROR:  role "nonsense" does not exist
 ALTER COLLATION test11 SET SCHEMA test_schema;
 COMMENT ON COLLATION test0 IS 'US English';
+ERROR:  collation "test0" for encoding "UTF8" does not exist
 SELECT collname, nspname, obj_description(pg_collation.oid, 'pg_collation')
     FROM pg_collation JOIN pg_namespace ON (collnamespace = pg_namespace.oid)
     WHERE collname LIKE 'test%'
     ORDER BY 1;
  collname |    nspname    | obj_description
 ----------+---------------+-----------------
- test0    | collate_tests | US English
  test11   | test_schema   |
  test5    | collate_tests |
-(3 rows)
+(2 rows)

 DROP COLLATION test0, test_schema.test11, test5;
+ERROR:  collation "test0" for encoding "UTF8" does not exist
 DROP COLLATION test0; -- fail
 ERROR:  collation "test0" for encoding "UTF8" does not exist
 DROP COLLATION IF EXISTS test0;
@@ -1078,10 +1081,17 @@
 SELECT collname FROM pg_collation WHERE collname LIKE 'test%';
  collname
 ----------
-(0 rows)
+ test11
+ test5
+(2 rows)

 DROP SCHEMA test_schema;
+ERROR:  cannot drop schema test_schema because other objects depend on it
+DETAIL:  collation test_schema.test11 depends on schema test_schema
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
 DROP ROLE regress_test_role;
+ERROR:  role "regress_test_role" cannot be dropped because some objects depend on it
+DETAIL:  owner of collation test_schema.test11
 -- ALTER
 ALTER COLLATION "en-x-icu" REFRESH VERSION;
 NOTICE:  version has not changed
diff -U3 /home/japin/Codes/postgres/build/../src/test/regress/expected/foreign_data.out /home/japin/Codes/postgres/build/src/test/regress/results/foreign_data.out
--- /home/japin/Codes/postgres/build/../src/test/regress/expected/foreign_data.out2023-08-14 17:37:31.964448260 +0800
+++ /home/japin/Codes/postgres/build/src/test/regress/results/foreign_data.out2023-08-14 21:30:55.571170376 +0800
@@ -5,10 +5,13 @@
 -- Suppress NOTICE messages when roles don't exist
 SET client_min_messages TO 'warning';
 DROP ROLE IF EXISTS regress_foreign_data_user, regress_test_role, regress_test_role2, regress_test_role_super, regress_test_indirect, regress_unprivileged_role;
+ERROR:  role "regress_test_role" cannot be dropped because some objects depend on it
+DETAIL:  owner of collation test_schema.test11
 RESET client_min_messages;
 CREATE ROLE regress_foreign_data_user LOGIN SUPERUSER;
 SET SESSION AUTHORIZATION 'regress_foreign_data_user';
 CREATE ROLE regress_test_role;
+ERROR:  role "regress_test_role" already exists
 CREATE ROLE regress_test_role2;
 CREATE ROLE regress_test_role_super SUPERUSER;
 CREATE ROLE regress_test_indirect;

分析

从上面的错误信息我们可以看到回归测试在执行 CREATE COLLATION test1 (provider = icu, lc_collate = 'C.UTF-8', lc_ctype = 'en_US.UTF-8'); 语句时出现了非期望的情况。通过查看 collate.icu.utf8.out 文件，我们可以定位到如下内容：

1
2
3
4
5
6
7
8
do $$
BEGIN
  EXECUTE 'CREATE COLLATION test1 (provider = icu, lc_collate = ' ||
          quote_literal(current_setting('lc_collate')) ||
          ', lc_ctype = ' ||
          quote_literal(current_setting('lc_ctype')) || ');';
END
$$;

测试用例使用 current_setting() 来获取 lc_collate 和 lc_ctype 的值，并且期望这两个值是相同的，但是在实际的回归测试时，lc_collate 为 C.UTF-8 而 lc_ctype 为 en_US.UTF-8，从而导致测试用例失败。

然而，当我尝试使用 make installcheck 运行回归测试时，一切又都正常了。

考虑到这个是和 locale 相关，因此我确认了一下该环境下的 locale 设置。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
$ locale
LANG=C.UTF-8
LANGUAGE=
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

可以看到 LANG 的设置与其他的不同，我尝试用 export LANG=en_US.UTF-8 重新导出环境变量，然后在执行 make check，此时一切就正常了。

从结果来看，回归测试的失败是由于 LANG 的设置不合理导致的。那么为什么 REL_15_STABLE 没有遇到这个情况呢？

如下所示，查看 REL_15_STABLE 的 collate.icu.utf8.out 测试，可以发现它将 lc_collate 和 lc_ctype 合并为 locale 了，因此没有导致回归测试失败。

1
2
3
4
5
6
7
$ git show origin/REL_15_STABLE:src/test/regress/expected/collate.icu.utf8.out | grep -A 3 -B 2 'CREATE COLLATION test1'
do $$
BEGIN
  EXECUTE 'CREATE COLLATION test1 (provider = icu, locale = ' ||
          quote_literal(current_setting('lc_collate')) || ');';
END
$$;

这是 PG 15 开发分支的 f2553d43 提交引入的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 242a7ce6b7..b0ddc7db44 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -366,13 +366,11 @@ $$;
 CREATE COLLATION test0 FROM "C"; -- fail, duplicate name
 do $$
 BEGIN
-  EXECUTE 'CREATE COLLATION test1 (provider = icu, lc_collate = ' ||
-          quote_literal(current_setting('lc_collate')) ||
-          ', lc_ctype = ' ||
-          quote_literal(current_setting('lc_ctype')) || ');';
+  EXECUTE 'CREATE COLLATION test1 (provider = icu, locale = ' ||
+          quote_literal(current_setting('lc_collate')) || ');';
 END
 $$;
-CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, need lc_ctype
+CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, needs "locale"
 CREATE COLLATION testx (provider = icu, locale = 'nonsense'); /* never fails with ICU */  DROP COLLATION testx;

 CREATE COLLATION test4 FROM nonsense;

那么为什么 LANG 会影响到数据库的 lc_collate 和 lc_ctype 呢？在我的环境变量中又是配置了 LC_COLLATE 和 LC_CTYPE 的，同时也可以注意到 LC_ALL 也配置了。

关于环境变量 LANG，LC_ALL 和 LC_* 的优先级如下所示：

The environment variables LANG, LC_ALL, LC_COLLATE, LC_CTYPE, LC_MESSAGES, LC_MONETARY, LC_NUMERIC, LC_TIME, (LC_*), and NLSPATH provide for the support of internationalised applications. The standard utilities make use of these environment variables as described in this section and the individual ENVIRONMENT VARIABLES sections for the utilities. If these variables specify locale categories that are not based upon the same underlying codeset, the results are unspecified.

The values of locale categories are determined by a precedence order; the first condition met below determines the value:

  1. If the LC_ALL environment variable is defined and is not null, the value of LC_ALL is used.
  2. If the LC_* environment variable ( LC_COLLATE, LC_CTYPE, LC_MESSAGES, LC_MONETARY, LC_NUMERIC, LC_TIME) is defined and is not null, the value of the environment variable is used to initialise the category that corresponds to the environment variable.
  3. If the LANG environment variable is defined and is not null, the value of the LANG environment variable is used.
  4. If the LANG environment variable is not set or is set to the empty string, the implementation-dependent default locale is used.

从上面我看可以看到，LC_ALL 的优先级是最高的，其次是 LC_*，最后是 LANG，那么为什么 make check 的时候会出现 LC_COLLATE 和 LC_CTYPE 不一致的情况呢？（这里更准确的说应该是 current_setting('lc_collate') 和 current_setting('lc_ctype') 不一致。）

待进一步分析。

解决办法

上面已经提到了如何解决这个问题，我们可以重新导出 LANG 环境变量，使其与 LC_ALL、LC_CTYPE 和 LC_COLLATE 一致。

参考

[1] https://pubs.opengroup.org/onlinepubs/7908799/xbd/envvar.html

笑林广记 -
]]> Tue, 15 Aug 2023 14:46:14 GMT http://blog.japinli.top/2023/08/postgresql-icu-regression-test-failed/ http://blog.japinli.top/2023/08/postgresql-icu-regression-test-failed/ <![CDATA[PostgreSQL 表空间备份]]> 本文简要记录一下 PostgreSQL 中关于表空间的备份，主要涉及到了 pg_basebackup 和 pg_probackup 两种备份方式。

环境介绍

下表是本次实验的基本环境，每个节点都安装了 PostgreSQL 15 及 pg_probackup 2.5.12。

IP 说明
10.9.10.106 数据库节点
10.9.10.109 备份节点

准备工作

首先，我们在数据库节点初始化数据库，并进行相关的配置。

1
2
3
4
5
6
7
8
9
10
11
$ initdb -k -D testdb
$ cat <<EOF >> testdb/pg_hba.conf
hostallall10.9.10.0/24scram-sha-256
hostreplicationall10.9.10.0/24scram-sha-256
EOF
$ cat <<EOF >> testdb/postgresql.auto.conf
listen_addresses = '*'
EOF

$ pg_ctl start -l log -D testdb
$ psql postgres -c "CREATE USER u01 WITH replication PASSWORD 'helloworld'"

为了确保 pg_probackup 能够正常运行，我们还需要授权 u01 用户对于 pg_backup_start()、pg_backup_stop() 和 pg_create_restore_point() 函数的可执行权限。

1
2
3
$ psql postgres -c "GRANT EXECUTE ON FUNCTION pg_backup_start TO u01"
$ psql postgres -c "GRANT EXECUTE ON FUNCTION pg_backup_stop TO u01"
$ psql postgres -c "GRANT EXECUTE ON FUNCTION pg_create_restore_point TO u01"

接着我们创建两个表空间，一个用于存储表、一个用于存储索引。

1
2
3
4
5
6
7
8
9
10
11
$ mkdir tblspc idxspc
$ psql postgres -c "CREATE TABLESPACE tblspc LOCATION '/home/japin/lab/tblspc'"
$ psql postgres -c "CREATE TABLESPACE idxspc LOCATION '/home/japin/lab/idxspc'"
$ psql postgres -c 'SELECT oid, spcname, pg_tablespace_location(oid) FROM pg_tablespace'
  oid  |  spcname   | pg_tablespace_location
-------+------------+------------------------
  1663 | pg_default |
  1664 | pg_global  |
 16389 | tblspc     | /home/japin/lab/tblspc
 16390 | idxspc     | /home/japin/lab/idxspc
(4 rows)

最后，使用 pgbench 导入数据。

1
2
3
4
5
6
7
8
9
10
11
12
$ pgbench -i -s 50 --tablespace tblspc --index-tablespace idxspc postgres
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
5000000 of 5000000 tuples (100%) done (elapsed 25.46 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 38.50 s (drop tables 0.01 s, create tables 0.01 s, client-side generate 25.71 s, vacuum 0.89 s, primary keys 11.89 s).

使用 pg_basebackup 备份恢复表空间

一切准备就绪，现在我们使用 pg_basebackup 来备份 PostgreSQL 表空间，在备份节点执行下面的命令：

1
2
3
4
5
6
7
8
$ pg_basebackup -h 10.9.10.106 -p 5432 -U u01 -D basebackupdb
Password:
$ ls -lh
total 16K
drwx------ 19 japin japin 4.0K Jun 20 18:42 basebackupdb
drwx------  3 japin japin 4.0K Jun 20 18:42 idxspc
-rw-rw-r--  1 japin japin  104 Jun 20 17:48 pg-env.sh
drwx------  3 japin japin 4.0K Jun 20 18:41 tblspc

可以看到表空间直接就备份过来了，无需任何额外的操作。我们将备库启动起来可以看到备份的数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
$ pg_ctl start -l log -D basebackupdb
waiting for server to start.... done
server started
$ psql postgres -c 'SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace'
  spcname   | pg_tablespace_location
------------+------------------------
 pg_default |
 pg_global  |
 tblspc     | /home/japin/lab/tblspc
 idxspc     | /home/japin/lab/idxspc
(4 rows)
$ psql postgres
[local]:29726 postgres=# SELECT count(1) from pgbench_accounts;
  count
---------
 5000000
(1 row)

如果我们想要将表空间备份到不同的路径，那么我们需要使用 --tablespace-mapping, -T 选项，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ pg_basebackup -h 10.9.10.106 -p 5432 -U u01 -D basebackupdb \
  -T /home/japin/lab/tblspc=/mnt/workspace/tblspc -T /home/japin/lab/idxspc=/mnt/workspace/idxspc
Password:
b$ pg_ctl start -l log -D basebackupdb
waiting for server to start.... done
server started
$ psql postgres -c 'SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace'
  spcname   | pg_tablespace_location
------------+------------------------
 pg_default |
 pg_global  |
 tblspc     | /mnt/workspace/tblspc
 idxspc     | /mnt/workspace/idxspc
(4 rows)

从上面可以看到表空间 tblspc, idxspc 分别指向了 /mnt/workspace/tblspc, /mnt/workspace/idxspc。

使用 pg_probackup 备份恢复表空间

首先，初始化备份目录。

1
2
$ pg_probackup init -B /mnt/workspace/BackupTest
INFO: Backup catalog '/mnt/workspace/BackupTest' successfully initialized

接着，创建备份实例（您需要先配置备份节点到数据库节点的 SSH 互信）。

1
2
3
$ pg_probackup add-instance -B /mnt/workspace/BackupTest --instance lab --pgdata /home/japin/lab/testdb \
  --remote-host 10.9.10.106 --remote-port 22 --remote-user japin --remote-path /opt/pg/bin
INFO: Instance 'lab' successfully initialized
  • --pgdata 指定数据库存储的路径
  • --remote-host 指定 SSH 连接地址
  • --remote-port 指定 SSH 连接端口
  • --remote-user 指定 SSH 连接用户
  • --remote-path 指定远端 pg_probackup 的路径

完成上述准备工作之后，现在我们可以执行备份操作了。这里我们以全备作为示例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ pg_probackup backup -B /mnt/workspace/BackupTest --instance lab --pgdata /home/japin/lab/testdb \
  --backup-mode FULL --stream --pghost 10.9.10.106 --pgport 5432 --pguser u01 --pgdatabase postgres \
  --remote-host 10.9.10.106 --remote-port 22 --remote-user japin --remote-path /opt/pg/bin
INFO: Backup start, pg_probackup version: 2.5.12, instance: lab, backup ID: RWSH8U, backup mode: FULL, wal mode: STREAM, remote: true, compress-algorithm: none, compress-level: 1
Password for user u01:
INFO: This PostgreSQL instance was initialized with data block checksums. Data block corruption will be detected
INFO: Database backup start
INFO: wait for pg_backup_start()
INFO: Wait for WAL segment /mnt/workspace/BackupTest/backups/lab/RWSH8U/database/pg_wal/000000010000000000000030 to be streamed
INFO: PGDATA size: 770MB
INFO: Current Start LSN: 0/30000028, TLI: 1
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 12s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: stop_lsn: 0/30000168
INFO: Getting the Recovery Time from WAL
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RWSH8U
INFO: Backup RWSH8U data files are valid
INFO: Backup RWSH8U resident size: 803MB
INFO: Backup RWSH8U completed

通过查看备份目录我们可以看到 pg_probackup 在备份的时候是将表空间的信息直接备份到了 pg_tblspc 目录下面。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ ls -al /mnt/workspace/BackupTest/backups/lab/RWSH8U/database/pg_tblspc/16389/PG_15_202209061/5/
total 656708
drwx------ 2 japin japin      4096 Jun 25 11:19 .
drwx------ 3 japin japin      4096 Jun 25 11:19 ..
-rw------- 1 japin japin 672137600 Jun 25 11:19 16403
-rw------- 1 japin japin    188416 Jun 25 11:19 16403_fsm
-rw------- 1 japin japin     24576 Jun 25 11:19 16403_vm
-rw------- 1 japin japin      8200 Jun 25 11:19 16404
-rw------- 1 japin japin     24576 Jun 25 11:19 16404_fsm
-rw------- 1 japin japin      8192 Jun 25 11:19 16404_vm
-rw------- 1 japin japin     24600 Jun 25 11:19 16406
-rw------- 1 japin japin     24576 Jun 25 11:19 16406_fsm
-rw------- 1 japin japin      8192 Jun 25 11:19 16406_vm
$ ls -al /mnt/workspace/BackupTest/backups/lab/RWSH8U/database/pg_tblspc/16390/PG_15_202209061/5/
total 109876
drwx------ 2 japin japin      4096 Jun 25 11:19 .
drwx------ 3 japin japin      4096 Jun 25 11:19 ..
-rw------- 1 japin japin     16400 Jun 25 11:19 16407
-rw------- 1 japin japin     32800 Jun 25 11:19 16409
-rw------- 1 japin japin 112446600 Jun 25 11:19 16411

最后，我们来测试恢复过程。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ pg_probackup restore -B /mnt/workspace/BackupTest --instance lab --pgdata $PWD/db-backup
INFO: Tablespace 16390 will be restored using old path "/home/japin/lab/idxspc"
INFO: Tablespace 16389 will be restored using old path "/home/japin/lab/tblspc"
INFO: Validating backup RWSH8U
INFO: Backup RWSH8U data files are valid
INFO: Backup RWSH8U WAL segments are valid
INFO: Backup RWSH8U is valid.
INFO: Restoring the database from backup at 2023-06-25 11:19:42+08
INFO: Start restoring backup files. PGDATA size: 802MB
INFO: Backup files are restored. Transfered bytes: 802MB, time elapsed: 1s
INFO: Restore incremental ratio (less is better): 100% (802MB/802MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 3s
INFO: Restore of backup RWSH8U completed.

可以看到表空间恢复到了 /home/japin/lab/idxspc 和 /home/japin/lab/tblspc，这与备份前的路径是一致的，我们启动数据库验证一下。

1
2
3
4
5
6
7
8
$ psql postgres -c 'SELECT oid, spcname, pg_tablespace_location(oid) FROM pg_tablespace';
  oid  |  spcname   | pg_tablespace_location
-------+------------+------------------------
  1663 | pg_default |
  1664 | pg_global  |
 16389 | tblspc     | /home/japin/lab/tblspc
 16390 | idxspc     | /home/japin/lab/idxspc
(4 rows)

同样地，我们可以在恢复的时候指定表空间的路径。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ pg_ctl stop -l log -D $PWD/db-backup
waiting for server to shut down.... done
server stopped
$ rm -rf db-backup/ idxspc/ tblspc/
$ rm -rf /mnt/workspace/{tblspc,idxspc}/*

$ pg_probackup restore -B /mnt/workspace/BackupTest --instance lab --pgdata $PWD/db-backup \
  --tablespace-mapping /home/japin/lab/tblspc=/mnt/workspace/tblspc \
  --tablespace-mapping /home/japin/lab/idxspc=/mnt/workspace/idxspc
INFO: Tablespace 16390 will be remapped from "/home/japin/lab/idxspc" to "/mnt/workspace/idxspc"
INFO: Tablespace 16389 will be remapped from "/home/japin/lab/tblspc" to "/mnt/workspace/tblspc"
INFO: Validating backup RWSH8U
INFO: Backup RWSH8U data files are valid
INFO: Backup RWSH8U WAL segments are valid
INFO: Backup RWSH8U is valid.
INFO: Restoring the database from backup at 2023-06-25 11:19:42+08
INFO: Start restoring backup files. PGDATA size: 802MB
INFO: Backup files are restored. Transfered bytes: 802MB, time elapsed: 2s
INFO: Restore incremental ratio (less is better): 100% (802MB/802MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 1s
INFO: Restore of backup RWSH8U completed.

从日志信息中可以看到 /home/japin/lab/idxspc 将被映射到 /mnt/workspace/idxspc，/home/japin/lab/tblspc 将被映射到 /mnt/workspace/tblspc。同样地，启动数据库服务器进行验证。

1
2
3
4
5
6
7
8
9
10
11
$ pg_ctl start -l log -D $PWD/db-backup
waiting for server to start.... done
server started
$ psql postgres -c 'SELECT oid, spcname, pg_tablespace_location(oid) FROM pg_tablespace';
  oid  |  spcname   | pg_tablespace_location
-------+------------+------------------------
  1663 | pg_default |
  1664 | pg_global  |
 16389 | tblspc     | /mnt/workspace/tblspc
 16390 | idxspc     | /mnt/workspace/idxspc
(4 rows)

总结

pg_basebackup 和 pg_probackup 均支持表空间的备份，它们的不同在于 pg_basebackup 在备份的时候需要指定表空间映射关系（在与源数据库不一致的情况下），而 pg_probackup 在备份时则不需要指定表空间映射，但是在恢复的时候需要提供映射关系（如果需要）。

笑林广记 - 鸽舌

有涩舌者，俗云鸽口是也。
来到市中买桐油，向店主曰：“我要买桐桐桐……”油字再也说不出口。
店主取笑曰：“你这人倒会打铜鼓，何不再敲通铜锣与我听？”
鸽者怒曰：“你不要当当当面来腾腾腾倒刮刮刮削我。”

]]> Tue, 20 Jun 2023 09:58:51 GMT http://blog.japinli.top/2023/06/postgresql-backup-tablespace/ http://blog.japinli.top/2023/06/postgresql-backup-tablespace/ <![CDATA[PostgreSQL SCRAM 认证密码迭代次数]]> 在上一篇文章中，我们聊到了 PostgreSQL 的认证机制，其中提到了 SCRAM-SHA-256 认证，在我写那篇文章的前两天，PostgreSQL 支持了 SCRAM 迭代次数的修改，它引入了一个 scram_iterations 参数来定义 SCRAM 的迭代次数。

用法

参数 scram_iterations 的默认值为 4096，这与 PostgreSQL 之前的硬编码值相同（同时也是 RFC 7677 里面定义的最小迭代次数）。如下所示，我们创建了三个用户，它们使用了不同的迭代次数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
[local]:889416 postgres=# \timing
Timing is on.
[local]:889416 postgres=# SET scram_iterations TO 1;
SET
Time: 0.533 ms
[local]:889416 postgres=# CREATE USER scram_1 WITH password 'hello';
CREATE ROLE
Time: 1.055 ms
[local]:889416 postgres=# SET scram_iterations TO 100000;
SET
Time: 0.320 ms
[local]:889416 postgres=# CREATE USER scram_4096 WITH password 'hello';
CREATE ROLE
Time: 9.182 ms
[local]:889416 postgres=# SET scram_iterations TO 100000;
SET
Time: 0.430 ms
[local]:889416 postgres=# CREATE USER scram_100000 WITH password 'hello';
CREATE ROLE
Time: 214.233 ms

从创建用户语句的执行时间上我们可以看到迭代次数越小、执行的速度越快。接着我们看看 pg_authid 表中 rolpassword 的存储。

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT rolname,
       regexp_replace(rolpassword,
                      '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+/=]+):([a-zA-Z0-9+/=]+)',
                      '\1$\2:<slat>$<storeKey>:<serverKey>') AS rolpassword
FROM pg_authid WHERE rolname ~ 'scram*';
   rolname    |                    rolpassword
--------------+----------------------------------------------------
 scram_1      | SCRAM-SHA-256$1:<slat>$<storeKey>:<serverKey>
 scram_4096   | SCRAM-SHA-256$4096:<slat>$<storeKey>:<serverKey>
 scram_100000 | SCRAM-SHA-256$100000:<slat>$<storeKey>:<serverKey>
(3 rows)

Time: 4.046 ms

可以看到 rolpassword 里面记录了 SCRAM 的迭代次数。

性能

SCRAM 的迭代次数越大，对抗暴力攻击的能力就越强，但是任何事情都是有两面性的；迭代次数越大，带来的问题就是连接的时候认证的时间越长（从上面创建用户的执行时间就可以看出端倪）。这里我们使用 pgbench 对不同的迭代次数进行连接测试。

首先创建一个测试文件用于 pgbench 执行，如下所示：

1
2
$ cat pgbench-empty.sql
\set a 10

接着我们需要修改 pg_hba.conf，确保其使用 SCRAM-SHA-256 认证方式，修改之后的结果如下所示（这里我使用的是本地连接）：

1
2
3
4
5
6
7
8
9
10
[local]:914251 postgres=# select * from pg_hba_file_rules ;
 rule_number |                        file_name                         | line_number | type  |   database    | user_name |  address  |                 netmask                 |  auth_method  | options | error
-------------+----------------------------------------------------------+-------------+-------+---------------+-----------+-----------+-----------------------------------------+---------------+---------+-------
           1 | /home/japin/Codes/postgresql/Debug/pg/pgdata/pg_hba.conf |         117 | local | {all}         | {all}     |           |                                         | scram-sha-256 |         |
           2 | /home/japin/Codes/postgresql/Debug/pg/pgdata/pg_hba.conf |         119 | host  | {all}         | {all}     | 127.0.0.1 | 255.255.255.255                         | scram-sha-256 |         |
           3 | /home/japin/Codes/postgresql/Debug/pg/pgdata/pg_hba.conf |         121 | host  | {all}         | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | scram-sha-256 |         |
           4 | /home/japin/Codes/postgresql/Debug/pg/pgdata/pg_hba.conf |         124 | local | {replication} | {all}     |           |                                         | trust         |         |
           5 | /home/japin/Codes/postgresql/Debug/pg/pgdata/pg_hba.conf |         125 | host  | {replication} | {all}     | 127.0.0.1 | 255.255.255.255                         | trust         |         |
           6 | /home/japin/Codes/postgresql/Debug/pg/pgdata/pg_hba.conf |         126 | host  | {replication} | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust         |         |
(6 rows)

接着我们需要配置用户的密码，您可以使用 ~/.pgpass 进行配置，这里为了简便，我们在创建用户的时候使用了相同的密码，因此使用 PGPASSWORD 环境变量。

1
$ export PGPASSWORD=hello

测试结果如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
$ pgbench -n -T 30 -f pgbench-empty.sql -C -U scram_1 postgres
pgbench (16beta1)
transaction type: pgbench-empty.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 4088
number of failed transactions: 0 (0.000%)
latency average = 7.340 ms
average connection time = 7.335 ms
tps = 136.248832 (including reconnection times)

$ pgbench -n -T 30 -f pgbench-empty.sql -C -U scram_4096 postgres
pgbench (16beta1)
transaction type: pgbench-empty.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 2106
number of failed transactions: 0 (0.000%)
latency average = 14.247 ms
average connection time = 14.241 ms
tps = 70.191579 (including reconnection times)

$ pgbench -n -T 30 -f pgbench-empty.sql -C -U scram_100000 postgres
pgbench (16beta1)
transaction type: pgbench-empty.sql
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 30 s
number of transactions actually processed: 153
number of failed transactions: 0 (0.000%)
latency average = 196.268 ms
average connection time = 196.258 ms
tps = 5.095077 (including reconnection times)

虽然这只是一个默认的数据库实例，但是不同迭代次数对数据库的性能影响还是比较大的，因此在使用的时候我们需要在安全性与性能之间做好权衡。

参考

[1] https://www.postgresql.org/docs/16/runtime-config-connection.html#GUC-SCRAM-ITERATIONS
[2] https://paquier.xyz/postgresql-2/postgres-16-scram-iterations/

笑林广记 - 臭辣梨

北地产梨甚佳，北人至南，索梨食，不得。
南人因进萝卜，曰：“此敝乡土产之梨也。”
北人曰：“此物吃下，转气就臭，味又带辣，只该唤他做臭辣梨。”

]]> W ```