pg 数据到pg 数据的迁移，同时支持名称的变更
环境准备
docker-compose文件
内容偏多可以忽略部分
version: "3"
services:
pgloader-csv:
image: dimitri/pgloader
volumes:
- "./init-db:/opt/db"
- "./pgloader-config:/loader"
command: pgloader /loader/csv/csv.load
pgloader-fixed2:
image: dimitri/pgloader
volumes:
- "./init-db:/opt/db"
- "./pgloader-config:/loader"
command: pgloader /loader/fixed/app.load
pgloader-fixed:
image: dimitri/pgloader
volumes:
- "./init-db:/opt/db"
- "./pgloader-config:/loader"
command: pgloader /loader/csv/fixed.load
pgloader-sqlite:
image: dimitri/pgloader
volumes:
- "./init-db:/opt/db"
- "./pgloader-config:/loader"
command: pgloader https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite_AutoIncrementPKs.sqlite postgresql://postgres:dalong@postgres:5432/postgres
pgloader-pg:
image: dimitri/pgloader
volumes:
- "./init-db:/opt/db"
- "./pgloader-config:/loader"
command: pgloader /loader/pg/pg.load
pgloader-mysql:
image: dimitri/pgloader
volumes:
- "./init-db:/opt/db"
- "./pgloader-config:/loader"
command: pgloader mysql://root:dalongrong@mysql/f1db postgresql://postgres:dalong@postgres:5432/f1db
mysql:
image: mysql:5.7.16
ports:
- 3306:3306
command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
environment:
MYSQL_ROOT_PASSWORD: dalongrong
MYSQL_DATABASE: gogs
MYSQL_USER: gogs
MYSQL_PASSWORD: dalongrong
TZ: Asia/Shanghai
postgres:
image: postgres:9.6.11
ports:
- "5432:5432"
environment:
- "POSTGRES_PASSWORD:dalong"
postgres2:
image: postgres:9.6.11
ports:
- "5433:5432"
environment:
- "POSTGRES_PASSWORD:dalong"
pg 源数据库准备
主要是初始数据
CREATE TABLE fixed (
a integer,
b date,
c time without time zone,
d text
);
INSERT INTO "public"."fixed"("a","b","c","d")
VALUES
(123456789,E'2008-05-20',E'11:43:12.5',E'firstline'),
(133456789,E'2008-05-20',E'11:53:12.5',E'firstline'),
(123456,E'2008-05-21',E'15:18:23',E'leftblank-padded');
定义加载
说明，只同步fixed 表，同时被命名为fixed_app
load database
from pgsql://postgres:dalong@postgres:5432/postgres
into pgsql://postgres:dalong@postgres2:5432/postgres
including only table names matching 'fixed' in schema 'public'
ALTER TABLE NAMES MATCHING 'fixed' IN SCHEMA 'public' RENAME TO 'fixed_app';
启动pg 数据
docker-compose up -d postgres postgres2
运行数据迁移
docker-compose up pgloader-pg
效果
pgloader-pg_1 | WARNING:
pgloader-pg_1 | Couldn't re-execute SBCL with proper personality flags (/proc isn't mounted? setuid?)
pgloader-pg_1 | Trying to continue anyway.
pgloader-pg_1 | 2019-06-13T13:06:31.053000Z LOG pgloader version "3.6.2~devel"
pgloader-pg_1 | 2019-06-13T13:06:31.305000Z LOG Migrating from #<PGSQL-CONNECTION pgsql://postgres@postgres:5432/postgres {1007111323}>
pgloader-pg_1 | 2019-06-13T13:06:31.306000Z LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@postgres2:5432/postgres {10071129B3}>
pgloader-pg_1 | 2019-06-13T13:06:31.625000Z LOG report summary reset
pgloader-pg_1 | table name errors rows bytes total time
pgloader-pg_1 | ----------------------- --------- --------- --------- --------------
pgloader-pg_1 | fetch meta data 0 1 0.145s
pgloader-pg_1 | Create Schemas 0 0 0.001s
pgloader-pg_1 | Create SQL Types 0 0 0.008s
pgloader-pg_1 | Create tables 0 2 0.027s
pgloader-pg_1 | Set Table OIDs 0 1 0.001s
pgloader-pg_1 | ----------------------- --------- --------- --------- --------------
pgloader-pg_1 | "public".fixed_app 0 3 0.1 kB 0.021s
pgloader-pg_1 | ----------------------- --------- --------- --------- --------------
pgloader-pg_1 | COPY Threads Completion 0 4 0.018s
pgloader-pg_1 | Index Build Completion 0 0 0.000s
pgloader-pg_1 | Reset Sequences 0 0 0.010s
pgloader-pg_1 | Primary Keys 0 0 0.000s
pgloader-pg_1 | Create Foreign Keys 0 0 0.000s
pgloader-pg_1 | Create Triggers 0 0 0.001s
pgloader-pg_1 | Install Comments 0 0 0.000s
pgloader-pg_1 | ----------------------- --------- --------- --------- --------------
pgloader-pg_1 | Total import time ? 3 0.1 kB 0.029s
目标pg 数据
一个支持schema 迁移的load
load database
from pgsql://postgres:dalong@postgres:5432/postgres
into pgsql://postgres:dalong@postgres2:5432/postgres
including only table names matching 'fixed' in schema 'public'
ALTER TABLE NAMES MATCHING 'fixed' IN SCHEMA 'public' RENAME TO 'fixed_app'
BEFORE LOAD DO
$$
create schema if not exists mv;
$$
AFTER LOAD DO
$$
ALTER TABLE fixed_app set schema mv;
$$;
说明
以上只是简单的同步，实际上我们可以处理schema 的设置，以及类型的变更，同时before after 等的依
参考资料
https://pgloader.readthedocs.io/en/latest/pgloader.html
https://github.com/rongfengliang/pgloader-learning