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

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

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

PostgreSQL から、export

export の方法

```bash
./sql_gen.sh cities
```

```bash: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 ファイル

```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
--
```

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

```bash
./postgresql_mysql.py < cities.sql > cities_mod.sql
```

```py3: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 に変換されます。

```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) へ取り込みます。

```bash
./to_mariadb.sh cities
```

```bash: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 のマニュアル修正

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

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

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

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

変換後

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

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

エラーメッセージは

```text
Data too long for column
```

## 文字コード

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

```text
Incorrect string value:
```

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

```text
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)
```

文字コードの設定は、

```text:/etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8mb4
```

```text:/etc/my.cnf.d/server.cnf
[mysqld]
character-set-server=utf8mb4
```

Ubuntu の場合は

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

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

PostgreSQL

```text
select reltuples, relname FROM pg_class ORDER BY reltuples DESC;
```

MariaDB

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