MySQL
PostgreSQL
mariadb

PostgreSQL から MySQL (MariaDB) へデータを移す

PostgreSQL から MySQL (MariaDB) へ テーブル単位でデータを移す方法です。

ユーザー scott
データベース city
テーブル cities を例として説明します。

PostgreSQL から、export

export の方法

./sql_gen.sh cities
sql_gen.sh
#! /bin/bash
#
#   sql_gen.sh
#
#                   Sep/3/2017
# -----------------------------------------------------------------
TABLE=$1
TABLE_SQL=$TABLE".sql"
TABLE_SQL_MOD=$TABLE"_mod.sql"
USER="scott"
DBASE="city"
#
# echo $TABLE
#
#   --schema-only \
pg_dump -t $TABLE \
    -U $USER \
    --inserts \
        --no-owner \
        --no-privileges \
        --disable-dollar-quoting \
        --column-inserts $DBASE > $TABLE_SQL
#
# -----------------------------------------------------------------

データが多量の時は、pg_dump のオプションに、
--schema-only を加えて、まずスキーマだけを移してみると良いです。

export した sql ファイル

cities.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.4
-- Dumped by pg_dump version 9.6.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: cities; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE cities (
    id character varying(10) NOT NULL,
    name text,
    population integer,
    date_mod date
);


--
-- Data for Name: cities; Type: TABLE DATA; Schema: public; Owner: -
--

INSERT INTO cities (id, name, population, date_mod) VALUES ('t3461', '広島', 72814, '2001-09-14');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3462', '福山', 41738, '2001-07-21');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3463', '東広島', 92513, '2001-06-12');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3464', '呉', 93167, '2001-09-29');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3465', '尾道', 95419, '2001-03-18');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3466', '竹原', 82314, '2001-02-21');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3467', '三次', 76152, '2001-08-16');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3468', '大竹', 37541, '2001-07-07');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3469', '府中', 46518, '2001-10-09');


--
-- Name: cities cities_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY cities
    ADD CONSTRAINT cities_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

これを、次のように変換します。

./postgresql_mysql.py < cities.sql > cities_mod.sql
postgresql_mysql.py
#! /usr/bin/python
# -*- coding: utf-8 -*-
#
#   postgresql_mysql.py
#
#                       Sep/3/2017
# --------------------------------------------------------------------
import sys
# --------------------------------------------------------------------
# sys.stderr.write("*** 開始 ***\n")

flag_sharp = False
lines = sys.stdin.readlines()
for line in lines:
    target=line[:-1]
    target = target.replace("ALTER TABLE ONLY","ALTER TABLE")
    target = target.replace("without time zone DEFAULT now()","")
    target = target.replace("without time zone","")
#
    if '::regclass' in target:
        target = "# " + target
    if (target[:3] == "SET"):
        target = "# " + target
    if 'CREATE INDEX' in target:
        target = "# " + target
    if 'ALTER SEQUENCE' in target:
        target = "# " + target
    if 'SELECT pg_catalog.setval' in target:
                target = "# " + target
#
    if 'CREATE SEQUENCE' in target:
        flag_sharp = True
    if (flag_sharp):
        target = "# " + target 
    print(target)
    if ';' in target:
        flag_sharp = False
# sys.stderr.write("*** 終了 ***\n")
# --------------------------------------------------------------------

次のような sql に変換されます。

cities_mod.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.4
-- Dumped by pg_dump version 9.6.4

# SET statement_timeout = 0;
# SET lock_timeout = 0;
# SET idle_in_transaction_session_timeout = 0;
# SET client_encoding = 'UTF8';
# SET standard_conforming_strings = on;
# SET check_function_bodies = false;
# SET client_min_messages = warning;
# SET row_security = off;

# SET search_path = public, pg_catalog;

# SET default_tablespace = '';

# SET default_with_oids = false;

--
-- Name: cities; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE cities (
    id character varying(10) NOT NULL,
    name text,
    population integer,
    date_mod date
);


--
-- Data for Name: cities; Type: TABLE DATA; Schema: public; Owner: -
--

INSERT INTO cities (id, name, population, date_mod) VALUES ('t3461', '広島', 72814, '2001-09-14');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3462', '福山', 41738, '2001-07-21');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3463', '東広島', 92513, '2001-06-12');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3464', '呉', 93167, '2001-09-29');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3465', '尾道', 95419, '2001-03-18');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3466', '竹原', 82314, '2001-02-21');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3467', '三次', 76152, '2001-08-16');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3468', '大竹', 37541, '2001-07-07');
INSERT INTO cities (id, name, population, date_mod) VALUES ('t3469', '府中', 46518, '2001-10-09');


--
-- Name: cities cities_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE cities
    ADD CONSTRAINT cities_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

MySQL (MariaDB) へ取り込みます。

./to_mariadb.sh cities
to_mariadb.sh
#! /bin/bash
#
#   to_mariadb.sh
#
#                   Sep/3/2017
#
TABLE=$1
TABLE_SQL_MOD=$TABLE"_mod.sql"
USER="scott"
PASSWORD="tiger123"
DBASE="city"
#
echo $TABLE
#
mysql -u$USER -p$PASSWORD $DBASE < $TABLE_SQL_MOD
#

データ取り込み時に、例えば、次のようなエラーが出た時は、

ERROR 1071 (42000) at line 38: Specified key was too long; max key length is 767
 bytes

マニュアルで、取り込み前の sql を修正します。
このエラーは、text に PRIMARY KEY を設定している時に出ます。
変換前

ALTER TABLE tbl_aaa
    ADD CONSTRAINT tbl_aaa_pkey PRIMARY KEY (bkup_name);

変換後

ALTER TABLE tbl_aaa
    ADD CONSTRAINT tbl_aaa_pkey PRIMARY KEY (bkup_name(191));