迁移mariadb/mysql数据库到pgsql

2024-12-14 14:00

Tags:

0. 修改mysql数据库中tinyint为smallint

pgloader默认会把mysql的tinyint字段转为boolean字段，如果需要整型，需要事先转换

-- 1. 查询tinyint字段并组装为更新表字段语句
SELECT
	CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' SMALLINT;')
FROM
	INFORMATION_SCHEMA.COLUMNS
WHERE
	TABLE_SCHEMA = 'database_name'
	AND DATA_TYPE = 'tinyint';
-- 2. 复制查询结果并执行

1. 安装最新版pgloader迁移数据

apt-get install sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev
git clone https://github.com/dimitri/pgloader
cd pgloader
make save
./build/bin/pgloader --help
./build/bin/pgloader mysql://mysqluser:password@<mysql-server>:<mysql-port>/<source-database> postgresql://<pgsql-user>:password@<pgsql_server>:<postgresql-port>/<target-database>

2. 修改schema

迁移成功后, 默认会在pg指定数据库中创建名为mysql数据库名的schema, 因此需要迁移数据到public的schema中

-- 连接数据库
BEGIN TRANSACTION;
  ALTER SCHEMA public          RENAME TO public_original;
  ALTER SCHEMA <source-database>           RENAME TO public;
  DROP SCHEMA  public_original CASCADE;
COMMIT;

reference

Updated: 2024-12-14 22:00
Created: 2024-12-14 14:00