创建新的数据库
|
1 2 3 |
psql -U postgres -c "CREATE DATABASE $DB_NAME;" |
创建新的用户
|
1 2 3 |
psql -U postgres -c "CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';" |
将新数据库的所有权限授予新用户
|
1 2 3 |
psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;" |
删除数据库
|
1 2 3 |
psql -U postgres -c "DROP DATABASE IF EXISTS $DB_NAME;" |
删除用户
|
1 2 3 |
psql -U postgres -c "DROP USER IF EXISTS $DB_USER;" |
-EOF-
如果在 WordPress Site Health 页面遇到 “Some files are not writable by WordPress”, 可以通过如下的方式解决：
1. 检查 web server 所使用的 user 账号， 比如 nginx:
|
1 2 3 4 5 6 7 8 |
cat /etc/nginx/nginx.conf user www-data; worker_processes auto; pid /run/nginx.pid; error_log /var/log/nginx/error.log; include /etc/nginx/modules-enabled/*.conf; |
如上 nginx 所使用的 user 为 www-data.
2. 将 WordPress 的文件 Owner 更改成 web server user, 比如：
|
1 2 3 |
chown -R www-data:www-data /the/folder/place/the/wordpress/ |
重新刷新页面即可。
-EOF-
Unix/Linux在默认情况下在网络包转发是处于禁用状态的，在安装了 WireGuard 等网络流量转发软件后，需要开启IP包转发才能正常的处理来自客户的流量转发请求。
以下命令可以查询当前的 ip.forwarding 标记状态, 0表示已禁用，1表示已开启。
|
1 2 3 4 |
sysctl net.inet.ip.forwarding sysctl net.inet6.ip6.forwarding |
我们可以通过设置该值为1开启IP包转发功能。
|
1 2 3 4 |
sysctl net.inet.ip.forwarding=1 sysctl net.inet6.ip6.forwarding=1 |
-EOF-
之前时不时会收到 Google Search Console 发来的邮件告知在索引页面的时候遇到了5xx，一直都没有管。 直到上周我自己重现了一次才开始重视起来。
Search Console has identified that your site is affected by 1 Page indexing issue(s). The following issues were found on your site.
Top Issues
Server error (5xx)
We recommend that you fix these issues when possible to enable the best experience and coverage in Google Search.
检查日志发现如下内容：
2023/12/12 01:23:45 [error] 2175086#0: *57111 connect() to unix:/run/php-fpm/www.sock failed (11: Resource temporarily unavailable) while connecting to upstream, client: 192.3.114.12, server: example.com, request: "GET / HTTP/1.1", upstream: "fastcgi://unix:/run/php-fpm/www.sock:", host: "example.com", referrer: "http://example.com/"
于是跑了一遍压力测试，发现将近一半的请求都遇到5xx错误，同时日志中产生如上记录。
wrk -t12 -c400 -d30s https://ioio.name
便因此做了一下研究，主要参考 11: Resource temporarily unavailable, while connecting to upstream + Bad Gateway (Nginx)
通过执行如下命令调整了 net.core.somaxconn 及 net.core.netdev_max_backlog
echo "net.core.somaxconn = 65535" | sudo tee -a /etc/sysctl.conf
sudo sysctl -p
echo "net.core.netdev_max_backlog = 65535" | sudo tee -a /etc/sysctl.conf
sudo sysctl -p
更新nginx配置文件，给 FastCGI service 添加
|
1 2 3 |
fastcgi_keep_conn on; |
然后重启服务
|
1 2 3 4 |
systemctl restart php-fpm.service systemctl restart nginx |
再次运行压力测试，错误消失。
wrk -t12 -c400 -d30s https://ioio.name
Running 30s test @ https://ioio.name
12 threads and 400 connections
Thread Stats Avg Stdev Max +/- Stdev
Latency 180.51ms 88.60ms 1.91s 89.36%
Req/Sec 109.17 32.60 212.00 71.29%
39114 requests in 30.08s, 1.86GB read
Socket errors: connect 158, read 0, write 0, timeout 0
Requests/sec: 1300.31
Transfer/sec: 63.29MB
-EOF-
1. Update all available repository and upgrade all packages to the latest
|
1 2 3 4 |
pkg update pkg upgrade |
2. Install PostgreSQL 13
|
1 2 3 |
pkg install postgresql13-server postgresql13-client |
3. Add the PostgreSQL to the system boot:
|
1 2 3 |
sysrc postgresql_enable=yes |
4. Initialize the PostgreSQL database
|
1 2 3 |
/usr/local/etc/rc.d/postgresql initdb |
5. Start the PostgreSQL service and check its status
|
1 2 3 4 |
service postgresql start service postgresql status |
-EOF-