2008-10-15

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

• 安裝 PostgreSQL 與 phpPGadmin
  • [參考] ​Debian Linux 架設 PostgreSQL + 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
    

    etc/apache2/conf.d/phppgadmin

    @@ -9 +9 @@
     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
  • [參考] ​PostgreSQL 7.3.21 Documentation pg_dumpall
  • [參考] ​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

• [工具] ​pg2mysql converter (PostgreSQL to MySQL Converter)
• [參考] ​遇到 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 (序列)

    account.sql

    @@ -1 +1 @@
     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 +10 @@
         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;

    data_type.sql

    @@ -1 +1 @@
     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

    maincase.sql

    @@ -1 +1 @@
     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 +15 @@
         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;

    maincase_detail.sql

    @@ -1 +1 @@
     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';

• 為了讓後續 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
  

• [工具二] ​SQL::Translator (aka SQLFairy)
• [參考] ​用 Perl 寫的 SQL::Translator (aka SQLFairy)

  jazz@oceandb:~$ 
  

備忘

• ​常用 rpm 與 dpkg 指令對照