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;

  • [錯誤] #1293 - 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),
    @@ -19 +19 @@
         end_time timestamp NOT NULL,
         user_id smallint NOT NULL,
         checked bool DEFAULT 0 NOT NULL,
    -    insert_time timestamp DEFAULT CURRENT_TIMESTAMP,
    +    insert_time timestamp,
         cover bool DEFAULT 0 NOT NULL
    -, PRIMARY KEY(`int(11)`)
    +, PRIMARY KEY(`serial`)
     ) TYPE=MyISAM;

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

  jazz@oceandb:~$ 
  

備忘

• ​常用 rpm 與 dpkg 指令對照