MySQL에서 PostgreSQL로 이행

export PGPASSWORD='<PASSWORD>'
export PGCLIENTENCODING=UTF8
mkdir -p '<WORK_DIR>' && cd '<WORK_DIR>'
mysql -e "SELECT CURRENT_USER(), DATABASE(), VERSION()"
psql -h '<HOST>' -U '<USERNAME>' -d '<MAINTENANCE_DB>' -c "SELECT current_user, version()"

SELECT table_name, table_rows
  FROM information_schema.tables
 WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE'
 ORDER BY 1;

SELECT routine_type, routine_name
  FROM information_schema.routines
 WHERE routine_schema = DATABASE()
 ORDER BY 1, 2;

SELECT trigger_name, action_timing, event_manipulation, event_object_table
  FROM information_schema.triggers
 WHERE trigger_schema = DATABASE();

SELECT table_name AS view_name
  FROM information_schema.views
 WHERE table_schema = DATABASE();

SELECT table_name, view_definition
  FROM information_schema.views
 WHERE table_schema = DATABASE()\G

SELECT trigger_name, action_statement
  FROM information_schema.triggers
 WHERE trigger_schema = DATABASE()\G

SELECT routine_name
  FROM information_schema.routines
 WHERE routine_schema = DATABASE()
   AND routine_definition LIKE '%<REPORT_USER>.%';
EXIT

mysqldump --no-data --routines --triggers --skip-comments --no-tablespaces '<SOURCE_DB_NAME>' > my_src.sql
grep -nE "^CREATE|CREATE DEFINER|^DELIMITER" my_src.sql

PGPASSWORD='<PASSWORD>' psql -h '<HOST>' -U '<ADMIN_USER>'

CREATE DATABASE <TARGET_DB_NAME> OWNER <USERNAME>;
\c <TARGET_DB_NAME>
CREATE SCHEMA <USERNAME> AUTHORIZATION <USERNAME>;
CREATE SCHEMA <REPORT_USER> AUTHORIZATION <REPORT_USER>;

CREATE TABLE <REPORT_USER>.rpt_daily
( rpt_date  TIMESTAMP(0),
  order_cnt NUMERIC(38,10),
  total_amt NUMERIC(38,10) );

ALTER TABLE <REPORT_USER>.rpt_daily OWNER TO <REPORT_USER>;

GRANT USAGE ON SCHEMA <REPORT_USER> TO <USERNAME>;

GRANT SELECT, INSERT ON <REPORT_USER>.rpt_daily TO <USERNAME>;
\dn
\q

cat > gen_ddl_pg.sql <<'EOF'
SET SESSION group_concat_max_len = 1000000;
SELECT CONCAT('DROP TABLE IF EXISTS ', t.table_name, ' CASCADE;\n',
              'CREATE TABLE ', t.table_name, ' (\n',
              GROUP_CONCAT(CONCAT('  ', RPAD(c.column_name, 12, ' '), ' ',
                CASE c.data_type
                  WHEN 'bigint'   THEN 'BIGINT'
                  WHEN 'int'      THEN 'INTEGER'
                  WHEN 'smallint' THEN 'SMALLINT'
                  WHEN 'tinyint'  THEN 'SMALLINT'
                  WHEN 'decimal'  THEN CONCAT('NUMERIC(', c.numeric_precision, ',', c.numeric_scale, ')')
                  WHEN 'varchar'  THEN CONCAT('VARCHAR(', c.character_maximum_length, ')')
                  WHEN 'char'     THEN CONCAT('CHAR(', c.character_maximum_length, ')')
                  WHEN 'datetime' THEN CONCAT('TIMESTAMP(', c.datetime_precision, ')')
                  WHEN 'date'     THEN 'DATE'
                  WHEN 'double'   THEN 'DOUBLE PRECISION'
                  WHEN 'text'     THEN 'TEXT'
                  WHEN 'longtext' THEN 'TEXT'
                  WHEN 'longblob' THEN 'BYTEA'
                  ELSE CONCAT('TEXT /* 확인 필요: ', c.column_type, ' */')
                END,
                IF(c.extra LIKE '%auto_increment%', ' GENERATED BY DEFAULT AS IDENTITY', ''),
                IF(c.is_nullable = 'NO', ' NOT NULL', ''))
                ORDER BY c.ordinal_position SEPARATOR ',\n'),
              '\n);\n')
  FROM information_schema.tables t
  JOIN information_schema.columns c
    ON c.table_schema = t.table_schema AND c.table_name = t.table_name
 WHERE t.table_schema = DATABASE()
   AND t.table_type = 'BASE TABLE'
 GROUP BY t.table_name
 ORDER BY t.table_name;
EOF

mysql -N -B --raw < gen_ddl_pg.sql > pg_01_tables.sql
cat pg_01_tables.sql

cat > export_tsv.sh <<'EOF'
#!/bin/bash
# 테이블마다 TSV로 내려받고, 적재 명령(\copy)을 pg_02_load.sql 로 만든다
mkdir -p data
for t in $(mysql -N -B -e "SELECT table_name FROM information_schema.tables
                            WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE' ORDER BY 1"); do
  mysql -N -B -e "SELECT * FROM \`$t\`" > data/$t.tsv
  echo "\copy $t FROM 'data/$t.tsv' WITH (FORMAT text, NULL 'NULL')"
done > pg_02_load.sql
wc -l data/*.tsv
EOF

chmod +x export_tsv.sh
./export_tsv.sh
cat pg_02_load.sql
head -3 data/orders.tsv data/emp.tsv

cat > gen_cons_pg.sql <<'EOF'
SELECT ddl FROM (
  -- 기본키·유니크
  SELECT 1 AS ord, tc.table_name AS tn,
         CONCAT('ALTER TABLE ', tc.table_name, ' ADD ',
                IF(tc.constraint_type = 'PRIMARY KEY', 'PRIMARY KEY (',
                   CONCAT('CONSTRAINT ', tc.constraint_name, ' UNIQUE (')),
                GROUP_CONCAT(k.column_name ORDER BY k.ordinal_position), ');') AS ddl
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage k
      ON k.constraint_schema = tc.constraint_schema
     AND k.constraint_name = tc.constraint_name AND k.table_name = tc.table_name
   WHERE tc.table_schema = DATABASE() AND tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
   GROUP BY tc.table_name, tc.constraint_name, tc.constraint_type
  UNION ALL
  -- 외래키
  SELECT 2, k.table_name,
         CONCAT('ALTER TABLE ', k.table_name, ' ADD CONSTRAINT ', k.constraint_name,
                ' FOREIGN KEY (', GROUP_CONCAT(k.column_name ORDER BY k.ordinal_position),
                ') REFERENCES ', k.referenced_table_name,
                ' (', GROUP_CONCAT(k.referenced_column_name ORDER BY k.ordinal_position), ')',
                IF(r.delete_rule = 'CASCADE', ' ON DELETE CASCADE', ''), ';')
    FROM information_schema.key_column_usage k
    JOIN information_schema.referential_constraints r
      ON r.constraint_schema = k.constraint_schema AND r.constraint_name = k.constraint_name
   WHERE k.table_schema = DATABASE() AND k.referenced_table_name IS NOT NULL
   GROUP BY k.table_name, k.constraint_name, k.referenced_table_name, r.delete_rule
  UNION ALL
  -- 제약조건이 아닌 일반 인덱스
  SELECT 3, s.table_name,
         CONCAT('CREATE ', IF(s.non_unique = 0, 'UNIQUE ', ''), 'INDEX ', s.index_name,
                ' ON ', s.table_name, ' (', GROUP_CONCAT(s.column_name ORDER BY s.seq_in_index), ');')
    FROM information_schema.statistics s
   WHERE s.table_schema = DATABASE() AND s.index_name <> 'PRIMARY'
     AND s.index_name NOT IN (SELECT constraint_name FROM information_schema.table_constraints
                               WHERE table_schema = DATABASE())
   GROUP BY s.table_name, s.index_name, s.non_unique
) x
ORDER BY ord, tn;
EOF

cat > gen_seq_pg.sql <<'EOF'
SET SESSION information_schema_stats_expiry = 0;   -- 캐시된 AUTO_INCREMENT 말고 지금 값을 읽음
SELECT CONCAT('SELECT setval(pg_get_serial_sequence(''', c.table_name, ''', ''', c.column_name, '''), ',
              t.auto_increment, ', false);')
  FROM information_schema.columns c
  JOIN information_schema.tables t
    ON t.table_schema = c.table_schema AND t.table_name = c.table_name
 WHERE c.table_schema = DATABASE()
   AND c.extra LIKE '%auto_increment%';
EOF

mysql -N -B --raw < gen_cons_pg.sql > pg_03_constraints.sql
mysql -N -B --raw < gen_seq_pg.sql  > pg_04_identity.sql

cat pg_03_constraints.sql pg_04_identity.sql

SELECT setval(pg_get_serial_sequence('orders', 'order_id'), 1010, false);

cat > mig_data.sh <<'EOF'
#!/bin/bash
set -e
PSQL="psql -h <HOST> -U <USERNAME> -d <TARGET_DB_NAME> -v ON_ERROR_STOP=1"

echo "[1] MySQL에서 생성문·데이터 만들기"
mysql -N -B --raw < gen_ddl_pg.sql  > pg_01_tables.sql
mysql -N -B --raw < gen_cons_pg.sql > pg_03_constraints.sql
mysql -N -B --raw < gen_seq_pg.sql  > pg_04_identity.sql
./export_tsv.sh

echo "[2] PostgreSQL에 적용"
$PSQL -q -f pg_01_tables.sql
$PSQL    -f pg_02_load.sql
$PSQL -q -f pg_03_constraints.sql
$PSQL    -f pg_04_identity.sql
echo "[완료]"
EOF

chmod +x mig_data.sh
./mig_data.sh

cat > both_count.sql <<'EOF'
SELECT 'dept' AS table_name, COUNT(*) AS cnt FROM dept
UNION ALL SELECT 'emp',         COUNT(*) FROM emp
UNION ALL SELECT 'emp_m',       COUNT(*) FROM emp_m
UNION ALL SELECT 'emp_t',       COUNT(*) FROM emp_t
UNION ALL SELECT 'emp_t_audit', COUNT(*) FROM emp_t_audit
UNION ALL SELECT 'order_hist',  COUNT(*) FROM order_hist
UNION ALL SELECT 'orders',      COUNT(*) FROM orders
ORDER BY 1;
SELECT SUM(amount) AS sum_amount FROM orders;
SELECT SUM(sal) AS sum_sal FROM emp_m;
EOF

echo "===== MySQL ====="
mysql -t < both_count.sql
echo "===== PostgreSQL ====="
psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -f both_count.sql

DROP TABLE IF EXISTS products;

CREATE TABLE products
( prod_id    INT          NOT NULL AUTO_INCREMENT,
  prod_name  VARCHAR(50)  NOT NULL,
  category   VARCHAR(20),
  price      DECIMAL(10,0),
  stock      INT,
  reg_date   DATETIME,
  PRIMARY KEY (prod_id)
);

INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('갤럭시 버즈3', '전자', 189000, 40, '2026-09-01 09:10:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('아이패드 에어', '전자', 929000, 12, '2026-09-01 10:20:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('로지텍 마우스', '전자', 39000, 85, '2026-09-02 11:00:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('기계식 키보드', '전자', 129000, 30, '2026-09-02 14:30:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('27인치 모니터', '전자', 349000, 15, '2026-09-03 09:00:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('USB-C 충전기', '전자', 29000, 120, '2026-09-03 16:45:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('SQL 200제', '도서', 28000, 60, '2026-09-04 10:00:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('친절한 SQL 튜닝', '도서', 36000, 45, '2026-09-04 13:20:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('오라클 19c 관리', '도서', 42000, 25, '2026-09-05 09:30:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('PostgreSQL 입문', '도서', 32000, NULL, '2026-09-05 15:10:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('리눅스 첫걸음', '도서', 25000, 50, '2026-09-06 11:40:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('파이썬 데이터분석', '도서', 34000, 38, '2026-09-06 17:00:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('제주 감귤 5kg', '식품', 27000, 70, '2026-09-07 08:50:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('유기농 우유 1L', '식품', 3500, 200, '2026-09-07 09:15:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('햇반 24개입', '식품', 23900, 90, '2026-09-08 10:05:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('커피 원두 1kg', '식품', 31000, 35, '2026-09-08 13:30:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('견과류 선물세트', '식품', 45000, 20, '2026-09-09 10:10:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('생수 2L 12병', '식품', 9900, 150, '2026-09-09 14:00:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('후드 티셔츠', '의류', 49000, 55, '2026-09-10 09:40:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('청바지', '의류', 59000, 40, '2026-09-10 12:00:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('경량 패딩', '의류', 89000, 22, '2026-09-11 10:30:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('운동화', '의류', 99000, 30, '2026-09-11 15:20:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('니트 가디건', '의류', 69000, 18, '2026-09-12 11:00:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('양말 10족', '의류', 12900, 100, '2026-09-12 16:30:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('원목 책상', '가구', 259000, 8, '2026-09-13 10:00:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('사무용 의자', '가구', 189000, 14, '2026-09-13 13:45:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('3단 책장', '가구', 79000, 20, '2026-09-14 09:20:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('스탠드 조명', '가구', 45000, 33, '2026-09-14 14:10:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('수납 선반', '가구', 39000, 27, '2026-09-15 10:50:00');
INSERT INTO products (prod_name, category, price, stock, reg_date) VALUES ('접이식 테이블', '가구', 55000, 16, '2026-09-15 17:30:00');

CREATE UNIQUE INDEX uk_products_name ON products (prod_name);
CREATE INDEX idx_products_cat_price  ON products (category, price);
CREATE INDEX idx_products_regdate    ON products (reg_date);

SELECT COUNT(*) FROM products;
SHOW INDEX FROM products;
EXIT

-- mysql 에서
SHOW CREATE TABLE products\G

SELECT index_name, non_unique, seq_in_index, column_name
  FROM information_schema.statistics
 WHERE table_schema = DATABASE() AND table_name = 'products'
 ORDER BY index_name, seq_in_index;

SET SESSION information_schema_stats_expiry = 0;

SELECT auto_increment FROM information_schema.tables
 WHERE table_schema = DATABASE() AND table_name = 'products';
EXIT

mkdir -p data
mysql -N -B -e "SELECT prod_id, prod_name, category, price, stock, reg_date FROM products ORDER BY prod_id" > data/products.tsv
wc -l data/products.tsv
grep -n NULL data/products.tsv

cat > q4_products.sql <<'EOF'
DROP TABLE IF EXISTS products CASCADE;
CREATE TABLE products (
  prod_id    INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  prod_name  VARCHAR(50) NOT NULL,
  category   VARCHAR(20),
  price      NUMERIC(10,0),
  stock      INTEGER,
  reg_date   TIMESTAMP(0)
);

\copy products FROM 'data/products.tsv' WITH (FORMAT text, NULL 'NULL')

ALTER TABLE products ADD PRIMARY KEY (prod_id);
ALTER TABLE products ADD CONSTRAINT uk_products_name UNIQUE (prod_name);
CREATE INDEX idx_products_cat_price ON products (category, price);
CREATE INDEX idx_products_regdate   ON products (reg_date);

SELECT setval(pg_get_serial_sequence('products', 'prod_id'), 31, false);
EOF

psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -v ON_ERROR_STOP=1 -f q4_products.sql

cat > q4_check.sql <<'EOF'
SELECT COUNT(*) AS cnt, SUM(price) AS sum_price, SUM(stock) AS sum_stock,
       COUNT(*) - COUNT(stock) AS null_stock
  FROM products;
SELECT category, COUNT(*) AS cnt, SUM(price) AS sum_price
  FROM products GROUP BY category ORDER BY category;
EOF

echo "===== MySQL =====";      mysql -t < q4_check.sql
echo "===== PostgreSQL ====="; psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -f q4_check.sql
psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>' -c "SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'products' ORDER BY 1"

-- psql -h '<HOST>' -U '<USERNAME>' -d '<TARGET_DB_NAME>'
BEGIN;
INSERT INTO products (prod_name, category, price, stock, reg_date)
VALUES ('테스트 상품', '기타', 1000, 1, now()) RETURNING prod_id;
INSERT INTO products (prod_name, category, price, stock, reg_date)
VALUES ('SQL 200제', '도서', 28000, 1, now());
ROLLBACK;