wiki:oid/WorkLog/08-10-15

2008-10-15

  1. 2008-10-15
    1. 備份聯合目錄 PostgreSQL 到實驗主機
    2. 轉換 PostgreSQL 到 MySQL
    3. 備忘

備份聯合目錄 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;
    • [錯誤] 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),
        66    quality bool NOT NULL,
        77    handle_level smallint NOT NULL,
        88    handle_desc varchar(40),
        9     location1 point NOT NULL,
        10     location2 point,
         9    location1 varchar(50) NOT NULL,
         10    location2 varchar(50),
        1111    can_supply bool NOT NULL,
        1212    take_free bool NOT NULL,
        1313    owner varchar(10),
        … …  
        1515    taker varchar(10),
        1616    taker_org varchar(15),
        1717    website text,
        18     start_time timestamp NOT NULL,
        19     end_time timestamp NOT NULL,
         18    start_time timestamp NULL,
         19    end_time timestamp 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 NULL,
        2323    cover bool DEFAULT 0 NOT NULL
        24 , PRIMARY KEY(`int(11)`)
         24, PRIMARY KEY(`serial`)
        2525) TYPE=MyISAM;
        2626
         27SET NAMES 'utf8';

      • maincase_detail.sql

        old new  
        11CREATE TABLE maincase_detail (
        2     int(11) auto_increment bigint auto_increment NOT NULL,
         2    serial int(11) auto_increment NOT NULL,
        33    maincase_id int(11),
        44    type_id smallint
        5 , PRIMARY KEY(`int(11)`)
         5, PRIMARY KEY(`serial`)
        66) TYPE=MyISAM;
        77
         8SET NAMES 'utf8';

      • maincase_storage.sql

        old new  
        11CREATE TABLE maincase_storage (
        2     int(11) auto_increment int(11) auto_increment NOT NULL,
         2    serial int(11) auto_increment NOT NULL,
        33    maincase_id int(11) NOT NULL,
        44    storage_type smallint NOT NULL
        5 , PRIMARY KEY(`int(11)`)
         5, PRIMARY KEY(`serial`)
        66) TYPE=MyISAM;
        77
         8SET 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)

備忘

Last modified 16 years ago Last modified on Oct 21, 2008, 4:47:14 PM