Java实现postgre数据库每日定时自动备份

前提：该备份仅为同数据库不同schema备份

假设需要备份的数据库为test，schema为public。代码如下

 public void backupAllTables() {
        log.info("备份全表开始执行" + System.currentTimeMillis());
        String origScheme1 = "public";
        String origScheme = "\"" + "public" + "\"";

        //获取数据库中所有的表
        List<String> allTable = SQL1.getAllTable(origScheme1);
        log.info(String.join(",", allTable));
        String date = date();
        //备份出来的样式为 b20230818
        String schemeName = "\"b" + date + "\"";
        String fiveDaysAgo = "\"b" + date_Plus(-5, "yyyyMMdd") + "\"";
        //删除5天前的这个备份
        SQL2.dropSchemeIfExist(fiveDaysAgo);
        //删除今天的备份避免重复备份
        SQL2.dropSchemeIfExist(schemeName);
        SQL3.createScheme(schemeName);
        if (!CollectionUtils.isEmpty(allTable)) {
            List<String> list = Arrays.asList("不需要备份的表1", "2023年8月18日发表于CSDN", "不需要备份的表2", "不需要备份的表3", "CSDN作者 weixin_43620015");
            allTable.removeAll(list);
            for (String t : allTable) {
                String finalT = "\"" + t + "\"";
                //此方法为从原SCHEMA复制到新的SHHEMA
                SQL4.copyTable(origScheme, schemeName, finalT, finalT);
            }
        }
        log.info("备份全表执行完毕" + System.currentTimeMillis());
    }
//下面两个方法为工具类
//2023年8月18日 17:34:49 CSDN weixin_43620015
    public static String date() {
        LocalDateTime localDateTime = LocalDateTime.now();
        String res;
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMMdd");
        res = dateTimeFormatter.format(localDateTime);
        return res;
    }

    public static String date_Plus(int num, String pattern) {
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime localDateTime = now.plusDays(-5);
        String res;
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern(pattern);
        res = dateTimeFormatter.format(localDateTime);
        return res;
    }

代码中对应位置的SQL语句

– SQL1

SELECT   c.relname AS tablename FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 	WHERE ((c.relkind = 'r'::"char") OR (c.relkind = 'f'::"char") OR (c.relkind = 'p'::"char"))
 	AND n.nspname = #{schemeName}

SQL2 注意其中要为$符号

DROP SCHEMA if EXISTS ${schemeName}  CASCADE;

SQL3 注意其中要为$符号

CREATE SCHEMA  ${schemeName} ;

SQL4 注意其中要为$符号SQL4为主要SQL

drop table if EXISTS  ${news}.${newt}  CASCADE;
create table  ${news}.${newt} (like  ${olds}.${oldt} including all);
insert into   ${news}.${newt} SELECT * from ${olds}.${oldt} ;

效果图如下

新的SCHEMA中包含所有的表