数据从mysql迁移到postgresql

参考文章：

项目从 MySQL 切换 PostgreSQL，踩了太多的坑！

需要安装一个工具pgloader

Installing pgloader — pgloader 3.6.9 documentation

windows下没有可用的exec打包，只能通过docker使用

docker pull ghcr.io/dimitri/pgloader:latest
docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version

迁移命令举例

docker run --rm -it --memory 4g --memory-swap 4g ghcr.io/dimitri/pgloader:latest pgloader --encoding UTF8 --set "client_encoding = 'UTF8'" --cast "type int to integer drop typemod" --cast "type bigint to bigint drop typemod" --cast "type timestamp to timestamp" --cast "type datetime to timestamp" mysql://用户名:密码@192.168.0.102:3357/库名 postgresql://用户名:密码@192.168.0.102:5432/库名

数据库不能太大，不然会报错oom，目前还没找到解决办法

另外：

pgloader迁移，默认会把日期timestamp、datetime等变成postgresql下带时区的timestamptz格式，上面的迁移指令里--cast "type timestamp to timestamp"的指令，就是要求把时间转成不带时区的timestamp格式，但是有个bug：

`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
这样的带有ON UPDATE CURRENT_TIMESTAMP的字段，仍然会转成timestamptz格式

解决办法：

-- 在postgresql命令行里执行，把所有update_time转成不带时区格式timestamp
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT table_schema, table_name
        FROM information_schema.columns
        WHERE column_name = 'update_time'
          AND data_type = 'timestamp with time zone'
    LOOP
        EXECUTE format('ALTER TABLE %I.%I ALTER COLUMN update_time TYPE TIMESTAMP', r.table_schema, r.table_name);
    END LOOP;
END $$;

批量设置时间默认值脚本：

批量修改模式名下的所有字段类型为timestamp的并且字段名为 create_time 或者 update_time的字段的默认值为 CURRENT_TIMESTAMP

-- 注意 || 号拼接的后面的字符串前面要有一个空格
DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec INSELECT table_name, column_name,data_type
               FROM information_schema.columns
               where table_schema = '要处理的模式名'
                 AND data_type = 'timestamp without time zone'
                 -- 修改的字段名
          and column_name in ('create_time','update_time')
    LOOP
         EXECUTE'ALTER TABLE ' || rec.table_name || ' ALTER COLUMN ' || rec.column_name || ' SET DEFAULT CURRENT_TIMESTAMP;';
    ENDLOOP;
END $$;

如果不想使用pgloader或者觉得pgloader的转换有问题（比如自增字段的转换），也可以使用navicat复制数据库转换，然后自行修改自增字段数据类型。

注意，navicat15对postgresql的支持尚不完善，我升级用navicat17

postgresql数据备份和恢复

#备份数据
pg_dump -h localhost -p 5432 -U postgres -W -d 数据库名 -n public -f backup.sql
Password:

#恢复数据
psql -h localhost -p 5432 -U postgres -W -d 数据库名 -n public -f backup.sql
Password:

授予超级用户权限

将目标用户设置为超级用户。超级用户在 PostgreSQL 中拥有所有权限，可以执行任何操作。

ALTER USER your_username WITH SUPERUSER;