前提：该备份仅为同数据库不同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();//备份出来的样式为 b20230818String 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复制到新的SHHEMASQL4.copyTable(origScheme, schemeName, finalT, finalT);}}log.info("备份全表执行完毕" + System.currentTimeMillis());}
//下面两个方法为工具类
//2023年8月18日 17:34:49 CSDN weixin_43620015public 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.relnamespaceWHERE ((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中包含所有的表