資料庫-工作日誌: PostgreSQL

顯示具有 PostgreSQL 標籤的文章。顯示所有文章

2010年11月23日星期二

PostgreSQL-Pgdump && Pgdumpall

這次介紹Pgdump 與Pgdumpall 這兩個指令都是PostgreSQL的備份指令
相關的備份指令我就不多講了~~底下兩個教學文件有很詳細的介紹~~
Pgdump :http://twpug.net/docs/postgresql-doc-8.0-zh_TW/app-pgdump.html
Pgdumpall :http://twpug.net/docs/postgresql-doc-8.0-zh_TW/app-pg-dumpall.html

這次主要是講Pgdump 與 Pgdumpall差異!!!
顧名思義Pgdumpall是一次備份全資料庫~Pgdump 是一次備份單一資料庫~~
Pgdumpall可以把目前資料庫用戶和組，以及適用於整個資料庫的訪問權限備份出來(Pgdump 不行)!!
Pgdump 可以對"大對像"做備份(Pgdumpall不行)!!!
關於"大對像":
因為PostgreSQL 允許表的大小大於您的系統允許的最大文件大小，可能把表轉儲到一個文件會有問題，因為生成的文件很可能比您的系統允許的最大文件大。因為pg_dump 輸出到標準輸出，您可以用標準的Unix 工具繞開這個問題：
使用壓縮的轉儲. 使用您熟悉的壓縮程序，比如說gzip， split。

根據上面的官方說法:
我的PostgreSQL是安裝於FreeBSD上面~~查了一下他用的UFS filesystem ~~單一檔案最大SIZE已經超過TB了~~所以我不用使用Pgdump 的"大對像"功能!!!改成使用Pgdumpall ~~他又能備資料~~又能備份使用者加權限~~非常符合我需求!!!

2010年11月8日星期一

PostgreSQL 跨資料庫查詢!!!!

什麼是跨資料庫查詢呢~?!
例如:有A,B這兩個Databaes,
         隨便下一個Join SQL: select * from A.table inner join B.table on a.id=b.id;
這就是跨資料庫查詢了!我記得Oracle,SQL SERVER,MySQL都能支援這種跨資料庫查詢的功能!
可是當PostgreSQL 要做這總功能時會產生下列
Error Code:ERROR: cross-database references are not implemented
到GOOGLE查了一下原來PostgreSQL 不支援跨資料庫查詢~~如果要達到這種功能要用DBLINK~~~看起來挺麻煩的!!接下來來介紹怎麼去用PostgreSQL的DBLINK!!!

1.安裝postgresql-contrib
   #cd /root/postgresql-8.4.4/contrib/
   #make && make install
2.安裝dblink
   #cd /root/postgresql-8.4.4/contrib/dblink
   #psql -Upgsql -p6789 dbname -f dblink.sql
   (這裡的dbname 是指你要在哪個DB上安裝dblink.sql,如果全部的DB都要裝那應該要多裝幾次
   如果是剛新裝的DB那就裝在template1裡面~~之後開新DB時就可以用template1當模板~這樣     dblink就不用一直重覆裝了)
   裝完後會看到很多DDlink的Function!!

3.使用dblink
    SELECT *
    FROM dblink('host=IP port=6789 dbname=dbname user=user password=pw', 'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

   這樣就達成PostgreSQL的跨資料庫查詢了~~還真麻煩!!!
   感覺SQL很長看了很刺目~~如果想變短一點可以把他寫成View~~

參考資料:
http://www.postgresql.org/docs/current/static/contrib-dblink.html
http://zh-tw.w3support.net/index.php?db=so&id=46324
http://space.itpub.net/7351491/viewspace-615645

2010年10月14日星期四

PostgreSQL-連線校對編碼錯誤!!!

目前在Postgresql DB上有遇到使用pgAdmin1.10.5去連線資料庫編碼為EUC_TW時會發生資料無法讀取的問題!

Error code: character 0xbabf of encoding "EUC_TW" has no equivalent in "UTF8"

問題原因:因為UTF8與EUC_TW不相容所以才會導致此原因!!!

解決方法: 有兩個(建議用第二個方法)
1.直接在SERVER 端的postgresql.conf設定CLIENT_ENCODING= 'EUC_TW' ,缺點是導致pgAdmin1.10.5無法連線EUC_TW編碼的DB

2.每次連線時都執行一段SQL : SET CLIENT_ENCODING TO 'EUC_TW';缺點是每次開啟pgAdmin1.10.5都要執行這段SQL

PostgreSQL-如何在Freebsd底下使用pg_dump 定時對DB做備份

介紹pg_dump && pg_restore

pg_dump :
先說明幾個比較有用的參數!!!
參數-F p:輸出純SQL文件（預設）
參數-F t:輸出適合輸入到 pg_restore 裡的tar歸檔文件。
參數-F c:輸出適於給 pg_restore 用的客戶化歸檔。這是最靈活的格式，它允許對裝載的資料和對像定義進行重新排列。
參數-b:在轉儲中包含大對象。必須選擇一種非文本輸出格式。
參數-v:顯示一些dump 的log
參數-O:不把對象的所有權設置為對應源資料庫。
Step1: Create Shell Script
           底下我提供一個Script,此Script是針對DB使用的,如果要拿到別的地方用要改一下參數
           基本上改路經就OK了~~將此檔存成一個文件就能執行了(副檔名.sh為了方便識別)
============================================================================
#!/bin/sh
date=`date +%Y%m%d`
times=`date +%Y%m%d%H%M`
hostname="7s_DB"
# Location of the backup logfile.
logfile="/usr/home/cuser/backup/DUMP/$date/backup_logfile.log"
# Location to place backups.
backup_dir="/usr/home/cuser/backup/DUMP/$date"
mkdir -p $backup_dir
touch $logfile
databases=`/usr/local/bin/psql -h 127.0.0.1 -U pgsql -p 6789-q -d postgres -c "SELECT datname FROM pg_database;" | sed -n 5,/\eof/p | grep -v rows\)`
for i in $databases; do
        timeinfo=`date +%Y%m%d%H%M`
        echo "Backup and Vacuum complete at $timeinfo for time slot $times on database: $i " >> $logfile
        /usr/local/bin/vacuumdb -z -h 127.0.0.1 -p 6789 -U pgsql $i >/dev/null 2>&1
        /usr/local/bin/pg_dump -F c -b -v $i -U pgsql -h 127.0.0.1 -p 6789 > "$backup_dir/$hostname-$i-$times.sql"
done
echo "-----------------------------------------------------------------------------" >> $logfile
============================================================================

Step2: Create Crontab
           這邊是說明如何~ 建立備份排程!同樣是以七魂DB為例!
           設定每天凌晨1點去執行/usr/home/cuser/backup_dump.sh 這支SCRIPT============================================================================
#crontab -e(編輯crontab )

00 01 * * * /bin/sh /usr/home/cuser/backup_dump.sh > /dev/null &
#crontab -l(列出crontab )

============================================================================

pg_restore:
         這邊同樣以DB的restore來講!!
         如果今天我們要將備份檔還原到其他機器上的話!!可以參考以下範例!

EX: pg_restore -h 127.0.0.1 -p 6789 -U postgres -W -C -O -d test -v "/backup/20100902/7s_DB-TOOLDB-201009021106.sql"
參數-c及-C不能同時使用
參數-O:不要將原本的DB OWNER帶進來!!而是以執行pg_restore的USER變成該DB OWNER(上例:DB OWNER=postgres)
參數-d:如果不與-C同時使用,則是將資料復原到某個資料庫中(上例:復原到test)
          如果不與-C同時使用,則是將資料復原到某個資料庫中(上例:復原到TOOLDB)
參數-v:顯示一些restore 的log

PostgreSQL-Partition Table

1.Partition Table簡單介紹:
    是針對比較大型的TABLE做分區的功能,
優點:能使TABLE的查詢(Select)效能增加!!效能的增加通常是以倍數計算,
         如果能善用Partition Key能讓查詢SQL變很快(真的快到很有感覺)!!

缺點:會影響到異動(insert,update,delete)TABLE的效能!!此效能下降不會太大!!
         之前測試各家的Partition 功能差不多效能會下降10%左右!!

至於TABLE要多大才要做Partition呢??
通常我的判斷是只要TABLE大於本機實體記憶體的話就應該做Partition,
或者是一些很常用到查詢的TABLE(筆數很少跟常異動的就比較不適合囉!!)~~
所以資料庫在設計的一開始就應該把Partition Table這個功能考慮進去!!
避免之後要將Non-Partition Table改成Partition Table浪費的時間及停機的次數!!

2.如何建立PostgreSQL Partition Table!!!
PostgreSQL 是利用資料庫的CHECK 及RULE 來實現Partition 功能,相較其他幾家資料庫來看是比較麻煩一點的!
以下為建立PostgreSQL Partition Table的一些流程(以官方文件來說明)!!!

   2-0:設定postgresql.conf 設定檔
    把postgresql.conf 設定檔中的constraint exclusion參數打開，
這樣針對主資料表的查詢，會針對子資料表進行最佳化。
    (SET constraint_exclusion = on;)

   2-1:Create Master Table
CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
);

2-2:增加非重疊的表約束//以logdate 做為Select條件
CREATE TABLE measurement_yy04mm02 (
    CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);

CREATE TABLE measurement_yy04mm03 (
    CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);

2-3:設置一個非常簡單的規則來插入數據//以logdate 做為Insert條件
CREATE RULE measurement_insert_yy04mm02 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
DO INSTEAD
    INSERT INTO measurement_yy04mm02 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );
CREATE RULE measurement_insert_yy04mm03 AS
ON INSERT TO measurement WHERE
    ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
DO INSTEAD
    INSERT INTO measurement_yy04mm03 VALUES ( NEW.city_id,
                                              NEW.logdate,
                                              NEW.peaktemp,
                                              NEW.unitsales );

2-4:執行SQL,觀察SQL PLAN(以下舉幾個簡單的SQL來觀察)
insert into measurement values(1, DATE'2004-02-02',2,3) \\ insert measurement_insert_yy04mm02

insert into measurement values(1, DATE'2004-03-12',2,3) \\ insert measurement_insert_yy04mm03

insert into measurement values(1, DATE'2004-04-02',2,3)  \\ insert measurement

select * from measurement   \\ scan measurement && measurement_insert_yy04mm03 && measurement_insert_yy04mm02
select * from measurement
where logdate >= DATE '2004-02-01' AND logdate < DATE '2004-02-12' \\ scan measurement && measurement_insert_yy04mm02

select * from measurement
where logdate >= DATE '2004-03-05' AND logdate < DATE '2004-03-20' \\ scan measurement && measurement_insert_yy04mm03select * from measurement
where logdate >= DATE '2004-04-01' AND logdate < DATE '2004-05-20' \\ scan measurement

delete from measurement   \\ scan measurement && measurement_insert_yy04mm03 && measurement_insert_yy04mm02
delete from measurement
where logdate >= DATE '2004-02-01' AND logdate < DATE '2004-02-12' \\ scan measurement && measurement_insert_yy04mm02

delete from measurement
where logdate >= DATE '2004-03-05' AND logdate < DATE '2004-03-20' \\ scan measurement && measurement_insert_yy04mm03
delete from measurement
where logdate >= DATE '2004-04-01' AND logdate < DATE '2004-05-20' \\ scan measurement
update measurement set city_id=2   \\ scan measurement && measurement_insert_yy04mm03 && measurement_insert_yy04mm02
update measurement set city_id=2
where logdate >= DATE '2004-02-01' AND logdate < DATE '2004-02-12' \\ scan measurement && measurement_insert_yy04mm02

update measurement set city_id=2
where logdate >= DATE '2004-03-05' AND logdate < DATE '2004-03-20' \\ scan measurement && measurement_insert_yy04mm03
update measurement set city_id=2  where logdate >= DATE '2004-04-01' AND logdate < DATE '2004-05-20' \\ scan measurement
2-5:結論    要實現PostgreSQL Partition功能,跟其他資料庫比起來操作上有點麻煩(不好用),也不是非常SMART!!
但是基本上有達到Partition的精神!!

資料庫-工作日誌

2010年11月23日星期二

PostgreSQL-Pgdump && Pgdumpall

2010年11月8日星期一

PostgreSQL 跨資料庫查詢!!!!

2010年10月14日星期四

PostgreSQL-連線校對編碼錯誤!!!

PostgreSQL-如何在Freebsd底下使用pg_dump 定時對DB做備份

PostgreSQL-Partition Table

追蹤者

關於我

2010年11月23日 星期二

PostgreSQL-Pgdump && Pgdumpall

2010年11月8日 星期一

PostgreSQL 跨資料庫查詢!!!!

2010年10月14日 星期四

PostgreSQL-連線校對編碼錯誤!!!

PostgreSQL-如何在Freebsd底下使用pg_dump 定時對DB做備份

PostgreSQL-Partition Table

2010年11月23日星期二

2010年11月8日星期一

2010年10月14日星期四