= 2008-10-15 =

[[PageOutline]]

== 備份聯合目錄 PostgreSQL 到實驗主機 ==

 * 安裝 PostgreSQL 與 phpPGadmin
   * [參考] [http://plog.longwin.com.tw/my_note-unix/2007/07/13/debian_postgresql_phppgadmin_2007 Debian Linux 架設 PostgreSQL + PhpPgAdmin]
   * [http://packages.debian.org/etch/phppgadmin phpPGAdmin] - 官方網站: http://phppgadmin.sf.net
{{{
jazz@oceandb:~$ sudo apt-get install postgresql phppgadmin
jazz@oceandb:~$ sudo -u postgres psql template1
Welcome to psql 8.3.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# create user PG_USER with password '${PASSWORD}' createdb createuser;
CREATE ROLE
template1=# alter user postgres with password '${PASSWORD}';
ALTER ROLE
template1=# \q
jazz@oceandb:~$ sudo vi /etc/apache2/conf.d/phppgadmin
jazz@oceandb:~$ sudo apache2ctl restart
}}}
{{{
#!diff
--- /etc/apache2/conf.d/phppgadmin
+++ /etc/apache2/conf.d/phppgadmin
@@ -9,7 +9,7 @@
 order deny,allow
 deny from all
-allow from 127.0.0.0/255.0.0.0 ::1/128
-# allow from all
+# allow from 127.0.0.0/255.0.0.0 ::1/128
+allow from all

 <IfModule mod_php5.c>
}}}
 * 把 PostgreSQL 資料庫 dump 出來
   * 先查詢 PostgreSQL 版本: 8.0.3
   * [參考] [http://www.postgresql.org/docs/7.3/static/app-pg-dumpall.html PostgreSQL 7.3.21 Documentation pg_dumpall] 
   * [參考] [http://twpug.net/docs/postgresql-doc-8.0-zh_TW/backup.html PostgreSQL 8.0.0 中文文件 Chapter 22. 備份和恢復]
{{{
# 只看到有安裝 PostgreSQL 的 JDBC 套件，卻沒看到 PostgreSQL Server 套件
[odb@ncor odb]$ rpm -qa | grep sql
libdbi-dbd-mysql-0.6.5-5
rh-postgresql-libs-7.3.9-2
mysql-3.23.58-16.RHEL3.1
php-mysql-4.3.2-23.ent
mysql-devel-3.23.58-16.RHEL3.1
mod_auth_mysql-20030510-2.ent
postgresql-jdbc-8.0.3-1PGDG
mysql-server-3.23.58-16.RHEL3.1
mysql-bench-3.23.58-16.RHEL3.1
# 從目前執行中的 process 知道安裝在 /usr/local/pgsql 底下
[odb@ncor odb]$ ps aux
postgres  2643  0.0  0.1 17864 1708 ?        S    Sep26   0:00 /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
# 從 /usr/local/pgsql/doc/index.html 可以得知版本為 8.0.3
[odb@ncor odb]$ sudo -u postgres /usr/local/pgsql/bin/pg_dumpall > ncor_pgsql.dumpall
[odb@ncor odb]$ du -sh ncor_pgsql.dumpall
3.5M    ncor_pgsql.dumpall
}}}
 * 用 scp 拷貝到實驗主機，並用 psql 指令把 dump 結果復原到實驗主機上
{{{
jazz@oceandb:~$ sudo -u postgres psql template1 < ncor_pgsql.dumpall
}}}

== 轉換 PostgreSQL 到 MySQL ==

 * [工具] [http://www.lightbox.ca/pg2mysql.php pg2mysql converter (PostgreSQL to MySQL Converter)]
 * [參考] [http://forums.mysql.com/read.php?83,128328,226468#msg-226468 遇到 triggers , sequences , domain 這幾個差異時就沒輒了]
 * 原始 account Table 表
{{{
--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: account; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE account (
    serial smallint DEFAULT nextval(('account_seq'::text)::regclass) NOT NULL,
    name character varying(10) NOT NULL,
    tel character varying(20) NOT NULL,
    fax character varying(20),
    mail character varying(20),
    department character varying(20) NOT NULL,
    unit character varying(20) NOT NULL,
    title character varying(20),
    admin boolean NOT NULL,
    account character varying(20) NOT NULL,
    password character varying(20) NOT NULL
);


ALTER TABLE public.account OWNER TO postgres;
}}}
   * 經 pg2mysql 轉出來的結果有錯，因為有用到 sequence (序列)
{{{
#!diff
--- account.sql.org     2008-10-15 23:23:54.000000000 +0800
+++ account.sql 2008-10-15 23:47:22.000000000 +0800
@@ -1,5 +1,5 @@
 CREATE TABLE account (
-    int(11) auto_increment smallint auto_increment NOT NULL,
+    serial int(11) auto_increment NOT NULL,
     name varchar(10) NOT NULL,
     tel varchar(20) NOT NULL,
     fax varchar(20),
@@ -10,5 +10,5 @@
     admin bool NOT NULL,
     account varchar(20) NOT NULL,
     password varchar(20) NOT NULL
-, PRIMARY KEY(`int(11)`)
-) TYPE=MyISAM;
+, PRIMARY KEY(`serial`)
+) TYPE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
}}}
{{{
#!diff
--- data_type.sql.org   2008-10-15 23:54:34.000000000 +0800
+++ data_type.sql       2008-10-15 23:55:03.000000000 +0800
@@ -1,8 +1,8 @@
 CREATE TABLE data_type (
-    int(11) auto_increment smallint NOT NULL,
+    serial smallint auto_increment NOT NULL,
     name varchar(30) NOT NULL,
     parent_id smallint NOT NULL
-, PRIMARY KEY(`int(11)`)
+, PRIMARY KEY(`serial`)
 ) TYPE=MyISAM;
}}}
   * [錯誤] Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
{{{
#!diff
--- maincase.sql.org    2008-10-15 23:58:18.000000000 +0800
+++ maincase.sql        2008-10-16 00:29:51.000000000 +0800
@@ -1,13 +1,13 @@
 CREATE TABLE maincase (
-    int(11) auto_increment int(11) auto_increment NOT NULL,
+    serial int(11) auto_increment NOT NULL,
     name varchar(60) NOT NULL,
     description varchar(255),
     storage_desc varchar(50),
     quality bool NOT NULL,
     handle_level smallint NOT NULL,
     handle_desc varchar(40),
-    location1 point NOT NULL,
-    location2 point,
+    location1 varchar(50) NOT NULL,
+    location2 varchar(50),
     can_supply bool NOT NULL,
     take_free bool NOT NULL,
     owner varchar(10),
@@ -15,13 +15,13 @@
     taker varchar(10),
     taker_org varchar(15),
     website text,
-    start_time timestamp NOT NULL,
-    end_time timestamp NOT NULL,
+    start_time timestamp NULL,
+    end_time timestamp NULL,
     user_id smallint NOT NULL,
     checked bool DEFAULT 0 NOT NULL,
-    insert_time timestamp DEFAULT CURRENT_TIMESTAMP,
+    insert_time timestamp NULL,
     cover bool DEFAULT 0 NOT NULL
-, PRIMARY KEY(`int(11)`)
+, PRIMARY KEY(`serial`)
 ) TYPE=MyISAM;

+SET NAMES 'utf8';
}}}
{{{
#!diff
--- maincase_detail.sql.org     2008-10-16 01:13:41.000000000 +0800
+++ maincase_detail.sql 2008-10-16 01:15:25.000000000 +0800
@@ -1,10 +1,12 @@
 CREATE TABLE maincase_detail (
-    int(11) auto_increment bigint auto_increment NOT NULL,
+    serial int(11) auto_increment NOT NULL,
     maincase_id int(11),
     type_id smallint
-, PRIMARY KEY(`int(11)`)
+, PRIMARY KEY(`serial`)
 ) TYPE=MyISAM;

+SET NAMES 'utf8';
}}}
{{{
#!diff
--- maincase_storage.sql.org    2008-10-16 01:21:07.000000000 +0800
+++ maincase_storage.sql        2008-10-16 01:22:12.000000000 +0800
@@ -1,10 +1,12 @@
 CREATE TABLE maincase_storage (
-    int(11) auto_increment int(11) auto_increment NOT NULL,
+    serial int(11) auto_increment NOT NULL,
     maincase_id int(11) NOT NULL,
     storage_type smallint NOT NULL
-, PRIMARY KEY(`int(11)`)
+, PRIMARY KEY(`serial`)
 ) TYPE=MyISAM;

+SET NAMES 'utf8';
}}}
   * 為了讓後續 INSERT 進去的中文字元維持 UTF-8, 必須在一開始的 SQL 語法加進 "SET NAMES 'utf8';"
{{{
jazz@oceandb:~$ head -n 2 maincase.sql
SET NAMES 'utf8';
INSERT INTO maincase VALUES (9744, 'SBES_OR1_236', '海研一號236航次沿測線 single beam EK500 38 kHz 水深資料', '', false, 1, '', '(119.53454000000001,21.88411)', '(120.40064,22.782060000000001)', true, true, 'NCOR', 'NCOR', 'NCOR', 'NCOR', 'http://www.ncor.ntu.edu.tw/ODBS/odbs_old/cruise/gif/OR1_236.gif', '1989-12-11 00:00:00', '1989-12-12 00:00:00', 1, true, '2005-10-12 14:18:28.929034', false);
jazz@oceandb:~$ mysql ncor -u oceandb -p < maincase.sql
}}}
 * [工具二] [http://sqlfairy.sourceforge.net/ SQL::Translator (aka SQLFairy)]
   * [參考] [http://forums.mysql.com/read.php?83,34517,70646#msg-70646 用 Perl 寫的 SQL::Translator (aka SQLFairy)]
   * [套件] [http://packages.debian.org/etch/sqlfairy sqlfairy] - SQL translation utilities

== 備忘 ==

 * [http://www.xspace.idv.tw/bo_blog/read.php?16 常用 rpm 與 dpkg 指令對照]