Spring MybatisPlus项目MySQL迁移PostgreSQL_mybatis mysql如何迁到pg

PostgreSQL安装与配置

系统与安装版本

OS：Linux Centos7

PostgreSQL: 14.10

安装rpm

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
1

安装PostgreSQL14

sudo yum install -y postgresql14-server 
1

安装完成后

初始化并启用PG

 sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
 sudo systemctl enable postgresql-14
 sudo systemctl start postgresql-14
1
2
3

查看pg的状态，如果是 active 说明启动成功

sudo systemctl status postgresql-14
1

修改配置文件

更改配置文件pghba.conf

使用搜索命令找到对应的配置文件

find / -name pg_hba.conf
1

编辑文件，将所有ip放开

vi /var/lib/pgsql/14/data/pg_hba.conf
1

在文件末尾加上

# all user access
host    all             all             0.0.0.0/0               trust
1
2

参数一: 类型(local, host, hostssl) 本地连接,外部连接,ssl加密连接

参数二: 数据库名称 填写需要连接的数据库名称 all表示所有

参数三: Linux用户 填写能访问的用户 all表示所有用户

参数四: ip地址 可以访问的ip地址 0.0.0.0/0表示所有地址
参数五: “trust”, “reject”, “md5”, “password”, “scram-sha-256”, “gss”, “sspi”, “ident”, “peer”, “pam”, “ldap”, “radius” ,“cert”

修改postgresql.conf

查找文件位置

find / -name postgresql.conf
1

编辑文件，将所有ip放开

vi /var/lib/pgsql/14/data/pg_hba.conf
1

将地址设置成所有地址可以访问

# - Connection Settings -
listen_addresses = '*'
1
2

修改两个配置文件后，重启数据库

systemctl restart postgresql-14
1

登录连接数据库

本地连接

默认用户名是 postgres， 密码为空

切换到 postgres 用户

su - postgres
1

进入pg

psql
1

修改密码

进入PG后执行

alter role postgres with password '密码';
1

重启数据库

systemctl restart postgressql-14
1

Navicat连接

默认没有密码，直接设置为空即可

MySQL数据迁移到PostgreSQL

数据传输

使用Navicat的数据传输功能

在PG中创建一个同名的库

选择运行期间的全部表

并勾选遇到错误时继续和创建前删除目标对象

点击开始，等待数据传输完成

数据传输遇到的问题

索引传输失败

MySQL中不同表中的索引名称可以重复，但是PostgreSQL中不能重复

这就会导致部分索引会丢失，需要手动给PostgreSQL中表的字段添加索引

主键自增丢失

MySQL中的数据通过navicat传输到PostgreSQL中默认值会丢失，主键的默认自增也会丢失

通过自定义一个序列，并加入到主键上来解决自增问题

CREATE SEQUENCE table_a INCREMENT 1 MINVALUE 1 MAXVALUE 99999999999 START WITH 1 CACHE 1;

ALTER TABLE table_a ALTER COLUMN ID SET DEFAULT nextval( 'table_a_id_seq');
1
2
3

序列参数

• INCREMENT 自增数量

• MINVALUE 最小值

• MAXVALUE 最大值

• STRAT WITH 开始位置

• CACHE 确定有多少序列号被预先分配，并存储在内存中以便更快地访问

序列函数

nextval('test_seq')  --获取下一个序列值,并自增
1

currval('test_seq'); --获取当前序列值
1

setval('test_seq', 1) -- 设置序列值
1

select setval('test_seq', (select max(id) from test)); --将当前最大id设置为当前序列值
1

一键生成主键序列，并设置最大id到序列上

-- 为主键创建序列，并设置序列值为当前id
SELECT
    concat ( 'CREATE SEQUENCE ', TABLE_NAME, '_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 99999999999 START WITH 1 CACHE 1;
    ALTER TABLE "', TABLE_NAME, '" ALTER COLUMN ID SET DEFAULT nextval(#', TABLE_NAME, '_id_seq#);
', 'select setval(#', TABLE_NAME, '_id_seq#, ', '(select max(id) from ', TABLE_NAME, '));' ) 
FROM
    information_schema.tables 
WHERE
    table_schema = 'public' 
    AND table_type = 'BASE TABLE' 
ORDER BY
    TABLE_NAME;
1
2
3
4
5
6
7
8
9
10
11
12

复制生成SQL，将#全局替换成单引号，执行SQL来生成序列

SpringBoot项目切换PostgreSQL

引入PostgreSQL的依赖

<dependency>
     <groupId>org.postgresql</groupId>
     <artifactId>postgresql</artifactId>
     <scope>runtime</scope>
</dependency>
1
2
3
4
5

在配置文件中配置

    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://192.168.253.100:5432/banew?autoReconnect=true&autoReconnectForPools=true&useUnicode=true&characterEncoding=utf8&createDatabaseIfNotExist=true&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull
    username: postgres
    password: ''
1
2
3
4

由于MySQL的Limit和PostgreSQL的Limit语法不一样，分页插件需要设置成PostgreSQL的模式

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        //mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
        return mybatisPlusInterceptor;
    }
1
2
3
4
5
6
7

SQL语法差异

这里只展示在mybatis mapper中需要更改的语法

数据类型不兼容

在MySQL中，传入string类型的值与int比较会正常运行(或者数据更新插入)

而在PostgreSQL中，会报错

错误: 操作符不存在: integer = character varying
Hint: 没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
1
2

开启日志

-- 是否将日志重定向至文件中，默认是off
alter system set logging_collector = 'on';
-- 日志目录位置 PGDATA的相对路径，即PGDATA的相对路径，即{PGDATA}/pg_log
alter system set log_directory = 'pg_log';
-- -1示不可用，0将记录所有SQL语句和它们的耗时，>0只记录那些耗时超过（或等于）这个值（ms）的SQL语句。
alter system set log_min_duration_statement = '1000';
-- 日志文件名称  最多保存一周的日志,每天一个文件
alter system set log_filename = 'postgresql-%w.log';
-- 单个日志的最大大小 超过另生成文件
alter system set log_rotation_size = '1024MB';
-- 开启自定义日志文件名
alter system set log_truncate_on_rotation = 'on';
1
2
3
4
5
6
7
8
9
10
11
12