MySQL 数据库切换为 PostgreSQL 数据库方案

一、背景

近期由于公司业务产品发展需要，要求项目逐渐国产化：（1）项目国产操作系统部署；（2）数据库国产化；国产数据库最终选型为highgo（瀚高），该数据库基于pg开发，所以要求先将mysql适配到postgresql数据库；

一、初识postgresql

1.1 docker安装postgresql

1.1.1 镜像拉取

dockerhub官网选取自己想安装的版本（https://hub.docker.com/_/postgres/tags），我这里选取的是13.9版本；

拉取镜像到本地

docker pull postgres:13.9

1.1.2 执行镜像安装postgresql

docker run --name 1.postgres \
--restart=always \
-e POSTGRES_PASSWORD='jY%kng8cc&' \
-p 5432:5432 \
-v /data/postgresql:/var/lib/postgresql/data \
-d postgres:13.9

1.1.3 创建数据库,设置默认查询模式

-- 建库
CREATE DATABASE "test"
WITH
  OWNER = "testuser" -- 数据库用户
;

-- 创建模式
CREATE SCHEMA "test" AUTHORIZATION "test";

-- 设置默认查询模式 pg连接时默认使用public这个schmel，想让项目连接时使用自己创建的模式需要修改一下默认查询模式
ALTER ROLE testuser SET search_path="test";

1.2 postgresql学习

这里放上一个学习地址，大家可以参照性学习，边使用边学习

https://www.sjkjc.com/postgresql/psql-commands/

1.3 项目中引入postgresql数据库

pg为所有具有主键的表创建序列

DO $$ 
DECLARE
  table_name text;
  primary_key_column text;
    sequence_name_origin text;
    sequence_name text;
    alert_sql text;
    reg_sql text;
    exec_alert_sql text;
 BEGIN
   -- 遍历特定库中的所有表
   FOR table_name, primary_key_column IN
        SELECT tc."table_name",column_name
        FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu USING (constraint_schema, constraint_name)
        WHERE constraint_type = 'PRIMARY KEY' and tc.table_schema = 'public'
    LOOP
    -- 创建序列
    --- EXECUTE format('CREATE SEQUENCE %I_%I_seq''', table_name, primary_key_column);
        --- EXECUTE format('CREATE SEQUENCE %I_%I_seq', replace(table_name, '"', ''), replace(primary_key_column, '"', ''));
        sequence_name_origin := format('CREATE SEQUENCE %I_%I_seq', table_name, primary_key_column);
        sequence_name := replace(sequence_name_origin, '"', '');
        EXECUTE sequence_name;

    -- 设置表的主键列的默认值为序列的下一个值
    --EXECUTE format('ALTER TABLE %I ALTER COLUMN %I SET DEFAULT nextval(''%I_%I_seq''::regclass)', table_name, primary_key_column, table_name, primary_key_column);
        alert_sql := format('ALTER TABLE %I ALTER COLUMN %I ', table_name, primary_key_column);

        reg_sql := format('SET DEFAULT nextval(''%I_%I_seq''::regclass)', table_name, primary_key_column);

        reg_sql := replace(reg_sql, '"', '');

        exec_alert_sql := concat(alert_sql, reg_sql);

        EXECUTE exec_alert_sql;

    RAISE NOTICE 'Created sequence for table % and column %', table_name, primary_key_column;
  END LOOP;

END $$;

这段脚本是一个PL/pgSQL脚本，用于在PostgreSQL数据库中为所有具有主键的表创建序列，并将主键列的默认值设置为序列的下一个值。

脚本的逻辑如下：

1. 使用FOR循环遍历特定数据库（在此脚本中是'public'模式）中的所有表和其主键列。
2. 对于每个表和主键列组合，首先使用EXECUTE语句创建一个序列，命名为表名_主键列名_seq。format()函数用于构建动态的SQL语句，确保安全性和正确的引用。
3. 接下来，使用EXECUTE语句将表的主键列的默认值设置为序列的下一个值。nextval()函数用于获取序列的下一个值，::regclass用于将序列名称转换为regclass类型。
4. 使用RAISE NOTICE语句输出一个通知，表示为表和主键列创建了序列。

请注意，这个脚本假设数据库中的所有表都位于'public'模式中，并且只处理具有主键的表。如果数据库中有其他模式或不具有主键的表，需要进行适当的修改才能满足要求。

另外，确保在执行这段脚本之前备份数据库，以防止意外发生。

手动创建序列化

-- ----------------------------
-- Sequence structure for test_depart_organization_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "public"."test_depart_organization_id_seq";
CREATE SEQUENCE "public"."test_depart_organization_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

添加自增序列：

CREATE SEQUENCE incremental_test1_id_seq
    START WITH 1
    INCREMENT by 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE incremental_test1 ALTER COLUMN id SET DEFAULT nextval('incremental_test1_id_seq');

实际应用：

--- 添加主建序列化
DROP SEQUENCE IF EXISTS "public"."dt_blog_model_template_template_id_seq";
CREATE SEQUENCE "public"."dt_blog_model_template_template_id_seq" 
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

ALTER TABLE "public"."dt_blog_model_template" ALTER COLUMN template_id SET DEFAULT nextval('dt_blog_model_template_template_id_seq');

相关文章：
超全mysql转换postgresql数据库方案
mysql表结构以及数据导入postgresql常见问题
数据库mysql转为postgresql变动
MySQL迁移到PostgreSQL
项目的Mysql数据库迁移到Postgresql遇到的坑
PostgreSQL自增序列创建与修改

为者常成，行者常至