Pleroma | 维护日志(二)

Posted on May 15, 2023

前情：postgreSQL 数据库达到了惊人的 15G，把硬盘撑爆了且抢修无果。这是炸站重来后的运维日志。Pleroma | 维护日志(一) 已记录的内容不再重复

数据库相关

登录数据库，报错：psql: error: FATAL: Peer authentication failed for user “postgres”

按照 https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge

# 修改配置文件（改成 md5 也可以，但是 trust 能免密登录）
vim /etc/postgresql/13/main/pg_hba.conf
+ local   all             postgres                                trust
# 重启数据库
service postgresql restart

@debula 和 @Seviche 帮忙提取了我的远程缓存 orz，记录一下：

今天尝试了一下，在 JSON 中可以使用 ->> 来提取 jsonb 的 key 对应的 value，比如：

select data from activities where data->>'actor' = 'https://ponderduck.cc/users/yaya' order by data->>'published'

可以提取用户所有的 activities，再 join 一下 objects 就得到了结果：

SELECT a."actor", a."data"->>'type' AS type, a."updated_at", o."data" 
FROM "activities" AS a 
INNER JOIN "objects" AS o
ON (o."data"->>'id') = COALESCE(a."data"->'object'->>'id', a."data"->>'object')
WHERE a."data"->>'actor' = 'https://ponderduck.cc/users/yaya'
OR a."data"->'to' ? 'https://ponderduck.cc/users/yaya'
OR a."data"->'cc' ? 'https://ponderduck.cc/users/yaya'
ORDER BY a."updated_at" LIMIT 5;

最后再 copy 到本地目录就好了～

copy(SELECT ...) to '/var/lib/postgresql/yaya.csv' (FORMAT 'csv', DELIMITER ',', HEADER true, NULL 'UNKOWN', ENCODING 'UTF8');

我导出的包括收藏和别人的互动（回复/收藏/转发），如果只要创建的话：

pleroma=# select count(data) from activities where data->>'actor' = 'https://ponderduck.cc/users/yaya' and data->>'type' = 'Create';
 count
\-------
  1624
(1 row)

也可以看这篇超棒的博客：社交网络：Pleroma

定期清理数据库的 objects

磁盘

自动清理 tmp 目录

# 打开系统级别的 crontab
crontab -e
# 在文件末尾粘贴保存以下命令（每天 00:00 删除 最近三天未使用并不属于 root 用户的文件）
0 0 * * * sudo find /tmp -type f ! -user root -atime +3 -delete

[xxx@xxx ~]# psql -U postgres
psql (13.11 (Debian 13.11-0+deb11u1))
Type "help" for help.

postgres=# SELECT pg_size_pretty( pg_database_size('pleroma') );
 pg_size_pretty 
----------------
 576 MB
(1 row)

Admin-FE

settings/Logger：把日志级别改为 :warn

参考 & 致谢

Linux 临时目录 /tmp 与 /var/tmp

https://navicat.com/en/company/aboutus/blog/673-design-select-queries-using-navicat-s-query-builder