PostgreSQL定时清理旧数据的实现方法


    目录
  • 前言
  • 环境说明
    • 1、对原来的docker—compose进行修改
    • 2、pgadmin基本操作
    • 3、安装pgagent插件（重点）
    • 4、定时任务的开启和清理的sql语句
  • 尾声

    前言
    最近leader觉得数据库中每日数据不需要都保持，只需要保留30天的，就让我去研究下。坑是真的大，最后才勉强实现，故记录一下。
    技术涉及：docker docker-conpose SQL pgadmin4 pgagent
    环境说明
    我们的数据库是部署是使用docker-conponse直接部署在服务器上的，默认为debain。
    postgre为14，所以下面默认你已经在docker中安装了数据库
    1、对原来的docker—compose进行修改
    在原来的基础上添加：
    
pgadmin: 
    container_name: pgadmin4_container 
    image: dpage/pgadmin4 
    restart: always 
    environment: 
        PGADMIN_DEFAULT_EMAIL: <admin@admin.com> PGADMIN_DEFAULT_PASSWORD: root 
    ports: - "5050:80"

    添加完毕后，重启下容器
    
docker-compose up -d

    这个时候我们访问下服务器ip:5050就能看到一个可视化的数据库管理页面
    
    记得在postgres中添加以下sql代码
    
CREATE OR REPLACE FUNCTION delete_old_data() RETURNS void AS $$
DECLARE
tbl_name record;
table_date date;
BEGIN
FOR tbl_name IN SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE 'position_record_%' AND table_name ~ '\d{8}' LOOP
table_date := to_date(substring(tbl_name.table_name from '\d{8}'), 'YYYYMMDD');
IF table_date < current_date - 30 THEN
EXECUTE format('DROP TABLE IF EXISTS public.%I', tbl_name.table_name);
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

    2、pgadmin基本操作
    输入账号和密码：admin@admin.com、root
    登陆成功
    
    （修改中文为：左上角-FILE-Preferences-User language-Chinese）
    右击Servers-创建-服务器
    输入对应的ip账号密码，就可以看到自己的数据库了。
    3、安装pgagent插件（重点）
    你需要进入postgres容器、环境配置、安装pgagent、pgagent配置
    
docker-compose exec pgsql bash # 进入容器
apt-get update # 更新源
apt install vim # 安装vim工具
vim /etc/apt/sources.list # 进入系统源配置文件把原来的注释了添加

deb <http://mirrors.aliyun.com/debian/> bullseye main deb 
<http://mirrors.aliyun.com/debian-security> bullseye-security main deb 
<http://mirrors.aliyun.com/debian> bullseye-updates main
# 修改成国内源

apt-get update
apt install pgagent # 安装pgagent
psql -U postgres -h localhost # 创建db扩展
CREATE EXTENSION pgagent; 
\q 
exit
# 在postgres数据库上启用pgagent:
pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres password=***** 
# 和你的docker-conpose保持一致就好

    如果输入pgagent hostaddr...指令没有报错的话就安装插件完毕了。
    这个时候你再去访问pgadmin就可以看到
    
    即pgagent配置完毕！
    4、定时任务的开启和清理的sql语句
    鼠标右击pgAgent任务-创建-pgAgent作业...
    
    输入名称后，点击步骤
    
    由于我是本地所以连接字符串不需要（按照你的需求来） 在代码中添加
    
SELECT delete_old_data();

    这个清理的具体逻辑按照你实际情况来
    
    
    
    这样就是一天执行一次，按照你的需求来，如果是小时为00分钟为30，那就是到30分钟就清理一次（一小时）
    点击保存就好了。
    
    这里可以看到配置的状态
    尾声
    我就是简单配置下，看看会不会生效。如果可以的话，我将把定时备份数据库的操作也放到上面去。如果我上的有错，也会修改。
相关文章！