LoginSignup
2
3

More than 1 year has passed since last update.

@ekzemplaro(内田 雅智)

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

Last updated at Posted at 2017-09-03

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
#
#	postgresql_mysql.py
#
#						Mar/12/2023
# --------------------------------------------------------------------
import sys
# --------------------------------------------------------------------
# sys.stderr.write("*** 開始 ***\n")

flag_sharp = False
lines = sys.stdin.readlines()
for line in lines:
	target=line[:-1]
	target = target.replace(" timestamp"," datetime")
	target = target.replace("smallint DEFAULT (0)::smallint","smallint DEFAULT (0)")
	target = target.replace("ALTER TABLE ONLY","ALTER TABLE")
	target = target.replace("without time zone DEFAULT now()","")
	target = target.replace("without time zone","")
#
	target = target.replace("CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;","")
#
	target = target.replace("CREATE TABLE public.","CREATE TABLE ")
	target = target.replace("INSERT INTO public.","INSERT INTO ")
#
	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.set' in target:
		target = "# " + target
	if 'PROCEDURAL LANGUAGE plpgsql' in target:
		target = "# " + target
	if 'ALTER TABLE public.' in target:
		target = "# " + target
		if not ';' in target:
			flag_sharp = True
#
#
	if 'CREATE SEQUENCE' in target:
		flag_sharp = True
	elif 'CREATE EXTENSION' in target:
		flag_sharp = True
	elif 'COMMENT ON' in target:
		flag_sharp = True

	if (flag_sharp):
		target = "# " + target 
	print(target)
	llx = len(target)
#	if ';' in target:
	if (0 < llx):
		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
#

SQL のマニュアル修正

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

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));

テーブル定義で、
text を、
mediumtext
に変更すれば、取り込めるというケースもありました。

エラーメッセージは

Data too long for column

文字コード

次のようなエラーメッセージが出た時は、文字コードが原因の可能性があります。

Incorrect string value:

次のようになっていれば大丈夫です。

MariaDB [(none)]> SHOW VARIABLES LIKE 'chara%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)

文字コードの設定は、

/etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8mb4
/etc/my.cnf.d/server.cnf
[mysqld]
character-set-server=utf8mb4

Ubuntu の場合は

/etc/mysql/mariadb.conf.d/50-client.cnf
[client]
default-character-set=utf8mb4

データベース内のテーブルのレコード数をカウントする SQL

PostgreSQL

select reltuples, relname FROM pg_class ORDER BY reltuples DESC;

MariaDB

select table_name, table_rows from information_schema.TABLES where table_schema = 'データベース名' order by table_rows desc;
2
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
3