AWS S3 Static Website Hosting 404

公司的前端项目是以静态化网页的方式部署在aws s3上的, 由于前端项目是用react写的, 页面的路由是前端代码控制,页面实际是不存在于s3中的, 所以每次刷新网页的时候控制台会产生一条404错误, 研究了下这个错误可以在cloudfront那里修复.

解决办法:
Cloudfront->选择项目的cdn->Error Pages->Create custom error response

1
2
3
4
Http error code      -> 404
Custom error resonse -> Yes
Response page path -> /index.html
Http Response code -> 200:OK

Custom error response

Github Actions动态密钥名称

test.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
name: LS

...

jobs:
deploy:
runs-on: ubuntu-latest

steps:

- name: Update and restart server
uses: appleboy/ssh-action@v0.1.4
with:
host: ${{ secrets[format('{0}_HOST', github.workflow)] }}
username: ${{ secrets[format('{0}_USER', github.workflow)] }}

比如workflow的名称为LS, 密钥名称就为:

1
2
3
with:
host: ${{ secrets['LS_HOST'] }}
username: ${{ secrets['LS_USER'] }}

Spring Data JPA保存jsonb类型数据

创建表

1
2
3
4
5
6
7
CREATE TABLE test
(
id integer NOT NULL
CONSTRAINT test_pk
PRIMARY KEY,
test_data jsonb
);

定义方法

1
2
3
4
5
6
7
8
9
@Modifying
@Query(
value = "INSERT INTO test (id, test_data) VALUES (:id, CAST(:testData AS JSONB))",
nativeQuery = true
)
fun saveTest(
@Param("id") id: Long,
@Param("testData") testData: String
): Int

调用代码

1
2
3
4
5
6
7
val id = 1L
val testData = listOf("1", "2", "3")
val testDataJsonStr = ObjectMapper().writeValueAsString(testData)

transactionTemplate.execute {
repository.saveTest(id, testDataJsonStr)
}

Terraform connection ssh private key用法

base64 encode一下private key:

1
cat ~/.ssh/your_private_key | base64

connection中decode一下:

1
2
3
4
5
6
7
connection {
type = "ssh"
user = "admin"
private_key = base64decode(var.private_key)
host = self.public_ip_address
timeout = "3600s"
}

MIUI系统下Tasker保持后台常驻的方法

之前一直是用苹果的短信同步功能来同步短信的,期间老是出现一些莫名其妙不能同步短信的问题,最近实在是受不了这问题了,打算换到用android备用机来转发短信。

一开始用的是ifttt来同步短信的,一切运行正常,唯一的缺陷是iftt同步短信会有大概30秒左右时间的延时,有点接受不了。
后来开始用tasker来转发短信,尝试了下效果比ifttt好,几乎没有延时,但遇到一个问题就是tasker不能常驻后台,参考了官网的方案也没有解决这问题,研究了两天发现还有两个地方忘记了设置,在这里记录一下。

测试环境 MIUI 12.0.9

MIUI设置

将Tasker加到白名单

手机管家 -> 优化加速 -> 设置 -> 锁定任务 -> 将Tasker添加到已锁定任务

1.png
2.png
3.png
4.png

关闭智能场景省电

手机管家 -> 省电与电池 -> 场景配置 -> 睡眠模式 -> 关闭

a.png
b.png
c.png
d.png

其他设置

Terraform multiple line script example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
resource "null_resource" "cluster" {

connection {
type = "ssh"
user = "admin"
private_key = base64decode(var.private_key)
host = aws_lightsail_instance.lightsail.public_ip_address
timeout = "3600s"
}

provisioner "local-exec" {
command = <<EOT
echo "{\"host\":\"${random_string.subdomain.result}\",\"type\":\"A\",\"answer\":\"${aws_lightsail_instance.lightsail.public_ip_address}\",\"ttl\":300}" | \
curl "https://api.name.com/v4/domains/${var.name_domain}/records" \
-s \
-X POST \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-u "${var.name_username}":"${var.name_token}" \
-d @-
EOT
}

}

Chrome清除DNS缓存

  1. chrome://net-internals/#dns -> “Clear host cache”
  2. chrome://net-internals/#sockets -> “Flush socket pools”

macOS安装配置Podman 2.x

macOS环境下的podman只是客户端,server端实际运行在linux环境下,所以需要安装linux虚拟机。

安装

1
2
3
brew install virtualbox
brew install vagrant
brew install podman

配置虚拟机

新建个文件夹,里面创建Vagrantfile文件,内容如下:

1
2
3
4
5
6
7
8
Vagrant.configure("2") do |config|
config.vm.box = "generic/fedora32"
config.vm.hostname = "fedora"
config.vm.provider "virtualbox" do |vb|
vb.memory = "1024"
vb.cpus = 1
end
end

启动虚拟机

vagrant up

配置fedora

安装podman server端

1
2
3
4
sudo dnf -y install podman
systemctl --user enable --now podman.socket
sudo loginctl enable-linger $USER
sudo systemctl enable --now sshd

验证安装是否成功

1
podman --remote info

查看虚拟机ssh配置

vagrant ssh-config

podman客户端配置

添加连接

podman system connection add baude –identity /Users/chen/vm/fedora/.vagrant/machines/default/virtualbox/private_key ssh://vagrant@127.0.0.1:2222/run/user/1000/podman/podman.sock

替换docker

1
alias docker=podman

参考

某2ray白名单路由正确写法

之前一直用的Loyalsoldier的路由规则文件,路由是这么写的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
"routing": {
"rules": [
{
"type": "field",
"outboundTag": "direct",
"domain": [
"geosite:private",
"geosite:apple-cn",
"geosite:cn"
]
},
{
"type": "field",
"outboundTag": "direct",
"ip": [
"geoip:private",
"geoip:cn"
]
}
]
}

最近发现gstatic.com域名无法访问了,研究发现gstatic.com域名是包含在getsite:cn里面的,正确写法如下:

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
"routing": {
"rules": [
{
"type": "field",
"outboundTag": "Direct",
"domain": [
"geosite:private",
"geosite:tld-cn"
]
},
{
"type": "field",
"outboundTag": "Proxy",
"domain": [
"geosite:geolocation-!cn"
]
},
{
"type": "field",
"outboundTag": "Direct",
"domain": [
"geosite:cn"
]
}
]
}

SQL分页性能优化

JDBC的分页是通过offset+limit实现的,数据库是通过抓取所有数据并跳过前面n行来数据来实现offset功能的,越到后面跳过的数据越多offset越耗时:

1
2
3
4
5
6
7
CREATE TABLE players AS
SELECT generate_series(1, 10000000) AS id,
uuid_in(md5(random()::TEXT || clock_timestamp()::TEXT)::CSTRING) AS username,
floor(random() * 100) AS score;

CREATE UNIQUE INDEX players_id_uindex
ON players (id);

OFFSET + LIMIT:

1
2
3
4
5
SELECT * FROM players ORDER BY id OFFSET 10 LIMIT 10; // Execution Time: 0.034 ms
SELECT * FROM players ORDER BY id OFFSET 500000 LIMIT 10; // Execution Time: 115.706 ms
SELECT * FROM players ORDER BY id OFFSET 1000000 LIMIT 10; // Execution Time: 235.941 ms
SELECT * FROM players ORDER BY id OFFSET 5000000 LIMIT 10; // Execution Time: 1212.642 ms
SELECT * FROM players ORDER BY id OFFSET 9000000 LIMIT 10; // Execution Time: 2181.703 ms

解决办法就是用keyset pagination,用表里面一些唯一的列用来分页:

1
2
3
4
5
SELECT * FROM players WHERE id > 10 ORDER BY id LIMIT 10; // Execution Time: 0.033 ms
SELECT * FROM players WHERE id > 500000 ORDER BY id LIMIT 10; // Execution Time: 0.043 ms
SELECT * FROM players WHERE id > 1000000 ORDER BY id LIMIT 10; // Execution Time: 0.041 ms
SELECT * FROM players WHERE id > 5000000 ORDER BY id LIMIT 10; // Execution Time: 0.037 ms
SELECT * FROM players WHERE id > 9000000 ORDER BY id LIMIT 10; // Execution Time: 0.069 ms

实际情况中可能会以score排序:

创建索引

1
CREATE INDEX players_score_username_index ON players (score DESC, username DESC);

OFFSET + LIMIT

1
SELECT * FROM players ORDER BY score DESC, username DESC OFFSET 500000 LIMIT 10; //Execution Time: 1508.534 ms

Keyset pagination

1
2
3
4
5
6
7
SELECT * FROM players WHERE (score, username) < (94, 'fdf62b1a-5b4e-c561-38ad-74ac4f099a46') ORDER BY score DESC, username DESC LIMIT 10; //Execution Time: 0.077 ms


(score, username) < (94, 'fdf62b1a-5b4e-c561-38ad-74ac4f099a46')

// 效果等同于
score < 94 OR (score = 94 AND username < 'fdf62b1a-5b4e-c561-38ad-74ac4f099a46')

进阶

找出分页边界,这里的关键点是你要找出一列或者多列组成的唯一的列,如果你只按score排序的话,score会有重复的值,会导致排序结果不一致。

1
2
3
4
5
6
7
8
9
10
SELECT p.id,
p.username,
p.score,
CASE row_number()
OVER (ORDER BY p.score DESC , p.username DESC ) % 10
WHEN 0 THEN 1
ELSE 0
END page_boundary
FROM players AS p
ORDER BY p.score DESC, p.username DESC

查询结果:

id username score page_boundary
6603631 ffffa267-7102-d2e9-0316-572c0044ca4f 99 0
3647464 ffff44d0-f54a-9139-afb0-f3d188c79a77 99 0
422584 ffff378c-b897-2498-8133-9d88183e52ec 99 0
6213135 fffe9b37-c685-4d5f-7781-31489e3b04ea 99 0
7930940 fffbde40-2009-8faf-4423-d28116f8dfee 99 0
1078914 fffb2654-23d1-e7f4-cf83-d4298b3c7208 99 0
9984059 fffb0de3-366c-5d42-ce61-9f7ee197707b 99 0
798388 fffac15d-a418-83eb-9597-487aa0a9136b 99 0
9247069 fff8cea2-378d-7bb8-1954-3bc6e6971eee 99 0
1711860 fff79537-86b8-5bd1-a474-717f151e0448 99 1
4319563 fff786e7-75b4-5cd7-09c1-eb4dc690dbbc 99 0
6021293 fff774fb-cf17-d02e-96b0-15555cb36b39 99 0
987629 fff756f7-e76b-8e8b-9941-c39061dac542 99 0
149766 fff75386-890f-af96-1c4c-64df0f6f4546 99 0
5546133 fff6ebcb-f3fb-d4b3-6b33-b56c718d44e4 99 0
5167740 fff6ce5a-b4c1-5363-554b-a285b6917054 99 0
5931868 fff5d7a6-1b0d-cf87-e0df-2440a53a7e05 99 0
4577984 fff55361-9b07-dfc6-9d1a-5fc08cdec7a6 99 0
4013672 fff4c37a-7ddf-6042-6b8f-eb9d7f8ea691 99 0
7863996 fff47c1f-18d8-9980-72fb-e51213ff0f17 99 1

找出每一页的最后一条数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT p.id,
p.username,
p.score,
page_boundary,
row_number()
OVER (ORDER BY p.score DESC , p.username DESC) + 1 page_number
FROM (
SELECT p.id,
p.username,
p.score,
CASE row_number()
OVER (ORDER BY p.score DESC , p.username DESC ) % 10
WHEN 0 THEN 1
ELSE 0
END page_boundary
FROM players AS p
ORDER BY p.score DESC, p.username DESC
) AS p
WHERE p.page_boundary = 1;

查询结果:

id username score page_boundary page_number
1711860 fff79537-86b8-5bd1-a474-717f151e0448 99 1 2
7863996 fff47c1f-18d8-9980-72fb-e51213ff0f17 99 1 3
7016430 ffe9a2c3-f257-24f2-e6f1-f7219c3581ea 99 1 4
994965 ffe61629-a620-7fcb-af3f-4c2c15821b77 99 1 5
9267095 ffe1b897-24ac-61d0-a5c2-9103a43b4e77 99 1 6
936851 ffda271d-5c69-1a4b-061f-077d75b77f49 99 1 7
9772528 ffd521da-c0bf-9909-77a1-9bce8e2c39a6 99 1 8
3913178 ffccfdeb-5608-c02e-6dd8-fe372245ee2b 99 1 9
3757222 ffc54f77-1f21-d63c-d660-a35910625bfe 99 1 10
9150019 ffbe0ffe-db67-44e2-2e84-41d0b54467b6 99 1 11

所以如果我们要查询第二页的数据,执行下面的sql即可:

1
SELECT * FROM players WHERE (score, username) < (99, 'fff79537-86b8-5bd1-a474-717f151e0448') ORDER BY score DESC, username DESC;

查询结果:

id username score
4319563 fff786e7-75b4-5cd7-09c1-eb4dc690dbbc 99
6021293 fff774fb-cf17-d02e-96b0-15555cb36b39 99
987629 fff756f7-e76b-8e8b-9941-c39061dac542 99
149766 fff75386-890f-af96-1c4c-64df0f6f4546 99
5546133 fff6ebcb-f3fb-d4b3-6b33-b56c718d44e4 99
5167740 fff6ce5a-b4c1-5363-554b-a285b6917054 99
5931868 fff5d7a6-1b0d-cf87-e0df-2440a53a7e05 99
4577984 fff55361-9b07-dfc6-9d1a-5fc08cdec7a6 99
4013672 fff4c37a-7ddf-6042-6b8f-eb9d7f8ea691 99
7863996 fff47c1f-18d8-9980-72fb-e51213ff0f17 99

OFFSET + LIMIT:

1
SELECT * FROM players ORDER BY score DESC, username DESC OFFSET 10 LIMIT 10;

查询结果:

id username score
4319563 fff786e7-75b4-5cd7-09c1-eb4dc690dbbc 99
6021293 fff774fb-cf17-d02e-96b0-15555cb36b39 99
987629 fff756f7-e76b-8e8b-9941-c39061dac542 99
149766 fff75386-890f-af96-1c4c-64df0f6f4546 99
5546133 fff6ebcb-f3fb-d4b3-6b33-b56c718d44e4 99
5167740 fff6ce5a-b4c1-5363-554b-a285b6917054 99
5931868 fff5d7a6-1b0d-cf87-e0df-2440a53a7e05 99
4577984 fff55361-9b07-dfc6-9d1a-5fc08cdec7a6 99
4013672 fff4c37a-7ddf-6042-6b8f-eb9d7f8ea691 99
7863996 fff47c1f-18d8-9980-72fb-e51213ff0f17 99

参考