背景
mrdoc由python开发，运行时占用内存达300M+。
mindoc由golang开发，运行时占用内存不到100M。
就是在完成同样功能的情况下，节省内存。
迁移
需要使用代码。把mrdoc表中的数据添加一份到mindoc。
说明：mrdoc是mysql库，mindoc是pgsql库。
版本：
- mrdoc: 0.9.4
- mindoc: v2.2-beta.1
code
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.5.5</version>
</parent>
<groupId>me.vt.example</groupId>
<artifactId>transfermrdoc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<java.version>21</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>${postgresql.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<!-- <optional>true</optional>-->
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.14.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.18.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-collections4</artifactId>
<version>4.4</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<annotationProcessorPaths>
<path>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</path>
</annotationProcessorPaths>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>package me.vt.transfermrdoc;
import lombok.Data;
import java.io.Serializable;
@Data
public class ProjectPO implements Serializable {
private Long id;
private String name;
private String intro;
private String icon;
private int role;
}package me.vt.transfermrdoc;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
@Data
public class DocPO implements Serializable {
private Long id;
private String name;
private Long top_doc;
private String content;
private String pre_content;
private Long parent_doc;
private Date create_time;
private Date modify_time;
private int sort;
}package me.vt.transfermrdoc;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.postgresql.Driver;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.time.Duration;
import java.util.Comparator;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;
@Slf4j
public class TransferMrdocTest {
private static JdbcTemplate jdbcTemplateSource;
private static JdbcTemplate jdbcTemplateTarget;
private BeanPropertyRowMapper<ProjectPO> BPRM_ProjectPO = new BeanPropertyRowMapper<>(ProjectPO.class);
private BeanPropertyRowMapper<DocPO> BPRM_DocPO = new BeanPropertyRowMapper<>(DocPO.class);
private static void fillJdbcTemplateSource() {
String url = "jdbc:mysql://ip:3306/mrdoc?useUnicode=true&characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true";
String username = "root";
String passcode = "a";
HikariDataSource hikariDataSource = new HikariDataSource();
hikariDataSource.setDriverClassName(com.mysql.cj.jdbc.Driver.class.getName());
hikariDataSource.setJdbcUrl(url);
hikariDataSource.setUsername(username);
hikariDataSource.setPassword(passcode);
jdbcTemplateSource = new JdbcTemplate(hikariDataSource);
jdbcTemplateSource.afterPropertiesSet();
}
private static void fillJdbcTemplateTarget() {
String url = "jdbc:postgresql://ip:5432/db_mindoc";
String username = "postgres";
String passcode = "a";
HikariDataSource hikariDataSource = new HikariDataSource();
hikariDataSource.setDriverClassName(Driver.class.getName());
hikariDataSource.setJdbcUrl(url);
hikariDataSource.setUsername(username);
hikariDataSource.setPassword(passcode);
jdbcTemplateTarget = new JdbcTemplate(hikariDataSource);
jdbcTemplateTarget.afterPropertiesSet();
}
static {
fillJdbcTemplateSource();
fillJdbcTemplateTarget();
Runtime.getRuntime().addShutdownHook(new Thread(() -> {
DataSource dataSource = jdbcTemplateSource.getDataSource();
if (dataSource instanceof HikariDataSource hikariDataSource) {
hikariDataSource.close();
try {
Thread.sleep(Duration.ofSeconds(3));
} catch (InterruptedException e) {
throw new RuntimeException(e);
}
}
}));
Runtime.getRuntime().addShutdownHook(new Thread(() -> {
DataSource dataSource = jdbcTemplateTarget.getDataSource();
if (dataSource instanceof HikariDataSource hikariDataSource) {
hikariDataSource.close();
try {
Thread.sleep(Duration.ofSeconds(3));
} catch (InterruptedException e) {
throw new RuntimeException(e);
}
}
}));
}
@Test
public void show() {
log.info("aaa");
}
@Test
public void transferDocs() {
List<ProjectPO> projects = getAllPublicProjects(jdbcTemplateSource);
List<DocPO> docs = getAllDocs(jdbcTemplateSource);
transferBooks(projects);
List<Long> bookIds = projects.stream().map(ProjectPO::getId).sorted().toList();
List<Long> savedDocIds = transferDocs(bookIds, docs);
List<Long> originalDocIds = docs.stream().map(e -> e.getId()).collect(Collectors.toList());
boolean removed = originalDocIds.removeAll(savedDocIds);
if (removed) {
log.info("unimported docs: {}", originalDocIds);
}
}
private List<Long> transferDocs(List<Long> bookIds, List<DocPO> docsAll) {
final List<Long> savedDocIds = new LinkedList<>();
bookIds.forEach(id -> {
List<DocPO> docsInProject = docsAll.stream()
.filter(e -> id.equals(e.getTop_doc()))
.sorted(Comparator.comparingInt(DocPO::getSort))
.toList();
transferDocsByBooks(docsInProject, 0L, 0, savedDocIds);
});
return savedDocIds;
}
private void transferDocsByBooks(List<DocPO> docsInProject,
Long parentDoc,
int indent,
List<Long> savedDocIds) {
List<DocPO> docs = docsInProject.stream()
.filter(e -> e.getParent_doc().equals(parentDoc))
.sorted(Comparator.comparing(DocPO::getSort))
.toList();
for (DocPO doc : docs) {
Long id = doc.getId();
transferDoc(doc, parentDoc, savedDocIds);
transferDocsByBooks(docsInProject, id, indent + 1, savedDocIds);
}
}
private void transferDoc(DocPO doc, Long parentDocId, List<Long> savedDocIds) {
String sqlTpl = """
INSERT INTO "public"."md_documents" ("document_id", "document_name", "identify", "book_id", "parent_id", "order_sort", "markdown", "markdown_theme", "release", "content", "create_time", "member_id",
"modify_time", "modify_at", "version", "is_open", "view_count")
VALUES (?, ?, ?, ?, ?, ?, ?, 'theme__light', ?, ?, ?, 1, ?, 1, 1, 0, 0);
""";
jdbcTemplateTarget.update(sqlTpl,
doc.getId(), doc.getName(), "doc-"+doc.getId(), doc.getTop_doc(), parentDocId, doc.getSort(), doc.getPre_content(), doc.getContent(), doc.getContent(),
doc.getCreate_time(), doc.getModify_time()
);
savedDocIds.add(doc.getId());
}
private void transferBooks(List<ProjectPO> projects) {
for (ProjectPO project : projects) {
Long id = project.getId();
String name = project.getName();
String intro = project.getIntro();
String icon = project.getIcon();
int role = project.getRole();
String sqlTplForBook = """
INSERT INTO "public"."md_books" ("book_id", "book_name", "item_id", "identify", "auto_release", "is_download", "order_index", "description", "publisher", "label", "privately_owned", "private_token", "book_password", "status", "editor", "doc_count", "comment_status", "comment_count", "cover", "theme", "create_time", "history_count", "is_enable_share", "member_id", "modify_time", "version", "is_use_first_document", "auto_save")
VALUES (?, ?, 1, ?, 1, 0, 0, ?, '', '', ?, '', '', 0, 'markdown', 1, 'closed', 0, '/static/images/book.jpg', 'default', now(), 0, 0, 1, now(), 1, 1, 0);
""";
jdbcTemplateTarget.update(sqlTplForBook, id, name, "doc-id-" + (id), intro, 0 == role ? 0 : 1);
String sqlTplForRelation = """
INSERT INTO "public"."md_relationship" ( "member_id", "book_id", "role_id") VALUES ( 1, ?, 0);
""";
jdbcTemplateTarget.update(sqlTplForRelation, id);
}
}
private List<DocPO> getAllDocs(JdbcTemplate jdbcTemplate) {
final String sqlTag = " SELECT "
+ " id, name, top_doc, content, pre_content, parent_doc, sort,create_time,modify_time"
+ " FROM mrdoc.`app_doc_doc` ";
return jdbcTemplate.query(sqlTag, BPRM_DocPO);
}
private List<ProjectPO> getAllPublicProjects(JdbcTemplate jdbcTemplate) {
final String sqlTag = "SELECT * FROM mrdoc.`app_doc_project` /* where role=0 */ ";
return jdbcTemplate.query(sqlTag, BPRM_ProjectPO);
}
}
迁移完成后需要将pgsql中的表的最大id更新一下。使用如下sql即可。
-- 获取表中主键的名称
-- 一般是id，但有些表的主键不是id
CREATE OR REPLACE FUNCTION get_primary_key_column(p_table_name text)
RETURNS text AS $$
DECLARE
pk_column text;
BEGIN
SELECT a.attname
INTO pk_column
FROM pg_index i
JOIN pg_attribute a
ON a.attrelid = i.indrelid
AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = p_table_name::regclass
AND i.indisprimary
LIMIT 1;
RETURN pk_column;
END;
$$ LANGUAGE plpgsql;
-- 修改多个表的序列的值为表的最大id+1
DO $$
DECLARE
table_list text[] := array[
'public.md_attachment',
'public.md_blogs',
'public.md_books',
'public.md_comments',
'public.md_dingtalk_accounts',
'public.md_document_history',
'public.md_documents',
'public.md_itemsets',
'public.md_label',
'public.md_logs',
'public.md_member_token',
'public.md_members',
'public.md_migrations',
'public.md_options',
'public.md_relationship',
'public.md_team_member',
'public.md_team_relationship',
'public.md_teams',
'public.md_templates',
'public.md_workweixin_accounts'
];
table_name text;
column_name text; -- 自增字段名
sequence_name text; -- 序列名称变量
max_val bigint; -- 当前序列值
BEGIN
FOREACH table_name IN ARRAY table_list LOOP
-- 调用我们写的函数
column_name := get_primary_key_column(table_name);
RAISE NOTICE '自增字段名: %', column_name;
-- 1. 获取序列名称
SELECT pg_get_serial_sequence(table_name, column_name) INTO sequence_name;
RAISE NOTICE '序列名称: %', sequence_name;
-- 2. 检查序列是否存在
IF sequence_name IS NULL THEN
RAISE NOTICE '未找到表% 的字段 % 的关联序列，跳过当前表处理', table_name, column_name;
CONTINUE; -- 跳过当前循环，处理下一个表
END IF;
-- 3. 获取当前序列值
EXECUTE format('SELECT max(' || column_name || ') FROM ' || table_name) INTO max_val;
-- 处理max_val为NULL的情况（表中无数据时）
IF max_val IS NULL THEN
max_val := 0;
RAISE NOTICE '表中无数据，将max_val设为0';
END IF;
RAISE NOTICE '当前序列值: %', max_val;
-- 4. 将序列值更新为当前值+1
EXECUTE format('SELECT setval(%L, %s)', sequence_name, max_val + 1);
RAISE NOTICE '已将序列更新为: %', max_val + 1;
-- 5. 验证更新结果
EXECUTE format('SELECT last_value FROM ' || sequence_name) INTO max_val;
RAISE NOTICE '更新后序列值: %', max_val;
END LOOP;
END $$;
作者：张三 创建时间：2025-09-19 10:04
最后编辑：张三 更新时间：2025-09-25 09:36
最后编辑：张三 更新时间：2025-09-25 09:36
