= 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 這幾個差異時就沒輒了]
{{{
jazz@oceandb:~$ wget http://www.lightbox.ca/pg2mysql-1.2.tar.bz2
jazz@oceandb:~$ tar jxvf pg2mysql-1.2.tar.bz2
jazz@oceandb:~$ cd pg2mysql-1.2/
jazz@oceandb:~/pg2mysql-1.2$ sudo apt-get install php5-cli
jazz@oceandb:~/pg2mysql-1.2$ 
}}}
   * 轉出來的結果有錯，因為有用到 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;
}}}
 * [工具二] [http://sqlfairy.sourceforge.net/ SQL::Translator (aka SQLFairy)]
 * [參考] [http://forums.mysql.com/read.php?83,34517,70646#msg-70646 用 Perl 寫的 SQL::Translator (aka SQLFairy)]
{{{
jazz@oceandb:~$ 
}}}

== 備忘 ==

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