wiki:oid/WorkLog/08-10-15

Version 19 (modified by jazz, 16 years ago) (diff)

--

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

         
        99order deny,allow
        1010deny from all
        11 allow from 127.0.0.0/255.0.0.0 ::1/128
        12 # allow from all
         11# allow from 127.0.0.0/255.0.0.0 ::1/128
         12allow from all
        1313
        1414<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

        old new  
        11CREATE TABLE account (
        2     int(11) auto_increment smallint auto_increment NOT NULL,
         2    serial int(11) auto_increment NOT NULL,
        33    name varchar(10) NOT NULL,
        44    tel varchar(20) NOT NULL,
        55    fax varchar(20),
         
        1010    admin bool NOT NULL,
        1111    account varchar(20) NOT NULL,
        1212    password varchar(20) NOT NULL
        13 , PRIMARY KEY(`int(11)`)
        14 ) TYPE=MyISAM;
         13, PRIMARY KEY(`serial`)
         14) TYPE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
      • data_type.sql

        old new  
        11CREATE TABLE data_type (
        2     int(11) auto_increment smallint NOT NULL,
         2    serial smallint auto_increment NOT NULL,
        33    name varchar(30) NOT NULL,
        44    parent_id smallint NOT NULL
        5 , PRIMARY KEY(`int(11)`)
         5, PRIMARY KEY(`serial`)
        66) TYPE=MyISAM;
    • [錯誤] #1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
      • maincase.sql

        old new  
        11CREATE TABLE maincase (
        2     int(11) auto_increment int(11) auto_increment NOT NULL,
         2    serial int(11) auto_increment NOT NULL,
        33    name varchar(60) NOT NULL,
        44    description varchar(255),
        55    storage_desc varchar(50),
         
        1919    end_time timestamp NOT NULL,
        2020    user_id smallint NOT NULL,
        2121    checked bool DEFAULT 0 NOT NULL,
        22     insert_time timestamp DEFAULT CURRENT_TIMESTAMP,
         22    insert_time timestamp,
        2323    cover bool DEFAULT 0 NOT NULL
        24 , PRIMARY KEY(`int(11)`)
         24, PRIMARY KEY(`serial`)
        2525) TYPE=MyISAM;
  • [工具二] SQL::Translator (aka SQLFairy)
  • [參考] 用 Perl 寫的 SQL::Translator (aka SQLFairy)
    jazz@oceandb:~$ 
    

備忘